A nightly SQLAlchemy script inserts daily totals without error, but the rows never appear, and echo=True logs BEGIN (implicit), the INSERT, then ROLLBACK; what happened and how do you fix it?
answer
- read the last line of the block
- implicit means nothing sent
- close ends the transaction
- parameters land in the log too
basics
~10 sThe INSERT ran inside a SQLAlchemy Connection whose block ended without commit(), so Connection.close() rolled it back; the ROLLBACK line proves it. Wrap the write in engine.begin(), or call conn.commit() before the block ends.
solid answer
~40 sIn the echo log, `BEGIN (implicit)` means SQLAlchemy considered a transaction started without sending any statement, then the INSERT ran, and the final `ROLLBACK` is `Connection.close()` ending the still-open transaction when the `with engine.connect()` block exited. No exception is raised, so the script looks healthy while every write is discarded. The fix is to make the commit unforgettable: write each unit of work in `with engine.begin() as conn:`, which logs `COMMIT` on success and `ROLLBACK` on an exception, or call `conn.commit()` explicitly where batches must commit separately. Then confirm it: the same log should now end in `COMMIT`. Before leaving `echo=True` on in production, note that it writes every statement's bound parameters to the log; `hide_parameters=True` keeps the SQL and drops the values.
code
plaintext · 4 linesINFO sqlalchemy.engine.Engine BEGIN (implicit)
INFO sqlalchemy.engine.Engine INSERT INTO daily_totals (day, total) VALUES (?, ?)
INFO sqlalchemy.engine.Engine [generated in 0.00008s] ('2026-10-09', 1250)
INFO sqlalchemy.engine.Engine ROLLBACKgo deeper
Know that a ROLLBACK at the end of a write block in the echo log means the writes were discarded, and that engine.begin() commits for you.
Explain BEGIN (implicit), why Connection.close() rolls back, and how hide_parameters and echo="debug" change what the log shows.
Diagnose silent lost writes from the log alone, prove it from a second connection, and change the write paths to engine.begin() with a test that reads back separately.
Decide how SQL logging runs in each environment, which values may reach the logs, and which commit pattern reviews enforce so silent rollbacks cannot ship.
## Reading the echo log `create_engine(url, echo=True)` turns on SQLAlchemy's statement logging: each statement and the `repr()` of its parameters go to the `sqlalchemy.engine.Engine` logger, with a handler that writes to standard output. For the failing nightly run the block of interest reads: 1. `BEGIN (implicit)` — the Connection **autobegan** a transaction on the first `execute()`. "Implicit" means SQLAlchemy **sent no command**; it records that the DBAPI's implicit transaction has started. 2. `INSERT INTO daily_totals (day, total) VALUES (?, ?)` — the statement, compiled for the driver's placeholder style. 3. `[generated in 0.00008s] ('2026-10-09', 1250)` — the bound parameters and whether the compiled form came from SQLAlchemy's statement cache. 4. `ROLLBACK` — the transaction ended without a commit. The INSERT succeeded inside the database's transaction; it was discarded at step 4. Nothing failed, so no exception, no warning and no non-zero exit code appeared. ## Why the ROLLBACK happened The script used **commit-as-you-go** style without the commit: ```python with engine.connect() as conn: conn.execute(text("INSERT INTO daily_totals (day, total) VALUES (:day, :total)"), params) ``` When the `with` block exits it calls `Connection.close()`, which returns the DBAPI connection to the pool and **unconditionally rolls back** any transaction still open. SQLAlchemy has no library-level autocommit, so writes persist only when something commits them. A common origin is code written for an older release that relied on automatic commits, or a refactor that moved the `commit()` call into a branch that no longer runs. ## Confirming the diagnosis - Check where the log block ends: `COMMIT` means the transaction was committed, `ROLLBACK` means it was not. A `ROLLBACK` at the end of a read-only block is normal and harmless. - Query the table from a **separate** connection after the run; a count of zero confirms the rows never committed. - Search the code for `engine.connect()` blocks that execute INSERT, UPDATE or DELETE and contain no `commit()` on every path. - Rule out the other explanations: an exception caught and swallowed inside the block also ends in `ROLLBACK`, so check the script's own error handling; and a script pointed at the wrong database would log `COMMIT` while the rows land somewhere else, which printing `engine.url` (the password is masked) settles. ## The fix | Pattern | Logged ending on success | On an exception | |---|---|---| | `with engine.connect()` and no `commit()` | `ROLLBACK` | `ROLLBACK` | | `with engine.connect()` plus `conn.commit()` | `COMMIT` | `ROLLBACK` at close | | `with engine.begin()` | `COMMIT` | `ROLLBACK`, exception re-raised | For a unit of work with a single outcome, `engine.begin()` is the robust choice because there is no commit call to forget. Keep `connect()` with explicit `conn.commit()` calls only where one connection commits several batches independently, for example one commit per day so a bad day does not undo the others. Re-run with `echo=True` and confirm the block now ends in `COMMIT`. ## What echo writes, and how to run it in production The same log line that made the diagnosis easy also prints the **bound values**. For the orders database that can mean customer names, email addresses or amounts written to whatever collects standard output. - `hide_parameters=True` on `create_engine()` keeps the statements but logs "[SQL parameters hidden due to hide_parameters=True]", and also keeps the values out of the string form of a `StatementError`. - `echo="debug"` additionally logs every result row (`Col` and `Row` lines), which is useful locally and rarely appropriate in production. - Rather than hard-coding `echo=True`, configure the `sqlalchemy.engine` logger with Python's `logging` module: `INFO` gives statements, `DEBUG` adds result rows, and the level can follow the environment. ## Keeping it from coming back - Prefer `engine.begin()` for writes in scripts and services alike. - Cover the write path with a test that reads the data back through a second connection, since the first connection can still see its own uncommitted rows. - Treat a write path whose log never shows `COMMIT` as a defect, not a style choice. Counting statements per request and asserting on the log in tests belong with test tooling; the pool's own reset behaviour when a connection is returned belongs with pool configuration.
- Why does a SQLAlchemy test that writes and then reads on the same connection miss this bug?Inside one transaction the connection sees its own uncommitted rows, so the read succeeds even though nothing will be committed. Read the data back through a separate connection, or assert that the write path commits, to catch a missing `commit()`.
- How do you get SQLAlchemy's SQL log in production without echo=True in the code?Configure Python's `logging` for the `sqlalchemy.engine` logger: `INFO` logs statements and parameters, `DEBUG` adds result rows. Pair it with `create_engine(..., hide_parameters=True)` so values such as customer details stay out of the log and out of error messages.
- BEGIN (implicit) means SQLAlchemy sent a BEGIN statement to the database.
- A missing commit would raise an exception, so the database must be dropping rows.
- Leaving echo=True on in production is harmless because it only prints SQL text.
- Calling conn.close() explicitly before the block ends will commit the rows.
- The fix is to pass autocommit=True to create_engine().