PostgreSQL Python: Connect & Query

PostgreSQL is the world's most advanced open-source relational database, and psycopg is Python's gold-standard adapter for connecting to it. While SQLite3 is ideal for development, PostgreSQL powers production systems at companies like Spotify, Instagram, and AWS—handling millions of queries per second across distributed infrastructure. In this tutorial, you'll install psycopg, authenticate to a PostgreSQL server, and execute real queries.

Unlike SQLite3 (which uses a local file), PostgreSQL is a separate server process running on a machine. Your Python code connects over TCP/IP using credentials, making it suitable for multi-user, multi-machine deployments. The psycopg library (version 3, released in 2023) is thread-safe, supports async operations, and integrates seamlessly with Django, FastAPI, and other Python frameworks.

Installing PostgreSQL and psycopg

First, ensure PostgreSQL is installed. On macOS, use Homebrew:

brew install postgresql
brew services start postgresql

On Linux (Ubuntu/Debian):

sudo apt-get install postgresql postgresql-contrib
sudo systemctl start postgresql

On Windows, download the installer from postgresql.org.

Next, install the psycopg Python adapter:

pip install psycopg

Verify the installation:

import psycopg
print(psycopg.__version__)  # Should print 3.x.x

Connecting to PostgreSQL

When you start PostgreSQL, it creates a default superuser called postgres. You connect by providing the host, port (5432 by default), database name, and credentials:

import psycopg

# Connect to the local PostgreSQL server
conn = psycopg.connect(
    host='localhost',
    port=5432,
    database='postgres',
    user='postgres',
    password='your_password'  # Replace with your PostgreSQL password
)

cursor = conn.cursor()
print("Connection successful!")

conn.close()

If you're connecting to a remote PostgreSQL server (e.g., on AWS RDS or Heroku), use the full hostname:

conn = psycopg.connect(
    host='mydb.c5ijnxgv4gb2.us-east-1.rds.amazonaws.com',
    port=5432,
    database='myapp',
    user='admin',
    password='secure_password'
)

A more Pythonic approach is to use a connection string (DSN format):

conn = psycopg.connect('postgresql://postgres:password@localhost:5432/postgres')

Creating a Table and Inserting Data

Here's a complete example that creates a users table and inserts records:

import psycopg

conn = psycopg.connect('postgresql://postgres:password@localhost:5432/postgres')
cursor = conn.cursor()

# Create a table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) UNIQUE,
        age INTEGER,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
''')

# Insert a record
cursor.execute(
    'INSERT INTO users (name, email, age) VALUES (%s, %s, %s)',
    ('Alice Johnson', '[email protected]', 30)
)

# Insert multiple records efficiently
users_data = [
    ('Bob Smith', '[email protected]', 28),
    ('Charlie Davis', '[email protected]', 35),
]
cursor.executemany(
    'INSERT INTO users (name, email, age) VALUES (%s, %s, %s)',
    users_data
)

conn.commit()
print("Data inserted successfully!")

conn.close()

Notice that PostgreSQL uses %s placeholders (not ? like SQLite3) and SERIAL for auto-incrementing integers. The executemany() method efficiently inserts many rows at once—much faster than looping and calling execute() repeatedly.

Querying and Fetching Results

Retrieve data with SELECT queries and fetch results using the same methods as SQLite3:

import psycopg

conn = psycopg.connect('postgresql://postgres:password@localhost:5432/postgres')
cursor = conn.cursor()

# Fetch all rows
cursor.execute('SELECT id, name, email, age FROM users WHERE age > %s', (25,))
for row in cursor.fetchall():
    print(f"ID: {row[0]}, Name: {row[1]}, Email: {row[2]}, Age: {row[3]}")

# Fetch one row
cursor.execute('SELECT name, email FROM users WHERE name = %s', ('Alice Johnson',))
user = cursor.fetchone()
if user:
    print(f"Found: {user[0]} ({user[1]})")

# Iterate over results without fetchall() — memory-efficient
cursor.execute('SELECT * FROM users ORDER BY created_at DESC LIMIT 10')
for row in cursor:
    print(row)

conn.close()

PostgreSQL (unlike SQLite3) lets you iterate directly over the cursor in a for loop, which fetches rows on demand—ideal for large result sets that don't fit in memory.

PostgreSQL vs. SQLite3: Key Differences

Feature	SQLite3	PostgreSQL
Placeholder syntax	?	%s
Auto-increment	INTEGER PRIMARY KEY	SERIAL or BIGSERIAL
Server model	File-based (embedded)	Client-server (separate process)
Connection string	File path	postgresql://user:pass@host:port/db
Concurrent writes	Limited (file locks)	Handles thousands simultaneously
Data types	NULL, INTEGER, REAL, TEXT, BLOB	30+ types: JSONB, arrays, UUID, etc.
Transaction isolation	Basic	SERIALIZABLE, REPEATABLE READ, etc.

A common gotcha: SQLite3 uses ?, PostgreSQL uses %s. Mixing them causes ProgrammingError.

Managing Connections Safely

Always close connections when done to release server resources. The with statement is the safest approach:

import psycopg

with psycopg.connect('postgresql://postgres:password@localhost:5432/postgres') as conn:
    with conn.cursor() as cursor:
        cursor.execute('SELECT COUNT(*) FROM users')
        count = cursor.fetchone()[0]
        print(f"Total users: {count}")
    # Automatically commits when exiting the inner with block
# Automatically closes the connection when exiting the outer with block

This ensures connections are closed even if an exception occurs.

Key Takeaways

• PostgreSQL is a full-featured relational database server; psycopg3 is the recommended Python adapter.
• Connect using a DSN string: postgresql://user:pass@host:port/database.
• PostgreSQL uses %s placeholders for safe query parameterization (not ?).
• Use SERIAL for auto-incrementing IDs and executemany() for bulk inserts.
• Always use with statements to ensure connections are closed safely.
• PostgreSQL supports advanced features (JSONB, arrays, custom types) that SQLite3 lacks.

Frequently Asked Questions

What is the default PostgreSQL password?

During installation, PostgreSQL creates a superuser postgres with no password (on some systems) or requires you to set one. If you're unsure, reset it:

sudo -u postgres psql
ALTER USER postgres WITH PASSWORD 'your_new_password';
\q

How do I check if PostgreSQL is running?

On Linux: sudo systemctl status postgresql. On macOS: brew services list. On Windows: Check Task Manager or run pg_isready in the terminal.

What is the difference between execute() and executemany()?

execute() runs a single query; executemany() runs the same query multiple times with different parameters. executemany() is much faster for bulk inserts because it sends all data to the server in one batch.

Can I use environment variables to store the database password?

Yes, and you should. Never hardcode passwords:

import os
import psycopg

password = os.environ.get('PG_PASSWORD')
conn = psycopg.connect(
    host='localhost',
    database='myapp',
    user='postgres',
    password=password
)

Then set the environment variable before running: export PG_PASSWORD=your_password.

Does PostgreSQL support transactions like SQLite3?

Yes, and better. PostgreSQL supports multiple isolation levels (READ COMMITTED, SERIALIZABLE, etc.) and is designed for concurrent transactions. Commit and rollback work the same way as SQLite3.

Further Reading

• PostgreSQL Official Documentation
• Psycopg3 User's Guide
• PostgreSQL Data Types
• How to Install PostgreSQL