Back to Study Material Python

MySQL

MySQL is one of the world's most popular open-source relational databases. It is used by countless web applications and enterprise systems. Connecting Pyth...

MySQL

Connecting Python to MySQL with mysql-connector-python

MySQL is one of the world's most popular open-source relational databases. It is used by countless web applications and enterprise systems. Connecting Python to MySQL requires an external library because there is no built-in MySQL adapter. The most commonly used options are mysql-connector-python (the official driver from Oracle) and PyMySQL (a pure-Python alternative).

To install the connector:

pip install mysql-connector-python

Once installed, the pattern for connecting and querying is similar to sqlite3, but the connection takes more configuration parameters:

import mysql.connector

conn = mysql.connector.connect( host="localhost", user="root", password="your_password", database="school" )

cursor = conn.cursor()

# Create a table cursor.execute(""" CREATE TABLE IF NOT EXISTS students ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, grade INT ) """)

# Insert data using parameterized queries (%s for MySQL) cursor.execute("INSERT INTO students (name, grade) VALUES (%s, %s)", ("Diana", 88)) conn.commit()

# Query data cursor.execute("SELECT * FROM students") for row in cursor.fetchall(): print(row)

cursor.close() conn.close()

Notice that MySQL uses %s as the placeholder for parameterized queries, unlike SQLite's ?. This is a common point of confusion when switching between databases.

MySQL supports features that SQLite lacks, including strict data type enforcement, stored procedures, multi-user concurrent access, replication, and a broader set of SQL features. For web applications expected to handle many simultaneous users, MySQL is a much stronger choice than SQLite.

Connection parameters should never be hardcoded in your source code. Store them in environment variables or a configuration file that is excluded from version control. Use os.getenv() to load them at runtime:

import os password = os.getenv("MYSQL_PASSWORD")

Interview tip: Be ready to explain connection pooling. Creating a new database connection for every request is slow. In production Python web applications, libraries like mysql-connector-python's pooling or SQLAlchemy's connection pool manage a set of pre-opened connections that can be reused across requests, dramatically improving performance.