SQLite carries a project surprisingly far, but the first time two users hit your app in the same second, a file on disk stops being enough. A Python MySQL or PostgreSQL connection is how real apps talk to a real database server. This guide walks the whole path: install the driver, connect, run safe parameterized queries, pool connections, and keep the password out of your code.

“One size fits all is an idea whose time has come and gone.”

Michael Stonebraker, Turing Award

Last Updated: July 2026 | Tested on: Python 3.14.6 | Difficulty: Intermediate | Reading Time: 17 minutes

SQLite is great when one person uses the app at a time. But the moment a real crowd shows up, such as a website, an Application Programming Interface (API), or a service several people hit at once, you need a database that lives on its own server and lets many users in together. MySQL and PostgreSQL are the two big names for that job. Both handle lots of readers and writers at the same time, copy themselves to backup servers, check who is allowed in, and do everything a production app needs.

Think of SQLite as the notebook on your own desk and MySQL or PostgreSQL as the shared filing room in an office. The notebook is fine for you alone. The filing room has a door, a key, and a clerk at the desk, so a whole team can fetch and file records without bumping into each other. That extra door and key is the only real difference in your Python code: you connect with a username and password over the network, and after that everything looks like the SQLite code you already wrote.

Here is the good news. The Python code is almost the same for both. You install a driver, connect with your login details, make a cursor, run parameterized SQL (Structured Query Language), and commit. The placeholder you put in your SQL is %s for both MySQL and PostgreSQL (SQLite used ? instead). Learn the steps once and you can talk to either database.

The diagram shows how your Python script reaches MySQL or PostgreSQL. Your code talks to a driver library (mysql-connector-python for MySQL, psycopg2 for PostgreSQL), the driver logs in to the database server over the network, sends your SQL, and brings the results back. The middle strip is the part that never changes: connect, make a cursor, run parameterized SQL, then commit or fetch, then close. It is the same shape you saw with SQLite, just with a login step and a network hop added. So everything you already know about cursors, parameterized queries, and transactions carries straight over.

Install and Verify the Driver

Python does not ship with a MySQL or PostgreSQL driver built in. You install the one you need. A driver is like the right charging cable for your phone: Python on one end, the database on the other, and nothing flows until the plug matches. Install only the driver for the database you actually use, not both.

📄 Terminal: install the database driver

# MySQL driver
pip install mysql-connector-python

# PostgreSQL driver
pip install psycopg2-binary

To check the install worked, import the driver and print its version. If this prints a version number instead of an error, you are ready.

📄 Terminal: confirm the driver imports

# MySQL
python -c "import mysql.connector; print(mysql.connector.__version__)"

# PostgreSQL
python -c "import psycopg2; print(psycopg2.__version__)"

What happened here: pip install downloads the driver from PyPI (the Python Package Index). The -binary in psycopg2-binary means it comes pre-compiled, so you skip needing a C compiler and the PostgreSQL development headers on your machine. The one-line import check is your “did it actually install” test. A clean version number means yes. An ImportError: No module named ... means the install landed in a different Python or virtual environment than the one you just ran.

Quick Win: Connect and Read the Version

The fastest way to know your setup works is to connect and ask the database what version it is running. It is the phone-call test: you dial, someone picks up and says their name, and now you know the line works. Four lines of login details, a cursor, one query, and you have proof the whole chain works: driver, network, login, and server. Here is the Python MySQL version first.

📄 connect_mysql.py: connect to MySQL and read its version

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your_password",
    database="myapp",
)
cursor = conn.cursor(dictionary=True)

cursor.execute("SELECT VERSION() AS version")
row = cursor.fetchone()
print(f"MySQL version: {row['version']}")

conn.close()

▶ Output (illustrative: needs a running MySQL server)

MySQL version: 8.4.5

Now the PostgreSQL version. Notice how little changes. The library name and a couple of words differ, the shape is the same.

📄 connect_pg.py: connect to PostgreSQL and read its version

import psycopg2
from psycopg2.extras import RealDictCursor

conn = psycopg2.connect(
    host="localhost",
    user="postgres",
    password="your_password",
    dbname="myapp",
)
cursor = conn.cursor(cursor_factory=RealDictCursor)

cursor.execute("SELECT version()")
row = cursor.fetchone()
print(f"PostgreSQL version: {row['version']}")

conn.close()

▶ Output (illustrative: needs a running PostgreSQL server)

PostgreSQL version: PostgreSQL 17.4 on x86_64-pc-linux-gnu, compiled by gcc 13.3.0, 64-bit

