Python and Databases: How One Language Talks to PostgreSQL, MySQL, and SQLite

You've written a Python script that generates data. Now what? If you're dumping it to a CSV file in 2024, you're living in the past. Python speaks to databases fluently — but here's the catch: each database speaks a slightly different dialect, and Python needs a translator for each.

Let's cut through the hype and see how Python actually gets data into PostgreSQL, MySQL, and SQLite — without the tutorial fluff.

The Connector Layer: Your Database's Phone Line

Every interaction between Python and a database works like this: your Python code sends SQL text through a "connector" library, the database executes it, and the response comes back. No magic, just network packets or file I/O.

Here's what you're actually installing:

• PostgreSQL: psycopg2 (or asyncpg for async). Both talk the PostgreSQL wire protocol.
• MySQL: mysql-connector-python or PyMySQL. MySQL changed hands with Oracle, so connectors have quirks.
• SQLite: sqlite3 — ships with Python. Zero installation. Life is good.

# SQLite — no dependencies required
import sqlite3
conn = sqlite3.connect("local.db")

# PostgreSQL — you installed psycopg2, right?
import psycopg2
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="app_user",
    password="hunter2"
)

# MySQL — watch out for driver differences
import mysql.connector
conn = mysql.connector.connect(
    host="localhost",
    database="mydb",
    user="app_user",
    password="hunter2"
)

The DB-API 2.0 Standard: One Ring to Rule Them All?

Python's PEP 249 defined a standard interface called DB-API 2.0. The idea: write once, run on any database. In practice? It works — with footnotes.

The standard API gives you: - conn.cursor() to execute queries - cursor.execute(sql, params) to run SQL safely - cursor.fetchone(), .fetchall(), .fetchmany() to get results - conn.commit() and conn.rollback() for transactions

Here's a real example that works across all three databases (with the right connector installed):

def insert_user(conn, name, email):
    cursor = conn.cursor()
    cursor.execute(
        "INSERT INTO users (name, email) VALUES (%s, %s)",
        (name, email)
    )
    conn.commit()

Notice %s placeholders? That's DB-API 2.0. PostgreSQL's psycopg2 expects %s. MySQL's connector expects %s too. SQLite also uses %s. But be warned: some older MySQL drivers used ? or %s interchangeably. Always check docs.

Where They Diverge: The Pain Points

1. Connection Strings

SQLite uses file paths. PostgreSQL and MySQL need host/port/auth. Your code structure changes immediately:

# SQLite: local file, always "online"
conn = sqlite3.connect(":memory:")  # temp database

# PostgreSQL: network service
conn = psycopg2.connect(
    "postgresql://user:pass@localhost:5432/mydb"
)

# MySQL: similar but different port
conn = mysql.connector.connect(
    host="localhost", port=3306, user="root"
)

Real talk: PostgreSQL's connection string format (libpq) is basically standard now. MySQL's connector is slightly different. Annoying when switching projects.

2. Transaction Handling

All three support transactions, but the defaults differ:

• SQLite: Auto-commits by default unless you call conn.isolation_level. Newbies lose data.
• PostgreSQL: Always in a transaction. Explicit conn.commit() required. You learn this the hard way.
• MySQL: Depends on storage engine. InnoDB (default) behaves like PostgreSQL. MyISAM doesn't support transactions at all.

# This code is wrong for PostgreSQL and InnoDB MySQL
cursor.execute("INSERT INTO logs (msg) VALUES ('hello')")
# Nothing saved until you do:
conn.commit()

3. Bulk Insert Performance

Inserting 100,000 rows? Here's where they differ dramatically:

# SQLite: wrap in transaction or die
conn.execute("BEGIN")
for row in data:
    cursor.execute("INSERT INTO t VALUES (?)", (row,))
conn.commit()  # 10x faster than auto-commit

# PostgreSQL: use executemany() or copy_from()
psycopg2.extras.execute_values(
    cursor, "INSERT INTO t VALUES %s", data
)  # Actually fast

# MySQL: executemany() works, but max_allowed_packet matters
cursor.executemany("INSERT INTO t VALUES (%s)", data)

Benchmark rule of thumb: PostgreSQL with copy_from() is fastest. SQLite is surprisingly competitive for small datasets. MySQL sits in the middle.

4. Datetime Handling

This catches everyone. Python datetime objects need to survive the round trip:

• PostgreSQL: Native timestamp support. Just works.
• MySQL: Connector converts to Python datetime if you set use_pure=False. Otherwise you get strings.
• SQLite: No native datetime type. Stores as text. You must convert manually.

from datetime import datetime
now = datetime.now()

# PostgreSQL — fine
cursor.execute("INSERT INTO events (ts) VALUES (%s)", (now,))

# SQLite — stores as text, retrieves as text
cursor.execute("INSERT INTO events (ts) VALUES (?)", (now.isoformat(),))

The ORM Escape Hatch (But It Costs You)

Tools like SQLAlchemy and Django ORM promise database-agnostic code. They deliver — at a price:

• You lose query control
• Debugging becomes hunting through layers
• Performance tuning is harder

# SQLAlchemy example — works on all three
from sqlalchemy import create_engine

engine = create_engine("sqlite:///local.db")
# engine = create_engine("postgresql://user:pass@localhost/db")
# engine = create_engine("mysql://user:pass@localhost/db")

The ORM handles dialect differences. But when your query is slow, you're looking at raw SQL logs anyway.

When to Use Each Database from Python

Don't be a purist. I've seen teams fighting with SQLite for a web app serving 100 requests/second. Use SQLite for what it's good at: single-user, local, or embedded scenarios. For anything networked, PostgreSQL is the modern default.

The Takeaway

Python's database support is pragmatic, not elegant. The DB-API 2.0 standard gives you 80% common ground, but the remaining 20% — connection management, performance tuning, and type handling — requires knowing which database you're talking to.

Install the right connector, learn the transaction semantics, and test on your actual database. SQLite in development, PostgreSQL in production? Fine — but test the schema migration early, because AUTOINCREMENT syntax differs, and that's the kind of bug that wastes hours.