Python SQLite: Syntax, Usage, and Examples

SQLite is a lightweight, serverless database engine that integrates seamlessly with Python. It allows you to store, retrieve, and manage structured data using SQL without the need for a separate database server. With Python's built-in sqlite3 module, you can create, query, and manipulate an SQLite database quickly. Whether you’re building a small application, logging data, or prototyping a larger system, SQLite provides a simple yet powerful way to manage data.

How to Use SQLite in Python

To start using SQLite in Python, import the sqlite3 module and establish a connection to a database. If the database file does not exist, SQLite automatically creates it.

import sqlite3

# Create a connection to the database (or create it if it doesn't exist)
conn = sqlite3.connect("example.db")

Once the connection is established, you use a cursor to execute SQL commands and interact with the database.

What Is a Python SQLite Cursor?

A cursor acts as a bridge between your Python application and the SQLite database. It allows you to execute SQL queries and retrieve results.

cursor = conn.cursor()  # Create a cursor object
cursor.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)")
conn.commit()  # Save changes
conn.close()   # Close the connection

Using a cursor ensures that your SQL commands run efficiently and that data retrieval is structured.

When to Use SQLite in Python

SQLite is an excellent choice when:

1. You need a lightweight, self-contained database – It stores data in a single file and does not require a database server.
2. You are building local applications – Many desktop and mobile applications use SQLite for storing user preferences, logs, and cached data.
3. You need a simple, fast solution for structured data – SQLite is efficient for prototyping and small-to-medium applications.

Examples of Using SQLite in Python

Creating a SQLite Database in Python

To create a new SQLite database, use the following code:

import sqlite3

# Connect to a new database (creates one if it doesn't exist)
conn = sqlite3.connect("my_database.db")
print("Database created successfully")
conn.close()

This command generates a file named my_database.db that stores all your data.

Python SQLite Insert

To insert data into a table:

conn = sqlite3.connect("my_database.db")
cursor = conn.cursor()

# Insert a new record
cursor.execute("INSERT INTO users (name) VALUES (?)", ("Alice",))

conn.commit()
conn.close()

Using ? as a placeholder prevents SQL injection and ensures safe execution.

Python SQLite Example: Querying Data

To retrieve data from a table:

conn = sqlite3.connect("my_database.db")
cursor = conn.cursor()

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

for row in rows:
    print(row)

conn.close()

SQLite Transaction and Rollback Python Examples

Transactions ensure that a set of database operations execute as a single unit. If one step fails, you can roll back all changes to maintain database integrity.

try:
    conn = sqlite3.connect("my_database.db")
    cursor = conn.cursor()

    # Start a transaction
    cursor.execute("BEGIN TRANSACTION")
    cursor.execute("INSERT INTO users (name) VALUES ('Bob')")

    # Simulate an error
    raise Exception("Something went wrong!")

    conn.commit()  # This will not execute
except Exception as e:
    conn.rollback()  # Rollback changes if an error occurs
    print("Transaction rolled back:", e)
finally:
    conn.close()

When an error occurs, the rollback prevents partial changes from being saved, keeping your data consistent.

Drop a Table SQLite Python

If you need to delete a table, use the DROP TABLE command:

conn = sqlite3.connect("my_database.db")
cursor = conn.cursor()

cursor.execute("DROP TABLE IF EXISTS users")
conn.commit()
conn.close()

This removes the table permanently, so use it carefully.

Learn More About Python SQLite

Python SQLite Connection vs Cursor

A connection represents the link between your Python program and the SQLite database, while a cursor executes SQL commands.

• Connection: Establishes and maintains access to the database.
• Cursor: Executes SQL queries and fetches results.

Example:

conn = sqlite3.connect("my_database.db")  # Connection
cursor = conn.cursor()  # Cursor
cursor.execute("SELECT * FROM users")  # Query execution
conn.close()  # Close connection

You need both a connection and a cursor to interact with SQLite.

Python SQLite Returns None Type

If a query returns None, it means:

• The query did not find any matching records.
• The connection was closed before executing the query.
• The table or column does not exist.

To handle cases where a result might be None:

cursor.execute("SELECT * FROM users WHERE id = 10")
result = cursor.fetchone()

if result is None:
    print("No record found")

Python SQLite Install

SQLite is included with Python, so you don’t need to install it separately. However, if you need additional SQLite tools, install them using:

pip install pysqlite3

This provides additional functionality beyond the built-in sqlite3 module.

Create a SQLite Database in Python

To create a database and add a table:

conn = sqlite3.connect("my_database.db")
cursor = conn.cursor()

cursor.execute("""
    CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY,
        name TEXT,
        price REAL
    )
""")

conn.commit()
conn.close()

Python and SQLite: Why Use It?

• No external dependencies
• Fast and lightweight
• Ideal for small projects, testing, and local applications

Python SQLite Insert with Multiple Values

You can insert multiple records in a single command using executemany():

conn = sqlite3.connect("my_database.db")
cursor = conn.cursor()

data = [("Laptop", 999.99), ("Phone", 499.99), ("Tablet", 299.99)]
cursor.executemany("INSERT INTO products (name, price) VALUES (?, ?)", data)

conn.commit()
conn.close()

This is more efficient than inserting records one by one.

Generate Documentation from Python SQLite

To generate database schema documentation programmatically, you can query SQLite’s metadata tables:

conn = sqlite3.connect("my_database.db")
cursor = conn.cursor()

cursor.execute("PRAGMA table_info(users)")
columns = cursor.fetchall()

for column in columns:
    print(column)

conn.close()

This retrieves all column names and their properties, helping document database structures.

SQLite is a lightweight and reliable database solution built into Python. You can use it to create databases, manage structured data, and execute SQL queries with minimal setup. With support for transactions, rollbacks, and metadata queries, SQLite is a powerful choice for developers looking for a simple yet effective database system.