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(orpip 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
# 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()
connected: True | server version: 8.4.2
Create a table and perform CRUD with %s placeholders
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()
new id: 1
{'id': 1, 'name': 'Asha', 'city': 'Pune'}
{'id': 3, 'name': 'Meera', 'city': 'Pune'}
updated: 1
deleted: 1
Transactions with a connection pool
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")
order 101 placed
The same queries with PyMySQL
# 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())
{'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.
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
%sas 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
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.
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