Add Data to SQLite Database Python

Adding data to SQLite databases in Python is straightforward once you understand the basics. This guide covers essential techniques for inserting records safely and efficiently.

We'll explore the sqlite3 module, which comes built-in with Python. No external packages required. You can start inserting data immediately after setting up your database connection.

Basic INSERT Statement

The simplest way to add data uses an INSERT statement. First, connect to your database and create a cursor object.

import sqlite3

# Connect to database (or create it)
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

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

# Insert single record
cursor.execute("INSERT INTO users (name, age) VALUES ('Alice', 30)")
conn.commit()
print("Data added successfully")

Data added successfully

Always remember to call commit() to save changes permanently. Without it, your data won't persist in the database.

Parameterized Queries

Using parameterized queries prevents SQL injection attacks. They also handle special characters automatically.

# Safe way using parameters
user_data = ('Bob', 25)
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", user_data)
conn.commit()

# Another example with dictionary-style parameters
cursor.execute(
    "INSERT INTO users (name, age) VALUES (:name, :age)",
    {'name': 'Charlie', 'age': 35}
)
conn.commit()
print("Records inserted using parameterized queries")

Records inserted using parameterized queries

Parameterized queries use placeholders like question marks (?) or named parameters (:name). This approach is highly recommended for all database operations involving user input.

Insert Multiple Records

To insert many records at once, use the executemany() method. It's faster than executing individual INSERT statements in a loop.

# Prepare multiple records
users = [
    ('David', 40),
    ('Eva', 28),
    ('Frank', 33)
]

# Insert all records in one go
cursor.executemany(
    "INSERT INTO users (name, age) VALUES (?, ?)",
    users
)
conn.commit()
print(f"{cursor.rowcount} records inserted")

3 records inserted

The rowcount attribute tells you how many rows were affected by the last operation. This helps verify successful data insertion.

Error Handling Best Practices

Wrap database operations in try-except blocks. This catches errors gracefully instead of crashing your program.

try:
    cursor.execute(
        "INSERT INTO users (name, age) VALUES (?, ?)",
        ('Grace', 22)
    )
    conn.commit()
    print("User added successfully")
except sqlite3.Error as e:
    print(f"Database error: {e}")
    conn.rollback()  # Undo any partial changes
finally:
    conn.close()     # Always close the connection

User added successfully

Always use rollback() when errors occur during transactions. This maintains database integrity by undoing incomplete changes.

Working with Existing Tables

When adding data to existing tables, ensure column names match exactly. Mismatched names cause runtime errors.

# Check table structure first
cursor.execute("PRAGMA table_info(users)")
columns = cursor.fetchall()
print("Table columns:")
for col in columns:
    print(f"  {col[1]} ({col[2]})")

# Now insert matching the schema
cursor.execute(
    "INSERT INTO users (name, age) VALUES (?, ?)",
    ('Henry', 45)
)
conn.commit()

Table columns:
  id (INTEGER)
  name (TEXT)
  age (INTEGER)

Advanced Techniques

For complex scenarios, combine data insertion with other operations inside transactions. This ensures atomicity.

def add_user_with_log(name, age):
    try:
        # Start transaction implicitly
        cursor.execute(
            "INSERT INTO users (name, age) VALUES (?, ?)",
            (name, age)
        )
        
        # Log the action
        cursor.execute(
            "INSERT INTO logs (message) VALUES (?)",
            (f"Added user: {name}",)
        )
        
        conn.commit()
        return True
    except Exception as e:
        conn.rollback()
        print(f"Operation failed: {e}")
        return False

# Usage
result = add_user_with_log('Ivy', 27)
print(f"Operation successful: {result}")

Operation successful: True

Transactions group related operations. If one fails, all changes roll back automatically, maintaining consistency.

Related Resources

For beginners starting fresh, check our guide on creating SQLite databases in Python. It explains database setup step by step.

Learn about handling text data types properly in our text_factory guide. This helps manage encoding issues.

Understand transaction management better with our isolation level guide. It covers advanced transaction control.

Common Pitfalls to Avoid

Never concatenate strings directly into SQL queries. This creates security vulnerabilities and bugs.

# BAD: Vulnerable to SQL injection
# name = input("Enter name: ")
# cursor.execute(f"INSERT INTO users (name) VALUES ('{name}')")

# GOOD: Always use parameterized queries
name = "O'Brien"  # Special character handled safely
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", (name, 29))
conn.commit()
print("Special character inserted safely")

Special character inserted safely

Parameterized queries handle quotes automatically. Names like O'Brien work without extra escaping.

Retrieving Inserted Data

After inserting data, verify it was stored correctly by querying the database.

# Verify inserted data
cursor.execute("SELECT * FROM users ORDER BY id DESC LIMIT 5")
rows = cursor.fetchall()

print("Latest users:")
for row in rows:
    print(f"  ID: {row[0]}, Name: {row[1]}, Age: {row[2]}")

conn.close()

Latest users:
  ID: 8, Name: Ivy, Age: 27
  ID: 7, Name: Henry, Age: 45
  ID: 6, Name: Grace, Age: 22
  ID: 5, Name: Frank, Age: 33
  ID: 4, Name: Eva, Age: 28

Conclusion

Adding data to SQLite databases in Python requires understanding several key concepts. Use parameterized queries for security, handle errors properly, and always commit transactions.

The sqlite3 module provides powerful tools for data manipulation. Master basic INSERT operations first, then progress to advanced techniques like batch inserts and transaction management.

Remember to close database connections after use. This prevents resource leaks and ensures data integrity. With practice, inserting data becomes second nature.

Start with simple examples from this guide. Gradually incorporate error handling and advanced features into your projects. Soon you'll confidently manage SQLite databases in Python.