Python Psycopg Insert, Select, Update, and Transactions

python psycopg insert select update and transactions requires a clear understanding of the core syntax, runtime behavior, and practical implementation patterns demonstrated in the examples below.

When working with PostgreSQL from Python, psycopg is the standard adapter. This article covers the core operations—insert, select, update—and how to manage transactions correctly with psycopg. We'll use psycopg2 syntax, but most concepts apply to psycopg3 as well; differences are noted where relevant.

Setting Up a Connection and Cursor

Every operation begins with a connection. Use psycopg2.connect() with the appropriate parameters. The connection object holds the database session, and the cursor is used to execute SQL and fetch results.

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="postgres",
    password="secret"
)
cur = conn.cursor()

The cursor is not thread-safe; each thread should have its own cursor. For a one-off script, this is fine. For long-running applications, consider a connection pool (see the performance section).

Inserting Rows with Parameterized Queries

Never build SQL by string concatenation; it invites SQL injection and breaks with special characters. Use parameterized queries with %s placeholders. Psycopg will quote and escape values correctly.

cur.execute(
    "INSERT INTO users (name, email) VALUES (%s, %s)",
    ("Alice", "alice@example.com")
)
conn.commit()

The commit() is essential—without it, the insert is not persisted. If you are inserting many rows, use executemany() to reduce round trips:

user_data = [
    ("Bob", "bob@example.com"),
    ("Carol", "carol@example.com"),
]
cur.executemany(
    "INSERT INTO users (name, email) VALUES (%s, %s)",
    user_data
)
conn.commit()

executemany is not a true batch insert; it sends multiple statements in one round trip but still executes them individually. For very large inserts, consider psycopg2.extras.execute_values() for a single multi-row INSERT.

Selecting Data and Iterating Results

After a SELECT, the cursor holds the result set. Use fetchone(), fetchmany(n), or fetchall() depending on how much data you expect.

cur.execute("SELECT id, name, email FROM users WHERE active = %s", (True,))
rows = cur.fetchall()
for row in rows:
    print(row[0], row[1], row[2])

Each row is a tuple by default. To access columns by name, use psycopg2.extras.RealDictCursor when creating the cursor:

cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute("SELECT id, name FROM users")
for row in cur.fetchall():
    print(row["id"], row["name"])

For large result sets, fetchmany() avoids loading everything into memory. The cursor itself is iterable, so you can also loop directly over cur.

Updating Existing Rows

Updates follow the same parameterized pattern. The key is to commit after the change, and to check cur.rowcount if you need to confirm how many rows were affected.

cur.execute(
    "UPDATE users SET email = %s WHERE id = %s",
    ("newalice@example.com", 1)
)
conn.commit()
print(f"Updated {cur.rowcount} row(s)")

If the update does not match any row, rowcount is 0. That is not an error—it simply means the condition did not match.

Managing Transactions: Commit, Rollback, and Context Managers

Psycopg starts a transaction implicitly when the first SQL statement runs. The transaction remains open until you call commit() or rollback(). If you close the connection without committing, the transaction is rolled back automatically.

Use the connection as a context manager to ensure commit/rollback is handled correctly:

with conn:
    cur.execute("INSERT INTO users (name) VALUES (%s)", ("Dave",))
    # If any exception occurs here, the transaction is rolled back.
    # If no exception, it is committed.

Note: In psycopg2, the with conn block commits on success and rolls back on exception. In psycopg3, the behavior is the same for the connection context manager. However, the cursor context manager in psycopg2 does not close the cursor; it only releases the result. In psycopg3, the cursor context manager closes the cursor.

For finer control, call commit() and rollback() explicitly. This is useful when you need to commit after a series of statements but roll back on a specific condition:

try:
    cur.execute("INSERT ...")
    cur.execute("UPDATE ...")
    conn.commit()
except:
    conn.rollback()
    raise

Always close the cursor and connection when done:

cur.close()
conn.close()

A try/finally or context manager for the connection is safer. The connection context manager does not close the connection; it only manages the transaction. Use a separate with block or finally to close.

Handling Errors and Connection Cleanup

Database errors are raised as psycopg2.Error subclasses. Catch them to log or recover. The connection is still usable after an error, but you should roll back to clear the aborted transaction.

from psycopg2 import OperationalError, IntegrityError

try:
    cur.execute("INSERT ...")
    conn.commit()
except IntegrityError as e:
    conn.rollback()
    print(f"Integrity violation: {e}")
except OperationalError as e:
    conn.rollback()
    print(f"Connection problem: {e}")

If the connection is broken, you may need to reconnect. A common pattern is to create a new connection on OperationalError. For production, use a connection pool to handle reconnects transparently.

Performance and Operational Considerations

Transactions are not free. Holding a transaction open for a long time can lock rows and increase contention. Keep transactions short: execute the necessary statements, then commit. For long-running scripts, commit periodically if you can tolerate partial failures.

Batch inserts with executemany or execute_values reduce network round trips. For reads, use fetchmany instead of fetchall when the result set is large. Always use parameterized queries to avoid parsing overhead and security risks.

Connection pooling is essential for multi-threaded applications. Libraries like psycopg2.pool.ThreadedConnectionPool or psycopg_pool (for psycopg3) reuse connections and reduce the cost of establishing a new session. This is especially important when you have many short-lived requests.

Finally, be aware of the isolation level. Psycopg defaults to READ COMMITTED, which is fine for most use cases. If you need stronger guarantees, set conn.set_isolation_level() before starting a transaction. This affects how concurrent transactions behave, so choose the level that matches your consistency requirements.