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.

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.
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.
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:
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.
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:
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.
| Method | Returns | Use case |
|---|---|---|
fetchone() | One tuple or None | When you expect at most one row |
fetchmany(n) | List of up to n tuples | Streaming large result sets |
fetchall() | List of all tuples | Small 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.
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:
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:
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.
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 toolsSmall utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related Python guides
All Python guidesPython iter(): How the Built-in Function Works
Explains Python's iter() built-in: how it creates iterators, the two-argument sentinel form, custom iterator classes, and common edge cases.
Python Pika: Queues, Exchanges, Routing Keys, and Acknowledgements
Learn how to use Python's Pika client with RabbitMQ: declare queues and exchanges, publish with routing keys, consume messages, and handle acknowledgements reliably.
Python Kafka: kafka-python vs Confluent Client
python kafka python vs confluent kafka: Compare kafka-python and Confluent Kafka Python clients: API differences, performance, feature coverage, and when to choose eac...
Python Kafka Offsets, Partitions, and Manual Commits
Learn how Kafka offsets and partitions work and how to manually commit offsets in Python consumers with kafka-python and confluent-kafka. Covers sync vs async commits, rebalances, and seeking.
More in Python
All Python guidesThe guides filed next to this one in Python, in library order.