this does mean there's a bug in the mariadb driver, if you can provide a reproducing test case
On Tue, May 11, 2021, at 12:41 PM, Rob Marshall wrote: > Hi Mike, > > Apparently it was the driver. I changed the create_engine to: > > engine = create_engine('mysql://user:password@127.0.0.1/options') > > And that appears to handle that particular insert without issue. > > Thank-you, > > Rob > On Tuesday, May 11, 2021 at 10:41:13 AM UTC-4 Mike Bayer wrote: >> __ >> 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:pass...@127.0.0.1/options >>> <http://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+...@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/ee8c0fda-e198-45f5-acb3-2f00820da952n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/ee8c0fda-e198-45f5-acb3-2f00820da952n%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/67e627c8-cd74-42b6-8cd0-a54284c3767d%40www.fastmail.com.