How to Use psycopg2 in Python: Connect and Query PostgreSQL

To use psycopg2 in Python, first install it with pip install psycopg2-binary. Then, import it, create a connection to your PostgreSQL database using psycopg2.connect(), create a cursor with conn.cursor(), execute SQL commands, and finally commit and close the connection.

Syntax

The basic steps to use psycopg2 are:

• Import psycopg2: Load the library to use its functions.
• Connect: Use psycopg2.connect() with parameters like database name, user, password, host, and port.
• Create a cursor: Call conn.cursor() to get a cursor object to run SQL commands.
• Execute SQL: Use cursor.execute() to run queries or commands.
• Commit: Save changes with conn.commit() if you modified data.
• Close: Close cursor and connection to free resources.

python

import psycopg2

conn = psycopg2.connect(
    dbname="your_db",
    user="your_user",
    password="your_password",
    host="localhost",
    port=5432
)
cursor = conn.cursor()
cursor.execute("YOUR SQL QUERY HERE")
conn.commit()  # if needed
cursor.close()
conn.close()

Example

This example connects to a PostgreSQL database, creates a table, inserts a row, fetches the data, and prints it.

python

import psycopg2

try:
    conn = psycopg2.connect(dbname="testdb", user="postgres", password="secret", host="localhost", port=5432)
    cursor = conn.cursor()

    cursor.execute("DROP TABLE IF EXISTS users")
    cursor.execute("CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(50))")

    cursor.execute("INSERT INTO users (name) VALUES (%s)", ("Alice",))
    conn.commit()

    cursor.execute("SELECT id, name FROM users")
    rows = cursor.fetchall()

    for row in rows:
        print(f"User ID: {row[0]}, Name: {row[1]}")

except Exception as e:
    print(f"Error: {e}")

finally:
    if 'cursor' in locals():
        cursor.close()
    if 'conn' in locals():
        conn.close()

Output

User ID: 1, Name: Alice

Common Pitfalls

• Forgetting to commit changes after INSERT, UPDATE, or DELETE causes no data to be saved.
• Not closing the cursor and connection can lead to resource leaks.
• Using string formatting to build SQL queries can cause SQL injection; always use parameterized queries with placeholders like %s.
• Incorrect connection parameters cause connection failures.

python

import psycopg2

# Wrong way: vulnerable to SQL injection
name = "Alice'; DROP TABLE users; --"
cursor.execute(f"INSERT INTO users (name) VALUES ('{name}')")

# Right way: safe parameterized query
cursor.execute("INSERT INTO users (name) VALUES (%s)", (name,))

Quick Reference

Action	Function/Method	Description
Connect to DB	psycopg2.connect()	Create a connection to PostgreSQL database
Create cursor	conn.cursor()	Get cursor to execute SQL commands
Execute query	cursor.execute(sql, params)	Run SQL with optional parameters
Fetch results	cursor.fetchone()/fetchall()	Retrieve query results
Commit changes	conn.commit()	Save changes to database
Close cursor	cursor.close()	Release cursor resources
Close connection	conn.close()	Close database connection

Key Takeaways

Always use parameterized queries with %s placeholders to avoid SQL injection.

Remember to commit your transaction after data changes to save them.

Close your cursor and connection to free resources.

Use try-except-finally blocks to handle errors and ensure cleanup.

Install psycopg2 with pip before importing it in your Python code.