Lesson 61 of 70 – MySQL with Python

87%

MySQL with Python

Python can connect to MySQL databases and perform operations such as creating databases, creating tables, inserting records, reading data, updating records, and deleting records.

MySQL is a popular relational database management system, while Python provides libraries that allow applications to communicate with MySQL servers.

Note: One commonly used MySQL driver for Python is mysql-connector-python. It can be installed using PIP.

What is MySQL?

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

For example, a student database may contain a table such as:

id	name	course	fee
1	Rahul	Python	15000
2	Amit	Java	18000

Why Use MySQL with Python?

Python and MySQL can be used together to build database-driven applications.

Student management systems.
Library management systems.
Billing applications.
Employee management systems.
School and coaching applications.
E-commerce applications.
Attendance systems.
Web applications.

Installing MySQL Connector

To connect Python to MySQL, install the MySQL Connector/Python package.

pip install mysql-connector-python

You can also use:

python -m pip install mysql-connector-python

Note: The second command explicitly runs PIP through the Python interpreter.

Importing mysql.connector

After installing the package, import the connector module.

import mysql.connector

This module provides the functionality required to establish a connection with MySQL.

Connecting to MySQL

The connect() function can be used to create a connection.

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your_password"
)

print("Connected successfully")

Output:

Connected successfully

Replace the connection details with the credentials configured on your MySQL server.

Connecting to a Specific Database

import mysql.connector

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

print("Connected to school database")

The database argument selects the database to work with after the connection is established.

Checking the Connection

The is_connected() method can be used to check whether the connection is active.

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your_password"
)

if connection.is_connected():
    print("MySQL connection successful")

Creating a Database

A cursor is used to execute SQL statements.

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")

Output:

Database created

Creating a Table

import mysql.connector

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

cursor = connection.cursor()

sql = """
CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    course VARCHAR(100)
)
"""

cursor.execute(sql)

print("Table created")

Output:

Table created

Inserting Data

Use an SQL INSERT statement to add records.

sql = """
INSERT INTO students
(name, age, course)
VALUES (%s, %s, %s)
"""

values = ("Rahul", 20, "Python")

cursor.execute(sql, values)

connection.commit()

Important: Call commit() after successful INSERT, UPDATE, or DELETE operations when using the default transaction behavior.

Why Use %s Placeholders?

Values should be passed separately from the SQL statement using parameters.

sql = "INSERT INTO students (name, age) VALUES (%s, %s)"

values = ("Amit", 22)

cursor.execute(sql, values)

connection.commit()

Parameterized queries help separate SQL code from data and are important for avoiding SQL injection when handling user input.

Do not:

name = "Rahul"

sql = "INSERT INTO students (name) VALUES ('" + name + "')"

Inserting Multiple Records

The executemany() method can execute the same parameterized statement for multiple sets of values.

sql = """
INSERT INTO students
(name, age, course)
VALUES (%s, %s, %s)
"""

students = [
    ("Rahul", 20, "Python"),
    ("Amit", 21, "Java"),
    ("Priya", 19, "SQL")
]

cursor.executemany(sql, students)

connection.commit()

Reading Data with SELECT

cursor.execute(
    "SELECT * FROM students"
)

rows = cursor.fetchall()

for row in rows:
    print(row)

Example Output:

(1, 'Rahul', 20, 'Python')
(2, 'Amit', 21, 'Java')
(3, 'Priya', 19, 'SQL')

fetchone()

The fetchone() method retrieves the next row from the result set.

cursor.execute(
    "SELECT * FROM students"
)

row = cursor.fetchone()

print(row)

Example Output:

(1, 'Rahul', 20, 'Python')

fetchmany()

The fetchmany() method retrieves a specified number of rows.

cursor.execute(
    "SELECT * FROM students"
)

rows = cursor.fetchmany(2)

for row in rows:
    print(row)

Example Output:

(1, 'Rahul', 20, 'Python')
(2, 'Amit', 21, 'Java')

fetchall()

The fetchall() method retrieves all remaining rows from the current result set.

cursor.execute(
    "SELECT * FROM students"
)

rows = cursor.fetchall()

for row in rows:
    print(row)

Selecting Specific Columns

cursor.execute(
    "SELECT name, course FROM students"
)

rows = cursor.fetchall()

for row in rows:
    print(row)

Example Output:

('Rahul', 'Python')
('Amit', 'Java')
('Priya', 'SQL')

Using WHERE

The WHERE clause filters records.

sql = """
SELECT * FROM students
WHERE course = %s
"""

cursor.execute(sql, ("Python",))

rows = cursor.fetchall()

for row in rows:
    print(row)

Updating Data

