How to insert a Python object in MySQL?

To insert Python objects like tuples, lists, or dictionaries into a MySQL database, you need to establish a connection and use parameterized queries. This tutorial shows how to safely insert Python data structures into MySQL tables.

Prerequisites

First, install the PyMySQL module and ensure you have a MySQL database with an employee table ?

pip install PyMySQL

Create the employee table structure ?

CREATE TABLE employee (
    fname VARCHAR(50),
    lname VARCHAR(50), 
    age INT,
    gender CHAR(1),
    salary INT
);

Inserting a Tuple Object

Let's insert a tuple containing employee data into the MySQL database ?

import pymysql

# Sample tuple object
employee_data = ('Steven', 'Assange', 21, 'M', 2001)

# Database connection (replace with your credentials)
try:
    # Open database connection
    db = pymysql.connect(
        host='localhost',
        user='root',
        password='',
        database='test'
    )
    
    # Prepare a cursor object
    cursor = db.cursor()
    
    # Use parameterized query for safety
    sql = "INSERT INTO employee (fname, lname, age, gender, salary) VALUES (%s, %s, %s, %s, %s)"
    
    # Execute the query
    cursor.execute(sql, employee_data)
    
    # Commit the transaction
    db.commit()
    print("Record inserted successfully!")
    
except Exception as e:
    print(f"Error: {e}")
    db.rollback()
    
finally:
    # Close the connection
    db.close()

Record inserted successfully!

Inserting Multiple Records

You can insert multiple Python objects using executemany() ?

import pymysql

# List of tuples
employees = [
    ('John', 'Doe', 25, 'M', 3000),
    ('Jane', 'Smith', 30, 'F', 3500),
    ('Bob', 'Johnson', 28, 'M', 2800)
]

try:
    db = pymysql.connect(
        host='localhost',
        user='root', 
        password='',
        database='test'
    )
    
    cursor = db.cursor()
    
    sql = "INSERT INTO employee (fname, lname, age, gender, salary) VALUES (%s, %s, %s, %s, %s)"
    
    # Insert multiple records
    cursor.executemany(sql, employees)
    
    db.commit()
    print(f"{cursor.rowcount} records inserted successfully!")
    
except Exception as e:
    print(f"Error: {e}")
    db.rollback()
    
finally:
    db.close()

3 records inserted successfully!

Inserting Dictionary Objects

You can also insert data from Python dictionaries using named placeholders ?

import pymysql

# Dictionary object
employee_dict = {
    'fname': 'Alice',
    'lname': 'Wilson', 
    'age': 26,
    'gender': 'F',
    'salary': 4000
}

try:
    db = pymysql.connect(
        host='localhost',
        user='root',
        password='',
        database='test'
    )
    
    cursor = db.cursor()
    
    # Using dictionary placeholders
    sql = """INSERT INTO employee (fname, lname, age, gender, salary) 
             VALUES (%(fname)s, %(lname)s, %(age)s, %(gender)s, %(salary)s)"""
    
    cursor.execute(sql, employee_dict)
    
    db.commit()
    print("Dictionary data inserted successfully!")
    
except Exception as e:
    print(f"Error: {e}")
    db.rollback()
    
finally:
    db.close()

Dictionary data inserted successfully!

Best Practices

Conclusion

Use parameterized queries with execute() for single records or executemany() for multiple records. Always handle exceptions and commit transactions to ensure data integrity when inserting Python objects into MySQL.