MX Linux Python3 sqlalchemy hangs on ROLLBACK

166 views

Skip to first unread message

Rob Marshall

unread,

May 11, 2021, 10:29:06 AM5/11/21

to sqlalchemy

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')

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: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:[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

[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

Mike Bayer

unread,

May 11, 2021, 10:41:13 AM5/11/21

to noreply-spamdigest via sqlalchemy

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

--
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.

Message has been deleted

Mike Bayer

unread,

May 11, 2021, 7:42:28 PM5/11/21

to noreply-spamdigest via sqlalchemy

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:pass...@127.0.0.1/options')

And that appears to handle that particular insert without issue.

Thank-you,

Rob

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ee8c0fda-e198-45f5-acb3-2f00820da952n%40googlegroups.com.