SQLite is a lightweight, file-based database engine that's built into Python. It requires no separate server and is perfect for local development, small projects, and prototyping. In this guide, you’ll learn how to get started with SQLite in Python, including setting up a database, creating tables, and performing basic operations.

✅ What Is SQLite?

SQLite is:

• Serverless – data is stored in a single file.

• Zero configuration – no setup or installation required.

• Built-in – included with Python via the sqlite3 module.

Great for:

• Local storage in desktop apps

• Quick prototyping

• Unit testing database logic

• Lightweight embedded systems

Prerequisites

• Python 3.x installed (Python 3.5+ recommended)

• No additional libraries required — sqlite3 is built into the Python Standard Library

Step 1: Create or Connect to a SQLite Database

When you connect to a SQLite database file, it will be created if it doesn’t exist.

import sqlite3

# Connect to (or create) a new database file
conn = sqlite3.connect('my_database.db')

# Create a cursor object to interact with the database
cursor = conn.cursor()

The file my_database.db is your entire database stored on disk.

Step 2: Create a Table

Let’s create a simple Users table.

cursor.execute('''
CREATE TABLE IF NOT EXISTS Users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    age INTEGER
)
''')
conn.commit()

✅ Always use IF NOT EXISTS to avoid errors if the table already exists.

➕ Step 3: Insert Data into the Table

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

conn.commit()

Using ? placeholders prevents SQL injection.

Step 4: Query the Table

cursor.execute("SELECT * FROM Users")
rows = cursor.fetchall()

for row in rows:
    print(row)

This will print something like:

(1, 'Alice', '[email protected]', 30)

Full Example: Create, Insert, and Read

import sqlite3

# Connect to database
conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

# Create table
cursor.execute('''
CREATE TABLE IF NOT EXISTS Users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    age INTEGER
)
''')

# Insert user
cursor.execute('''
INSERT INTO Users (name, email, age)
VALUES (?, ?, ?)
''', ("Bob", "[email protected]", 25))

# Query all users
cursor.execute("SELECT * FROM Users")
rows = cursor.fetchall()

for row in rows:
    print(row)

# Close connection
conn.commit()
conn.close()

Tips for Using SQLite in Python

⚠️ Common Pitfalls

Best Practices

• Store the connection and cursor in a context manager or close them manually.

• Avoid keeping the connection open longer than necessary.

• Validate data before inserting into the database.

• Use sqlite3.Row to access columns by name:

conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute("SELECT * FROM Users")
row = cursor.fetchone()
print(row["name"])  # Access by column name

Conclusion

SQLite is a powerful and easy-to-use embedded database that’s great for Python projects. With just a few lines of code, you can create a database, insert data, and run queries — all without installing a server. Whether you’re building a quick prototype or a lightweight application, SQLite is a fantastic choice for local storage.