Skip to main content

Python MySQL Connection

Introduction​

Database connectivity is a crucial skill for any programmer. In this tutorial, you'll learn how to connect Python applications to MySQL databases, one of the most popular relational database management systems in the world. By the end of this guide, you'll be able to establish connections, execute queries, manipulate data, and perform common database operations using Python.

MySQL is an open-source database that powers many web applications, and Python provides several libraries to interact with it. We'll focus on the official MySQL Connector/Python package, which offers a straightforward and reliable way to communicate with MySQL databases.

Prerequisites​

Before we begin, you should have:

  • Basic knowledge of Python programming
  • Basic understanding of SQL and database concepts
  • MySQL server installed and running
  • Python installed on your system

Installing MySQL Connector/Python​

The first step is to install the MySQL Connector/Python package using pip:

bash
pip install mysql-connector-python

This package provides all the functionality you need to connect to MySQL databases from your Python applications.

Establishing a Connection​

To connect to a MySQL database, you need to provide the following information:

  • Host: The server where MySQL is running (usually "localhost" for local development)
  • Username: Your MySQL user account
  • Password: Your MySQL password
  • Database name: The specific database you want to connect to

Here's a basic connection example:

python
import mysql.connector

try:
    # Establish connection
    connection = mysql.connector.connect(
        host="localhost",
        user="root",
        password="your_password",
        database="your_database"
    )
    
    if connection.is_connected():
        print("Successfully connected to MySQL database")
        
except mysql.connector.Error as error:
    print(f"Error connecting to MySQL: {error}")
    
finally:
    # Always close the connection
    if 'connection' in locals() and connection.is_connected():
        connection.close()
        print("MySQL connection is closed")

Output:

Successfully connected to MySQL database
MySQL connection is closed
tip

Always close your database connections when you're done using them to free up resources.

Creating a Database​

If you don't have a database yet, you can create one using Python:

python
import mysql.connector

try:
    connection = mysql.connector.connect(
        host="localhost",
        user="root",
        password="your_password"
    )
    
    cursor = connection.cursor()
    cursor.execute("CREATE DATABASE IF NOT EXISTS python_tutorial")
    print("Database created successfully")
    
except mysql.connector.Error as error:
    print(f"Error: {error}")
    
finally:
    if 'connection' in locals() and connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output:

Database created successfully
MySQL connection is closed

Creating a Table​

After connecting to your database, you can create tables to store your data:

python
import mysql.connector

try:
    connection = mysql.connector.connect(
        host="localhost",
        user="root",
        password="your_password",
        database="python_tutorial"
    )
    
    cursor = connection.cursor()
    
    # Define the table creation SQL
    create_table_query = """
    CREATE TABLE IF NOT EXISTS students (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        age INT,
        email VARCHAR(100) UNIQUE
    )
    """
    
    cursor.execute(create_table_query)
    connection.commit()
    print("Table created successfully")
    
except mysql.connector.Error as error:
    print(f"Error: {error}")
    
finally:
    if 'connection' in locals() and connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output:

Table created successfully
MySQL connection is closed

Inserting Data​

Now that we have a table, let's insert some data:

python
import mysql.connector

try:
    connection = mysql.connector.connect(
        host="localhost",
        user="root",
        password="your_password",
        database="python_tutorial"
    )
    
    cursor = connection.cursor()
    
    # Single row insert
    insert_query = """
    INSERT INTO students (name, age, email) 
    VALUES (%s, %s, %s)
    """
    
    # Insert one student
    student_data = ("John Doe", 21, "[email protected]")
    cursor.execute(insert_query, student_data)
    
    # Insert multiple students at once
    students_data = [
        ("Jane Smith", 22, "[email protected]"),
        ("Bob Johnson", 20, "[email protected]"),
        ("Alice Brown", 23, "[email protected]")
    ]
    cursor.executemany(insert_query, students_data)
    
    # Commit changes to the database
    connection.commit()
    print(f"{cursor.rowcount} records inserted successfully")
    
except mysql.connector.Error as error:
    print(f"Error: {error}")
    
finally:
    if 'connection' in locals() and connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output:

3 records inserted successfully
MySQL connection is closed
note

We use parameterized queries with %s placeholders to avoid SQL injection attacks. Always follow this practice rather than formatting SQL strings directly.

Querying Data​

Retrieving data from your database is a common operation:

python
import mysql.connector

try:
    connection = mysql.connector.connect(
        host="localhost",
        user="root",
        password="your_password",
        database="python_tutorial"
    )
    
    cursor = connection.cursor()
    
    # Simple SELECT query
    select_query = "SELECT * FROM students"
    cursor.execute(select_query)
    
    # Fetch all rows
    records = cursor.fetchall()
    
    print("Student records:")
    for row in records:
        print(f"ID: {row[0]}")
        print(f"Name: {row[1]}")
        print(f"Age: {row[2]}")
        print(f"Email: {row[3]}")
        print("-" * 30)
    
