Introduction

Connecting Python to MySQL is usually straightforward once you choose a driver and keep credentials, queries, and cleanup under control. The most common approach is to use a DB-API compatible driver such as mysql-connector-python or PyMySQL, then execute parameterized SQL through a cursor.

Install A Driver First

One popular driver is Oracle's connector:

pip install mysql-connector-python

After installation, a basic connection looks like this:

1import mysql.connector
2
3connection = mysql.connector.connect(
4    host="127.0.0.1",
5    port=3306,
6    user="app_user",
7    password="secret",
8    database="app_db",
9)
10
11print(connection.is_connected())
12connection.close()

This opens a TCP connection to MySQL and selects the database immediately.

Run Queries With A Cursor

Once connected, create a cursor and execute SQL:

1import mysql.connector
2
3connection = mysql.connector.connect(
4    host="127.0.0.1",
5    user="app_user",
6    password="secret",
7    database="app_db",
8)
9
10cursor = connection.cursor()
11cursor.execute("SELECT id, email FROM users ORDER BY id LIMIT 3")
12
13for row in cursor.fetchall():
14    print(row)
15
16cursor.close()
17connection.close()

For inserts or updates, call commit():

1cursor = connection.cursor()
2cursor.execute(
3    "INSERT INTO users (email) VALUES (%s)",
4    ("[email protected]",),
5)
6connection.commit()

Using placeholders such as %s is important. It keeps values separate from the SQL text and helps prevent injection bugs.

Prefer Parameterized Queries

Do not build SQL by concatenating user input:

email = "[email protected]"
cursor.execute("SELECT id FROM users WHERE email = %s", (email,))

That pattern is safer and avoids quoting mistakes. It also lets the driver convert Python values into MySQL-friendly forms.

Handle Errors And Cleanup Explicitly

Database code should clean up cursors and connections even when something fails:

1import mysql.connector
2from mysql.connector import Error
3
4connection = None
5cursor = None
6
7try:
8    connection = mysql.connector.connect(
9        host="127.0.0.1",
10        user="app_user",
11        password="secret",
12        database="app_db",
13    )
14    cursor = connection.cursor(dictionary=True)
15    cursor.execute("SELECT NOW() AS server_time")
16    print(cursor.fetchone())
17except Error as exc:
18    print(f"MySQL error: {exc}")
19finally:
20    if cursor is not None:
21        cursor.close()
22    if connection is not None and connection.is_connected():
23        connection.close()

The dictionary=True option is useful if you want rows as dictionaries instead of tuples.

Consider SQLAlchemy For Larger Projects

If the project grows, SQLAlchemy can manage engines, transactions, pooling, and ORM models. Even when you do not use the ORM, its connection layer is useful:

1from sqlalchemy import create_engine, text
2
3engine = create_engine("mysql+mysqlconnector://app_user:[email protected]/app_db")
4
5with engine.connect() as conn:
6    result = conn.execute(text("SELECT 1 AS value"))
7    print(result.fetchone())

For small scripts, a direct connector is usually enough. For larger applications, SQLAlchemy reduces repeated connection boilerplate.

Localhost, Ports, And Remote Servers

Most connection failures are not Python syntax problems. They are usually one of these:

• wrong host or port
• invalid username or password
• MySQL not listening for TCP connections
• the user not being allowed from the client host

If you are running Python in Docker, 127.0.0.1 may be wrong because the database may live in another container. In that case, connect using the service name on the Docker network instead.

It is also a good habit to keep connection settings in environment variables instead of embedding them directly in source code. That makes local development, CI, and production deployment easier to configure without editing the script itself.

Common Pitfalls

One common mistake is interpolating values directly into SQL strings instead of using parameterized queries.

Another issue is forgetting connection.commit() after inserts, updates, or deletes, which makes it look like the statement ran but saved nothing.

A third problem is leaking cursors or connections by returning early from a function without cleanup.

Finally, developers often debug the Python code first when the real problem is MySQL permissions, host binding, or firewall access.

Summary

• Install a MySQL driver such as mysql-connector-python before connecting.
• Use mysql.connector.connect(...) with host, user, password, and database settings.
• Execute SQL through a cursor and use placeholders for parameters.
• Call commit() for writes and always close cursors and connections.
• For larger applications, consider SQLAlchemy for cleaner connection and transaction management.