Hi,
I'm updating a MySQL (MariaDB) database using Pandas and sqlalchemy. I am
seeing it hang on a ROLLBACK but I'm not sure how to determine what is
causing the ROLLBACK. Below is a session with DEBUG enabled. Any help would
be appreciated.
The engine is created with:
engine =
create_engine('mariadb+mariadbconnector://user:[email protected]/options')
The original data collected is returned as a Python dictionary, and I
convert each of the rows of calls/puts by expiration date to a Pandas data
frame and use to_sql() to update the database.
Thank-you,
Rob
-----------
20210511.10:10:22 stock_option_updates.py:101 [DEBUG] get_db_stocks: Entered
DEBUG:Stock/Option Updates:get_db_stocks: Entered
INFO:sqlalchemy.engine.Engine:SHOW VARIABLES LIKE 'sql_mode'
INFO:sqlalchemy.engine.Engine:[raw sql] ()
DEBUG:sqlalchemy.engine.Engine:Col ('Variable_name', 'Value')
DEBUG:sqlalchemy.engine.Engine:Row ('sql_mode',
'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION')
INFO:sqlalchemy.engine.Engine:SHOW VARIABLES LIKE 'lower_case_table_names'
INFO:sqlalchemy.engine.Engine:[generated in 0.00016s] ()
DEBUG:sqlalchemy.engine.Engine:Col ('Variable_name', 'Value')
DEBUG:sqlalchemy.engine.Engine:Row ('lower_case_table_names', '0')
INFO:sqlalchemy.engine.Engine:SELECT DATABASE()
INFO:sqlalchemy.engine.Engine:[raw sql] ()
DEBUG:sqlalchemy.engine.Engine:Col ('DATABASE()',)
DEBUG:sqlalchemy.engine.Engine:Row ('options',)
INFO:sqlalchemy.engine.Engine:SHOW DATABASES
INFO:sqlalchemy.engine.Engine:[raw sql] ()
DEBUG:sqlalchemy.engine.Engine:Col ('Database',)
DEBUG:sqlalchemy.engine.Engine:Row ('information_schema',)
DEBUG:sqlalchemy.engine.Engine:Row ('mysql',)
DEBUG:sqlalchemy.engine.Engine:Row ('options',)
DEBUG:sqlalchemy.engine.Engine:Row ('performance_schema',)
DEBUG:sqlalchemy.engine.Engine:Row ('stocks',)
INFO:sqlalchemy.engine.Engine:DROP DATABASE options
INFO:sqlalchemy.engine.Engine:[raw sql] ()
INFO:sqlalchemy.engine.Engine:COMMIT
INFO:sqlalchemy.engine.Engine:CREATE DATABASE options
INFO:sqlalchemy.engine.Engine:[raw sql] ()
INFO:sqlalchemy.engine.Engine:COMMIT
Expiration dates for AAPL: [1620345600, 1620950400, 1621555200, 1622160000,
1622764800, 1623369600, 1623974400, 1626393600, 1629417600, 1631836800,
1634256000, 1642723200, 1655424000, 1663286400, 1674172800, 1679011200,
1686873600]
For ticker AAPL and expires on 2021-05-07 00:00:00: calls: 60 puts: 59
Starting calls (0) for AAPL number: 60
Converted to dataframe
INFO:sqlalchemy.engine.Engine:SELECT * FROM information_schema.tables WHERE
table_schema = ? AND table_name = ?
INFO:sqlalchemy.engine.Engine:[generated in 0.00027s] ('options', 'AAPL')
DEBUG:sqlalchemy.engine.Engine:Col ('TABLE_CATALOG', 'TABLE_SCHEMA',
'TABLE_NAME', 'TABLE_TYPE', 'ENGINE', 'VERSION', 'ROW_FORMAT',
'TABLE_ROWS', 'AVG_ROW_LENGTH', 'DATA_LENGTH', 'MAX_DATA_LENGTH',
'INDEX_LENGTH', 'DATA_FREE', 'AUTO_INCREMENT', 'CREATE_TIME',
'UPDATE_TIME', 'CHECK_TIME', 'TABLE_COLLATION', 'CHECKSUM',
'CREATE_OPTIONS', 'TABLE_COMMENT', 'MAX_INDEX_LENGTH', 'TEMPORARY')
INFO:sqlalchemy.engine.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.Engine:
CREATE TABLE `AAPL` (
`index` BIGINT,
`contractSymbol` TEXT,
`lastTradeDate` BIGINT,
strike FLOAT(53),
`lastPrice` FLOAT(53),
bid FLOAT(53),
ask FLOAT(53),
`change` FLOAT(53),
`percentChange` FLOAT(53),
volume BIGINT,
`openInterest` BIGINT,
`impliedVolatility` FLOAT(53),
`inTheMoney` BOOL,
`contractSize` TEXT,
currency TEXT,
`Type` TEXT,
`Date` DATETIME,
`Expiration` DATETIME
)
INFO:sqlalchemy.engine.Engine:[no key 0.00026s] ()
INFO:sqlalchemy.engine.Engine:CREATE INDEX `ix_AAPL_index` ON `AAPL`
(`index`)
INFO:sqlalchemy.engine.Engine:[no key 0.00018s] ()
INFO:sqlalchemy.engine.Engine:COMMIT
INFO:sqlalchemy.engine.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.Engine:INSERT INTO `AAPL` (`index`,
`contractSymbol`, `lastTradeDate`, strike, `lastPrice`, bid, ask, `change`,
`percentChange`, volume, `openInterest`, `impliedVolatility`, `inTheMoney`,
`contractSize`, currency, `Type`, `Date`, `Expiration`) VALUES (?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
INFO:sqlalchemy.engine.Engine:[generated in 0.00113s] ((0,
'AAPL210507C00065000', 1619713738, 65.0, 62.45, 62.55, 63.85, -5.299999,
-7.822877, 1, 1, 3.917968955078125, 1, 'REGULAR', 'USD', 'calls',
datetime.datetime(2021, 5, 4, 0, 0), datetime.datetime(2021, 5, 7, 0, 0)),
(1, 'AAPL210507C00080000', 1619790996, 80.0, 52.55, 47.55, 49.0, 0.0, 0.0,
1, 29, 2.9414088964843748, 1, 'REGULAR', 'USD', 'calls',
datetime.datetime(2021, 5, 4, 0, 0), datetime.datetime(2021, 5, 7, 0, 0)),
(2, 'AAPL210507C00085000', 1619715379, 85.0, 48.13, 41.9, 43.95, 0.0, 0.0,
1, 4, 1.984375078125, 1, 'REGULAR', 'USD', 'calls', datetime.datetime(2021,
5, 4, 0, 0), datetime.datetime(2021, 5, 7, 0, 0)), (3,
'AAPL210507C00090000', 1619812194, 90.0, 37.6, 37.85, 38.1, -3.8500023,
-9.288304, 108, 184, 1.8671881640625, 1, 'REGULAR', 'USD', 'calls',
datetime.datetime(2021, 5, 4, 0, 0), datetime.datetime(2021, 5, 7, 0, 0)),
(4, 'AAPL210507C00095000', 1620149743, 95.0, 32.2, 32.85, 33.1, -5.7199974,
-15.084381, 4, 7, 1.61328318359375, 1, 'REGULAR', 'USD', 'calls',
datetime.datetime(2021, 5, 4, 0, 0), datetime.datetime(2021, 5, 7, 0, 0)),
(5, 'AAPL210507C00100000', 1620153536, 100.0, 27.35, 27.85, 28.05,
-6.6499996, -19.558823, 59, 122, 1.3203158984374999, 1, 'REGULAR', 'USD',
'calls', datetime.datetime(2021, 5, 4, 0, 0), datetime.datetime(2021, 5, 7,
0, 0)), (6, 'AAPL210507C00105000', 1620149743, 105.0, 22.1, 22.85, 23.1,
-5.3999996, -19.636362, 9, 71, 1.1328168359375002, 1, 'REGULAR', 'USD',
'calls', datetime.datetime(2021, 5, 4, 0, 0), datetime.datetime(2021, 5, 7,
0, 0)), (7, 'AAPL210507C00106000', 1619795066, 106.0, 27.35, 21.85, 22.1,
0.0, 0.0, 1, 2, 1.0859420703125, 1, 'REGULAR', 'USD', 'calls',
datetime.datetime(2021, 5, 4, 0, 0), datetime.datetime(2021, 5, 7, 0, 0))
... displaying 10 of 60 total bound parameter sets ... (58,
'AAPL210507C00170000', 1619799667, 170.0, 0.01, 0.0, 0.01, 0.0, 0.0, 1,
1059, 1.0312548437500002, 0, 'REGULAR', 'USD', 'calls',
datetime.datetime(2021, 5, 4, 0, 0), datetime.datetime(2021, 5, 7, 0, 0)),
(59, 'AAPL210507C00175000', 1619703345, 175.0, 0.01, 0.0, 0.01, 0.0, 0.0,
48, 138, 1.125004375, 0, 'REGULAR', 'USD', 'calls', datetime.datetime(2021,
5, 4, 0, 0), datetime.datetime(2021, 5, 7, 0, 0)))
INFO:sqlalchemy.engine.Engine:COMMIT
INFO:sqlalchemy.engine.Engine:SHOW FULL TABLES FROM `options`
INFO:sqlalchemy.engine.Engine:[raw sql] ()
DEBUG:sqlalchemy.engine.Engine:Col ('Tables_in_options', 'Table_type')
DEBUG:sqlalchemy.engine.Engine:Row ('AAPL', 'BASE TABLE')
Starting puts (0) for AAPL number: 59
Converted to dataframe
INFO:sqlalchemy.engine.Engine:SELECT * FROM information_schema.tables WHERE
table_schema = ? AND table_name = ?
INFO:sqlalchemy.engine.Engine:[cached since 5.071s ago] ('options', 'AAPL')
DEBUG:sqlalchemy.engine.Engine:Col ('TABLE_CATALOG', 'TABLE_SCHEMA',
'TABLE_NAME', 'TABLE_TYPE', 'ENGINE', 'VERSION', 'ROW_FORMAT',
'TABLE_ROWS', 'AVG_ROW_LENGTH', 'DATA_LENGTH', 'MAX_DATA_LENGTH',
'INDEX_LENGTH', 'DATA_FREE', 'AUTO_INCREMENT', 'CREATE_TIME',
'UPDATE_TIME', 'CHECK_TIME', 'TABLE_COLLATION', 'CHECKSUM',
'CREATE_OPTIONS', 'TABLE_COMMENT', 'MAX_INDEX_LENGTH', 'TEMPORARY')
DEBUG:sqlalchemy.engine.Engine:Row ('def', 'options', 'AAPL', 'BASE TABLE',
'InnoDB', 10, 'Dynamic', 60, 273, 16384, 0, 16384, 0, None,
datetime.datetime(2021, 5, 11, 10, 10, 23), datetime.datetime(2021, 5, 11,
10, 10, 23), None, 'utf8mb4_general_ci', None, '', '', 0, 'N')
INFO:sqlalchemy.engine.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.Engine:INSERT INTO `AAPL` (`index`,
`contractSymbol`, `lastTradeDate`, strike, `lastPrice`, bid, ask, `change`,
`percentChange`, volume, `openInterest`, `impliedVolatility`, `inTheMoney`,
`contractSize`, currency, `Type`, `Date`, `Expiration`) VALUES (?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
INFO:sqlalchemy.engine.Engine:[generated in 0.00097s] ((0,
'AAPL210507P00065000', 1619792652, 65.0, 0.01, 0.0, 0.01, 0.0, 0.0, 1, 6,
2.3750040624999995, 0, 'REGULAR', 'USD', 'puts', datetime.datetime(2021, 5,
4, 0, 0), datetime.datetime(2021, 5, 7, 0, 0)), (1, 'AAPL210507P00070000',
1619789412, 70.0, 0.01, 0.0, 0.01, 0.0, 0.0, 1, 306, 2.1250046874999997, 0,
'REGULAR', 'USD', 'puts', datetime.datetime(2021, 5, 4, 0, 0),
datetime.datetime(2021, 5, 7, 0, 0)), (2, 'AAPL210507P00075000',
1619704218, 75.0, 0.01, 0.0, 0.01, 0.0, 0.0, 1, 16, 1.9375003125, 0,
'REGULAR', 'USD', 'puts', datetime.datetime(2021, 5, 4, 0, 0),
datetime.datetime(2021, 5, 7, 0, 0)), (3, 'AAPL210507P00080000',
1618428171, 80.0, 0.04, 0.0, 0.01, 0.0, 0.0, 3, 71, 1.6875015625, 0,
'REGULAR', 'USD', 'puts', datetime.datetime(2021, 5, 4, 0, 0),
datetime.datetime(2021, 5, 7, 0, 0)), (4, 'AAPL210507P00085000',
1619449132, 85.0, 0.01, 0.0, 0.01, 0.0, 0.0, 151, 1458, 1.5000025, 0,
'REGULAR', 'USD', 'puts', datetime.datetime(2021, 5, 4, 0, 0),
datetime.datetime(2021, 5, 7, 0, 0)), (5, 'AAPL210507P00090000',
1620156971, 90.0, 0.01, 0.0, 0.01, 0.0, 0.0, 1, 1370, 1.3125034374999998,
0, 'REGULAR', 'USD', 'puts', datetime.datetime(2021, 5, 4, 0, 0),
datetime.datetime(2021, 5, 7, 0, 0)), (6, 'AAPL210507P00095000',
1619811807, 95.0, 0.01, 0.0, 0.01, 0.0, 0.0, 1, 618, 1.125004375, 0,
'REGULAR', 'USD', 'puts', datetime.datetime(2021, 5, 4, 0, 0),
datetime.datetime(2021, 5, 7, 0, 0)), (7, 'AAPL210507P00100000',
1620157425, 100.0, 0.02, 0.0, 0.01, 0.01, 100.0, 131, 1545, 0.937500625, 0,
'REGULAR', 'USD', 'puts', datetime.datetime(2021, 5, 4, 0, 0),
datetime.datetime(2021, 5, 7, 0, 0)) ... displaying 10 of 59 total bound
parameter sets ... (57, 'AAPL210507P00170000', 1620057879, 170.0, 37.35,
41.85, 42.7, 0.0, 0.0, 2, 8, 1.4882838085937498, 1, 'REGULAR', 'USD',
'puts', datetime.datetime(2021, 5, 4, 0, 0), datetime.datetime(2021, 5, 7,
0, 0)), (58, 'AAPL210507P00175000', 1619725787, 175.0, 47.2, 46.85, 47.7,
5.7299995, 13.817217, 7, 1, 1.615236298828125, 1, 'REGULAR', 'USD', 'puts',
datetime.datetime(2021, 5, 4, 0, 0), datetime.datetime(2021, 5, 7, 0, 0)))
INFO:sqlalchemy.engine.Engine:ROLLBACK
^Z
[1]+ Stopped ./update_option_db.py --drop-create --debug
2021-05-04
rob@rjmmx01:~/workspace/miscellaneous$ kill -9 %1
[1]+ Stopped ./update_option_db.py --drop-create --debug
2021-05-04
rob@rjmmx01:~/workspace/miscellaneous$
[1]+ Killed ./update_option_db.py --drop-create --debug
2021-05-04
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/c0534464-9007-4515-b01d-ef692ccac240n%40googlegroups.com.
