Database INSERT Operation in Python

The INSERT operation is used to add new records to a database table in Python. This operation requires establishing a database connection, preparing SQL statements, and handling transactions properly.

Basic INSERT Statement

The following example executes an SQL INSERT statement to create a record in the EMPLOYEE table ?

#!/usr/bin/python
import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
    LAST_NAME, AGE, SEX, INCOME)
    VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""

try:
    # Execute the SQL command
    cursor.execute(sql)
    # Commit your changes in the database
    db.commit()
    print("Record inserted successfully")
except:
    # Rollback in case there is any error
    db.rollback()
    print("Error: unable to insert data")

# disconnect from server
db.close()

Dynamic INSERT with String Formatting

You can create SQL queries dynamically by using string formatting to insert variable values ?

#!/usr/bin/python
import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Define values to insert
first_name = 'Mac'
last_name = 'Mohan'
age = 20
sex = 'M'
income = 2000

# Prepare SQL query to INSERT a record into the database
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
    LAST_NAME, AGE, SEX, INCOME) \
    VALUES ('%s', '%s', %d, '%c', %d)" % \
    (first_name, last_name, age, sex, income)

try:
    # Execute the SQL command
    cursor.execute(sql)
    # Commit your changes in the database
    db.commit()
    print("Record inserted successfully")
except:
    # Rollback in case there is any error
    db.rollback()
    print("Error: unable to insert data")

# disconnect from server
db.close()

Parameterized INSERT (Recommended)

The safest way to insert data is using parameterized queries to prevent SQL injection attacks ?

#!/usr/bin/python
import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")

# prepare a cursor object using cursor() method
cursor = db.cursor()

# User data
user_id = "test123"
password = "password"

try:
    # Execute parameterized query
    cursor.execute('INSERT INTO Login VALUES (%s, %s)', (user_id, password))
    # Commit the transaction
    db.commit()
    print("Login record inserted successfully")
except:
    # Rollback in case there is any error
    db.rollback()
    print("Error: unable to insert login data")

# disconnect from server
db.close()

Key Points

• Always use try-except blocks to handle database errors
• Use db.commit() to save changes permanently
• Use db.rollback() to undo changes if errors occur
• Close the database connection with db.close()
• Prefer parameterized queries over string formatting for security

Conclusion

Database INSERT operations in Python require proper connection handling, SQL statement preparation, and transaction management. Always use parameterized queries to prevent SQL injection attacks and ensure data security.