Why Your psycopg2 INSERT Is Not Saved (and How to Commit It)

The symptom

POST /reviews returns 201 Created with the new review and its id. Then GET /reviews does not show it. No error appears anywhere.

def get_db():
    conn = psycopg2.connect(DATABASE_URL)
    conn.autocommit = False
    return conn

@app.post("/reviews")
def create_review():
    data = request.get_json()
    conn = get_db()
    cur = conn.cursor(cursor_factory=RealDictCursor)
    cur.execute(
        "INSERT INTO reviews (product, author, rating, body) "
        "VALUES (%s, %s, %s, %s) RETURNING *",
        (data["product"], data["author"], data["rating"], data["body"]),
    )
    review = cur.fetchone()
    cur.close()
    conn.close()          # transaction was never committed
    return jsonify(review), 201

Why the row disappears

With autocommit = False (the psycopg2 default), the first execute starts a transaction. Everything you do inside it is visible only to that connection until you commit. RETURNING * works because your own connection can see its own uncommitted row - so the response looks correct.

When conn.close() runs with the transaction still open, psycopg2 does not commit for you. Postgres rolls the transaction back, and the row never becomes visible to any other connection. The next request opens a new connection and finds nothing.

The fix: commit after the write

    review = cur.fetchone()
    conn.commit()         # make the INSERT permanent
    cur.close()
    conn.close()
    return jsonify(review), 201

Only now is the row saved. commit() is also the point where Postgres checks deferred constraints, so an error can appear here - keep it before you send the success response.

Roll back when something fails

If a statement fails inside a transaction, the transaction is aborted. Every later statement on that connection fails with current transaction is aborted, commands ignored until end of transaction block until you roll back. Handle it explicitly:

conn = get_db()
try:
    cur = conn.cursor(cursor_factory=RealDictCursor)
    cur.execute("INSERT INTO reviews (...) VALUES (...) RETURNING *", params)
    review = cur.fetchone()
    conn.commit()
except psycopg2.Error:
    conn.rollback()
    raise
finally:
    conn.close()

A shorter way: the connection context manager

A psycopg2 connection used in a with block commits when the block ends without an error, and rolls back if an exception is raised:

conn = get_db()
try:
    with conn:                       # commit on success, rollback on error
        with conn.cursor(cursor_factory=RealDictCursor) as cur:
            cur.execute("INSERT INTO reviews (...) VALUES (...) RETURNING *", params)
            review = cur.fetchone()
finally:
    conn.close()                     # with conn: does NOT close the connection

Two details catch people here. In psycopg2, with conn: ends the transaction but leaves the connection open, so you still need close() (or you return it to a pool). In psycopg 3 the behaviour is different: with psycopg.connect(...) as conn: commits and also closes the connection. Check which library your project uses.

What about autocommit = True?

With conn.autocommit = True, every statement is committed as soon as it runs. That avoids this bug, but you lose the ability to group statements. For example, an order and its line items should be saved together or not at all. With autocommit on, a failure after the first insert leaves half an order in the database. Use autocommit for simple scripts and read-only work; use explicit transactions when several writes belong together.

Confirm the fix

Post a review, then count from a separate request (a separate connection):

curl -s -X POST localhost:8000/reviews \
  -H 'Content-Type: application/json' \
  -d '{"product":"Test","author":"Me","rating":5,"body":"Great product!"}'

curl -s localhost:8000/reviews | python3 -c "import sys,json; print(json.load(sys.stdin)['count'])"

You can also check directly in psql, which is another connection:

SELECT count(*) FROM reviews;

If the count goes up after each POST, the write is committed. A test that writes and reads back on the same connection cannot catch this bug, so always read back on a new one.