Insert Into SQLite Python: Step-by-Step Guide

SQLite is a lightweight, serverless database engine widely used in Python applications. Inserting data into an SQLite database in Python is straightforward once you understand the process. This guide will walk you through the steps, from connecting to a database to executing INSERT statements safely.

Prerequisites: Installing SQLite and Python

Before inserting data, ensure Python and the built-in sqlite3 module are available. Python’s sqlite3 module is included by default. To check, run a simple script. If not installed, refer to the Python SQLite Install: Complete Setup Guide for instructions.

Connecting to a SQLite Database

To interact with an SQLite database, first connect to it using sqlite3.connect(). This function creates a database file if it doesn't exist. Here's how:

import sqlite3

# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('example.db')
print("Connected to SQLite database.")
    

Connected to SQLite database.
    

Creating a Table for Inserting Data

Before inserting data, create a table. Use the cursor.execute() method to run a CREATE TABLE statement. For example:

# Create a cursor object
cursor = conn.cursor()

# Create a table
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER
)
''')
print("Table created successfully.")
    

Table created successfully.
    

Inserting Data Using the INSERT Statement

Use the INSERT INTO SQL command to add rows. Here's an example with hardcoded values:

# Insert a single row
cursor.execute('''
INSERT INTO users (name, age)
VALUES ('Alice', 30)
''')
print("Data inserted successfully.")
    

Data inserted successfully.
    

Using Parameterized Queries for Security

Hardcoding values can expose your app to SQL injection. Instead, use parameterized queries with ? placeholders:

# Insert data using a parameterized query
user_data = ('Bob', 25)
cursor.execute('''
INSERT INTO users (name, age)
VALUES (?, ?)
''', user_data)
print("Secure data inserted.")
    

Secure data inserted.
    

Committing Changes and Closing the Connection

Always commit your changes to save them. Then close the connection to free resources:

# Commit the transaction
conn.commit()
print("Changes committed.")

# Close the connection
conn.close()
print("Connection closed.")
    

Changes committed.
Connection closed.
    

Fetching Inserted Data for Verification

To verify the insertion, query the table using cursor.fetchall(). For a single row, use fetchone(). Learn more about retrieving data in the Fetchone Python SQLite: Retrieve Single Rows guide.

# Reconnect to verify data
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
print("Inserted records:")
for row in rows:
    print(row)
conn.close()
    

Inserted records:
(1, 'Alice', 30)
(2, 'Bob', 25)
    

Common Errors and Troubleshooting

1. OperationalError: no such table – Ensure the table exists before inserting.

2. IntegrityError: UNIQUE constraint failed – Check for duplicate primary keys.

3. Database is locked – Avoid overlapping writes or reduce transaction duration.

For more basics, explore the Python SQLite Example: Simple Database Operations guide.

Conclusion

Inserting data into SQLite with Python is a critical skill for database-driven applications. By following best practices like parameterized queries and proper connection management, you can avoid common pitfalls. With this guide, you’re ready to insert and manage data efficiently. Keep experimenting and refer to SQLite documentation for advanced features.