Insert Data

Learn how to safely insert data into a database using Python by leveraging parameterized queries to prevent SQL injection and ensure transaction integrity.

What it is

Inserting data involves adding new rows to a table in a relational database. In Python, this is typically done using the execute() method of a cursor object. The critical concept here is parameterization. Instead of embedding values directly into the SQL string (which is dangerous), you use placeholders (like %s or ?) and pass the actual values as a separate tuple or list. The database driver handles the escaping and type conversion automatically.

Related terms include SQL Injection, Transactions, Commit, and Rollback.

Why it matters

• Security: Parameterized queries prevent attackers from injecting malicious SQL code through user input.
• Data Integrity: Proper handling ensures that special characters (like quotes) do not break the SQL syntax.
• Performance: Many databases can cache prepared statements, making repeated inserts faster.
• Atomicity: Using transactions ensures that either all changes are saved or none are, preventing partial data corruption.

Syntax or steps

1. Establish a connection to the database (conn = connect(...)).
2. Create a cursor object (cur = conn.cursor()).
3. Define the SQL statement with placeholders for values.
4. Execute the statement, passing the values as a second argument (a tuple).
5. Commit the transaction to save changes permanently.
6. Close the cursor and connection.

Example

import sqlite3

# 1. Connect to database (creates file if missing)
conn = sqlite3.connect('example.db')
cur = conn.cursor()

# Create table if it doesn't exist
cur.execute('''CREATE TABLE IF NOT EXISTS users
               (id INTEGER PRIMARY KEY, name TEXT, email TEXT)''')

# 2. Define safe insertion query with placeholders
sql = "INSERT INTO users (id, name, email) VALUES (?, ?, ?)"

# 3. Prepare data (note: single value must be a tuple with trailing comma)
user_data = (1, "Ada Lovelace", "ada@example.com")

try:
    # 4. Execute with parameters
    cur.execute(sql, user_data)
    
    # 5. Commit changes to make them permanent
    conn.commit()
    print("User inserted successfully.")
except Exception as e:
    # Rollback if something goes wrong
    conn.rollback()
    print(f"Error inserting user: {e}")
finally:
    # Clean up resources
    cur.close()
    conn.close()

Explanation: We use ? as placeholders because SQLite uses this style. For MySQL/PostgreSQL, you might use %s. The data is passed as a tuple (1, "Ada Lovelace", ...). If an error occurs during execution, conn.rollback() undoes any uncommitted changes, keeping the database consistent.

Common mistakes

• String Formatting: Never use f-strings or % formatting to build SQL queries (e.g., f"INSERT... '{name}'"). This opens the door to SQL injection.
• Missing Commits: Forgetting conn.commit() means your data exists only in the current session and will disappear when the connection closes.
• Tuple Syntax: When inserting a single column, forgetting the trailing comma in the tuple (e.g., ("value") instead of ("value",)) causes errors because Python treats parentheses without commas as grouping operators, not tuples.
• Resource Leaks: Not closing cursors or connections can exhaust database resources over time.

When to use it

Practice

Guided Exercise: Modify the example above to insert two users at once using cur.executemany(). Pass a list of tuples containing different names and emails.

Challenge: Write a script that attempts to insert a user with a duplicate ID (primary key). Catch the specific exception raised by the database and print "Duplicate entry detected." Ensure the previous successful inserts remain intact.

Quick check

Q: Why is cur.execute("INSERT INTO t VALUES (%s)", (val,)) safer than cur.execute(f"INSERT INTO t VALUES ({val})")?
A: The first method uses parameterization, where the database driver escapes the value, preventing SQL injection. The second embeds raw text into the command, allowing malicious input to alter the query structure.

Summary

Safe data insertion relies on separating SQL logic from data values using placeholders. Always commit transactions explicitly and handle exceptions to maintain database consistency. Mastering parameterized queries is the first step toward building secure backend applications.