What happened here: Spot the differences and you have learned both APIs at once. The keyword is database= for MySQL and dbname= for PostgreSQL. To get rows back as dictionaries (so you can write row['version'] instead of row[0]), MySQL takes cursor(dictionary=True) while psycopg2 takes a RealDictCursor. The dictionary cursor is the small comfort that makes your code read like English: you ask for a column by name, not by guessing its position. Everything else, connect then cursor then execute then fetch then close, is identical.

Core Concepts: CRUD With One Pattern

CRUD is the four things every app does with data: Create, Read, Update, Delete. The big idea here is that you never glue user values into your SQL string by hand. You leave a blank, a placeholder, and you hand the real values to execute() in a separate tuple. The driver fills the blanks safely. This is what stops SQL injection, and it is the single most important habit in any Python MySQL or PostgreSQL codebase.

Think of a placeholder like the blank line on a paper form: “Name: ______”. The form (your SQL) is fixed and trusted. The visitor only ever writes inside the blank, never on the rest of the page. So even if someone writes something nasty in the name field, it stays trapped in the blank and cannot rewrite your query. In the examples below, imagine a user named Rahul signing up to your app: his name, email, and age are the values that fill the blanks.

📄 crud_pattern.py: parameterized CRUD (same code for MySQL and PostgreSQL)

# This pattern works for BOTH MySQL and PostgreSQL.
# The placeholder is %s for both (SQLite used ? instead).
# conn and cursor come from the connect step shown earlier.

# CREATE: leave blanks (%s), pass the real values as a tuple
cursor.execute(
    "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)",
    ("Rahul", "rahul@example.com", 28),
)
conn.commit()  # nothing is saved until you commit

# READ: the value 25 fills the blank; users is a list of rows
cursor.execute("SELECT * FROM users WHERE age > %s", (25,))
users = cursor.fetchall()
for user in users:
    print(f"{user['name']} ({user['email']})")

# UPDATE
cursor.execute(
    "UPDATE users SET email = %s WHERE name = %s",
    ("rahul.new@example.com", "Rahul"),
)
conn.commit()

# DELETE
cursor.execute("DELETE FROM users WHERE name = %s", ("Rahul",))
conn.commit()

You cannot run that snippet on its own because it needs a live server and an existing users table. So here is the exact same logic in a self-contained form you can run right now: Rahul signs up along with a second user named Niranjan, and we read back only the older one. It uses Python’s built-in sqlite3 (no install, no server) to prove the pattern end to end. The only thing that changes for MySQL or PostgreSQL is the placeholder: swap ? for %s.

📄 crud_demo.py: the same flow you can run today (sqlite3, stdlib)

import sqlite3

conn = sqlite3.connect(":memory:")   # a throwaway database in RAM
conn.row_factory = sqlite3.Row       # rows you can read by column name
cursor = conn.cursor()
cursor.execute(
    "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT, age INTEGER)"
)

# CREATE (sqlite placeholder is ? ; MySQL and PostgreSQL use %s)
cursor.execute("INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
               ("Rahul", "rahul@example.com", 28))
cursor.execute("INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
               ("Niranjan", "niranjan@example.com", 24))
conn.commit()

# READ: only people older than 25
cursor.execute("SELECT * FROM users WHERE age > ?", (25,))
for user in cursor.fetchall():
    print(f"{user['name']} ({user['email']})")

conn.close()

▶ Output

Rahul (rahul@example.com)

What happened here: Two users go in, but only Rahul comes back, because Niranjan is 24 and the filter asked for age > 25. The value 25 was never pasted into the SQL text. It travelled separately in the tuple (25,) and the driver dropped it into the blank for you. Notice the trailing comma in (25,). That comma is what makes it a one-item tuple. Without it, (25) is just the number 25 in parentheses, and the driver will complain that it cannot line up your parameters. The other habit worth burning in: changes are not saved until conn.commit(). Forget the commit and your insert quietly vanishes when the connection closes.

Connection Pooling

Opening a database connection is slow, whether it is a Python MySQL link or a PostgreSQL one. There is a network round trip, a login check, and some setup, every single time. If your web app opens a fresh connection for every page view, that cost adds up fast. A connection pool fixes this by opening a small set of connections once, up front, and lending them out as requests come in. When you are done with one, it goes back into the pool ready for the next request, instead of being thrown away.

It is like a library with five copies of a popular book. The library buys them once. You borrow a copy, read it, and return it so the next person can take it out. Nobody buys a brand new book every time they want to read a chapter. With pool_size=5 you have five copies. One warning: if a sixth borrower shows up while all five are out, mysql.connector does not make them wait in line, it raises a PoolError right away. Size the pool for your real traffic and return connections promptly.

📄 pool.py: borrow and return connections instead of making new ones

# MySQL connection pool
from mysql.connector import pooling

# Create the pool ONCE, when your app starts up
pool = pooling.MySQLConnectionPool(
    pool_name="mypool",
    pool_size=5,
    host="localhost",
    user="root",
    password="your_password",
    database="myapp",
)

