How to insert a Python tuple in a MySQL database?

Inserting a Python tuple into a MySQL database is a common task when working with structured data. This tutorial shows how to insert tuple data into a MySQL table using the PyMySQL module.

Prerequisites

Before starting, ensure you have:

• A MySQL database named test
• A table named employee with fields: fname, lname, age, gender, salary
• PyMySQL module installed (pip install PyMySQL)

Sample Tuple Data

Let's define a tuple containing employee data ?

# Sample employee data as tuple
employee_data = ('Mac', 'Mohan', 20, 'M', 2000)
print("Employee data:", employee_data)

Employee data: ('Mac', 'Mohan', 20, 'M', 2000)

Establishing Database Connection

First, establish a connection to the MySQL database ?

import pymysql

# Open database connection
db = pymysql.connect(
    host="localhost",
    user="root", 
    password="",
    database="test"
)

# Prepare cursor object
cursor = db.cursor()

Method 1: Using String Formatting

Insert the tuple data using string formatting ?

import pymysql

# Sample data
employee_data = ('Mac', 'Mohan', 20, 'M', 2000)

# Database connection
db = pymysql.connect(host="localhost", user="root", password="", database="test")
cursor = db.cursor()

# Create SQL query using tuple data
sql = "INSERT INTO employee VALUES('%s', '%s', %d, '%s', %d)" % employee_data

try:
    # Execute the query
    cursor.execute(sql)
    db.commit()
    print("Record inserted successfully")
except Exception as e:
    db.rollback()
    print("Error:", e)
finally:
    db.close()

Method 2: Using Parameterized Queries (Recommended)

A safer approach using parameterized queries to prevent SQL injection ?

import pymysql

# Sample data
employee_data = ('John', 'Doe', 25, 'M', 3000)

# Database connection
db = pymysql.connect(host="localhost", user="root", password="", database="test")
cursor = db.cursor()

# Parameterized query (safer)
sql = "INSERT INTO employee (fname, lname, age, gender, salary) VALUES (%s, %s, %s, %s, %s)"

try:
    # Execute with tuple as parameter
    cursor.execute(sql, employee_data)
    db.commit()
    print("Record inserted successfully using parameterized query")
except Exception as e:
    db.rollback()
    print("Error:", e)
finally:
    db.close()

Inserting Multiple Tuples

You can insert multiple records using executemany() ?

import pymysql

# Multiple employee records
employees = [
    ('Alice', 'Smith', 28, 'F', 3500),
    ('Bob', 'Johnson', 32, 'M', 4000),
    ('Carol', 'Brown', 26, 'F', 2800)
]

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

try:
    # Insert multiple records
    cursor.executemany(sql, employees)
    db.commit()
    print(f"{cursor.rowcount} records inserted")
except Exception as e:
    db.rollback()
    print("Error:", e)
finally:
    db.close()

Best Practices

Conclusion

Use parameterized queries with cursor.execute(sql, tuple_data) for secure tuple insertion. Always handle exceptions and close database connections properly to ensure data integrity.