MySQL with Python allows Python applications to store, retrieve, update, and delete structured data using a MySQL database. Python can connect to MySQL using database connector libraries, execute SQL queries, and process the returned results. MySQL with Python is commonly used in web applications, data management systems, automation projects, and other applications that require a server-based relational database.
 

Table of Contents

---

What is MySQL?

MySQL is a popular relational database management system that stores data in tables consisting of rows and columns.

1. It uses SQL, or Structured Query Language, to create databases, manage tables, and perform operations on stored data.
2. Unlike SQLite, MySQL generally runs as a database server.
3. A Python application connects to the MySQL server and sends SQL queries to perform database operations.

Why Use MySQL with Python?

MySQL is useful when a Python application needs a reliable database for storing and managing structured data.

• Data Storage: MySQL allows applications to store large amounts of structured data in tables.
• Database Management: SQL commands can be used to insert, retrieve, update, and delete records.
• Web Applications: MySQL is commonly used as a database for web applications built with Python frameworks.
• Multiple Users: MySQL supports applications where multiple users or services need to access the same database.
• Scalability: MySQL can be used for applications that require more database functionality than a simple file-based database.

Installing MySQL Connector

Python needs a connector library to communicate with a MySQL server. One commonly used library is mysql-connector-python.
Install it using pip:

pip install mysql-connector-python

After installation, the connector can be imported into a Python program.

import mysql.connector

Before running the examples, make sure a MySQL server is installed and running, and that you have valid database credentials.

Connecting Python to MySQL

The mysql.connector.connect() function is used to establish a connection between Python and MySQL.

# Python program to connect Python with MySQL
import mysql.connector
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="YOUR_PASSWORD"
)
print("Connected to MySQL successfully.")
connection.close()

Output:
Connected to MySQL successfully.

The connection parameters identify the MySQL server and the account that Python should use.

• host: Specifies the MySQL server address.
• user: Specifies the MySQL username.
• password: Specifies the password for the MySQL account.

Creating a Database

A database can be created by executing a CREATE DATABASE SQL statement.

# Python program to create a MySQL database
import mysql.connector
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="YOUR_PASSWORD"
)
cursor = connection.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS school")
print("Database created successfully.")
connection.close()

Output:
Database created successfully.

The IF NOT EXISTS clause prevents an error when the database already exists.

Connecting to a Specific Database

After creating a database, the database parameter can be used when connecting to it.

# Python program to connect to a specific MySQL database
import mysql.connector
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="YOUR_PASSWORD",
    database="school"
)
print("Connected to the school database.")
connection.close()

Output:
Connected to the school database.

Creating a Table

A table can be created by executing a CREATE TABLE statement through the cursor.

