0

I'm saving some Forex data I'm getting for an API into an SQLite3 DB using Pandas to_sql() method but for some reason, it does not want to insert into the DB, I have tried using pure SQLite3 and I can get this to work. But as soon as I use SQLAlchemy as the engine I can't get it to insert. I'm using a Context Manager which I believe should handle the closing and commit of a connection but I still have no luck:

def insert(self, data: pd.DataFrame):
    engine = create_engine("sqlite:///{}".format("'C:\\Users\\Dirk Dirksen\\PycharmProjects\\FoxexBot\\forex.db'"))
    with engine.connect() as connection:
        connection.execute("CREATE TABLE IF NOT EXISTS {} "
                           "(pair TEXT NOT NULL, price FLOAT NOT NULL, time INTEGER NOT NULL);".format(self.index))
        data.to_sql(name="forex.db", con=connection, index=False, if_exists='append'

The connection.execute(SQL) is just to create the table if it is not there, the reall problem is with the MyDataframe.to_sql() this is what should populate my table. The error im getting is as follows:

"C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\python.exe" "C:/Users/Dirk Dirksen/PycharmProjects/FoxexBot/forex_server.py"
{'USDCAD': {'rate': 1.31535, 'timestamp': 1576978687}, 'USDJPY': {'rate': 109.44904, 'timestamp': 1576978687}, 'EURUSD': {'rate': 1.108, 'timestamp': 1576987745}, 'EURGBP': {'rate': 0.852013, 'timestamp': 1576987745}, 'NZDUSD': {'rate': 0.660004, 'timestamp': 1576987745}}
Traceback (most recent call last):
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 2276, in _wrap_pool_connect
    return fn()
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 303, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 760, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 492, in checkout
    rec = pool._do_get()
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\impl.py", line 238, in _do_get
    return self._create_connection()
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 308, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 437, in __init__
    self.__connect(first_connect_check=True)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 639, in __connect
    connection = pool._invoke_creator(self)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\default.py", line 482, in connect
    return self.dbapi.connect(*cargs, **cparams)
sqlite3.OperationalError: unable to open database file

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:/Users/Dirk Dirksen/PycharmProjects/FoxexBot/forex_server.py", line 70, in <module>
    fill_forex_data_to_db()
  File "C:/Users/Dirk Dirksen/PycharmProjects/FoxexBot/forex_server.py", line 62, in fill_forex_data_to_db
    db.insert(data=pairs)
  File "C:/Users/Dirk Dirksen/PycharmProjects/FoxexBot/forex_server.py", line 49, in insert
    with engine.connect() as connection:
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 2209, in connect
    return self._connection_cls(self, **kwargs)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 103, in __init__
    else engine.raw_connection()
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 2306, in raw_connection
    return self._wrap_pool_connect(
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 2279, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 1547, in _handle_dbapi_exception_noconnection
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\util\compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\util\compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 2276, in _wrap_pool_connect
    return fn()
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 303, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 760, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 492, in checkout
    rec = pool._do_get()
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\impl.py", line 238, in _do_get
    return self._create_connection()
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 308, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 437, in __init__
    self.__connect(first_connect_check=True)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 639, in __connect
    connection = pool._invoke_creator(self)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\default.py", line 482, in connect
    return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unable to open database file
(Background on this error at: http://sqlalche.me/e/e3q8)

It says it is unable to open the file but the only time I open the file is when I'm debugging it and it still gives me that error so I don't think its a locking issue. This leads me to believe it could be a problem with an incorrectly placed variable. At the moment my project layout looks like this:

enter image description here

My method to print out the contents of the table is below. This seems to work but is constantly printing an empty table:

def select_all(self):
    con = sqlite3.connect(database=self.db_path)
    return pd.read_sql_query("SELECT * FROM {}".format(self.index), con=con)

Where am I going wrong? is it the to_sql() method?

Improve this question
2
  • Why are there single quotes around the file's path? Try removing those, and see if it helps. Also, this seems like it's very close to your previous question, but with more info, so please consider editing the existing question instead of making 2 separate questions about the same issue. Commented Dec 22, 2019 at 7:58
  • Related: stackoverflow.com/questions/59433364/… Commented Dec 22, 2019 at 8:03

1 Answer 1

Reset to default
1

As already noted, the single quotes in the path should be removed.
Also, name in to_sql() should point to table name and not file name as per documentation https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html

Full code (removed self and used 'my_table' as the table name)

def insert(data: pd.DataFrame):
    engine = create_engine("sqlite:///{}".format("C:\\Users\\Dirk Dirksen\\PycharmProjects\\FoxexBot\\forex.db"))
    with engine.connect() as connection:
        connection.execute("CREATE TABLE IF NOT EXISTS {} "
                           "(pair TEXT NOT NULL, price FLOAT NOT NULL, time INTEGER NOT NULL);".format('my_table'))
        data.to_sql(name='my_table', con=connection, index=False, if_exists='append')

d = pd.DataFrame({
    'pair': ['abc'],
    'price': [1.2],
    'time': [123],
})

insert(d)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.