Python MySQL

Tutorial 52 of 65 · pythondeck.com Python course

Use the official mysql-connector-python or the pure-Python PyMySQL driver. For ORM use SQLAlchemy. Always parameterise queries to prevent SQL injection.

MySQL (and MariaDB) power countless LAMP stacks and SaaS databases. Python connects via drivers like mysql-connector-python or PyMySQL, often behind SQLAlchemy or Django ORM.

Understanding connections, charset (utf8mb4), and replication basics prevents encoding bugs and downtime surprises.

Most Python web frameworks assume a relational store—knowing when to push work into SQL versus Python loops keeps dashboards responsive.

ORMs hide dialect differences—still learn EXPLAIN and index basics when queries slow down in production.

Drivers — DB-API 2.0 cursors; prefer pooled connections in apps.

Charset — utf8mb4 for full Unicode including emoji.

Transactions — InnoDB row locks; keep transactions short.

Indexes — B-tree primary keys; explain plans for slow queries.

Replication — read replicas with lag awareness in application code.

ORM vs raw SQL — ORM for CRUD; raw SQL for reporting tuned queries.

Connection storms kill databases—use pool sizes matched to app workers. Prepared statements via drivers reduce parse overhead. JSON columns and generated columns blur document/relational boundaries; validate in app layer.

Operational concerns: backups (mysqldump, Percona XtraBackup), least-privilege users per service, and secrets in vaults—not settings.py committed to git.

Connection SSL modes and certificate verification protect data in transit between app tier and managed RDS instances.

Using utf8 (3-byte) instead of utf8mb4 and corrupting emoji.

SELECT * in hot paths pulling unnecessary columns over the network.

No index on foreign keys, turning joins into full scans.

Running schema changes on production without online DDL strategy.

Leaving idle connections open without pool recycle, exhausting max_connections during traffic spikes.

Parameterize all queries; centralize connection config from environment.

Monitor slow query log; add covering indexes based on evidence.

Use migrations (Flyway, Alembic) for repeatable schema history.

Separate read-only credentials for analytics replicas.

Pool pre-ping or equivalent health checks recover gracefully after database failover events.

Re-read the examples below with these ideas in mind; change variable names and inputs to match your own project.

The program below demonstrates connect. Read the comments on each line, run the code, then change names or values to see how the output shifts.

# Example: Connect
# Run in the REPL or save as a .py file and execute with python.
import mysql.connector
con = mysql.connector.connect(
    host="localhost", user="root", password="secret", database="demo")
cur = con.cursor()
cur.execute("SELECT VERSION()")
print(cur.fetchone())
con.close()

This sample walks through parameterised query in a small, runnable script. Paste it into the REPL or save it as a .py file before you continue to the next block.

# Example: Parameterised query
# Run in the REPL or save as a .py file and execute with python.
cur.execute("INSERT INTO user(name, email) VALUES (%s, %s)",
            ("Ada", "ada@example.com"))
con.commit()

Here is a hands-on illustration of with sqlalchemy. Follow the inline comments first; only then execute the snippet and compare the result with what you expected.

# Example: With SQLAlchemy
# Run in the REPL or save as a .py file and execute with python.
from sqlalchemy import create_engine, text
eng = create_engine("mysql+pymysql://user:pw@localhost/demo")
with eng.begin() as con:
    for row in con.execute(text("SELECT id, name FROM user")):
        print(row)

The program below demonstrates connect query. Read the comments on each line, run the code, then change names or values to see how the output shifts.

# mysql-connector (or PyMySQL) speaks to a MySQL server
# pip install mysql-connector-python  # install driver first
import mysql.connector as mysql  # official connector
cfg = {"host": "127.0.0.1", "user": "app", "password": "secret", "database": "shop"}  # DSN
conn = mysql.connect(**cfg)  # TCP connection
cur = conn.cursor()  # cursor executes SQL
cur.execute("SELECT id, name FROM products LIMIT 5")  # read sample
for row in cur.fetchall():  # iterate tuples
    print(row)  # each product row
cur.close(); conn.close()  # cleanup

This sample walks through parameterized insert in a small, runnable script. Paste it into the REPL or save it as a .py file before you continue to the next block.

# Never interpolate user input — use %s placeholders
import mysql.connector as mysql  # connector
conn = mysql.connect(host="127.0.0.1", user="app", password="secret", database="shop")  # connect
cur = conn.cursor()  # cursor
sql = "INSERT INTO products (name, price) VALUES (%s, %s)"  # placeholder SQL
cur.execute(sql, ("Pen", 1.5))  # safe parameters tuple
conn.commit()  # commit write
cur.execute("SELECT COUNT(*) FROM products")  # verify
print(cur.fetchone())  # row count tuple
cur.close(); conn.close()  # close resources

« Python SQLite All tutorials Python MongoDB »