Sqlite error during IBKR minute database collection

bjsun · January 4, 2022, 6:06am

Hi Brian,

I've been getting this error almost every day for the last few weeks and am unable to complete the daily collection of my IBKR minute-level history database.

2022-01-03 19:25:48 quantrocket.history: INFO [ibkr-minute-db] Expected remaining runtime to collect ibkr-minute-db history based on IBKR response times so far: 5:10:56 (15830 more requests for 7836 securities @ 4.71s/request across 4 gateways)
2022-01-03 19:30:25 quantrocket.history: ERROR Traceback (most recent call last):
2022-01-03 19:30:25 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1245, in _execute_context
2022-01-03 19:30:25 quantrocket.history: ERROR     self.dialect.do_execute(
2022-01-03 19:30:25 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 588, in do_execute
2022-01-03 19:30:25 quantrocket.history: ERROR     cursor.execute(statement, parameters)
2022-01-03 19:30:25 quantrocket.history: ERROR sqlite3.OperationalError: no such table: Price
2022-01-03 19:30:25 quantrocket.history: ERROR 
2022-01-03 19:30:25 quantrocket.history: ERROR The above exception was the direct cause of the following exception:
2022-01-03 19:30:25 quantrocket.history: ERROR 
2022-01-03 19:30:25 quantrocket.history: ERROR Traceback (most recent call last):
2022-01-03 19:30:25 quantrocket.history: ERROR   File "sym://qrocket_history_collect_ibkr_workers_py", line 28, in wrapped
2022-01-03 19:30:25 quantrocket.history: ERROR   File "sym://qrocket_history_collect_ibkr_storage_py", line 57, in run
2022-01-03 19:30:25 quantrocket.history: ERROR   File "sym://qrocket_history_collect_ibkr_storage_py", line 131, in _handle_adjustments_detected
2022-01-03 19:30:25 quantrocket.history: ERROR   File "sym://qrocket_history_collect_backends_base_py", line 43, in delete_by_sid
2022-01-03 19:30:25 quantrocket.history: ERROR   File "sym://qrocket_history_collect_backends_shard_py", line 132, in execute
2022-01-03 19:30:25 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2182, in execute
2022-01-03 19:30:25 quantrocket.history: ERROR     return connection.execute(statement, *multiparams, **params)
2022-01-03 19:30:25 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 976, in execute
2022-01-03 19:30:25 quantrocket.history: ERROR     return self._execute_text(object_, multiparams, params)
2022-01-03 19:30:25 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1143, in _execute_text
2022-01-03 19:30:25 quantrocket.history: ERROR     ret = self._execute_context(
2022-01-03 19:30:25 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1249, in _execute_context
2022-01-03 19:30:25 quantrocket.history: ERROR     self._handle_dbapi_exception(
2022-01-03 19:30:25 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception
2022-01-03 19:30:25 quantrocket.history: ERROR     util.raise_from_cause(sqlalchemy_exception, exc_info)
2022-01-03 19:30:25 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
2022-01-03 19:30:25 quantrocket.history: ERROR     reraise(type(exception), exception, tb=exc_tb, cause=cause)
2022-01-03 19:30:25 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
2022-01-03 19:30:25 quantrocket.history: ERROR     raise value.with_traceback(tb)
2022-01-03 19:30:25 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1245, in _execute_context
2022-01-03 19:30:25 quantrocket.history: ERROR     self.dialect.do_execute(
2022-01-03 19:30:25 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 588, in do_execute
2022-01-03 19:30:25 quantrocket.history: ERROR     cursor.execute(statement, parameters)
2022-01-03 19:30:25 quantrocket.history: ERROR sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: Price
2022-01-03 19:30:25 quantrocket.history: ERROR [SQL: 
2022-01-03 19:30:25 quantrocket.history: ERROR             DELETE
2022-01-03 19:30:25 quantrocket.history: ERROR             FROM Price
2022-01-03 19:30:25 quantrocket.history: ERROR             WHERE Sid IN (?)
2022-01-03 19:30:25 quantrocket.history: ERROR             ]
2022-01-03 19:30:25 quantrocket.history: ERROR [parameters: ('FIBBG000Q0BPZ4',)]
2022-01-03 19:30:25 quantrocket.history: ERROR (Background on this error at: http://sqlalche.me/e/e3q8)
2022-01-03 19:30:25 quantrocket.history: ERROR 
2022-01-03 19:30:28 quantrocket.history: INFO [ibkr-minute-db] Exiting due to errors

The SID referenced in the fourth line from the bottom is different each time I restart the collection. There is something about "_handle_adjustments_detected" in the traceback, so perhaps this happens only with SIDs that require adjustments on a particular day?

I can't give you SIDs to reproduce the error with, because they change with every attempt to collect.

I've gone into the sqlite file of the referenced SIDs to check for the Price table, and it does exist. For example:

jupyter:/var/lib/quantrocket/quantrocket.v2.history.ibkr-minute-db.sqlite/sid $ sqlite3 quantrocket.v2.history.ibkr-minute-db.FIBBG000Q0BPZ4.sqlite
SQLite version 3.34.1 2021-01-20 14:10:07
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE Price (

    Sid VARCHAR(20) NOT NULL,
    Date DATETIME PRIMARY KEY NOT NULL, -- Date alone is PK, since only 1 Sid per shard
    Open DOUBLE DEFAULT NULL,
    High DOUBLE DEFAULT NULL,
    Low DOUBLE DEFAULT NULL,
    Close DOUBLE DEFAULT NULL,
    Volume INT DEFAULT NULL,
    Wap DOUBLE DEFAULT NULL,
    TradeCount INT DEFAULT NULL,
    DayHigh DOUBLE DEFAULT NULL,
    DayLow DOUBLE DEFAULT NULL,
    DayVolume INT DEFAULT NULL
);
CREATE INDEX PriceTimeDateIndex ON Price(SUBSTR("Date",12,8),Date);
sqlite> .tables
Price
sqlite> SELECT * FROM Price;
sqlite>

Any thoughts on how to fix? I cannot reliably complete history IBKR collection and don't know how to debug further, since I can't access the code behind collect_history().

charles · January 4, 2022, 12:38pm

I literally just starting seeing a similar error for the first time this morning while trying to collect the daily bundle via the command below. Note that in my case, it looks like a problem with having an empty history database/shard for 2022 that was auto created; there's no tables inside this database. @Brian is this something that should happen automatically, or do we need to manually provision the new year's table?

quantrocket history collect 'usstock-daily'

Here's the error output:

2022-01-04 07:35:47 quantrocket.history: INFO [usstock-daily] Collecting US history from 2022-01 to present
2022-01-04 07:35:47 quantrocket.history: INFO [usstock-daily] Collecting updated US securities listings
2022-01-04 07:35:47 quantrocket.history: INFO [usstock-daily] Applying price adjustments for 92 securities
2022-01-04 07:35:47 quantrocket.history: ERROR Traceback (most recent call last):
2022-01-04 07:35:47 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1245, in _execute_context
2022-01-04 07:35:47 quantrocket.history: ERROR     self.dialect.do_execute(
2022-01-04 07:35:47 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 588, in do_execute
2022-01-04 07:35:47 quantrocket.history: ERROR     cursor.execute(statement, parameters)
2022-01-04 07:35:47 quantrocket.history: ERROR sqlite3.OperationalError: no such table: Price
2022-01-04 07:35:47 quantrocket.history: ERROR 
2022-01-04 07:35:47 quantrocket.history: ERROR The above exception was the direct cause of the following exception:
2022-01-04 07:35:47 quantrocket.history: ERROR 
2022-01-04 07:35:47 quantrocket.history: ERROR Traceback (most recent call last):
2022-01-04 07:35:47 quantrocket.history: ERROR   File "sym://qrocket_log_py", line 34, in wrapped
2022-01-04 07:35:47 quantrocket.history: ERROR   File "sym://qrocket_mule_py", line 150, in mule_collect_other_vendor
2022-01-04 07:35:47 quantrocket.history: ERROR   File "sym://qrocket_history_collect_usstock_collect_py", line 96, in collect
2022-01-04 07:35:47 quantrocket.history: ERROR   File "sym://qrocket_history_collect_usstock_adjust_py", line 50, in adjust
2022-01-04 07:35:47 quantrocket.history: ERROR   File "sym://qrocket_history_collect_usstock_adjust_py", line 169, in _adjust_sid
2022-01-04 07:35:47 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 976, in execute
2022-01-04 07:35:47 quantrocket.history: ERROR     return self._execute_text(object_, multiparams, params)
2022-01-04 07:35:47 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1143, in _execute_text
2022-01-04 07:35:47 quantrocket.history: ERROR     ret = self._execute_context(
2022-01-04 07:35:47 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1249, in _execute_context
2022-01-04 07:35:47 quantrocket.history: ERROR     self._handle_dbapi_exception(
2022-01-04 07:35:47 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception
2022-01-04 07:35:47 quantrocket.history: ERROR     util.raise_from_cause(sqlalchemy_exception, exc_info)
2022-01-04 07:35:47 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
2022-01-04 07:35:47 quantrocket.history: ERROR     reraise(type(exception), exception, tb=exc_tb, cause=cause)
2022-01-04 07:35:47 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
2022-01-04 07:35:47 quantrocket.history: ERROR     raise value.with_traceback(tb)
2022-01-04 07:35:47 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1245, in _execute_context
2022-01-04 07:35:47 quantrocket.history: ERROR     self.dialect.do_execute(
2022-01-04 07:35:47 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 588, in do_execute
2022-01-04 07:35:47 quantrocket.history: ERROR     cursor.execute(statement, parameters)
2022-01-04 07:35:47 quantrocket.history: ERROR sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: Price
2022-01-04 07:35:47 quantrocket.history: ERROR [SQL: 
2022-01-04 07:35:47 quantrocket.history: ERROR             UPDATE Price
2022-01-04 07:35:47 quantrocket.history: ERROR             SET
2022-01-04 07:35:47 quantrocket.history: ERROR                 Open = UnadjOpen,
2022-01-04 07:35:47 quantrocket.history: ERROR                 High = UnadjHigh,
2022-01-04 07:35:47 quantrocket.history: ERROR                 Low = UnadjLow,
2022-01-04 07:35:47 quantrocket.history: ERROR                 Close = UnadjClose,
2022-01-04 07:35:47 quantrocket.history: ERROR                 Volume = UnadjVolume,
2022-01-04 07:35:47 quantrocket.history: ERROR                 Vwap = UnadjVwap
2022-01-04 07:35:47 quantrocket.history: ERROR             WHERE Sid = ?
2022-01-04 07:35:47 quantrocket.history: ERROR             ]
2022-01-04 07:35:47 quantrocket.history: ERROR [parameters: ('FIBBG000B9X6L4',)]
2022-01-04 07:35:47 quantrocket.history: ERROR (Background on this error at: http://sqlalche.me/e/e3q8)
2022-01-04 07:35:47 quantrocket.history: ERROR

charles · January 4, 2022, 1:19pm

Hey @bjsun, not sure if this will help you, but I found that by deleting/dropping the troubled history database, and recollecting it, I was able to overcome this issue. Looks like you're using a history database with IBKR as a data provider though, so it might be painful to recollect if you have a large database. Fortunately the usstock daily database downloads super fast; was back up an running within 5min.

@Brian would still be interested in understanding what went wrong here; is it just an issue with turning over to a new year? My usstock-daily database is re-collected every weekday morning, and I figured it would just add a new yearly shard for 2022, but maybe not?

bjsun · January 4, 2022, 5:05pm

Interesting that you're getting a similar error with a different history database - suggests the issue is with the local ingestion and not the data provider.

I also ingest the daily bundle every morning, but the error only happens with my IBKR db.

For me, it's been happening throughout Dec, so probably not a new year's thing.

Yes, it's a huge pain (~ a week of download time) to recollect the entire IBKR database. I've tried deleting the individual sqlite files for the SIDs that appear in the exception, but the SIDs are different each time and seem to have something to do with the type of adjustments needed on that specific day.

Brian · January 5, 2022, 1:30pm

This error would happen when trying to access a sqlite database that doesn't yet exist, as sqlite will automatically create a database in that case but it won't yet have any tables in it.

Does it happen if you collect through 1 ibg service instead of 4?

bjsun · January 6, 2022, 12:43am

I can try collecting through 1 ibg service, but I won't get through a full collection for days. Is there a patch fix possible where the exception is handled by creating the Price table?

Alternatively, would it work if I looped through all the sids that are to be collected and made sure the the sqlite file and Price table exist before starting collection? Or would this be pointless b/c the database file gets deleted/re-created by the collection process itself (for adjustments, etc)?

Brian · January 6, 2022, 1:15pm

Database files are only created once, so ensuring the databases are there should work.

bjsun · January 17, 2022, 11:01pm

I tried to fix this over the weekend and still got the same error. I.e., I made sure the sqlite3 file with Price table existed for every sid in the universe to be collected before start collection.

Is there any other reason that the collection process would not recognize the Price table?

2022-01-17 00:51:40 quantrocket.history: ERROR Traceback (most recent call last):
2022-01-17 00:51:40 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1245, in _execute_context
2022-01-17 00:51:40 quantrocket.history: ERROR     self.dialect.do_execute(
2022-01-17 00:51:40 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 588, in do_execute
2022-01-17 00:51:40 quantrocket.history: ERROR     cursor.execute(statement, parameters)
2022-01-17 00:51:40 quantrocket.history: ERROR sqlite3.OperationalError: no such table: Price
2022-01-17 00:51:40 quantrocket.history: ERROR 
2022-01-17 00:51:40 quantrocket.history: ERROR The above exception was the direct cause of the following exception:
2022-01-17 00:51:40 quantrocket.history: ERROR 
2022-01-17 00:51:40 quantrocket.history: ERROR Traceback (most recent call last):
2022-01-17 00:51:40 quantrocket.history: ERROR   File "sym://qrocket_history_collect_ibkr_workers_py", line 28, in wrapped
2022-01-17 00:51:40 quantrocket.history: ERROR   File "sym://qrocket_history_collect_ibkr_storage_py", line 57, in run
2022-01-17 00:51:40 quantrocket.history: ERROR   File "sym://qrocket_history_collect_ibkr_storage_py", line 131, in _handle_adjustments_detected
2022-01-17 00:51:40 quantrocket.history: ERROR   File "sym://qrocket_history_collect_backends_base_py", line 43, in delete_by_sid
2022-01-17 00:51:40 quantrocket.history: ERROR   File "sym://qrocket_history_collect_backends_shard_py", line 132, in execute
2022-01-17 00:51:40 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2182, in execute
2022-01-17 00:51:40 quantrocket.history: ERROR     return connection.execute(statement, *multiparams, **params)
2022-01-17 00:51:40 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 976, in execute
2022-01-17 00:51:40 quantrocket.history: ERROR     return self._execute_text(object_, multiparams, params)
2022-01-17 00:51:40 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1143, in _execute_text
2022-01-17 00:51:40 quantrocket.history: ERROR     ret = self._execute_context(
2022-01-17 00:51:40 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1249, in _execute_context
2022-01-17 00:51:40 quantrocket.history: ERROR     self._handle_dbapi_exception(
2022-01-17 00:51:40 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception
2022-01-17 00:51:40 quantrocket.history: ERROR     util.raise_from_cause(sqlalchemy_exception, exc_info)
2022-01-17 00:51:40 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
2022-01-17 00:51:40 quantrocket.history: ERROR     reraise(type(exception), exception, tb=exc_tb, cause=cause)
2022-01-17 00:51:40 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
2022-01-17 00:51:40 quantrocket.history: ERROR     raise value.with_traceback(tb)
2022-01-17 00:51:40 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1245, in _execute_context
2022-01-17 00:51:40 quantrocket.history: ERROR     self.dialect.do_execute(
2022-01-17 00:51:40 quantrocket.history: ERROR   File "/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 588, in do_execute
2022-01-17 00:51:40 quantrocket.history: ERROR     cursor.execute(statement, parameters)
2022-01-17 00:51:40 quantrocket.history: ERROR sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: Price
2022-01-17 00:51:40 quantrocket.history: ERROR [SQL: 
2022-01-17 00:51:40 quantrocket.history: ERROR             DELETE
2022-01-17 00:51:40 quantrocket.history: ERROR             FROM Price
2022-01-17 00:51:40 quantrocket.history: ERROR             WHERE Sid IN (?)
2022-01-17 00:51:40 quantrocket.history: ERROR             ]
2022-01-17 00:51:40 quantrocket.history: ERROR [parameters: ('FIBBG000QXHGG7',)]
2022-01-17 00:51:40 quantrocket.history: ERROR (Background on this error at: http://sqlalche.me/e/e3q8)
2022-01-17 00:51:40 quantrocket.history: ERROR

Brian · January 28, 2022, 1:42pm

Unfortunately I don't have any insight on what might be happening here.