Once you've created a table in your SQLite database, the next essential step is to populate it with data. Python’s built-in sqlite3 module makes it easy to perform INSERT operations using parameterized queries, ensuring your data is safe and properly formatted.

In this tutorial, you’ll learn how to:

• Insert single and multiple rows into an SQLite table

• Use parameterized queries to prevent SQL injection

• Handle exceptions and commit transactions

• Avoid common pitfalls

✅ Prerequisites

• Python 3.x installed

• SQLite database and at least one table created

If you haven’t already created a table, refer to our Create Table in SQLite using Python guide.

Step 1: Connect to the SQLite Database

import sqlite3

# Connect to the database file (or create it if it doesn't exist)
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

Step 2: Insert a Single Row of Data

Let’s say we have a table named Users with columns: id, name, email, age.

cursor.execute('''
INSERT INTO Users (name, email, age)
VALUES (?, ?, ?)
''', ("Alice", "[email protected]", 30))

# Save (commit) the changes
conn.commit()

Why use ? placeholders?

These parameterized queries:

• Prevent SQL injection

• Automatically handle string quoting

• Make code cleaner and safer

Step 3: Insert Multiple Rows at Once

Use executemany() to insert multiple users efficiently.

users = [
    ("Bob", "[email protected]", 25),
    ("Charlie", "[email protected]", 35),
    ("Diana", "[email protected]", 28)
]

cursor.executemany('''
INSERT INTO Users (name, email, age)
VALUES (?, ?, ?)
''', users)

conn.commit()

✅ This is much faster than running multiple execute() calls in a loop.

Optional: Insert and Return Last Row ID

SQLite can return the ID of the last inserted row:

cursor.execute('''
INSERT INTO Users (name, email, age)
VALUES (?, ?, ?)
''', ("Eve", "[email protected]", 22))

user_id = cursor.lastrowid
print("Inserted user with ID:", user_id)

Full Working Example: Insert Data in SQLite with Python

import sqlite3

# Connect to SQLite DB
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Insert a single user
cursor.execute('''
INSERT INTO Users (name, email, age)
VALUES (?, ?, ?)
''', ("Alice", "[email protected]", 30))

# Insert multiple users
users = [
    ("Bob", "[email protected]", 25),
    ("Charlie", "[email protected]", 35),
    ("Diana", "[email protected]", 28)
]
cursor.executemany('''
INSERT INTO Users (name, email, age)
VALUES (?, ?, ?)
''', users)

# Commit changes
conn.commit()

# Close connection
conn.close()

print("Data inserted successfully.")

Tips for Inserting Data into SQLite with Python

Example with exception handling:

try:
    cursor.execute('''
    INSERT INTO Users (name, email, age)
    VALUES (?, ?, ?)
    ''', ("Frank", "[email protected]", 40))
    conn.commit()
except sqlite3.IntegrityError as e:
    print("Insert failed:", e)

⚠️ Common Pitfalls

Best Practices

• Use named placeholders (:name) for complex insert logic.

• Avoid hardcoding values directly into SQL strings.

• Keep inserts wrapped in transactions for performance and rollback support.

• Structure insert logic into reusable functions for cleaner code.

Example using named parameters:

cursor.execute('''
INSERT INTO Users (name, email, age)
VALUES (:name, :email, :age)
''', {"name": "Grace", "email": "[email protected]", "age": 26})

Conclusion

Inserting data into a SQLite database using Python is simple and secure using the sqlite3 module. Whether you’re adding a single row or importing hundreds of records, parameterized queries and good error handling will ensure your application is fast and safe. With just a few lines of code, you're ready to store persistent data in your application.