Home / Python / Day 9: Automation & Databases / Connecting Python to MySQL

Connecting Python to MySQL

For larger applications, Python connects to MySQL databases using libraries like mysql-connector-python or PyMySQL to store and retrieve data.

Why MySQL?

While SQLite is great for small/local apps, MySQL is a full client-server database system used for production web applications, supporting multiple simultaneous users, larger datasets, and advanced features.

Connecting to MySQL

Install a connector library such as mysql-connector-python (pip install mysql-connector-python) or PyMySQL. Connect using host, user, password, and database name.

Executing Queries

The pattern mirrors sqlite3: get a connection, create a cursor, execute SQL with parameterized placeholders (%s for mysql-connector), and commit changes.

Reading Results

fetchall() and fetchone() work the same way as in sqlite3.

Connection Pooling

For web applications handling many requests, connection pooling (reusing a pool of open connections) improves performance significantly compared to opening a new connection per request.

Security Best Practices

Never hardcode credentials in source code — use environment variables or config files excluded from version control. Always use parameterized queries to prevent SQL injection.

Syntax

<pre><code>import mysql.connector

# Connect to MySQL
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your_password",
    database="contact_book"
)
cursor = conn.cursor()

# Create a table
cursor.execute("""
    CREATE TABLE IF NOT EXISTS contacts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(150) UNIQUE,
        phone VARCHAR(20)
    )
""")

# Insert data (note: %s placeholders for mysql-connector)
cursor.execute(
    "INSERT INTO contacts (name, email, phone) VALUES (%s, %s, %s)",
    ("Alice", "alice@example.com", "555-1234")
)
conn.commit()

# Query data
cursor.execute("SELECT id, name, email FROM contacts WHERE name LIKE %s", ("A%",))
for row in cursor.fetchall():
    print(row)

# Update
cursor.execute("UPDATE contacts SET phone = %s WHERE name = %s", ("555-0000", "Alice"))
conn.commit()

# Always close
cursor.close()
conn.close()

# Using environment variables for credentials
import os
DB_HOST = os.environ.get("DB_HOST", "localhost")
DB_PASS = os.environ.get("DB_PASS", "")
</code></pre>

Revision Notes

• mysql-connector-python or PyMySQL connect Python to MySQL
• mysql.connector.connect(host, user, password, database) opens a connection
• Use %s placeholders (not ? like sqlite3) for mysql-connector
• commit() required for INSERT/UPDATE/DELETE to persist
• Never hardcode credentials — use environment variables/config files
• Connection pooling improves performance for multi-request apps

Build a Parameterized Insert Query

Easy

Write a function build_insert(table, columns, values) that returns a tuple (sql, params) representing a parameterized MySQL INSERT statement using %s placeholders, where sql is the SQL string and params is a tuple of values, ready to pass to cursor.execute().

Input:

table="users", columns=["name", "email"], values=("Alice", "alice@example.com")

Output:

("INSERT INTO users (name, email) VALUES (%s, %s)", ('Alice', 'alice@example.com'))

Show Hint

Join the column names with ", " for the column list, and create the same number of "%s" placeholders joined with ", " for the VALUES clause.

Solve this Challenge

Show Solution

def build_insert(table, columns, values):
    cols = ", ".join(columns)
    placeholders = ", ".join(["%s"] * len(columns))
    sql = f"INSERT INTO {table} ({cols}) VALUES ({placeholders})"
    return sql, tuple(values)

print(build_insert("users", ["name", "email"], ("Alice", "alice@example.com")))

Working with SQLite Databases Back to Course