Working with relational databases is a critical skill for backend developers and data engineers. Python, being one of the most powerful and flexible programming languages, offers excellent libraries to work with databases like MySQL. In this blog, we’ll walk you through how to connect a Python project to a MySQL database — step-by-step.

Whether you're building a data-driven application or performing advanced data analysis, this guide will equip you with the essential knowledge and practical examples to get started efficiently.

Why Connect Python to MySQL?

• Flexibility: With libraries like mysql-connector-python and SQLAlchemy, connecting and working with MySQL from Python is simple and powerful.
• Scalability: MySQL handles large datasets efficiently, making it ideal for startups and enterprises.
• Automation: Python scripts can automate database operations, reducing manual work.
  

Prerequisites for Connecting Python to MySQL

Before diving into coding, make sure the following tools are installed:

Python

Ensure Python is installed on your system. You can verify by running:

Code

    python --version              
            

If not installed, download the latest version from Download Python

MySQL Server Installed 

Make sure you have a running MySQL server. You can download MySQL from Download MySQL

Also, note your:

• Host (usually localhost)
• Port (default is 3306)
• Username & Password
• Database name 

Install MySQL Connector for Python

Python doesn’t natively support MySQL, so we need a driver. The most popular options are:

• mysql-connector-python (Official MySQL driver)
• PyMySQL (Pure Python alternative)
• SQLAlchemy (ORM for advanced use cases)

You’ll need the mysql-connector-python module to connect with MySQL

Code

    pip install mysql-connector-python             
            

Why Use MySQL-Connector-Python?

This is an official Oracle-supported library and comes with many advantages:

• Easy to use
• Pure Python implementation
• Actively maintained
• No external dependencies

If you're looking for advanced ORM support, you might consider SQLAlchemy, but for direct queries, mysql-connector-python is ideal.

Step-by-Step Guide to Connect Python with MySQL

Let’s build a simple Python script to demonstrate CRUD (Create, Read, Update, Delete) operations.

Step 1: Establish a Connection to MySQL 

Create a separate file (connector.py) to handle the connection setup:

Code

    # connector.py
    import mysql.connector
    
    # Database configuration
    config = {
        'user': 'your_username',
        'password': 'your_password',
        'host': 'localhost',
        'database': 'your_database_name',
        'raise_on_warnings': True
    }
    
    def get_connection():
        try:
            connection = mysql.connector.connect(**config)
            if connection.is_connected():
                return connection
    
        except Error as e:
                print("Error while connecting to MySQL:", e)
                return None                         
            

Step 2: Create a Table in the Database 

Once you’re connected, it’s time to chat with MySQL.

But you can’t just talk directly—you need a cursor

Code

    # create_table.py
    from connector import get_connection
    
    # SQL statement to create a table
    
    create_table_query = """
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100),
        email VARCHAR(100),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    """
    conn = get_connection()
    if conn:
        cursor = conn.cursor()
        cursor.execute(create_table_query)
        print("Table created! The database is listening.")
        
        cursor.close()
        conn.close()                                       
            

Step 3: Insert Some Data in the User Table 

You can now insert new records: 

Code

    # insert_data.py
    from connector import get_connection
    
    conn = get_connection()
    if conn:
        cursor = conn.cursor()
        insert_query = "INSERT INTO users (name, email) VALUES (%s, %s)"
        data = ("John Doe", "john@example.com")
        cursor.execute(insert_query, data)
    
        conn.commit()
        print(f"{cursor.rowcount} record inserted.")
    
        cursor.close()
        conn.close()                                             
            

Step 4: Read or Query Data 

To retrieve and display data from the users table:

Code

        # fetch_data.py
        from connector import get_connection
        
        conn = get_connection()
        
        if conn:
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM users")
            rows = cursor.fetchall()
            for row in rows:
                print(row)
        
            cursor.close()
            conn.close()                                      
                

Step 5: Update a Record

Update a user's information (e.g., name or email):

Code

    # update_data.py
    from connector import get_connection
    conn = get_connection()
    if conn:
        cursor = conn.cursor()
        update_query = "UPDATE users SET email = %s WHERE name = %s"
        data = ("john.doe@newdomain.com", "John Doe")
        cursor.execute(update_query, data)
        conn.commit()
        print(f"{cursor.rowcount} record updated.")
        cursor.close()
        conn.close()                                                        
                

Step 6: Delete a Record

Remove a user from the database:

Code

        # delete_data.py
        from connector import get_connection
        
        conn = get_connection()
        
        if conn:
            cursor = conn.cursor()
            delete_query = "DELETE FROM users WHERE name = %s"
            data = ("John Doe",)
            cursor.execute(delete_query, data)
        
            conn.commit()
            print(f"{cursor.rowcount} record deleted.")
        
            cursor.close()
            conn.close()                                                                           
                    

Step 7: Close the Connection

Although each script already closes the connection, it’s a good practice to wrap the connection

Code

    cursor.close()
    connection.close()
    print("Connection closed")                                                                          
                    

Best Practices

Use Environment Variables for Credentials

Avoid hardcoding credentials like DB host, user, or password. Store them in a .env file and load them using python-dotenv.

.env file example:

Code

    DB_HOST=localhost
    DB_PORT=3306
    DB_USER=my_user
    DB_PASSWORD=your_password
    DB_NAME=your_database                                                                                               
                    

connector.py:

Code

    from dotenv import load_dotenv
    import os, mysql.connector
    
    load_dotenv()
    
    def get_connection():
        return mysql.connector.connect(
            host=os.getenv("DB_HOST"),
            port=os.getenv("DB_PORT"),
            user=os.getenv("DB_USER"),
            password=os.getenv("DB_PASSWORD"),
            database=os.getenv("DB_NAME")
        )                                                                                                                    
                    

• Add .env to .gitignore
• Consider using ORMs like SQLAlchemy for complex applications to simplify data modeling and queries.

Security Considerations

When working with databases, it’s important to follow security best practices to protect your application and user data:

• Never hardcode credentials: Store your database username and password in environment variables or use a .env file with libraries like python-dotenv.
• Use parameterized queries: Always use placeholders (%s) in SQL statements to prevent SQL injection attacks.
• Limit database privileges: Create a MySQL user with only the necessary permissions (e.g., only read/write access to specific tables, not full admin rights).
• Avoid exposing database ports: If you’re hosting your database remotely, make sure to use a firewall and disable public access to the MySQL port (3306 by default).
• Use SSL connections: When connecting to remote databases, enable SSL to encrypt data in transit.

Hire Now!

Hire Python Developers Today!

Ready to bring your application vision to life? Start your project with Zignuts expert Python developers.

**Hire now**Hire Now**Hire Now**Hire now**Hire now

Hire now

Conclusion

Connecting Python with MySQL is straightforward and highly useful for real-world applications like inventory management, user authentication systems, and data analytics platforms. By mastering this integration, you unlock the full potential of Python in data-centric projects.

By following this guide, you should now be able to connect Python to a MySQL database, create tables, insert data, and fetch results with ease. Happy coding!