Use the SQL UPDATE statement to modify existing records.

sql = """
UPDATE students
SET age = %s
WHERE id = %s
"""

values = (21, 1)

cursor.execute(sql, values)

connection.commit()

The record with id = 1 now has an age of 21.

Deleting Data

Use the SQL DELETE statement to remove records.

sql = """
DELETE FROM students
WHERE id = %s
"""

cursor.execute(sql, (3,))

connection.commit()

The student whose ID is 3 is deleted.

Warning: Always use an appropriate WHERE condition when deleting specific records.

Getting Number of Affected Rows

The cursor's rowcount attribute provides information about rows affected by certain operations.

sql = """
UPDATE students
SET course = %s
WHERE id = %s
"""

cursor.execute(sql, ("Python Full Stack", 1))

connection.commit()

print(cursor.rowcount)

Example Output:

Getting Last Inserted ID

After inserting a row into a table with an auto-increment primary key, the cursor can provide the generated ID.

sql = """
INSERT INTO students
(name, age, course)
VALUES (%s, %s, %s)
"""

values = ("Neha", 22, "Python")

cursor.execute(sql, values)

connection.commit()

print(cursor.lastrowid)

The exact ID depends on the existing records in the table.

Transactions and commit()

Database changes are handled as part of transactions. The commit() method saves pending transaction changes.

cursor.execute(
    "UPDATE students SET age = %s WHERE id = %s",
    (25, 1)
)

connection.commit()

Important: If you make changes and do not commit them, they may not be permanently saved.

rollback()

The rollback() method can undo pending transaction changes that have not been committed.

try:

    cursor.execute(
        "UPDATE students SET age = %s WHERE id = %s",
        (30, 1)
    )

    connection.commit()

except mysql.connector.Error:

    connection.rollback()

Rollback is useful when an error occurs during a transaction.

Handling Database Errors

MySQL Connector/Python provides exceptions for database errors.

import mysql.connector

try:

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

    print("Connected")

except mysql.connector.Error as error:

    print("Database error:", error)

Closing Cursor and Connection

When database work is finished, close the cursor and connection.

cursor.close()
connection.close()

Closing resources helps prevent unnecessary resource usage.

Complete CRUD Example

CRUD stands for:

Create
Read
Update
Delete

import mysql.connector

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

cursor = connection.cursor()

# Create
cursor.execute("""
INSERT INTO students
(name, age, course)
VALUES (%s, %s, %s)
""", ("Rahul", 20, "Python"))

connection.commit()

# Read
cursor.execute(
    "SELECT * FROM students"
)

rows = cursor.fetchall()

for row in rows:
    print(row)

# Update
cursor.execute("""
UPDATE students
SET age = %s
WHERE id = %s
""", (21, 1))

connection.commit()

# Delete
cursor.execute("""
DELETE FROM students
WHERE id = %s
""", (1,))

connection.commit()

cursor.close()
connection.close()

Using Context Managers

Python's MySQL connector supports context-manager usage for managing cursors. This can help ensure that resources are cleaned up properly.

import mysql.connector

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

with connection.cursor() as cursor:

    cursor.execute(
        "SELECT * FROM students"
    )

    rows = cursor.fetchall()

    for row in rows:
        print(row)

connection.close()

Parameterized SELECT Query

Parameters can also be used with SELECT queries.

sql = """
SELECT * FROM students
WHERE age > %s
"""

cursor.execute(sql, (18,))

rows = cursor.fetchall()

for row in rows:
    print(row)

Important: Using parameters is preferred when values come from users or other external input.

MySQL with Python Workflow

A typical Python-MySQL application follows these steps:

Install the MySQL connector.
Import mysql.connector.
Create a database connection.
Create a cursor.
Execute SQL statements.
Fetch results when required.
Commit changes for write operations.
Handle errors.
Close the cursor and connection.

Key Points

Python can communicate with MySQL using a database connector.
mysql-connector-python is a commonly used MySQL driver for Python.
mysql.connector.connect() creates a database connection.
A cursor is used to execute SQL statements.
execute() executes a SQL statement.
executemany() executes a parameterized statement for multiple sets of values.
fetchone(), fetchmany(), and fetchall() retrieve query results.
commit() saves transaction changes.
rollback() can undo uncommitted changes after an error.
Parameterized queries help protect applications from SQL injection.
Close database resources after completing the work.
MySQL with Python is useful for building real-world database applications.

🧠 Quick Quiz

Question: Which Python package is commonly used to connect Python applications to MySQL?

mysql-python-sql
mysql-connector-python
python-mysql-server
mysql-connect

← Previous Next →

Python Tutorial

MySQL with Python

🧠 Quick Quiz