Databases for
Python Web Developers
A hands-on introduction to PostgreSQL & SQLite for Flask, Django, and FastAPI
projects.
PostgreSQL SQLite Python 3 20–30 min
1 Choosing a DBMS SQLite vs PostgreSQL — which to pick and why
2 Installation Platform-specific commands for all OSes
3 Basic DB Administration psql, create database, users & privileges
4 Creating Tables Schema design, PKs, FKs, constraints
5 Connecting from Python psycopg2, .env files, CRUD with safe queries
6 Best Practices Pooling, ORMs, error handling, security
7 Next Steps Mini project, migrations, indexing, transactions
Databases for Python Web Developers · Page 1
CHAPTER 1
Choosing a DBMS
A DBMS (Database Management System) stores your app's data in an organised, queryable way. Two
excellent choices for Python beginners are SQLite and PostgreSQL.
Feature SQLite PostgreSQL
Setup None (file-based) Install a server process
Python driver Built-in sqlite3 Install psycopg2
Multiple users Limited Full concurrent access
Production-ready Small/embedded apps only Yes, at any scale
Django default ✓ Yes Common in production
■ Recommendation: Start with PostgreSQL. It runs locally just fine, and you won't need to switch
databases when you deploy to production. This tutorial covers both where they differ.
Databases for Python Web Developers · Page 2
CHAPTER 2
Installation
Linux (Ubuntu / Debian)
bash
# Update package index and install PostgreSQL
sudo apt update
sudo apt install -y postgresql postgresql-contrib
# Start the service and enable on boot
sudo systemctl start postgresql
sudo systemctl enable postgresql
# Verify installation
psql --version
# Expected output: psql (PostgreSQL) 15.x
macOS
bash
brew update
brew install postgresql@16
# Add to your shell PATH (add to ~/.zshrc or ~/.bash_profile too)
export PATH="/opt/homebrew/opt/postgresql@16/bin:$PATH"
# Start the service
brew services start postgresql@16
# Verify
psql --version
Windows
PowerShell
# Option 1: Download the official installer from:
# [Link]
# Run the .exe and follow the setup wizard.
Databases for Python Web Developers · Page 3
# Note the password you set for the "postgres" superuser.
# Option 2: Use winget
winget install [Link]
# Verify (open a new terminal after install)
psql --version
■ On Windows, PostgreSQL runs as a background service automatically after install. Manage it
from Services (Win+R → [Link]).
SQLite — no installation needed
SQLite ships with Python 3. Verify it's available:
Python
python3 -c "import sqlite3; print(sqlite3.sqlite_version)"
# Expected: 3.x.x
Databases for Python Web Developers · Page 4
CHAPTER 3
Basic Database Administration
3a. Access the command line
bash
# PostgreSQL — connect as the default superuser
# Linux/macOS:
sudo -u postgres psql
# Windows (open the SQL Shell shortcut, or):
psql -U postgres
# SQLite — create / open a database file
sqlite3 my_web_app.db
3b. Create a new database
PostgreSQL SQL
-- Inside psql:
CREATE DATABASE my_web_app;
-- Verify it was created
\l
-- Connect to it
\c my_web_app
■ SQLite creates the database file automatically when you open it with sqlite3 my_web_app.db —
no explicit CREATE DATABASE needed.
3c. Create a user and grant privileges (PostgreSQL only)
Never use the superuser (postgres) for your application. Create a dedicated user:
PostgreSQL SQL
-- Still inside psql, connected as postgres superuser
-- Create a user with a strong password
CREATE USER webapp_user WITH PASSWORD 'your_strong_password_here';
Databases for Python Web Developers · Page 5
-- Grant full access to the new database
GRANT ALL PRIVILEGES ON DATABASE my_web_app TO webapp_user;
-- In PostgreSQL 15+, also grant schema access:
\c my_web_app
GRANT ALL ON SCHEMA public TO webapp_user;
-- Exit psql
\q
■■ Replace 'your_strong_password_here' with an actual strong password. You'll store this in a
.env file — never in your source code.
Databases for Python Web Developers · Page 6
CHAPTER 4
Creating Tables
Let's design a small schema for a blog web app: a users table and a posts table, with a foreign key
relationship between them.
Schema overview
Table Column Type Notes
users id SERIAL / INTEGER Primary key, auto-increment
username VARCHAR(50) NOT NULL, UNIQUE
email VARCHAR(255) NOT NULL, UNIQUE
password_hash VARCHAR(255) NOT NULL
created_at TIMESTAMP Defaults to now
posts id SERIAL / INTEGER Primary key, auto-increment
user_id INTEGER FK → [Link]
title VARCHAR(255) NOT NULL
body TEXT NOT NULL
created_at TIMESTAMP Defaults to now
PostgreSQL SQL
SQL
-- Connect to your database first: \c my_web_app
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
Databases for Python Web Developers · Page 7
title VARCHAR(255) NOT NULL,
body TEXT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);
SQLite equivalent
SQL
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
title TEXT NOT NULL,
body TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT (datetime('now')),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
■ Key concepts: PRIMARY KEY uniquely identifies each row. REFERENCES creates a foreign
key. NOT NULL prevents empty values. UNIQUE prevents duplicates. ON DELETE CASCADE
deletes a user's posts when the user is deleted.
Databases for Python Web Developers · Page 8
CHAPTER 5
Connecting from Python
5a. Install the driver
bash
# For PostgreSQL:
pip install psycopg2-binary python-dotenv
# For SQLite — no install needed, it's in the standard library
5b. Store credentials in a .env file
Create a file called .env in your project root. Add .env to your .gitignore immediately.
.env
DB_HOST=localhost
DB_PORT=5432
DB_NAME=my_web_app
DB_USER=webapp_user
DB_PASSWORD=your_strong_password_here
5c. Connect to the database
Python — PostgreSQL
import os
import psycopg2
from dotenv import load_dotenv
load_dotenv() # reads .env into [Link]
def get_connection():
return [Link](
host=[Link]("DB_HOST"),
port=[Link]("DB_PORT"),
dbname=[Link]("DB_NAME"),
user=[Link]("DB_USER"),
password=[Link]("DB_PASSWORD"),
Databases for Python Web Developers · Page 9
)
Python — SQLite
import sqlite3
def get_connection():
conn = [Link]("my_web_app.db")
conn.row_factory = [Link] # allows dict-like access to rows
return conn
5d. Create tables from Python
Python
def create_tables():
conn = get_connection()
try:
with [Link]() as cur:
[Link]("""
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
""")
[Link]("""
CREATE TABLE IF NOT EXISTS posts (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
title VARCHAR(255) NOT NULL,
body TEXT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
""")
[Link]()
print("Tables created successfully.")
Databases for Python Web Developers · Page 10
finally:
[Link]()
create_tables()
Databases for Python Web Developers · Page 11
5e. CRUD operations with parameterised queries
■■ Always use parameterised queries (%s for psycopg2, ? for sqlite3). Never format user input
directly into SQL strings — that's how SQL injection attacks happen.
Python — INSERT
def create_user(username, email, password_hash):
conn = get_connection()
try:
with [Link]() as cur:
[Link](
"INSERT INTO users (username, email, password_hash) "
"VALUES (%s, %s, %s) RETURNING id",
(username, email, password_hash)
user_id = [Link]()[0]
[Link]()
return user_id
finally:
[Link]()
Python — SELECT
def get_user_by_email(email):
conn = get_connection()
try:
with [Link]() as cur:
[Link](
"SELECT id, username, email FROM users WHERE email = %s",
(email,)
return [Link]() # returns None if not found
finally:
[Link]()
Python — UPDATE
def update_username(user_id, new_username):
conn = get_connection()
Databases for Python Web Developers · Page 12
try:
with [Link]() as cur:
[Link](
"UPDATE users SET username = %s WHERE id = %s",
(new_username, user_id)
[Link]()
finally:
[Link]()
Python — DELETE
def delete_user(user_id):
conn = get_connection()
try:
with [Link]() as cur:
[Link]("DELETE FROM users WHERE id = %s", (user_id,))
[Link]()
finally:
[Link]()
Databases for Python Web Developers · Page 13
CHAPTER 6
Best Practices for Backend Web Development
Connection pooling
Opening a new database connection on every request is expensive. A connection pool keeps a set of
connections ready to reuse:
Python
from psycopg2 import pool
connection_pool = [Link](
minconn=1,
maxconn=10,
host=[Link]("DB_HOST"),
dbname=[Link]("DB_NAME"),
user=[Link]("DB_USER"),
password=[Link]("DB_PASSWORD"),
def get_connection():
return connection_pool.getconn()
def release_connection(conn):
connection_pool.putconn(conn)
Keeping credentials out of source code
.gitignore
# Add this to your .gitignore
.env
*.db # SQLite database files too
■ For deployment, use your hosting platform's environment variable settings (Railway, Render,
Heroku config vars) instead of uploading a .env file.
ORM vs raw SQL
Raw SQL ORM (SQLAlchemy / Django ORM)
Databases for Python Web Developers · Page 14
Fine-grained control over queries Larger apps with many models
Complex or performance-critical queries Reduces repetitive INSERT/SELECT boilerplate
Learning how databases work Handles migrations and relationships
Lightweight scripts and small projects Team projects benefiting from consistency
Common pattern: learn raw SQL first (it makes ORMs much easier to understand), then adopt an ORM
when your project grows.
Error handling with try/except
Python
import psycopg2
from psycopg2 import errors
def create_user(username, email, password_hash):
conn = get_connection()
try:
with [Link]() as cur:
[Link](
"INSERT INTO users (username, email, password_hash) "
"VALUES (%s, %s, %s) RETURNING id",
(username, email, password_hash)
user_id = [Link]()[0]
[Link]()
return user_id
except [Link]:
[Link]()
raise ValueError("A user with that email or username already exists.")
except [Link] as e:
[Link]()
raise RuntimeError(f"Database error: {e}") from e
finally:
[Link]()
Databases for Python Web Developers · Page 15
CHAPTER 7
Next Steps
Congratulations — you now have a solid foundation for using databases in Python web apps! Here's a mini
project to cement your skills, and topics to explore next.
Mini project: user registration system
Build a Flask (or FastAPI) app that lets users register, log in, and post messages. A minimal scaffold to get
started:
Python
from flask import Flask, request, jsonify
import bcrypt
app = Flask(__name__)
@[Link]("/register")
def register():
data = request.get_json()
hashed = [Link](data["password"].encode(), [Link]()).decode()
user_id = create_user(data["username"], data["email"], hashed)
return jsonify({"id": user_id, "username": data["username"]}), 201
@[Link]("/users/<int:user_id>")
def get_user(user_id):
user = get_user_by_id(user_id)
if not user:
return jsonify({"error": "Not found"}), 404
return jsonify(dict(user))
if __name__ == "__main__":
create_tables()
[Link](debug=True)
Topics to learn next
Topic Why it matters Where to start
Migrations Safely evolve your schema over time without losing
Alembic
data (SQLAlchemy) or Django migrations
Indexing Speed up queries on large tables dramatically CREATE INDEX in the SQL docs
Databases for Python Web Developers · Page 16
Transactions Guarantee that related operations all succeed or BEGIN
all fail / COMMIT / ROLLBACK
SQLAlchemy ORM Reduce boilerplate, handle relationships [Link]
DB Backups Don't lose production data pg_dump for PostgreSQL
You've got this! The best way to cement these skills is to build something real. Pick a small web
app idea — a todo list, a blog, a recipe book — and work through it using everything in this tutorial.
The concepts will stick much faster with a real use case driving them.
Databases for Python Web Developers · Page 17