Connecting to an SQLite3 database in Python is surprisingly simpler, yet it is essential to understand the mechanics beneath the hood. The sqlite3 module included in the standard library offers a lightweight, disk-based database that doesn’t require a separate server process. This makes it perfect for many applications where simplicity and portability are key.
To begin, you need to import the module and establish a connection to the database file. If the file doesn’t exist, SQLite will create it for you. Here’s the basic form:
import sqlite3
connection = sqlite3.connect('example.db')
Notice that the argument to connect() is simply the path to your database file. This can be a relative path, an absolute path, or even ':memory:' if you want an in-memory database for temporary use.
Once connected, you get a Connection object. This object behaves like the gateway to all your database operations. However, before running any queries, you need a Cursor object, which you obtain by calling the cursor() method on the connection:
cursor = connection.cursor()
The cursor is your interface for executing SQL statements, fetching results, and managing transactions. It’s worth noting that SQLite supports transactions implicitly, so your changes won’t be saved until you explicitly commit them.
Remember to handle exceptions when connecting to the database, especially if your application depends heavily on database availability. Wrapping your connection code in a try-except block will help you catch errors gracefully:
try:
connection = sqlite3.connect('example.db')
cursor = connection.cursor()
except sqlite3.Error as e:
print(f"Database connection failed: {e}")
When your work with the database is done, always close the connection to free up resources:
connection.close()
Failing to close the connection can lead to locked database files or memory leaks, especially in long-running applications. Also, if you modify data and don’t call commit() on the connection, your changes will vanish when the connection closes:
connection.commit()
For example, if you create a new table or insert rows, those changes stay in a transaction until you commit. If you want to roll back changes, you can call connection.rollback() instead.
Now loading...
Crafting and executing SQL queries to retrieve data
With a cursor in hand, you’re ready to craft and execute SQL queries. The execute() method on the cursor is your primary tool. It accepts a string containing the SQL statement and optional parameters to safely inject variables into your query.
Suppose you want to retrieve all rows from a table named users. The simplest query looks like this:
cursor.execute("SELECT * FROM users")
After executing the query, you’ll want to fetch the results. The cursor provides several methods for this purpose: fetchone(), fetchmany(size), and fetchall(). Each returns rows as tuples, corresponding to the columns selected.
For example, to fetch all rows at once:
rows = cursor.fetchall()
for row in rows:
print(row)
Alternatively, you can iterate directly over the cursor after executing the query, which fetches rows lazily and can be more memory efficient for large datasets:
for row in cursor.execute("SELECT * FROM users"):
print(row)
When your query involves variables, never concatenate strings directly to avoid SQL injection vulnerabilities. Instead, use parameter substitution with placeholders. SQLite’s sqlite3 module supports the ? placeholder.
Here’s how to select users with a specific last name safely:
last_name = 'Smith'
cursor.execute("SELECT * FROM users WHERE last_name = ?", (last_name,))
rows = cursor.fetchall()
for row in rows:
print(row)
Notice the parameters are passed as a tuple. Even if there’s only one parameter, the trailing comma is necessary to define a tuple.
You can also insert data using the same parameter substitution technique. Ponder inserting a new user:
cursor.execute(
"INSERT INTO users (first_name, last_name, email) VALUES (?, ?, ?)",
('Alice', 'Johnson', '[email protected]')
)
connection.commit()
It’s important to commit after modifications to make the changes persistent. If you forget, the inserted row will not appear in subsequent queries.
For more complex queries, such as those involving joins or ordering, the process is the same. Here’s an example that retrieves users ordered by their last name:
cursor.execute("SELECT first_name, last_name, email FROM users ORDER BY last_name ASC")
for first_name, last_name, email in cursor.fetchall():
print(f"{first_name} {last_name} - {email}")
When dealing with large result sets, you might prefer fetchmany() to fetch a limited number of rows at a time, reducing memory usage:
cursor.execute("SELECT * FROM users")
while True:
rows = cursor.fetchmany(10) # fetch 10 rows at a time
if not rows:
break
for row in rows:
print(row)
This approach is especially useful in applications that process or display data incrementally.
Source: https://www.pythonfaq.net/how-to-query-data-from-a-sqlite3-database-in-python/
