PostgreSQL must be running locally or remotely before connecting from Python. Verify with
psql -U postgres -c "SELECT version();"Install psycopg2
Install the PostgreSQL Python driver
# Install psycopg2 (pre-compiled binary, easiest on Windows):
C:\> pip install psycopg2-binary
Successfully installed psycopg2-binary-2.9.9
# Or psycopg3 (modern async-ready version):
C:\> pip install "psycopg[binary]"
Use
psycopg2-binary on Windows to avoid compiling from source. The binary wheel includes all dependencies.Connect
Connect Python to PostgreSQL
import psycopg2
# Connect to local PostgreSQL:
conn = psycopg2.connect(
host="localhost",
port=5432,
database="mydb",
user="postgres",
password="your_password"
)
# Or with a connection string:
conn = psycopg2.connect("postgresql://postgres:password@localhost:5432/mydb")
Run queries
Execute SQL queries from Python
import psycopg2
conn = psycopg2.connect(host="localhost", database="mydb", user="postgres", password="pw")
cur = conn.cursor()
# Create a table:
cur.execute("""
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE
)
""")
# Insert with parameters (safe from SQL injection):
cur.execute("INSERT INTO users (name, email) VALUES (%s, %s)", ("Alice", "alice@example.com"))
conn.commit()
# Query rows:
cur.execute("SELECT id, name, email FROM users")
rows = cur.fetchall()
for row in rows:
print(row)
cur.close()
conn.close()
Best practice
Store credentials in environment variables
Never hardcode passwords in your Python code. Use environment variables instead:
C:\> setx PGPASSWORD "your_password"
C:\> setx PGUSER "postgres"
C:\> setx PGDATABASE "mydb"
import os, psycopg2
conn = psycopg2.connect(
host=os.environ.get("PGHOST", "localhost"),
database=os.environ["PGDATABASE"],
user=os.environ["PGUSER"],
password=os.environ["PGPASSWORD"]
)
FAQ
Python + PostgreSQL questions
psycopg2 installation fails on Windows
Use
pip install psycopg2-binary instead of pip install psycopg2. The binary version includes precompiled C extensions and does not require Visual Studio or PostgreSQL development headers to install.psycopg2 vs psycopg3 — which to use?
psycopg3 (installed as
psycopg[binary]) is the modern replacement with native async/await support, better type handling and active development. Use psycopg3 for new projects. Use psycopg2 if you need compatibility with existing code or libraries.Connection works in psql but fails in Python
Double-check the host, port, database, user and password parameters. Note that psql defaults to your Windows username if user is not specified, while Python requires explicit credentials. Also verify the pg_hba.conf has a rule allowing connections from localhost.
Can I use SQLAlchemy with PostgreSQL on Windows?
Yes. Install:
pip install sqlalchemy psycopg2-binary. Connection string: postgresql+psycopg2://postgres:password@localhost:5432/mydb. SQLAlchemy works identically on Windows and Linux.