# Python program to create a student table in MySQL
import mysql.connector
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="YOUR_PASSWORD",
    database="school"
)
cursor = connection.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    course VARCHAR(100)
)
""")
print("Table created successfully.")
connection.close()

Output:
Table created successfully.

The students table contains four columns:

• id: Stores a unique identifier for each student.
• name: Stores the student's name.
• age: Stores the student's age.
• course: Stores the student's course.

AUTO_INCREMENT allows MySQL to automatically generate a new ID for each inserted record.

Inserting Data into MySQL

The INSERT INTO SQL statement is used to add records to a MySQL table.

# Python program to insert a student record into MySQL
import mysql.connector
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="YOUR_PASSWORD",
    database="school"
)
cursor = connection.cursor()
sql = "INSERT INTO students (name, age, course) VALUES (%s, %s, %s)"
values = ("Tanvi", 20, "BSc IT")
cursor.execute(sql, values)
connection.commit()
print("Student record inserted successfully.")
connection.close()

Output:
Student record inserted successfully.

The %s placeholders are used for values in MySQL Connector/Python parameterized queries. The values are supplied separately through the values tuple.

Inserting Multiple Records

The executemany() method can be used to insert multiple records.

# Python program to insert multiple student records into MySQL
import mysql.connector
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="YOUR_PASSWORD",
    database="school"
)
cursor = connection.cursor()
sql = "INSERT INTO students (name, age, course) VALUES (%s, %s, %s)"
students = [
    ("Tanvi", 20, "BSc IT"),
    ("Rahul", 21, "BCA"),
    ("Priya", 20, "BSc CS")
]
cursor.executemany(sql, students)
connection.commit()
print("Multiple student records inserted successfully.")
connection.close()

Output:
Multiple student records inserted successfully.

The executemany() method executes the same SQL statement for multiple sets of values.

Reading Data from MySQL

The SELECT statement is used to retrieve records from a MySQL table.

# Python program to read student records from MySQL
import mysql.connector
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="YOUR_PASSWORD",
    database="school"
)
cursor = connection.cursor()
cursor.execute("SELECT * FROM students")
records = cursor.fetchall()
for record in records:
    print(record)
connection.close()

Output:
(1, 'Tanvi', 20, 'BSc IT')
(2, 'Rahul', 21, 'BCA')
(3, 'Priya', 20, 'BSc CS')

The fetchall() method retrieves all rows returned by the query.

Using fetchone()

The fetchone() method retrieves one record from the query result.

# Python program to retrieve one student record from MySQL
import mysql.connector
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="YOUR_PASSWORD",
    database="school"
)
cursor = connection.cursor()
cursor.execute("SELECT * FROM students")
record = cursor.fetchone()
print(record)
connection.close()

Output:
(1, 'Tanvi', 20, 'BSc IT')

This method is useful when the program needs to process one row at a time.

Updating Data in MySQL

The UPDATE statement is used to modify existing records.

# Python program to update a student record in MySQL
import mysql.connector
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="YOUR_PASSWORD",
    database="school"
)
cursor = connection.cursor()
sql = "UPDATE students SET age = %s WHERE name = %s"
values = (21, "Tanvi")
cursor.execute(sql, values)
connection.commit()
print("Student record updated successfully.")
connection.close()

Output:
Student record updated successfully.

The WHERE clause identifies the record that needs to be updated.

Deleting Data from MySQL

The DELETE statement is used to remove records from a table.

# Python program to delete a student record from MySQL
import mysql.connector
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="YOUR_PASSWORD",
    database="school"
)
cursor = connection.cursor()
sql = "DELETE FROM students WHERE name = %s"
values = ("Rahul",)
cursor.execute(sql, values)
connection.commit()
print("Student record deleted successfully.")
connection.close()

Output:
Student record deleted successfully.

The WHERE clause ensures that the intended record is selected for deletion.

Using Parameters in SQL Queries

Parameterized queries allow values to be passed separately from the SQL statement.

For example:

cursor.execute("SELECT * FROM students WHERE age = %s", (20,))

The %s acts as a placeholder for the value.
Using parameters is important because it helps prevent SQL injection and avoids problems caused by directly adding user input to SQL statements.

Committing Changes

The commit() method saves changes made to the MySQL database.

connection.commit()

It is generally required after operations such as INSERT, UPDATE, and DELETE.
Without committing the transaction, changes may not be permanently saved as expected.

Closing the Connection

After database operations are completed, the cursor and database connection should be closed.

cursor.close()
connection.close()

Closing them releases the resources used by the database connection.

Handling MySQL Errors

Database operations can fail because of incorrect credentials, connection problems, invalid SQL statements, or other database errors. Python can handle these errors using try-except.

# Python program to handle MySQL connection errors
import mysql.connector
from mysql.connector import Error
try:
    connection = mysql.connector.connect(
        host="localhost",
        user="root",
        password="YOUR_PASSWORD",
        database="school"
    )
    if connection.is_connected():
        print("Connected to MySQL successfully.")
except Error as error:
    print("Error while connecting to MySQL:", error)
finally:
    if "connection" in locals() and connection.is_connected():
        connection.close()
        print("MySQL connection closed.")

Output:
Connected to MySQL successfully.
MySQL connection closed.

The Error class provided by mysql.connector can be used to handle MySQL-related exceptions.

How MySQL with Python Works

The process of working with MySQL in Python follows a series of simple steps.

1. Install the Connector: The MySQL Connector/Python package is installed so that Python can communicate with MySQL.
2. Import the Connector: The mysql.connector module is imported into the Python program.
3. Create a Connection: The program connects to the MySQL server using the required connection details.
4. Create a Cursor: A cursor is created to execute SQL statements.
5. Execute SQL Queries: The cursor executes commands such as CREATE, INSERT, SELECT, UPDATE, or DELETE.
6. Commit Changes: The program commits changes after modifying database records.
7. Retrieve Results: The program retrieves results using methods such as fetchone() or fetchall().
8. Close Resources: The cursor and database connection are closed after the required operations are completed.

Complete MySQL Program

The following program creates a connection to a MySQL database, creates a table, inserts a student record, and displays the stored records.

# Python program to create and manage a MySQL student database
import mysql.connector
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="YOUR_PASSWORD",
    database="school"
)
cursor = connection.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    course VARCHAR(100)
)
""")
sql = "INSERT INTO students (name, age, course) VALUES (%s, %s, %s)"
values = ("Tanvi", 20, "BSc IT")
cursor.execute(sql, values)
connection.commit()
cursor.execute("SELECT * FROM students")
records = cursor.fetchall()
print("Student Records:")
for record in records:
    print(record)
