How to Insert Data into SQLite Using Python: Simple Guide

To insert data into SQLite using Python, use the sqlite3 module to connect to the database, create a cursor, and execute an INSERT INTO SQL statement with cursor.execute(). Finally, call connection.commit() to save changes.

Syntax

Here is the basic syntax to insert data into an SQLite database using Python:

• import sqlite3: Import the SQLite module.
• conn = sqlite3.connect('database.db'): Connect to the database file.
• cursor = conn.cursor(): Create a cursor to execute SQL commands.
• cursor.execute("INSERT INTO table_name (column1, column2) VALUES (?, ?)", (value1, value2)): Insert data using placeholders ? to avoid SQL injection.
• conn.commit(): Save the changes to the database.
• conn.close(): Close the connection when done.

python

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

value1 = 'some_value'
value2 = 123
cursor.execute("INSERT INTO tablename (column1, column2) VALUES (?, ?)", (value1, value2))

conn.commit()
conn.close()

Example

This example creates a table and inserts a row of data into it. It shows how to connect, insert, commit, and close the database.

python

import sqlite3

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

# Create a table
cursor.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')

# Insert data into the table
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Alice', 30))

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

# Query to verify insertion
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
for row in rows:
    print(row)

# Close the connection
conn.close()

Output

(1, 'Alice', 30)

Common Pitfalls

Common mistakes when inserting data into SQLite using Python include:

• Not calling conn.commit() after execute(), so changes are not saved.
• Using string formatting to insert values directly, which risks SQL injection. Always use placeholders ? and pass values as a tuple.
• Forgetting to close the connection, which can lock the database.

python

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# Wrong: vulnerable to SQL injection
# name = "Alice"
# age = 30
# cursor.execute(f"INSERT INTO users (name, age) VALUES ('{name}', {age})")

# Right: use placeholders
name = "Alice"
age = 30
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', (name, age))

conn.commit()
conn.close()

Quick Reference

Remember these key points when inserting data into SQLite with Python:

• Use sqlite3.connect() to open the database.
• Create a cursor with conn.cursor().
• Use cursor.execute() with ? placeholders for values.
• Call conn.commit() to save changes.
• Close the connection with conn.close().

Key Takeaways

Always use placeholders (?) in SQL statements to safely insert data and prevent SQL injection.

Call conn.commit() after executing insert statements to save changes to the database.

Close the database connection with conn.close() to avoid locking issues.

Use sqlite3.connect() and cursor() to manage database operations in Python.

Create tables before inserting data to avoid errors.