Of course! Inserting data into a MySQL database using Python is a common task. The most robust and recommended way to do this is by using a library called mysql-connector-python.

Here's a complete guide, starting from installation and covering best practices like preventing SQL injection.

Step 1: Install the MySQL Connector Library

First, you need to install the official MySQL driver for Python. Open your terminal or command prompt and run:

pip install mysql-connector-python

Step 2: Establish a Connection to Your MySQL Database

Before you can insert data, you need to connect to your MySQL server. You'll need your database credentials:

• Host: Usually localhost if your database is on the same machine.
• User: Your MySQL username (e.g., root).
• Password: Your MySQL password.
• Database: The name of the database you want to use.

Here's how you create a connection:

import mysql.connector
from mysql.connector import Error
try:
    # Establish the connection
    connection = mysql.connector.connect(
        host='localhost',
        user='your_username',
        password='your_password',
        database='your_database_name'
    )
    if connection.is_connected():
        db_info = connection.get_server_info()
        print(f"Successfully connected to MySQL Server version {db_info}")
        cursor = connection.cursor()
        # You can now execute queries using the 'cursor'
        cursor.execute("SELECT database();")
        record = cursor.fetchone()
        print(f"You're connected to the database: {record}")
except Error as e:
    print(f"Error while connecting to MySQL: {e}")
finally:
    # Closing the connection
    if 'connection' in locals() and connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed.")

Step 3: Insert a Single Row of Data

Once connected, you can use a cursor object to execute SQL INSERT statements.

Important Security Note: Never use string formatting (like f"INSERT INTO ... VALUES ({value})") to insert data directly into a query. This makes you vulnerable to SQL Injection attacks.

Instead, use placeholders (%s) and pass the data as a tuple to the cursor.execute() method. The connector will safely escape the data for you.

