DEV Community

Cover image for SQLAlchemy 1.x code on 2.x: no error, no row
ProvenPin
ProvenPin

Posted on

SQLAlchemy 1.x code on 2.x: no error, no row

#python #ai #mcp #programming

SQLAlchemy 2.0 removed library level autocommit. The migration guide says it plainly: in 1.x, conn.execute(text("INSERT ...")) on a connection from engine.connect() would commit on its own, and in 2.0 "this will not occur".

Code written in the 1.x style still looks fine. Here is what it does on sqlalchemy 2.1.4.

Before: 1.x style on 2.x 

import sqlalchemy as sa

engine = sa.create_engine("sqlite:///shop.db")
with engine.begin() as conn:
    conn.execute(sa.text("CREATE TABLE orders (sku TEXT)"))

# 1.x style write
with engine.connect() as conn:
    conn.execute(sa.text("INSERT INTO orders VALUES ('A1')"))

with engine.connect() as conn:
    print(conn.execute(sa.text("SELECT count(*) FROM orders")).scalar())  # 0
Enter fullscreen mode Exit fullscreen mode

No exception, and no warning even with python -W always. The INSERT runs inside a transaction that SQLAlchemy 2 begins for you, and nothing commits it, so it is rolled back when the connection closes. The same script on sqlalchemy 1.4.54 prints 1.

After: the 2.x version 

with engine.begin() as conn:  # commits on exit, rolls back on error
    conn.execute(sa.text("INSERT INTO orders VALUES ('A1')"))
Enter fullscreen mode Exit fullscreen mode

Or keep engine.connect() and call conn.commit() yourself ("commit as you go").

The tested recipe

The ProvenPin recipe pandas-sqlalchemy-to-sql writes and reads pandas DataFrames through a SQLAlchemy 2 engine. Its write helper uses engine.begin(), raw SQL goes through text() with bound params, and it covers dtype control, nullable ints and datetimes on sqlite. It pins sqlalchemy 2.1.4 and pandas 3.0.6, and passed CI on Oct 19, 2026 on Python 3.13.16. The receipt and full files are here: https://provenpin.com/recipes/pandas-sqlalchemy-to-sql/

Use it from your coding agent

ProvenPin is a free MCP server. No API key, no signup.

claude mcp add --transport http provenpin https://mcp.provenpin.com/mcp
Enter fullscreen mode Exit fullscreen mode

Cursor (~/.cursor/mcp.json):

{ "mcpServers": { "provenpin": { "url": "https://mcp.provenpin.com/mcp" } } }
Enter fullscreen mode Exit fullscreen mode

Then ask your agent for "a tested pandas to_sql recipe with SQLAlchemy 2", or call get_recipe("pandas-sqlalchemy-to-sql"). Setup for VS Code, Windsurf and Cline is in the docs.

Sources: SQLAlchemy 2.0 migration guide, recipe receipt.

Top comments (0)