Picture by Creator
SQLite is a light-weight, serverless relational database administration system (RDBMS) that’s broadly used attributable to its simplicity and ease of embedding inside functions.
So whether or not you are constructing a small utility, managing information domestically, or prototyping a mission, SQLite gives a handy resolution for storing and querying structured information. On this tutorial, you’ll learn to work with SQLite databases from Python utilizing the built-in sqlite3 module.
Significantly, you’ll learn to connect with an SQLite database from Python and carry out primary CRUD operations. Let’s get began.
Setting Up the Setting
As a primary step create a devoted digital surroundings to your mission (within the mission listing) and activate it. You are able to do it utilizing the built-in venv module like so:
$ python3 -m venv v1
$ supply v1/bin/activate
On this tutorial, we’ll use Faker to generate artificial information. So set up it utilizing pip:
The sqlite3 module is constructed into the Python customary library, so you do not have to put in it. So if you happen to’ve put in Faker and are utilizing a current model of Python, you’re good to go!
Connecting to an SQLite Database
Within the mission listing, create a Python script and get began. As a primary step to work together with the database, we must always set up a reference to the database.
To connect with a pattern database instance.db, you need to use the join() operate from the sqlite3 module like so:
conn = sqlite3.join(‘example.db’)
If the database already exists, then it connects to it. Else it creates the database within the working listing.
After connecting to the database, we’ll create a database cursor that may assist us run queries. The cursor object has strategies to execute queries and fetch the outcomes of the question. It really works very equally to a file handler.
Database Cursor | Picture by Creator
It’s typically useful to make use of the connection as a context supervisor in a with assertion like so:
import sqlite3
# Hook up with the db
with sqlite3.join('instance.db') as conn:
# create db cursor
# run queries
# commit modifications
This fashion you don’t have to fret about closing the connection object. The connection is routinely closed when the execution exits the with block. We’ll explicitly shut the cursor objects on this tutorial, although.
Creating Database Tables
Now let’s create a prospects desk with the required fields within the database. To take action, we first create a cursor object. We then run a CREATE TABLE assertion, and move within the question string to the execute() technique known as on the cursor object:
import sqlite3
# Hook up with the db
with sqlite3.join('instance.db') as conn:
cursor = conn.cursor()
# Create prospects desk
cursor.execute('''
CREATE TABLE IF NOT EXISTS prospects (
id INTEGER PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
e mail TEXT UNIQUE NOT NULL,
telephone TEXT,
num_orders INTEGER
);
''')
conn.commit()
print("Customers table created successfully.")
cursor.shut()
While you run the script, it’s best to see the next output:
Output >>>
Clients desk created efficiently.
Performing CRUD Operations
Let’s carry out some primary CRUD operations on the database desk. For those who’d like you might create separate scripts for every operation.
Inserting Data
Now we’ll insert some information into the prospects desk. We’ll use Faker to generate artificial information. To maintain the outputs readable, I’ve inserted solely 10 information. However you might insert as many information as you’d like.
import sqlite3
import random
from faker import Faker
# Initialize Faker object
pretend = Faker()
Faker.seed(24)
# Hook up with the db
with sqlite3.join('instance.db') as conn:
cursor = conn.cursor()
# Insert buyer information
num_records = 10
for _ in vary(num_records):
first_name = pretend.first_name()
last_name = pretend.last_name()
e mail = pretend.e mail()
telephone = pretend.phone_number()
num_orders = random.randint(0,100)
cursor.execute('''
INSERT INTO prospects (first_name, last_name, e mail, telephone, num_orders)
VALUES (?, ?, ?, ?, ?)
''', (first_name, last_name, e mail, telephone, num_orders))
print(f"{num_records} customer records inserted successfully.")
conn.commit()
cursor.shut()
Discover how we use parameterized queries: as an alternative of hardcoding the values into the INSERT assertion, we use ? placeholders and move in a tuple of values.
Working the script ought to give:
Output >>>
10 buyer information inserted efficiently.
Studying and Updating Data
Now that we’ve inserted information into the desk, let’s run a question to learn in all of the information. Discover how we use the execute() technique to run queries and the fetchall() technique on the cursor to retrieve the outcomes of the question.
As a result of we’ve saved the outcomes of the earlier question in `all_customers`, let’s additionally run an UPDATE question to replace the num_orders similar to the id 1. Right here’s the code snippet:
import sqlite3
# Hook up with the db
with sqlite3.join('instance.db') as conn:
cursor = conn.cursor()
# Fetch and show all prospects
cursor.execute('SELECT id, first_name, last_name, e mail, num_orders FROM prospects')
all_customers = cursor.fetchall()
print("All Customers:")
for buyer in all_customers:
print(buyer)
# Replace num_orders for a particular buyer
if all_customers:
customer_id = all_customers[0][0] # Take the ID of the primary buyer
new_num_orders = all_customers[0][4] + 1 # Increment num_orders by 1
cursor.execute('''
UPDATE prospects
SET num_orders = ?
WHERE id = ?
''', (new_num_orders, customer_id))
print(f"Orders updated for customer ID {customer_id}: now has {new_num_orders} orders.")
conn.commit()
cursor.shut()
This outputs each the information and the message after the replace question:
Output >>>
All Clients:
(1, 'Jennifer', 'Franco', 'jefferyjackson@instance.org', 54)
(2, 'Grace', 'King', 'erinhorne@instance.org', 43)
(3, 'Lori', 'Braun', 'joseph43@instance.org', 99)
(4, 'Wendy', 'Hubbard', 'christophertaylor@instance.com', 11)
(5, 'Morgan', 'Wright', 'arthur75@instance.com', 4)
(6, 'Juan', 'Watson', 'matthewmeadows@instance.internet', 51)
(7, 'Randy', 'Smith', 'kmcguire@instance.org', 32)
(8, 'Jimmy', 'Johnson', 'vwilliams@instance.com', 64)
(9, 'Gina', 'Ellison', 'awong@instance.internet', 85)
(10, 'Cory', 'Joyce', 'samanthamurray@instance.org', 41)
Orders up to date for buyer ID 1: now has 55 orders.
Deleting Data
To delete a buyer with a particular buyer ID, let’s run a DELETE assertion as proven:
import sqlite3
# Specify the shopper ID of the shopper to delete
cid_to_delete = 3
with sqlite3.join('instance.db') as conn:
cursor = conn.cursor()
# Execute DELETE assertion to take away the shopper with the desired ID
cursor.execute('''
DELETE FROM prospects
WHERE id = ?
''', (cid_to_delete,))
conn.commit()
f"Customer with ID {cid_to_delete} deleted successfully.")
cursor.shut()
This outputs:
Buyer with ID 3 deleted efficiently.
Filtering Data Utilizing the WHERE Clause
Picture by Creator
Let’s say we need to fetch information of shoppers who’ve made fewer than 10 orders, say to run focused campaigns and the like. For this, we run a SELECT question with the WHERE clause specifying the situation for filtering (on this case the variety of orders). Here is how one can obtain this:
import sqlite3
# Outline the edge for the variety of orders
order_threshold = 10
with sqlite3.join('instance.db') as conn:
cursor = conn.cursor()
# Fetch prospects with lower than 10 orders
cursor.execute('''
SELECT id, first_name, last_name, e mail, num_orders
FROM prospects
WHERE num_orders
And right here’s the output:
Output >>>
Clients with lower than 10 orders:
(5, 'Morgan', 'Wright', 'arthur75@instance.com', 4)
Wrapping Up
And that’s a wrap! This was a information to getting began with SQLite with Python. I hope you discovered it useful. You could find all of the code on GitHub. Within the subsequent half, we’ll take a look at working joins and subqueries, managing transactions in SQLite, and extra. Till then, completely happy coding!
For those who’re taken with studying how database indexes work, learn How To Velocity Up SQL Queries Utilizing Indexes [Python Edition].
Bala Priya C is a developer and technical author from India. She likes working on the intersection of math, programming, information science, and content material creation. Her areas of curiosity and experience embrace DevOps, information science, and pure language processing. She enjoys studying, writing, coding, and occasional! Presently, she’s engaged on studying and sharing her information with the developer group by authoring tutorials, how-to guides, opinion items, and extra. Bala additionally creates partaking useful resource overviews and coding tutorials.
