Python Tutorial
Python MySQL - Get Started
Connect Python to a MySQL database using the official connector and run your first query.
Install the Connector
Python talks to MySQL through a driver. The official one is mysql-connector-python. Install it with pip:
pip install mysql-connector-pythonPopular alternatives are PyMySQL (pure Python) and mysqlclient (fast C binding). They share the same DB-API 2.0 interface, so the code below transfers with minor import changes.
Create a Connection
import mysql.connector
conn = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
)
print(conn) # a connected MySQLConnection object
conn.close()Never hard-code credentials in source. Read them from environment variables or a secrets manager: os.environ["DB_PASSWORD"].
Cursors: How You Run SQL
A cursor executes statements and fetches results. Get one from the connection, run a query, then read rows.
cursor = conn.cursor()
cursor.execute("SELECT VERSION()")
print(cursor.fetchone()) # e.g. ('8.0.36',)
cursor.close()The Safe, Modern Pattern
Use context managers so connections and cursors always close, even if an error occurs. Set dictionary=True to get rows as dicts instead of tuples.
import mysql.connector
with mysql.connector.connect(
host="localhost", user="root", password="secret", database="shop"
) as conn:
with conn.cursor(dictionary=True) as cursor:
cursor.execute("SELECT 1 + 1 AS result")
print(cursor.fetchone()) # {'result': 2}Handling Connection Errors
import mysql.connector
from mysql.connector import Error
try:
conn = mysql.connector.connect(
host="localhost", user="root", password="wrong")
except Error as e:
print("Could not connect:", e)Best Practices
- Keep credentials out of code — use environment variables.
- Always close cursors and connections (context managers do this for you).
- Use a connection pool for web apps that open many short-lived connections.
- The next lessons cover creating databases and tables, then querying with SELECT and WHERE.
Try It Yourself
Exercise 1: Which package and import give you the official MySQL driver?
Show solution
pip install mysql-connector-python
# then:
import mysql.connectorExercise 2: Why should credentials come from environment variables rather than source code?
Show solution
Hard-coded secrets get committed to version control and leak. Environment variables (or a secrets manager) keep them out of the codebase.
📘 Real-World Deep Dive
MySQL is the lingua franca of relational databases. mysql-connector-python / PyMySQL give you a Pythonic surface; SQLAlchemy gives you ORM-level composability. Knowing both keeps you productive with raw SQL and with frameworks.
Real-Life Scenario
A small CRUD + JOIN example: create a <code>users</code> and <code>orders</code> table, insert a few rows, then a parameterised JOIN to list recent orders per user.
Real-Life Example
import mysql.connector
conn = mysql.connector.connect(
host="localhost", user="root", password="...", database="shop",
)
cur = conn.cursor(dictionary=True)
cur.execute("""
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(120) NOT NULL,
email VARCHAR(120) UNIQUE
) CHARACTER SET utf8mb4
""")
cur.execute("""
CREATE TABLE IF NOT EXISTS orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
total DECIMAL(10,2) NOT NULL,
placed DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) CHARACTER SET utf8mb4
""")
cur.executemany("INSERT IGNORE INTO users (name, email) VALUES (%s, %s)",
[("Ada", "ada@x"), ("Bo", "bo@x"), ("Cy", "cy@x")])
cur.executemany(
"INSERT INTO orders (user_id, total) VALUES (%s, %s)",
[(1, 24.50), (1, 12.75), (2, 88.00), (3, 7.20)],
)
conn.commit()
cur.execute("""
SELECT u.name, o.total, o.placed
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.placed >= NOW() - INTERVAL 7 DAY
ORDER BY o.placed DESC
""")
for row in cur.fetchall():
print(row)Expected Output
[{'name': 'Bo', 'total': Decimal('88.00'), 'placed': datetime.datetime(...)}, ...]Common mistakes
- Binding: f-strings in
cur.execute()are SQL-injection-friendly. Use%s+ tuple. - Calling
fetchall()on a 1 M-row result loads everything in RAM — stream withfetchmany. - Without
character set utf8mb4, emoji turn into?characters.
🚀 Performance & Best Practices
- Use
executemanyfor batched inserts; loops of single inserts are dramatically slower. - Index foreign-key columns; MySQL will only enforce cascades efficiently if an index exists.
- For very large SELECTs, use server-side cursors (
pymysql.cursors.SSCursor).
🧪 Try It Yourself
- Add a compound index on
(user_id, placed)and re-run the JOIN. - Wrap the connection in a small connection pool.
- Migrate the example to SQLAlchemy core.