4. Your first PostgreSQL connection in 5 minutes

Before any theory, proof. You'll install PostgreSQL, create a database, connect from Python, and run a real query. Five minutes from now you'll have a running server and a script that talks to it.

Install PostgreSQL

PostgreSQL installation varies by operating system.

macOS (using Homebrew):

brew install postgresql@15
brew services start postgresql@15

Ubuntu / Debian:

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

Windows: download the installer from postgresql.org/download/windows and run it. Accept all defaults during installation.

PostgreSQL now runs as a background service. Unlike SQLite (where you open a file), PostgreSQL is a server that's always running, waiting for connections.

Cloud alternative: skip local installation

Don't want to install PostgreSQL locally? Use a free tier from Railway, Render, or Supabase. These provide PostgreSQL databases accessible over the internet. You'll get connection credentials (host, database name, username, password) to plug into the Python code below.

Install psycopg2

Install psycopg2, the PostgreSQL adapter for Python:

pip install psycopg2-binary python-dotenv

The -binary suffix installs precompiled binaries, which avoids compilation issues on some systems. It's great for learning and local development. In production, many teams prefer psycopg2 built against system libraries. The python-dotenv package lets you load credentials from a .env file rather than hard-coding them.

Your first connection

First, create a database and a user for this book's projects. This avoids relying on default accounts like postgres. Open a psql session as the superuser and run:

psql -U postgres

Then, inside psql:

CREATE USER weather_user WITH PASSWORD 'change_me';
CREATE DATABASE weather_db OWNER weather_user;
\q

Verify you can connect with the new credentials. You should be prompted for the password you set:

psql -h localhost -U weather_user -d weather_db -c "SELECT 1;"

Next, create a .env file in your project folder so your credentials stay out of your code:

DB_HOST=localhost
DB_PORT=5432
DB_NAME=weather_db
DB_USER=weather_user
DB_PASSWORD=change_me

Now create first_postgres.py at the project root:

import os

import psycopg2
from dotenv import load_dotenv

load_dotenv()

conn = psycopg2.connect(
    host=os.getenv("DB_HOST", "localhost"),
    port=int(os.getenv("DB_PORT", "5432")),
    dbname=os.getenv("DB_NAME"),
    user=os.getenv("DB_USER"),
    password=os.getenv("DB_PASSWORD"),
)

with conn:
    with conn.cursor() as cur:
        cur.execute("SELECT version();")
        print("PostgreSQL version:")
        print(cur.fetchone()[0])

Run it from the project root:

python first_postgres.py

You'll see output like:

PostgreSQL version:
PostgreSQL 15.x on x86_64...

Green. You connected to PostgreSQL from Python and executed your first query.

If something failed, it's usually one of four things: the PostgreSQL server isn't running, the port is wrong, the username or password is wrong, or the database name doesn't exist. Verify psql connects with your credentials first; if that works, your Python code will too.

What just happened

Five moving parts. Each one shows up in every script you write from here on:

• Connection. psycopg2.connect() establishes a TCP connection to the PostgreSQL server. You provide host, database name, username, and password.
• Cursor. A cursor executes SQL commands and retrieves results. Think of it as a channel for sending queries.
• Execute. cursor.execute() sends SQL to the server. The server processes it and stores results.
• Fetch. fetchone() retrieves one row of results. Use fetchall() for all rows.
• Cleanup. Always close cursors and connections when done. The with statements in the script handle this for you.

Compared to SQLite

SQLite opens a file on disk:

conn = sqlite3.connect('database.db')  # Opens a file

PostgreSQL connects to a running server over the network:

conn = psycopg2.connect(host='localhost', dbname='weather_db', user='weather_user', ...)  # Connects to a server

This architectural difference is what enables PostgreSQL's concurrent access, but it requires server configuration that SQLite doesn't.

Create your first table

Let's create a simple table and insert data. Save this as first_table.py at the project root:

import os

import psycopg2
from dotenv import load_dotenv

load_dotenv()

conn = psycopg2.connect(
    host=os.getenv("DB_HOST", "localhost"),
    port=int(os.getenv("DB_PORT", "5432")),
    dbname=os.getenv("DB_NAME"),
    user=os.getenv("DB_USER"),
    password=os.getenv("DB_PASSWORD"),
)

with conn:
    with conn.cursor() as cur:
        # Create table
        cur.execute(
            """
            CREATE TABLE IF NOT EXISTS users (
                id SERIAL PRIMARY KEY,
                name VARCHAR(100),
                email VARCHAR(100)
            )
            """
        )

        # Insert data (psycopg2 uses %s placeholders)
        cur.execute(
            "INSERT INTO users (name, email) VALUES (%s, %s)",
            ("Alice", "alice@example.com"),
        )

        # Query data
        cur.execute("SELECT id, name, email FROM users ORDER BY id;")
        users = cur.fetchall()

print("Users in database:")
for user_id, name, email in users:
    print(f"  ID: {user_id}, Name: {name}, Email: {email}")

Run it from the project root:

python first_table.py

You'll see:

Users in database:
  ID: 1, Name: Alice, Email: alice@example.com

PostgreSQL is working. You've created a table, inserted data, and queried it back.

Three key differences from SQLite

Three things in first_table.py would have looked different in SQLite, and the differences are the most common source of bugs when porting code:

• Connection strings need credentials. SQLite: sqlite3.connect('file.db') opens a file. PostgreSQL: psycopg2.connect(host=..., dbname=..., user=..., password=...) connects to a server with authentication.
• Placeholder syntax changes. SQLite uses ? for placeholders ("INSERT INTO users VALUES (?, ?)"). PostgreSQL uses %s ("INSERT INTO users VALUES (%s, %s)"). This is the single most common migration bug.
• Explicit commits required. SQLite auto-commits by default. PostgreSQL requires explicit conn.commit() after INSERT/UPDATE/DELETE. The with conn: block in first_table.py commits for you on clean exit; without that pattern, your changes vanish when the connection closes.

You've proved PostgreSQL works. Next, you'll meet the architecture behind what you just did, the connection patterns the rest of the chapter depends on, and how to keep credentials out of source control before you start migrating real data.