cursor.close()
connection.close()
print("MySQL connection closed.")

Output:
Student Records:
(1, 'Tanvi', 20, 'BSc IT')
MySQL connection closed.

If the complete program is executed multiple times, it can insert the same student again. In a real application, appropriate validation or unique constraints should be used when duplicate records need to be prevented.

Advantages of MySQL with Python

• Easy Database Integration: Python can communicate with MySQL using connector libraries.
• Structured Data Storage: MySQL stores information in organized tables with rows and columns.
• Multi-User Support: MySQL can support applications where multiple users access the database.
• SQL Support: Python programs can use standard SQL operations to manage data.
• Suitable for Web Applications: MySQL can be integrated with Python web frameworks and backend applications.

Common Beginner Mistakes

1. Using Incorrect Connection Details: Incorrect host, username, password, or database details can prevent Python from connecting to MySQL.
2. Forgetting to Install the Connector: The mysql-connector-python package must be installed before importing mysql.connector.
3. Forgetting to Commit Changes: INSERT, UPDATE, and DELETE operations should be committed when the changes need to be saved.
4. Using Unparameterized Queries: Directly inserting user input into SQL statements can create security problems and SQL errors.
5. Forgetting to Close Resources: Leaving cursors and database connections open can unnecessarily consume resources.

Conclusion

MySQL with Python provides a powerful way to store and manage structured data in Python applications. The mysql-connector-python package allows Python programs to connect to a MySQL server and execute SQL queries. By learning database connections, cursors, CRUD operations, parameterized queries, transactions, and error handling, beginners can build Python applications that work with persistent database data.

Frequently Asked Questions

1. What is MySQL with Python?

MySQL with Python means connecting a Python application to a MySQL database so that the application can store, retrieve, update, and delete data.

2. Which library is used to connect Python with MySQL?

mysql-connector-python is a commonly used library that allows Python applications to communicate with MySQL.

3. What is the purpose of a cursor in MySQL with Python?

A cursor is used to execute SQL statements and retrieve results from the MySQL database.

4. Why is commit() used in MySQL?

The commit() method saves changes made by operations such as INSERT, UPDATE, and DELETE.

5. Why should parameterized queries be used?

Parameterized queries separate SQL statements from values and help protect applications from SQL injection and input-related SQL problems.