SQLite is a lightweight, serverless, self-contained relational database management system commonly used in many applications due to its simplicity, efficiency, and compact footprint.
This comprehensive guide will explore how to fully leverage SQLite in Python projects to create, access, modify, and manage database information in-depth.
Setting Up SQLite in Python
To work with SQLite in Python, we first need to import the sqlite3 module. This comes built-in with Python:
import sqlite3
The sqlite3 module provides all the APIs and tools necessary to connect to, create, and interact with an SQLite database from Python.
To connect to an SQLite database (or create one if it doesn't exist), use the sqlite3.connect() method:
import sqlite3
db = sqlite3.connect('database.db')
This opens a connection to the database file database.db and returns a connection object representing the database. If the file does not exist, SQLite will automatically create it.
It's good practice to close the connection once we are done interacting with the database. This can be done with the close() method:
db.close()
Creating Tables
To create a table in SQLite, we must execute a CREATE TABLE SQL statement. This is done by first obtaining a cursor object from the database connection, which enables us to execute SQL:
import sqlite3
db = sqlite3.connect('database.db')
cursor = db.cursor()
We can then execute a CREATE TABLE statement with the cursor's execute() method:
cursor.execute("""
CREATE TABLE inventory (
id integer PRIMARY KEY,
name text NOT NULL,
quantity integer,
price real
)
""")
This creates a table called inventory with columns for ID, name, quantity, and price. Note that we define the data type for each column.
It's essential to commit changes to the database after executing SQL. This persists the changes:
db.commit()
Let's put this together into a function to encapsulate the table creation logic:
def create_table():
db = sqlite3.connect('database.db')
cursor = db.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS inventory (
id integer PRIMARY KEY,
name text NOT NULL,
quantity integer,
price real
)
""")
db.commit()
db.close()
We use CREATE TABLE IF NOT EXISTS, so attempting to create the table again won't result in an error if it already exists.
Inserting Data
To insert data into a table, we can again execute an SQL statement using the execute() method. SQLite has an INSERT INTO statement that allows inserting new rows:
INSERT INTO inventory VALUES (1, 'Bananas', 150, 1.49)
This would insert a new row with id 1, name Bananas, quantity 150, and price 1.49.
While we could insert data using string formatting, a better approach is to use query parameters that safely separate values from the query:
data = (2, 'Oranges', 200, 2.49)
cursor.execute("INSERT INTO inventory VALUES (?, ?, ?, ?)", data)
They? act as placeholders for values. This helps prevent SQL injection attacks.
We can write a function to insert data:
def insert_data(values):
db = sqlite3.connect('database.db')
cursor = db.cursor()
cursor.execute("INSERT INTO inventory VALUES (?, ?, ?, ?)", values)
db.commit()
db.close()
# Usage:
insert_data((3, 'Apples', 75, 1.25))
This encapsulates the insertion logic while allowing us to pass in data as a tuple cleanly.
Viewing Data
We can use the SELECT statement to query and fetch data from the database. For example, to get all rows:
SELECT * FROM inventory
We can execute this query and then use cursor.fetchall() to retrieve the result set as tuples:
def get_all_data():
db = sqlite3.connect('database.db')
cursor = db.cursor()
cursor.execute("SELECT * FROM inventory")
rows = cursor.fetchall()
db.close()
return rows
This will return the full result set as a list of tuples, with each tuple representing a row and its values.
We can pass parameters to a SELECT query to filter results:
def get_by_name(name):
db = sqlite3.connect('database.db')
cursor = db.cursor()
cursor.execute("SELECT * FROM inventory WHERE name=?", (name,))
row = cursor.fetchone()
db.close()
return row
This allows us to retrieve a specific row by name. cursor.fetchone() returns just the first result.
Updating Data
To modify existing data, we can use an UPDATE statement:
UPDATE inventory SET quantity = 200 WHERE name = 'Bananas'
This updates the quantity of Bananas to 200.
We can write a function to handle the update logic:
def update_data(new_quantity, name):
db = sqlite3.connect('database.db')
cursor = db.cursor()
cursor.execute("UPDATE inventory SET quantity =? WHERE name=?",
(new_quantity, name))
db.commit()
db.close()
# Usage:
update_data(350, 'Bananas')
Similar to insert, this uses query parameters to pass in values safely.
Deleting Data
Removing a row can be done with a DELETE statement:
DELETE FROM inventory WHERE name = 'Oranges'
This would delete the orange row.
Here is how we can implement a delete function:
def delete_data(name):
db = sqlite3.connect('database.db')
cursor = db.cursor()
cursor.execute("DELETE FROM inventory WHERE name=?", (name,))
db.commit()
db.close()
# Usage:
delete_data('Oranges')
Again, using query parameters avoids vulnerabilities and makes the code easier to maintain.
Building with Sqlite3
To get started, clone the repo here and follow the tutorial. You must have a little knowledge of SQL to follow through, although this is a beginner course.
The first step is importing the sqlite3 module:
import sqlite3
This gives us access to all the SQLite database functionality in Python.
Creating a Table
Next, a create_table() function is defined to create a table called store with columns for item, quantity, and price:
def create_table():
new_data = sqlite3.connect('data_base1')
change = new_data.cursor()
change.execute("CREATE TABLE IF NOT EXISTS store (item TEXT, quantity INTEGER, price REAL)")
new_data.commit()
new_data.close()
Breaking this down:
sqlite3.connect() opens a connection to the database file.
new_data.cursor() gets a cursor object to execute SQL.
execute() runs the CREATE TABLE query.
commit() saves the changes.
close() closes the connection.
Inserting Data
The item() function handles inserting data into the table:
def item(item, quantity, price):
new_data = sqlite3.connect('data_base1')
change = new_data.cursor()
change.execute("INSERT INTO store VALUES(?,?,?)", (item, quantity, price))
new_data.commit()
new_data.close()
It takes an item, quantity, and price as arguments. The INSERT query uses placeholders ? to safely insert those values into a new row.
Viewing Data
To query the data, view() function is defined:
def view():
new_data = sqlite3.connect('data_base1')
change = new_data.cursor()
change.execute("SELECT * FROM store")
rows = change.fetchall()
change.close()
return rows
It selects all rows using SELECT *, fetches the results with fetchall(), and returns them.
Deleting Data
To delete a row by item name, the delete() function is used:
def delete(item):
new_data = sqlite3.connect('data_base1')
change = new_data.cursor()
change.execute("DELETE FROM store WHERE item =?", (item,))
new_data.commit()
new_data.close()
The WHERE clause identifies the row to delete by the item name.
Updating Data
Finally, update() can modify quantity and price for a given item:
def update(quantity, price, item):
new_data = sqlite3.connect('data_base1')
change = new_data.cursor()
change.execute("UPDATE store SET quantity =?, price =? WHERE item=?",
(quantity, price, item))
new_data.commit()
new_data.close()
The SET clause specifies the columns to update, and WHERE identifies the row.
Wrapping Up
In summary, the key steps for working with SQLite databases in Python are:
Import the sqlite3 module into your project.
Create a database connection with sqlite3.connect().
Obtain a cursor from the connection to execute SQL statements.
Construct SQL queries and use the cursor to execute them.
For inserts, updates, and deletes - commit changes to persist them.
For queries, use cursor.fetchone() or cursor.fetchall() to retrieve results.
Close database connections when done.
Use query parameters instead of concatenating values to avoid SQL injection.
Encapsulate database logic and queries in functions for reusability.
With these concepts, you can build robust applications utilizing the power of SQLite and Python. The SQLite3 module and SQL give you full control to create, modify, and query database information.
SQLite supports many more advanced features like transactions, isolation levels, backups, and encryption. Make sure to refer to the official SQLite documentation to leverage the full functionality.
I hope this comprehensive guide gives you a thorough understanding of integrating SQLite into your Python projects! Let me know if you have any other questions.