# Borrow a connection from the pool (per request)
conn = pool.get_connection()
cursor = conn.cursor(dictionary=True)
cursor.execute("SELECT COUNT(*) AS total FROM users")
print(cursor.fetchone())
conn.close()  # returns the connection to the pool, does NOT destroy it

▶ Output (illustrative: needs a running MySQL server)

{'total': 2}

What happened here: The surprise for most people is the last line. Calling conn.close() on a pooled connection does not actually close it. It hands the connection back to the pool so the next request can grab it. You create the pool one time at startup, then borrow and return for each piece of work. That is the whole trick, and it is why a pooled app stays fast under load while a connect-every-time app crawls.

Keep Credentials Out of Your Code

Your database password should never sit in your Python MySQL script, or any script. The moment you type it in code and push to git, the password is in your history forever, visible to anyone with access to the repo. The fix is simple: read the password from an environment variable at run time. The code knows the name of the secret, never the secret itself.

Picture a hotel room safe. The instructions taped inside say “enter your PIN”. The instructions are public, anyone can read them, but the PIN lives only in your head. Environment variables work the same way. Your code says “go fetch DB_PASSWORD“, and the actual value lives outside the code, in a .env file you never commit or in your deployment platform’s secrets. If you want the fuller picture on secrets, input validation, and dependency scanning, the Python security basics guide covers it all in one place.

📄 secure_connect.py: read the login from the environment

import os
import psycopg2

conn = psycopg2.connect(
    host=os.environ["DB_HOST"],
    user=os.environ["DB_USER"],
    password=os.environ["DB_PASSWORD"],
    dbname=os.environ["DB_NAME"],
)
# Set these in a .env file (kept out of git) or in your CI/CD secrets.
# NEVER commit passwords to git.

Here is the credential-loading part on its own, runnable, with the values printed instead of used to connect. It also shows what happens when a variable is missing, so the error does not surprise you later.

📄 env_demo.py: load config from the environment safely

import os

# In real life these come from a .env file or CI/CD secrets.
# We set a few here just so the demo runs on its own.
os.environ.setdefault("DB_HOST", "localhost")
os.environ.setdefault("DB_USER", "appuser")
os.environ.setdefault("DB_NAME", "myapp")

config = {
    "host": os.environ["DB_HOST"],
    "user": os.environ["DB_USER"],
    "dbname": os.environ["DB_NAME"],
}
print(config)

# What if a required variable was never set?
try:
    password = os.environ["DB_PASSWORD"]
except KeyError as exc:
    print(f"KeyError: {exc}")

▶ Output

{'host': 'localhost', 'user': 'appuser', 'dbname': 'myapp'}
KeyError: 'DB_PASSWORD'

What happened here: The three set variables read back fine. The fourth, DB_PASSWORD, was never set, so os.environ["DB_PASSWORD"] raised KeyError: 'DB_PASSWORD'. That is actually the behaviour you want in production: a missing secret should stop the app loudly at startup, not let it run half configured and fail in some confusing way later. If you would rather supply a fallback than crash, use os.environ.get("DB_PASSWORD"), which returns None instead of raising when the key is absent.

Common Mistakes

Mistake 1: Hardcoding the password in source code

🚫 Wrong

# Password in code means password in git means a leak waiting to happen
conn = psycopg2.connect(password="super_secret_password")

✅ Correct

# Read the secret from the environment, never write it down in code
conn = psycopg2.connect(password=os.environ["DB_PASSWORD"])

Why: Anything you commit to git stays in the history even after you delete it. A password in code is a password anyone with repo access can read. Keep it in an environment variable or a .env file that git ignores.

Mistake 2: Building SQL with f-strings

🚫 Wrong

# The user's input becomes part of the SQL. This is how SQL injection happens.
name = request.args["name"]
cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")

✅ Correct

# The value goes in a separate tuple; the driver fills the blank safely.
name = request.args["name"]
cursor.execute("SELECT * FROM users WHERE name = %s", (name,))

Why: With the f-string, a visitor who types ' OR '1'='1 in the name field can read your whole table. With a placeholder, that text is treated as a plain value to match, not as SQL. Parameterized queries are not a style choice, they are your defence.

Mistake 3: Forgetting to commit

🚫 Wrong

cursor.execute("INSERT INTO users (name) VALUES (%s)", ("Viraj",))
# ...connection closes here. The insert is gone. No error, no row.

✅ Correct

cursor.execute("INSERT INTO users (name) VALUES (%s)", ("Viraj",))
conn.commit()   # now the row is really saved

