All jobs

Fix "AttributeError: 'Engine' object has no attribute 'execute'" in SQLAlchemy 2.0

Yes. An agent rewrites engine.execute calls onto connections with explicit transactions, and checks that inserts still commit after the move to 2.0.

Run this on RushOpens the console with this prompt ready to send, in a Project that has your repository.

What the agent does

SQLAlchemy 2.0 removed Engine.execute(), and with it three habits that usually travel together: running SQL straight on the engine, passing SQL as a plain string, and relying on the library to commit for you. The agent treats the error as a pointer to all three. In apryor6/fastapi_example, whose requirements.txt pins SQLAlchemy==1.3.18, the init_db task in tasks.py does Base.metadata.create_all(), then engine.execute("insert into widget values (1, 'hey', 'there');"), then prints engine.execute("select * from widget;"). app/db.py sets Base.metadata.bind = engine, which is why create_all() needs no argument, and the session fixture in app/test/fixtures.py calls drop_all() and create_all() the same way.

Before editing it uses the bridge release as a map. On SQLAlchemy 1.4 with the environment variable SQLALCHEMY_WARN_20=1, the same task still works and prints one RemovedIn20Warning per habit: the bind argument for schema methods will be required, Engine.execute() is legacy, passing a string to Connection.execute() is deprecated in favour of text(), and the statement is being committed by implicit autocommit, which will be removed. That is the full to-do list, produced by the library, with file and line for each. On 2.0 the first thing to break is not even the title's error: create_all() raises TypeError: create_all() missing 1 required positional argument: 'bind', and only after that is fixed do the two engine.execute lines fail. The repository's tests show the same order. They are 19 passing on the pinned 1.3.18; on 2.0 nine pass and ten error, every one of them in the fixture's drop_all().

The rewrite has one trap, and the agent tests for it directly. The mechanical translation of engine.execute(sql) is with engine.connect() as conn: conn.execute(text(sql)). For a select that is right. For the insert it is silently wrong on 2.0. Run against this project's SQLite database, an insert made that way with no commit is still there afterwards on 1.3 and on 1.4, because those versions commit it for you, and is gone on 2.0, where the connection rolls back when the block ends. No exception, no warning, one missing row. So every write goes inside with engine.begin() as conn:, which commits on success, and the agent proves it by reading the row back through a second connection on each version.

The schema calls get bind=engine passed explicitly, in tasks.py and in the test fixture, which also has to import engine. With those edits the suite is back to 19 passing on 2.0 and still 19 on 1.3.18, so the change can merge before the pin moves. The agent leaves the ORM code alone. Calls like session.query(Widget).get(widget_id) in app/widget/service.py still work on 2.0 and print a LegacyAPIWarning pointing to Session.get(); it lists them as a follow-up and does not mix a query-style migration into a fix for a removed method.

Start it with one API call

POST https://api.phoenix.so/o/{org}/sessions with this body:

{
  "agent": "codex",
  "input": "After upgrading to SQLAlchemy 2.0 this project fails with \"AttributeError: 'Engine' object has no attribute 'execute'\". Read the dependency pins. Run the test suite and any database setup or seed script on the currently pinned SQLAlchemy and save the results. Then install the newest SQLAlchemy 1.4 release, set SQLALCHEMY_WARN_20=1, run the same things and collect every RemovedIn20Warning with its file and line; use that list as the scope. Fix each one: replace engine.execute with a connection; wrap raw SQL strings in text(); pass bind explicitly to MetaData.create_all and drop_all, including in test fixtures, instead of relying on metadata.bind. Every statement that writes must run inside engine.begin() or be followed by an explicit commit; after the change, on SQLAlchemy 2.0, read each written row back through a separate connection to prove it was committed, and report that check. Keep the code working on the currently pinned version as well, so the change can merge before the pin moves; run the tests on both. Do not rewrite ORM queries that still work on 2.0; list any LegacyAPIWarning lines as follow-ups. Then raise the pin to 2.0 in a separate commit. Open a pull request with the warning list from 1.4, the test results on the old pin and on 2.0, and the commit check.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
  1. Get an API key. In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
  2. Send the request. POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
  3. Read what came back. Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

What comes back

A pull request in two commits. The first changes how statements are executed and how schema calls are bound, and passes the tests on both the old pin and 2.0; for fastapi_example that is 19 tests each way, where 2.0 gave nine passes and ten errors before. The second commit moves the version pin. The description reproduces the 1.4 warning list as a checklist with each item tied to the line that resolved it, and reports the read-back check for every write.

A reviewer should spend their time on writes. For each changed statement that inserts, updates or deletes, they confirm it sits in a begin() block or has an explicit commit, because that is the one place where a wrong translation loses data without failing a test that only checks the call did not raise. The follow-up list of legacy ORM calls is information, not a blocker: those lines run on 2.0.

Run it on every dependency upgrade pull request

When a bot or a teammate opens the pull request that bumps SQLAlchemy across the major version, start this job on that branch. A github trigger on pull_request.labeled, with labels set to a label you reserve for this, fires when a repository owner, member or collaborator applies it. Bot senders never fire a trigger, so a label the bot adds to its own pull request starts nothing; a person applies it, or you start the run by API with the pull request URL as the resource so the agent works on that branch.

The event arrives after the saved prompt as data. The prompt should tell the agent to look at which package the pull request bumps and to do nothing unless it is SQLAlchemy crossing from 1.x to 2.x.

The use case this job follows, with its automation and trigger: Upgrade dependencies weekly.

Why run it on Rush?

Three installed versions of one library are needed to do this properly: the pinned one for a baseline, 1.4 for its warning list, and 2.0 for the result. Then the same script and the same tests run under each. The sandbox gives the agent a clean place to build those environments and a real database file to write to and read back from, and it keeps the output of every run. The lost-insert check in particular is only convincing as recorded output: a row count from a second connection, on each version, next to the code that produced it.

It also keeps the pull request reviewable. The pressure with a failing upgrade is to change everything that prints a warning. The run is scoped by the library's own removal list, verified on both sides of the version boundary, and it hands the optional modernisation back as a list instead of a diff.

Frequently asked questions

What replaces engine.execute in SQLAlchemy 2.0?

A connection: with engine.connect() as conn: conn.execute(text("select ...")) for reads, and with engine.begin() as conn: for writes so the block commits. In ORM code, Session.execute() does the same job. Raw SQL strings must be wrapped in text().

Why did my inserts stop being saved after the upgrade?

Version 1.x committed standalone statements automatically. Version 2.0 does not: a connection opened with connect() rolls back when it closes unless you call commit(). Use engine.begin() or commit explicitly. The agent checks each write by reading it back on a second connection.

Why does create_all() fail before engine.execute does?

Because 2.0 also removed bound metadata. Base.metadata.create_all() with no argument worked when metadata.bind was set; now it needs bind=engine. In fastapi_example that TypeError comes first, and it is the error behind all ten failing tests.

Can I see everything that will break before upgrading?

Yes. Install the latest 1.4 release, set SQLALCHEMY_WARN_20=1 and run your tests. Each use of a removed feature prints a RemovedIn20Warning with its location. The agent starts from that list.

More SQLAlchemy jobs

More jobs like this: Upgrade dependencies weekly

Keep reading