import mysql.connector
from mysql.connector import Error
def insert_single_record(name, email):
    """ Inserts a single record into the employees table. """
    try:
        connection = mysql.connector.connect(
            host='localhost',
            user='your_username',
            password='your_password',
            database='your_database_name'
        )
        if connection.is_connected():
            cursor = connection.cursor()
            # SQL query with placeholders (%s)
            sql_insert_query = "INSERT INTO employees (name, email) VALUES (%s, %s)"
            # Data to be inserted, passed as a tuple
            insert_tuple = (name, email)
            # Execute the query
            cursor.execute(sql_insert_query, insert_tuple)
            # Commit the transaction to make the change permanent
            connection.commit()
            print(f"{cursor.rowcount} record inserted successfully into employees table.")
    except Error as e:
        print(f"Failed to insert into table: {e}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
# --- Example Usage ---
# Make sure you have a table named 'employees' with columns 'name' and 'email'
# CREATE TABLE employees (
#     id INT AUTO_INCREMENT PRIMARY KEY,
#     name VARCHAR(255) NOT NULL,
#     email VARCHAR(255) NOT NULL UNIQUE
# );
insert_single_record('Alice', 'alice@example.com')

Step 4: Insert Multiple Rows (Bulk Insert)

If you need to insert many rows at once, it's much more efficient to do it in a single database call. You can pass a list of tuples to cursor.executemany().

import mysql.connector
from mysql.connector import Error
def insert_multiple_records(records_to_insert):
    """ Inserts multiple records into the employees table in a single transaction. """
    try:
        connection = mysql.connector.connect(
            host='localhost',
            user='your_username',
            password='your_password',
            database='your_database_name'
        )
        if connection.is_connected():
            cursor = connection.cursor()
            # SQL query with placeholders (%s)
            sql_insert_query = "INSERT INTO employees (name, email) VALUES (%s, %s)"
            # List of tuples, where each tuple is a row to be inserted
            insert_list_of_tuples = [
                ('Bob', 'bob@example.com'),
                ('Charlie', 'charlie@example.com'),
                ('Diana', 'diana@example.com')
            ]
            # Execute the query for all tuples in the list
            cursor.executemany(sql_insert_query, insert_list_of_tuples)
            # Commit the transaction
            connection.commit()
            print(f"{cursor.rowcount} records inserted successfully into employees table.")
    except Error as e:
        print(f"Failed to insert multiple records into table: {e}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
# --- Example Usage ---
insert_multiple_records([])

Step 5: Best Practices & Full Example

Here is a complete, well-structured example that demonstrates good practices:

1. Using Context Managers (with statements): This ensures your database connections and cursors are automatically closed, even if errors occur.
2. Getting the Last Insert ID: After an INSERT, you often need the id of the new row.
3. Error Handling: Gracefully handling potential database errors.

import mysql.connector
from mysql.connector import Error
def get_db_connection():
    """ Creates and returns a database connection. """
    try:
        connection = mysql.connector.connect(
            host='localhost',
            user='your_username',
            password='your_password',
            database='your_database_name'
        )
        return connection
    except Error as e:
        print(f"Error connecting to the database: {e}")
        return None
def insert_employee(name, email):
    """ Inserts a new employee and returns the new employee's ID. """
    connection = None
    cursor = None
    try:
        connection = get_db_connection()
        if not connection:
            return None
        cursor = connection.cursor()
        # Use a dictionary for named placeholders (optional, but can be clearer)
        sql_insert_query = "INSERT INTO employees (name, email) VALUES (%(name)s, %(email)s)"
        data_to_insert = {'name': name, 'email': email}
        cursor.execute(sql_insert_query, data_to_insert)
        # Commit the transaction
        connection.commit()
        # Get the ID of the last inserted row
        last_insert_id = cursor.lastrowid
        print(f"Successfully inserted {name} with ID: {last_insert_id}")
        return last_insert_id
    except Error as e:
        # If something goes wrong, rollback the transaction
        if connection:
            connection.rollback()
        print(f"Failed to insert employee: {e}")
        return None
    finally:
        # Ensure the cursor and connection are closed
        if cursor:
            cursor.close()
        if connection and connection.is_connected():
            connection.close()
# --- Example Usage ---
# 1. Make sure your table is created
# CREATE TABLE employees (
#     id INT AUTO_INCREMENT PRIMARY KEY,
#     name VARCHAR(255) NOT NULL,
#     email VARCHAR(255) NOT NULL UNIQUE
# );
# 2. Call the function
new_employee_id = insert_employee('Eve', 'eve@example.com')
if new_employee_id:
    print(f"The new employee's ID is: {new_employee_id}")

Alternative: Using SQLAlchemy

For larger applications, an Object-Relational Mapper (ORM) like SQLAlchemy is often preferred. It abstracts away the raw SQL and lets you interact with your database using Python objects.

Installation: pip install SQLAlchemy

Example using SQLAlchemy:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
# 1. Setup
Base = declarative_base()
class Employee(Base):
    __tablename__ = 'employees'
    id = Column(Integer, primary_key=True)
    name = Column(String(255))
    email = Column(String(255), unique=True)
    def __repr__(self):
        return f"<Employee(name='{self.name}', email='{self.email}')>"
# Replace with your MySQL connection string
# Format: mysql+mysqlconnector://user:password@host/dbname
DATABASE_URL = "mysql+mysqlconnector://your_username:your_password@localhost/your_database_name"
engine = create_engine(DATABASE_URL)
# Create the table if it doesn't exist
Base.metadata.create_all(engine)
# 2. Insert Data
Session = sessionmaker(bind=engine)
session = Session()
# Create a new employee object
new_employee = Employee(name='Frank', email='frank@example.com')
# Add the object to the session
session.add(new_employee)
# Commit the session to save the object to the database
session.commit()
print(f"New employee added with ID: {new_employee.id}")
# 3. Query to verify
employee_from_db = session.query(Employee).filter_by(name='Frank').first()
print(f"Retrieved from DB: {employee_from_db}")
session.close()

Summary