Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

Python Psycopg Insert, Select, Update, and Transactions

Learn how to insert, select, update, and manage transactions with Python psycopg. The examples use psycopg2 syntax, and the same transaction concepts apply to psycopg3.

~4 min readPython
#psycopg#PostgreSQL#CRUD#transactions
Illustration of a Python script interacting with a PostgreSQL database through psycopg, showing insert, select, update, and transaction control.

Working with PostgreSQL from Python usually means using psycopg. This article covers the core operations—insert, select, and update—and explains how psycopg handles transactions. The examples use psycopg2 syntax; most concepts apply to psycopg3, and 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 executes SQL and fetches results.

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

Cursors are not thread-safe. If you share a connection across threads, each thread should use its own cursor and serialize access to the connection. For many concurrent requests, a connection pool is usually simpler.

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 quotes and escapes values correctly.

python
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 several rows, executemany() is convenient:

python
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() runs the same statement for each parameter set. It is not a single multi-row INSERT, so it is not a bulk-loading shortcut. For large inserts with psycopg2, psycopg2.extras.execute_values() builds one INSERT with multiple value tuples; for very high-volume loads, PostgreSQL's COPY protocol is a better fit.

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.

python
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. The psycopg2.extras module is not imported by the base psycopg2 import, so add it separately:

python
import psycopg2 import psycopg2.extras 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.

MethodReturnsUse case
fetchone()One tuple or NoneWhen you expect at most one row
fetchmany(n)List of up to n tuplesStreaming large result sets
fetchall()List of all tuplesSmall result sets, simple scripts

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.

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

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

By default, psycopg starts a transaction implicitly when the first SQL statement runs. The transaction stays open until you call commit() or rollback(). If the connection is closed before either call, the transaction is rolled back automatically. If you do not want implicit transactions, set conn.autocommit = True.

Use the connection as a context manager for common commit/rollback handling:

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

In both psycopg2 and psycopg3, the connection context manager commits on success and rolls back on exception. It does not close the connection, so close it separately.

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

python
try: cur.execute('INSERT INTO users (name) VALUES (%s)', ('Dave',)) cur.execute('UPDATE users SET active = %s WHERE id = %s', (True, 1)) conn.commit() except Exception: conn.rollback() raise finally: cur.close() conn.close()

Handling Errors and Connection Cleanup

Database errors raised by psycopg2 are subclasses of psycopg2.Error. Catch them to log or recover. After a statement fails, the transaction may be aborted; call rollback() to clear it. An OperationalError can mean the connection is broken, so check whether you need a new connection.

python
from psycopg2 import OperationalError, IntegrityError try: cur.execute('INSERT INTO users (name) VALUES (%s)', ('Eve',)) conn.commit() except IntegrityError as e: conn.rollback() print(f'Integrity violation: {e}') except OperationalError as e: print(f'Connection problem: {e}') # Reconnect before retrying if the operation is safe to repeat.

Performance and Operational Considerations

Keeping a transaction open for a long time can hold locks and increase contention. Keep transactions short: execute the necessary statements, then commit. If a long-running script can tolerate partial failures, commit at reasonable checkpoints.

executemany() is convenient, but it is not a multi-row insert. For large inserts with psycopg2, psycopg2.extras.execute_values() is a better choice. For reads, use fetchmany() or iterate over the cursor instead of loading everything with fetchall() when the result set is large. Always use parameterized queries to avoid SQL injection and let the driver handle value formatting.

Connection pooling is important for multi-threaded applications. Libraries like psycopg2.pool.ThreadedConnectionPool (psycopg2) or psycopg_pool (psycopg3) reuse connections and reduce the cost of creating new sessions, especially for 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, call set_isolation_level() before any SQL statement starts a transaction. This affects how concurrent transactions behave, so choose a level that matches your consistency requirements.

Try matching browser tools

All tools

Small utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.

All Python guides

More in Python

All Python guides

The guides filed next to this one in Python, in library order.