Course topics

By WebNest Studio Engineering Team

Python Tutorial

Connecting Python to MySQL

MySQL is one of the most widely used database servers for web applications. Python connects to it with a driver package — the official mysql-connector-python from Oracle, or the pure-Python PyMySQL. Both follow the DB-API you learned with sqlite3, so the code looks almost the same; the main differences are connection settings, the %s placeholder style, and running a separate server.

These examples need a running MySQL server and the driver installed, so run them on your own computer.

Why it matters

MySQL is one of the most widely deployed databases for web applications, and a Python service that reports on or updates an existing MySQL database is a common first job. The code follows the same DB-API pattern as sqlite3, with a few differences that cause errors if you do not know them.

Setup

  • Install MySQL Server (or run it with Docker: docker run -e MYSQL_ROOT_PASSWORD=secret -p 3306:3306 mysql:8.4).
  • Create a database and a dedicated user: CREATE DATABASE shop; CREATE USER 'shop_app'@'%' IDENTIFIED BY '...'; GRANT ALL ON shop.* TO 'shop_app'@'%';
  • Install the driver into your virtual environment: pip install mysql-connector-python (or pip install pymysql).
  • Keep the password out of your code — read it from an environment variable or a secrets manager.

Differences from sqlite3

Placeholders are %s (or %(name)s) for every type — never use Python's % operator yourself. Autocommit is off by default, so call conn.commit(). conn.cursor(dictionary=True) returns rows as dictionaries. AUTO_INCREMENT ids are available from cursor.lastrowid. For web applications, use a connection pool so requests reuse connections instead of opening a new one each time.

Examples

Connecting and handling connection errors

Python
# pip install mysql-connector-python
import os
import mysql.connector
from mysql.connector import errorcode

config = {
    "host": "localhost",
    "port": 3306,
    "user": "shop_app",
    "password": os.environ["DB_PASSWORD"],     # never hard-code passwords
    "database": "shop",
}

try:
    conn = mysql.connector.connect(**config)
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Wrong user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
else:
    print("connected:", conn.is_connected(), "| server version:", conn.get_server_info())
    conn.close()
Output
connected: True | server version: 8.4.2

Create a table and perform CRUD with %s placeholders

Python
import os
import mysql.connector

conn = mysql.connector.connect(host="localhost", user="shop_app",
                               password=os.environ["DB_PASSWORD"], database="shop")
cur = conn.cursor(dictionary=True)             # rows as dicts

cur.execute("""
    CREATE TABLE IF NOT EXISTS customers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(255) NOT NULL UNIQUE,
        city VARCHAR(50)
    )
""")

cur.execute("INSERT INTO customers (name, email, city) VALUES (%s, %s, %s)",
            ("Asha", "asha@example.com", "Pune"))
print("new id:", cur.lastrowid)
cur.executemany("INSERT INTO customers (name, email, city) VALUES (%s, %s, %s)", [
    ("Ravi", "ravi@example.com", "Mumbai"),
    ("Meera", "meera@example.com", "Pune"),
])
conn.commit()                                  # autocommit is off by default

cur.execute("SELECT id, name, city FROM customers WHERE city = %s ORDER BY name", ("Pune",))
for row in cur.fetchall():
    print(row)

cur.execute("UPDATE customers SET city = %s WHERE email = %s", ("Nagpur", "ravi@example.com"))
print("updated:", cur.rowcount)
cur.execute("DELETE FROM customers WHERE email = %s", ("meera@example.com",))
print("deleted:", cur.rowcount)
conn.commit()

cur.close()
conn.close()
Output
new id: 1
{'id': 1, 'name': 'Asha', 'city': 'Pune'}
{'id': 3, 'name': 'Meera', 'city': 'Pune'}
updated: 1
deleted: 1

Transactions with a connection pool

Python
import os
import mysql.connector
from mysql.connector import pooling

pool = pooling.MySQLConnectionPool(
    pool_name="shop_pool",
    pool_size=5,
    host="localhost",
    user="shop_app",
    password=os.environ["DB_PASSWORD"],
    database="shop",
)

def place_order(customer_id, items):
    conn = pool.get_connection()
    try:
        conn.start_transaction()
        cur = conn.cursor()
        cur.execute("INSERT INTO orders (customer_id) VALUES (%s)", (customer_id,))
        order_id = cur.lastrowid
        cur.executemany(
            "INSERT INTO order_items (order_id, product, quantity) VALUES (%s, %s, %s)",
            [(order_id, product, qty) for product, qty in items],
        )
        conn.commit()                  # the order and all its items, or nothing
        return order_id
    except mysql.connector.Error:
        conn.rollback()
        raise
    finally:
        conn.close()                   # returns the connection to the pool

