Psycopg2 Cheat Sheet: Essential PostgreSQL Commands in Python

Psycopg2 Cheat Sheet: Essential PostgreSQL Commands in Python

Psycopg2 is the most popular PostgreSQL adapter for the Python programming language. It provides a powerful and flexible interface for interacting with PostgreSQL databases, allowing developers to execute SQL queries, manage transactions, and handle data efficiently. This comprehensive cheat sheet covers essential Psycopg2 commands, demonstrating how to perform various database operations within your Python applications.

I. Installation and Connection

bash
pip install psycopg2-binary

“`python
import psycopg2

try:
conn = psycopg2.connect(“dbname=your_database_name user=your_username password=your_password host=your_host port=your_port”)
cur = conn.cursor()
print(“Connection successful”)
except psycopg2.Error as e:
print(f”Error connecting to database: {e}”)
“`

Explanation:

• psycopg2.connect() establishes a connection to the PostgreSQL database. The connection string specifies the database name, username, password, host, and port.
• conn.cursor() creates a cursor object, which is used to execute SQL queries.
• The try...except block handles potential connection errors.

II. Executing SQL Queries

A. Basic Queries:

“`python
cur.execute(“SELECT * FROM your_table”)
rows = cur.fetchall()
for row in rows:
print(row)

cur.execute(“SELECT column1, column2 FROM your_table WHERE condition”)
row = cur.fetchone() # Fetch a single row
print(row)

cur.execute(“INSERT INTO your_table (column1, column2) VALUES (%s, %s)”, (value1, value2))
conn.commit() # Commit the changes

cur.execute(“UPDATE your_table SET column1 = %s WHERE condition”, (new_value,))
conn.commit()

cur.execute(“DELETE FROM your_table WHERE condition”)
conn.commit()
“`

Explanation:

• cur.execute() executes an SQL query. Parameterized queries using %s placeholders are recommended to prevent SQL injection vulnerabilities.
• cur.fetchall() retrieves all rows returned by the query.
• cur.fetchone() retrieves a single row.
• conn.commit() commits the changes to the database. For INSERT, UPDATE, and DELETE operations, the changes are not persisted until you commit.

B. Handling Large Result Sets:

python
cur.execute("SELECT * FROM large_table")
while True:
rows = cur.fetchmany(size=1000) # Fetch in batches
if not rows:
break
for row in rows:
# Process each row
print(row)

Explanation:
* cur.fetchmany(size=n) retrieves n rows at a time. This is useful for handling large result sets that might not fit in memory.

C. Retrieving Specific Columns by Name:

python
cur.execute("SELECT * FROM your_table")
columns = [desc[0] for desc in cur.description]
rows = cur.fetchall()
for row in rows:
row_dict = dict(zip(columns, row))
print(row_dict)
print(row_dict["specific_column"])

Explanation:

• cur.description provides metadata about the query results, including column names.
• This example demonstrates creating a dictionary for each row, allowing access to columns by name.

III. Transactions

python
try:
conn.autocommit = False # Disable autocommit
cur.execute("INSERT INTO your_table (column1, column2) VALUES (%s, %s)", (value1, value2))
cur.execute("UPDATE another_table SET column1 = %s WHERE condition", (new_value,))
conn.commit() # Commit both operations
except psycopg2.Error as e:
conn.rollback() # Rollback in case of error
print(f"Error: {e}")
finally:
conn.autocommit = True # Re-enable autocommit

Explanation:

• conn.autocommit = False disables automatic commits, allowing multiple operations to be performed within a single transaction.
• conn.commit() commits all changes in the transaction.
• conn.rollback() undoes all changes if an error occurs.

IV. Working with Different Data Types

Psycopg2 automatically handles most common data type conversions between Python and PostgreSQL. However, some specific types require extra handling:

• Arrays: Use psycopg2.extras.register_uuid() for UUIDs and psycopg2.extras.Json for JSON data.

• JSON/JSONB:

“`python
import json

cur.execute(“INSERT INTO your_table (json_column) VALUES (%s)”, (json.dumps({‘key’: ‘value’}),))

cur.execute(“SELECT json_column FROM your_table”)
row = cur.fetchone()
json_data = json.loads(row[0])
print(json_data)
“`

• Dates and Times:

“`python
import datetime

cur.execute(“INSERT INTO your_table (date_column) VALUES (%s)”, (datetime.date(2024, 1, 1),))

cur.execute(“SELECT date_column FROM your_table”)
row = cur.fetchone()
date_value = row[0]
print(date_value)
“`

V. Using COPY for Bulk Loading

For efficient bulk loading of data from files, use the COPY command:

python
with open('data.csv', 'r') as f:
cur.copy_from(f, 'your_table', sep=',', header=True)
conn.commit()

VI. Handling Errors and Exceptions

python
try:
# Your database operations here
except psycopg2.Error as e:
print(f"PostgreSQL error: {e}")
except Exception as e:
print(f"General error: {e}")

VII. Closing the Connection

python
cur.close()
conn.close()

VIII. Advanced Techniques

• Server-Side Cursors: For extremely large result sets, use server-side cursors to avoid fetching the entire result set into memory at once.

“`python
cur = conn.cursor(name=”my_server_side_cursor”) # Named cursor
cur.execute(“SELECT * FROM very_large_table”)

while True:
rows = cur.fetchmany(size=1000)
if not rows:
break
for row in rows:
# Process each row
print(row)

cur.close()

“`

• Prepared Statements: For repeated execution of the same query with different parameters, use prepared statements to improve performance:

“`python
cur.execute(“PREPARE my_statement (int) AS SELECT * FROM your_table WHERE id = $1”)
cur.execute(“EXECUTE my_statement (10)”)
rows = cur.fetchall()

“`

• psycopg2.extras: Explore the psycopg2.extras module for additional functionalities like namedtuple cursors, dictionary cursors, and handling specific data types like UUIDs and JSON.

This cheat sheet provides a comprehensive overview of essential Psycopg2 commands for interacting with PostgreSQL databases in Python. Remember to always handle errors appropriately, close connections after use, and utilize parameterized queries to prevent SQL injection vulnerabilities. By mastering these techniques, you can efficiently integrate PostgreSQL into your Python applications and leverage its powerful features for data management. For more advanced features and detailed explanations, consult the official Psycopg2 documentation.