PostgreSQL + Python (psycopg v3) — Basic Examples

This mini-project contains small, easy-to-read scripts that demonstrate the most common database operations with PostgreSQL using psycopg 3.

1) Prerequisites

• Python 3.10+ (recommended: 3.11+)
• A running PostgreSQL server
• A database and a role/user you can connect with

2) Setup (venv)

python3 -m venv .venv
source .venv/bin/activate
pip install --upgrade pip
pip install -r requirements.txt

3) Configuration (.env)

Copy .env.example to .env and edit the values:

cp .env.example .env

The scripts read connection settings from environment variables:

• PGHOST, PGPORT, PGDATABASE, PGUSER, PGPASSWORD

4) Run the examples

Run them in order:

python 00_check_connection.py
python 01_create_schema.py
python 02_insert_rows.py
python 03_select_rows.py
python 04_update_rows.py
python 05_delete_rows.py
python 06_transactions.py
python 07_pooling.py

5) What these scripts do

• 00_check_connection.py: tests connectivity and prints server info.
• 01_create_schema.py: creates a table with constraints.
• 02_insert_rows.py: inserts demo rows (safe parameters).
• 03_select_rows.py: reads rows and prints them.
• 04_update_rows.py: updates rows.
• 05_delete_rows.py: deletes rows.
• 06_transactions.py: shows commit/rollback behavior.
• 07_pooling.py: simple connection pool usage.

Notes

• These examples are intentionally minimal and verbose.
• In real apps, avoid hardcoding credentials and use secrets management.
• Always use query parameters (%s) — never string concatenation — to avoid SQL injection.