print("order", place_order(1, [("Pen", 3), ("Notebook", 2)]), "placed")
Output
order 101 placed

The same queries with PyMySQL

Python
# pip install pymysql
import os
import pymysql

conn = pymysql.connect(host="localhost", user="shop_app", password=os.environ["DB_PASSWORD"],
                       database="shop", cursorclass=pymysql.cursors.DictCursor)
with conn:
    with conn.cursor() as cur:
        cur.execute("SELECT name, city FROM customers WHERE id = %s", (1,))
        print(cur.fetchone())
Output
{'name': 'Asha', 'city': 'Pune'}

Common Mistakes

  • Using ? placeholders (sqlite3 style) with MySQL drivers, which expect %s.
  • Formatting values into the query with % or f-strings instead of passing them as the second argument.
  • Forgetting conn.commit() because autocommit is off.
  • Hard-coding database passwords in source code or committing them to Git.
  • Opening a new connection for every request in a web app instead of using a pool.

Exercise

Using mysql-connector-python, insert a user into a users(id, name, email) table and read that user back by email. This needs a running MySQL server with a shop database, so replace the connection details with your own. When it works, the program prints a tuple containing the new user's id and name.

Python · starter code
import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="app", password="secret", database="shop"
)
cursor = connection.cursor()

# TODO: insert ("Asha", "asha@example.com") using placeholders, then commit

# TODO: select id and name for that email, and print the row

cursor.close()
connection.close()
Show hints
  • MySQL drivers use %s as the placeholder for every type, not ?.
  • The values are still passed as a separate tuple; a single value needs a trailing comma: ("asha@example.com",).
Show solution
Python · solution
import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="app", password="secret", database="shop"
)
cursor = connection.cursor()

cursor.execute(
    "INSERT INTO users (name, email) VALUES (%s, %s)",
    ("Asha", "asha@example.com"),
)
connection.commit()

cursor.execute("SELECT id, name FROM users WHERE email = %s", ("asha@example.com",))
print(cursor.fetchone())

cursor.close()
connection.close()

Quick quiz

1. Which placeholder do the MySQL drivers use?

  • A. ?
  • B. :1
  • C. %s
  • D. @p
Show answer

C. It is %s for every type. It is not Python string formatting; the values are passed separately.

2. What is the main reason to use a connection pool in a web application?

  • A. To encrypt the data
  • B. To avoid writing SQL
  • C. To store query results
  • D. Opening a connection is slow, so a set of open connections is reused across requests
Show answer

D. A pool also limits how many connections the application can open.

3. How does MySQL differ from SQLite in the way it runs?

  • A. MySQL is a separate server that the program connects to with a host, user and password
  • B. MySQL is a file in the project folder
  • C. MySQL has no tables
  • D. MySQL cannot be used from Python
Show answer

A. SQLite is a library that reads and writes one local file.

Interview questions

How do you connect to MySQL from Python?

Show answer

Install a driver such as mysql-connector-python or PyMySQL, call its connect() function with the host, user, password and database name, and then use a cursor to execute statements with %s placeholders. Changes must be committed. Credentials should come from environment variables or configuration, never from the source code.

What is a connection pool?

Show answer

A pool keeps a number of database connections open and lends one to each piece of code that needs it, taking it back afterwards. Establishing a connection involves a network handshake and authentication, so reusing connections is much faster than opening one per request, and the pool's size limit protects the database from being overloaded.

Key Points to Remember

  • mysql-connector-python and PyMySQL are DB-API drivers for MySQL.
  • Use %s / %(name)s placeholders and pass values separately.
  • Commit explicitly; roll back on errors.
  • cursor(dictionary=True) or DictCursor returns rows as dictionaries.
  • Use connection pooling and environment variables for credentials in real applications.

Practice in the playground

Write and run your own code for this topic, or start from the exercise or an example. Runs Python in your browser. Your code for each topic is kept in this browser.

Run the examples

Change an input, predict the result, then compare it with the output. Explain why the result changes.

These examples need a project environment on your computer, so they can be copied but not run here. Codelab runs Python, FastAPI backends with SQLite, plain Java 17 and HTML/CSS/JavaScript.

WebNest Studio

Written by

WebNest Studio Engineering Team

Practical lessons from developers working with Java, Python, React, databases and production systems.

Led by Vansh Duggal, Founder, WebNest Studio · Full-stack & software engineering