except mysql.connector.Error as error:
    print(f"Error: {error}")
    
finally:
    if 'connection' in locals() and connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output:

Student records:
ID: 1
Name: John Doe
Age: 21
Email: [email protected]
------------------------------
ID: 2
Name: Jane Smith
Age: 22
Email: [email protected]
------------------------------
ID: 3
Name: Bob Johnson
Age: 20
Email: [email protected]
------------------------------
ID: 4
Name: Alice Brown
Age: 23
Email: [email protected]
------------------------------
MySQL connection is closed

Filtering Data with WHERE Clause​

You can filter data using the WHERE clause:

python
import mysql.connector

try:
    connection = mysql.connector.connect(
        host="localhost",
        user="root",
        password="your_password",
        database="python_tutorial"
    )
    
    cursor = connection.cursor()
    
    # SELECT with WHERE clause
    select_query = "SELECT * FROM students WHERE age > %s"
    age_threshold = 21
    cursor.execute(select_query, (age_threshold,))
    
    # Fetch all rows
    records = cursor.fetchall()
    
    print(f"Students older than {age_threshold}:")
    for row in records:
        print(f"ID: {row[0]}, Name: {row[1]}, Age: {row[2]}")
    
except mysql.connector.Error as error:
    print(f"Error: {error}")
    
finally:
    if 'connection' in locals() and connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output:

Students older than 21:
ID: 2, Name: Jane Smith, Age: 22
ID: 4, Name: Alice Brown, Age: 23
MySQL connection is closed

Updating Data​

Updating existing records is easy with SQL UPDATE statements:

python
import mysql.connector

try:
    connection = mysql.connector.connect(
        host="localhost",
        user="root",
        password="your_password",
        database="python_tutorial"
    )
    
    cursor = connection.cursor()
    
    # UPDATE statement
    update_query = "UPDATE students SET age = %s WHERE name = %s"
    data = (24, "Jane Smith")
    
    cursor.execute(update_query, data)
    connection.commit()
    
    print(f"{cursor.rowcount} record(s) updated")
    
except mysql.connector.Error as error:
    print(f"Error: {error}")
    
finally:
    if 'connection' in locals() and connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output:

1 record(s) updated
MySQL connection is closed

Deleting Data​

You can delete records from your database as follows:

python
import mysql.connector

try:
    connection = mysql.connector.connect(
        host="localhost",
        user="root",
        password="your_password",
        database="python_tutorial"
    )
    
    cursor = connection.cursor()
    
    # DELETE statement
    delete_query = "DELETE FROM students WHERE name = %s"
    data = ("Bob Johnson",)
    
    cursor.execute(delete_query, data)
    connection.commit()
    
    print(f"{cursor.rowcount} record(s) deleted")
    
except mysql.connector.Error as error:
    print(f"Error: {error}")
    
finally:
    if 'connection' in locals() and connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output:

1 record(s) deleted
MySQL connection is closed

Handling Transactions​

Transactions are important for ensuring data integrity. Here's how to use them:

python
import mysql.connector

try:
    connection = mysql.connector.connect(
        host="localhost",
        user="root",
        password="your_password",
        database="python_tutorial"
    )
    
    # Start a transaction
    connection.start_transaction()
    cursor = connection.cursor()
    
    try:
        # Execute multiple operations as part of the transaction
        cursor.execute("UPDATE students SET age = age + 1 WHERE name = 'John Doe'")
        cursor.execute("INSERT INTO students (name, age, email) VALUES ('Mary Wilson', 19, '[email protected]')")
        
        # Commit the transaction
        connection.commit()
        print("Transaction committed successfully")
        
    except mysql.connector.Error as error:
        # If anything goes wrong, roll back the transaction
        connection.rollback()
        print(f"Transaction rolled back because of error: {error}")
    
except mysql.connector.Error as error:
    print(f"Error: {error}")
    
finally:
    if 'connection' in locals() and connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output:

Transaction committed successfully
MySQL connection is closed

Real-World Application: Student Management System​

Let's combine what we've learned to create a simple student management system:

python
import mysql.connector
import datetime

