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.
python
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.
python
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:
- You need a lightweight, self-contained database – It stores data in a single file and does not require a database server.
- You are building local applications – Many desktop and mobile applications use SQLite for storing user preferences, logs, and cached data.
- 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:
python
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:
python
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:
python
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.
python
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:
python
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:
python
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:
python
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:
python
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():
python
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:
python
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.