Logging fails with SQLAlchemy 2.0

[hmgaudecker]

Bug description

• New environment gave me SQLAlchemy 2.0
• Running estimate_ml without logging works fine
• Running estimate_ml with logging throws:

ObjectNotExecutableError: Not an executable object: 'BEGIN DEFERRED'                 

Quickly searching shows that SQLAlchemy 2.0 introduced breaking changes and you can't pass strings anymore to conn.execute, stuff needs to be passed via its text function IIUC.

Downgrading to 1.4 helped.

To Reproduce

Hopefully just executing the test suite with recent SQLAlchemy.

Screenshots/Error messages

│ estimagic/optimization/optimize.py:829 in                                   │      
│ _create_and_initialize_database                                             │      
│                                                                             │      
│   826 │   database = load_database(path=path, fast_logging=fast_logging)    │      
│   827 │                                                                     │      
│   828 │   # create the optimization_iterations table                        │      
│ ❱ 829 │   make_optimization_iteration_table(                                │      
│   830 │   │   database=database,                                            │      
│   831 │   │   if_exists=if_table_exists,                                    │      
│   832 │   )                                                                 │      
│                                                                             │      
│estimagic/logging/database_utilities.py:118 in                              │      
│ make_optimization_iteration_table                                           │      
│                                                                             │      
│   115 │   │   table_name, database, *columns, sqlite_autoincrement=True, ex │      
│   116 │   )                                                                 │      
│   117 │                                                                     │      
│ ❱ 118 │   database.create_all(database.bind)                                │      
│   119                                                                       │      
│   120                                                                       │      
│   121 def make_steps_table(database, if_exists="extend"):                   │      

[...]

│ estimagic/logging/database_utilities.py:481 in do_begin                     │      
│                                                                             │      
│   478 │   @event.listens_for(engine, "begin")                               │      
│   479 │   def do_begin(conn):                                               │      
│   480 │   │   # emit our own BEGIN                                          │      
│ ❱ 481 │   │   conn.execute("BEGIN DEFERRED")                                │      
│   482 │                                                                     │      
│   483 │   @event.listens_for(engine, "connect")                             │      
│   484 │   def set_sqlite_pragma(dbapi_connection, connection_record):       │      
│                                                                             │      
│ sqlalchemy/engine/base.py:1412 in execute                                   │      
│                                                                             │      
│   1409 │   │   try:                                                         │      
│   1410 │   │   │   meth = statement._execute_on_connection                  │      
│   1411 │   │   except AttributeError as err:                                │      
│ ❱ 1412 │   │   │   raise exc.ObjectNotExecutableError(statement) from err   │      
│   1413 │   │   else:                                                        │      
│   1414 │   │   │   return meth(                                             │      
│   1415 │   │   │   │   self,                                                │      
╰─────────────────────────────────────────────────────────────────────────────╯      
ObjectNotExecutableError: Not an executable object: 'BEGIN DEFERRED'                 

System

• Version 0.4.3

[janosg]

Thanks for opening the Issue!

I started to work on it (and fixed the specific symptom you got) but there are a few more things we need to fix before our logging works with sqlalchemy 2.0.