class StudentDatabase:
    def __init__(self, host, user, password, database):
        self.connection = mysql.connector.connect(
            host=host,
            user=user,
            password=password,
            database=database
        )
        self.cursor = self.connection.cursor()
        self.setup_database()
        
    def setup_database(self):
        # Create tables if they don't exist
        self.cursor.execute("""
        CREATE TABLE IF NOT EXISTS students (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            birth_date DATE,
            enrollment_date DATE,
            email VARCHAR(100) UNIQUE
        )
        """)
        self.connection.commit()
        
    def add_student(self, name, birth_date, email):
        try:
            enrollment_date = datetime.date.today()
            query = """INSERT INTO students (name, birth_date, enrollment_date, email)
                    VALUES (%s, %s, %s, %s)"""
            self.cursor.execute(query, (name, birth_date, enrollment_date, email))
            self.connection.commit()
            return True
        except mysql.connector.Error as error:
            print(f"Failed to add student: {error}")
            return False
    
    def get_all_students(self):
        self.cursor.execute("SELECT * FROM students")
        return self.cursor.fetchall()
    
    def get_student_by_id(self, student_id):
        query = "SELECT * FROM students WHERE id = %s"
        self.cursor.execute(query, (student_id,))
        return self.cursor.fetchone()
    
    def update_student(self, student_id, name=None, email=None):
        try:
            if name and email:
                query = "UPDATE students SET name = %s, email = %s WHERE id = %s"
                self.cursor.execute(query, (name, email, student_id))
            elif name:
                query = "UPDATE students SET name = %s WHERE id = %s"
                self.cursor.execute(query, (name, student_id))
            elif email:
                query = "UPDATE students SET email = %s WHERE id = %s"
                self.cursor.execute(query, (email, student_id))
                
            self.connection.commit()
            return True
        except mysql.connector.Error as error:
            print(f"Failed to update student: {error}")
            return False
    
    def delete_student(self, student_id):
        try:
            query = "DELETE FROM students WHERE id = %s"
            self.cursor.execute(query, (student_id,))
            self.connection.commit()
            return True
        except mysql.connector.Error as error:
            print(f"Failed to delete student: {error}")
            return False
    
    def close(self):
        self.cursor.close()
        self.connection.close()

# Usage Example
if __name__ == "__main__":
    db = StudentDatabase("localhost", "root", "your_password", "python_tutorial")
    
    # Add students
    db.add_student("Sarah Connor", "1995-05-12", "[email protected]")
    db.add_student("John Connor", "1997-08-29", "[email protected]")
    
    # Get all students
    print("\nAll Students:")
    students = db.get_all_students()
    for student in students:
        print(f"ID: {student[0]}, Name: {student[1]}, Birth Date: {student[2]}, Enrollment: {student[3]}, Email: {student[4]}")
    
    # Update a student
    db.update_student(1, email="[email protected]")
    
    # Get a specific student
    print("\nUpdated Student:")
    student = db.get_student_by_id(1)
    print(f"ID: {student[0]}, Name: {student[1]}, Birth Date: {student[2]}, Enrollment: {student[3]}, Email: {student[4]}")
    
    # Delete a student
    db.delete_student(2)
    
    # Check remaining students
    print("\nRemaining Students after deletion:")
    students = db.get_all_students()
    for student in students:
        print(f"ID: {student[0]}, Name: {student[1]}, Email: {student[4]}")
    
    # Close the connection
    db.close()

This example demonstrates a practical implementation of a student database system. It encapsulates all the database operations in a class, making it easy to use and maintain.

Best Practices​

  1. Always use parameterized queries to prevent SQL injection attacks
  2. Close connections when you're done to free up resources
  3. Use transactions for operations that need to be executed as a unit
  4. Handle exceptions properly to make your application robust
  5. Consider connection pooling for high-performance applications
  6. Don't store database credentials in code for production applications; use environment variables instead

Connection Pooling​

For applications that need to handle multiple concurrent requests, connection pooling can significantly improve performance:

python
import mysql.connector
from mysql.connector import pooling

try:
    # Create a connection pool
    connection_pool = pooling.MySQLConnectionPool(
        pool_name="mypool",
        pool_size=5,  # Adjust based on your application's needs
        host="localhost",
        user="root",
        password="your_password",
        database="python_tutorial"
    )
    
    # Get a connection from the pool
    connection = connection_pool.get_connection()
    
    if connection.is_connected():
        print("Connected to MySQL using connection pool")
        
        cursor = connection.cursor()
        cursor.execute("SELECT * FROM students")
        results = cursor.fetchall()
        
        for row in results:
            print(row)
            
        cursor.close()
        
    # Return the connection to the pool (don't actually close it)
    connection.close()
    print("Connection returned to pool")
    
except mysql.connector.Error as error:
    print(f"Error: {error}")

Summary​

In this tutorial, you've learned how to:

  1. Install and import the MySQL Connector/Python library
  2. Establish connections to MySQL databases
  3. Create databases and tables
  4. Insert, query, update, and delete data
  5. Handle transactions for data integrity
  6. Implement a practical student management system
  7. Use connection pooling for improved performance

With these skills, you can now build Python applications that efficiently interact with MySQL databases. As you become more comfortable with these concepts, you can explore more advanced features like stored procedures, triggers, and optimization techniques.

Additional Resources​

Exercises​

  1. Create a simple address book application that stores contacts in a MySQL database
  2. Implement a library management system with tables for books, authors, and borrowers
  3. Build a blog post system with posts and comments tables with a one-to-many relationship
  4. Create a function that backs up a MySQL table to a CSV file
  5. Extend the student management system to include courses and enrollment (many-to-many relationship)

By completing these exercises, you'll gain practical experience and strengthen your understanding of Python MySQL connectivity.


If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)