two things to try: 1. try a different driver, like mysqlclient or pymysql, to see if error persists
2. when it hangs, look in information_schema.processlist for current info (it's the same in mariadb): https://dev.mysql.com/doc/refman/8.0/en/information-schema-processlist-table.html On Tue, May 11, 2021, at 10:29 AM, Rob Marshall wrote: > 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:password@127.0.0.1/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 sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/c0534464-9007-4515-b01d-ef692ccac240n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/c0534464-9007-4515-b01d-ef692ccac240n%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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 sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ad186593-9965-403b-bac4-f0c55a1c51e1%40www.fastmail.com.