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.
