Python: mysqlclient (MySQL)

Connects over the MySQL protocol to a MachSQL database created with the MySQL dialect; creates a table, inserts and counts. The code is the same against the original database and against MachSQL; only the address changes.

The only change

DB_HOST=old-mysql
DB_HOST=machsql-host

Nothing else changes: not the code, not the driver, not its version, not the SQL.

Versions

• Python 3.15
• mysqlclient 2.3.0

Run

python3 -m venv .venv && . .venv/bin/activate
pip install -r requirements.txt
export DB_HOST=machsql-host DB_PASSWORD=...
python main.py

Expected output: inserted todo <id>, <n> open.

Source

The program as it is kept and tested in the Mach Database for PostgreSQL repository; highlighted lines read the connection setting.

main.py

"""mysqlclient (MySQLdb) over the MySQL protocol."""

import os

import MySQLdb

conn = MySQLdb.connect(
    host=os.environ["DB_HOST"],  # the only value that changes
    port=int(os.environ.get("DB_PORT", "3306")),
    user=os.environ.get("DB_USER", "app"),
    password=os.environ["DB_PASSWORD"],
    database=os.environ.get("DB_NAME", "app"),
)
try:
    cur = conn.cursor()
    cur.execute("CREATE TABLE IF NOT EXISTS todo (id BIGINT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200) NOT NULL, done BOOLEAN NOT NULL DEFAULT FALSE)")
    cur.execute("INSERT INTO todo (title) VALUES (%s)", ("write the examples",))
    todo_id = cur.lastrowid
    conn.commit()
    cur.execute("SELECT count(*) FROM todo WHERE NOT done")
    (open_count,) = cur.fetchone()
    print(f"inserted todo {todo_id}, {open_count} open")
finally:
    conn.close()

requirements.txt

mysqlclient==2.3.0