Why: MySQL and PostgreSQL wrap your writes in a transaction. Until you call conn.commit(), those changes are pending and get rolled back when the connection closes. The silent part is what hurts: no error, just no row for the new user Viraj you thought you saved. Read queries do not need a commit, only writes (insert, update, delete) do.

Try It Yourself

Three quick exercises. Do each one on an in-memory sqlite3 database first (no server needed), then switch the placeholder from ? to %s and point the same logic at a real MySQL or PostgreSQL server.

1. Insert a team, then read it back. Create a users table, insert three users named Rahul (28), Niranjan (24), and Viraj (31), then run a parameterized query that returns only the users older than 25. Print each as name (email).
2. Write a safe lookup function. Build find_user_by_name(cursor, name) that uses a %s placeholder, returns the matching row, and returns None when nobody matches. Confirm it does the right thing when you pass it "' OR '1'='1".
3. Load config from the environment. Write a get_db_config() that reads DB_HOST, DB_USER, DB_PASSWORD, and DB_NAME, and raises a clear error naming exactly which variable is missing instead of a bare KeyError.

Conclusion

You now have the full production database toolkit: install the right driver, connect with credentials, run parameterized queries with %s placeholders, commit your writes, reuse connections through a pool, and keep the password in the environment instead of the code. The best part is that it is one pattern: what works for Python MySQL code works for PostgreSQL with only tiny keyword changes, and it is the same shape you already learned with SQLite.

Next up, we stop writing raw SQL strings altogether: SQLAlchemy turns your tables into Python classes and your queries into method calls, while everything you learned here about connections, transactions, and pooling keeps working underneath. And if you want to jump to any other topic, browse the full Python + AI/ML tutorial series home.

Frequently Asked Questions

Interview Questions on Python MySQL and PostgreSQL

These come from real screens and onsites. Practice answering before you read each answer.

Q: Your Flask app runs fine for an hour, then every request starts failing with “too many connections” errors from the database. What do you check first?

Look for a connection leak: connections that get opened (or borrowed from the pool) per request but never closed, so each one keeps holding a slot on the server until it hits its connection limit. Check that every code path, including the error paths, closes the connection in a finally block or a with statement. Also confirm the pool is created once at app startup, because creating a new pool inside the request handler opens a fresh batch of connections on every hit.

Q: Your teammate Anvay runs a script that inserts 500 rows, sees no errors, but the table is empty the next morning. What happened?

The script never called conn.commit(). Both mysql-connector-python and psycopg2 start you inside a transaction with autocommit off, so all 500 inserts were pending, and when the connection closed the database rolled them back silently. There is no error because rolling back an uncommitted transaction is normal behaviour, not a failure. The fix is one line: commit after the writes.

Q: Your PostgreSQL monitoring shows several sessions stuck in “idle in transaction”. What in your psycopg2 code causes that?

psycopg2 implicitly opens a transaction on the first execute(), even for a plain SELECT, and keeps it open until you commit, roll back, or close. A long-lived connection that ran one query and then sat idle keeps that transaction open, which can hold locks and stops PostgreSQL from vacuuming old row versions. Fix it by committing or rolling back promptly after each unit of work, or by turning on autocommit for read-only workloads.

Q: The placeholder is %s. Is cursor.execute() just doing Python percent-formatting on the SQL string?

No, and that distinction is the whole security story. With cursor.execute(sql, params) the values travel separately from the SQL text and the driver binds or escapes them, so a value like ' OR '1'='1 stays a plain string to match, never becomes SQL. It also explains why you never wrap %s in quotes inside the query: the driver handles quoting for you. Doing sql % params or an f-string yourself puts you right back in SQL injection territory.

Q: All five connections in your MySQLConnectionPool are busy and a sixth request calls get_connection(). What happens?

It raises a PoolError immediately; mysql-connector-python does not queue the caller until a connection frees up. In practice you handle this by sizing the pool to your expected concurrency, returning connections quickly with conn.close(), and catching the error to retry or fail gracefully. If you need real queueing and overflow behaviour, a library like SQLAlchemy’s pool gives you more control.

Q: You used psycopg2-binary in the tutorial. Would you ship it to production as-is?

For learning and development, yes; for production, the psycopg2 maintainers recommend the source package instead. The binary wheel bundles its own copies of libpq and OpenSSL, and those can clash with other libraries in the same process that load different versions. Building psycopg2 from source links against your system libraries and avoids that class of problem, at the cost of needing a C compiler and the PostgreSQL headers at install time.

Go deeper: the MySQL Reference Manual and the PostgreSQL documentation cover every edge case of their respective dialects.

Related Posts

Previous: Python SQLite: Database and CRUD Operations

Next: Python: SQLAlchemy Object-Relational Mapping (ORM), Models, Sessions, Queries

Series Home: Python + AI/ML Tutorial Series