Python sqlite3 examples provide a practical way to manage lightweight, file-based databases without requiring a separate server. The sqlite3 module is part of the standard library, which means you can start storing structured data immediately after installing Python. This makes local data storage ideal for small applications, scripts, configuration tracking, and learning database fundamentals.
Connecting to a SQLite Database
Establishing a connection is the first step in every workflow involving Python sqlite3 examples. You create or open a database file by calling connect with a filename, and the system handles the rest. If the file does not exist, SQLite creates it automatically, which simplifies initial setup for new projects.
Basic Connection and Table Creation
Below is a foundational Python sqlite3 example that demonstrates connecting to a database and creating a table. The cursor object allows you to execute SQL commands, such as creating a schema for user records. Using a context manager ensures that resources are released properly, even if an error occurs.
import sqlite3 conn = sqlite3.connect('example.db') cursor = conn.cursor() cursor.execute(''' CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER ) ''') conn.commit() conn.close() Inserting and Querying Data Once the structure exists, Python sqlite3 examples often focus on inserting and retrieving data. Parameterized queries are essential for preventing SQL injection and handling dynamic values safely. The module supports both single-row and executemany operations for flexible data insertion.
Inserting and Querying Data
Insert and Select Operations
The following Python sqlite3 example inserts a new user and then selects all records from the table. Notice the use of placeholders in the insert statement, which are later supplied as a tuple to the execute method. This approach keeps your code clean and secure while interacting with the database.
import sqlite3 conn = sqlite3.connect('example.db') cursor = conn.cursor() cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 30)) cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Bob', 25)) cursor.execute("SELECT * FROM users") rows = cursor.fetchall() for row in rows: print(row) conn.commit() conn.close() Updating and Deleting Records Real-world applications frequently need to modify existing data, which is where update and delete statements come into play. Python sqlite3 examples covering these operations help you maintain accurate and current information. Always use conditions in WHERE clauses to avoid unintentional mass updates.
Updating and Deleting Records
Conditional Updates and Deletion
This example shows how to update a user's age based on their name and then delete a specific record. By reusing the cursor and connection, you minimize overhead and keep the interaction with the database efficient. Committing after each change ensures that modifications are persisted to disk.
import sqlite3 conn = sqlite3.connect('example.db') cursor = conn.cursor() cursor.execute("UPDATE users SET age = ? WHERE name = ?", (26, 'Alice')) cursor.execute("DELETE FROM users WHERE name = ?", ('Bob',)) cursor.execute("SELECT * FROM users") print(cursor.fetchall()) conn.commit() conn.close() Using Context Managers and Error Handling Robust Python sqlite3 examples incorporate error handling to manage exceptions such as constraint violations or missing tables. Wrapping database interactions in try-except blocks allows you to log issues and maintain application stability. Context managers further simplify resource management by automatically closing connections.
