In this article, we are going to learn how to work with an SQLite database and Python to perform CRUD operations. SQLite is an easy to use SQL database engine. An SQLite database could be a simple file stored in our disk, or it can be an in-memory database that lives in our computer’s RAM. If we don’t want to spend time on configuring fully-fledged database management systems like MySQL, PostgreSQL then SQLite is a good choice.
Let’s say we have a web application that serves low to medium traffic then SQLite is a viable option. Also, if you have proof of concepts applications or testing purposes, then SQLite is the right choice. The most interesting part is that SQLite doesn’t require a server to run. SQLite database integrates with the application that accesses the database.
Python standard library includes a module called sqlite3 which contains DB-API 2.0 interface to work with SQLite databases. Since it’s already there, we don’t need to install SQLite on our machine. We need to import this module and work with it right out of the box.
Table of Contents
Create a Customer Class
Before dive into the Python SQLite CRUD Example, Let’s say that we want to create an application which handles customer data. The Created application should have the functionality to create, update, read, and delete customers. We have to save those customer data in a database so that we can perform above CRUD operations from the database.
Let’s create a Customer class first of all. Name it as customer.py
class Customer:
def __init__(self, first_name, last_name, age, city, country):
self.first_name = first_name
self.last_name = last_name
self.age = age
self.city = city
self.country = country
@property
def email(self):
return '{}.{}@gmail.com'.format(self.first_name, self.last_name)
@property
def fullname(self):
return '{} {}'.format(self.first_name, self.last_name)
def __repr__(self):
return "Customer('{}', '{}', '{}', '{}', '{}')".format(
self.first_name,
self.last_name,
self.age,
self.city,
self.country)@property is a built-in decorator for the use of getters and setters in object-oriented python. I have defined two methods for customer email and customer full name and mark them as decorators. Using the __repr__ method, I can get the actual object representation of the customer class.
Working with SQLite
Let’s create a sqlite_demo.py file. All the database related functionality goes here. First of all, we have to import the SQLite3 standard library to our code so that we can work with database operations.
Make a connection to a database
We need to create a connection object to represent our database. In this case, our database is the customer.db. sqlite3 has a method called connect(). Within the connect() method we can either pass in a file where we want to store our data or even we can make an in-memory database.
import sqlite3
# If we need an in-memory database use ":memory:"
# each time our application runs database will be flush from
# the RAM and create new database.
connection = sqlite3.connect(':memory:')In this example, I am going to choose a filename to store our database. I’ll choose the customer.db as the file name.
import sqlite3
connection = sqlite3.connect('customer.db')If we execute this code, it will create a customer.db file in our directory. Even if the file already existed, we won’t get errors next time we run the same code.
Creating a cursor object
A cursor allows us to execute SQL commands. We can create a cursor by executing the cursor() function of our connection object.
import sqlite3
connection = sqlite3.connect('customer.db')
cursor = connection.cursor()Using the execute() method of the cursor object, we can run SQL commands.
Creating the customer table
Using the cursor object, let’s create a customer table. It will have the first name, last name, city, country and age fields (columns).
The SQL command we want to run will go into the execute() method of the cursor object. I use triple quotes for wrapping the SQL command. We called it a “docstring”. The benefit of using docstring is that it allows us to write string values in multiple lines.
Using the execute() method of the cursor object, we can run SQL commands.
import sqlite3
connection = sqlite3.connect('customer.db')
cursor = connection.cursor()
cursor.execute("""CREATE TABLE customer(
first_name text,
last_name text,
age integer,
city text,
country text
)""")After creating the table, we need to commit our changes to the database. For that, we use the commit() method. Finally, close the database connection using the close() method.
import sqlite3
connection = sqlite3.connect('customer.db')
cursor = connection.cursor()
cursor.execute("""CREATE TABLE customer(
first_name text,
last_name text,
age integer,
city text,
country text
)""")
connection.commit()
connection.close()
Let’s execute the sqlite_demo.py. If we don’t get any error, that means we’ve created the customer table successfully.
If you execute the same code again, then you would get an error similar to below output. That means you’ve already created the database table.
Insert data to customer table
Now that we’ve already created the database table let’s add data to the customer table. For that we can use INSERT INTO <table name> VALUES() command.
import sqlite3
connection = sqlite3.connect('customer.db')
cursor = connection.cursor()
cursor.execute("""CREATE TABLE customer(
first_name text,
last_name text,
age integer,
city text,
country text
)""")
cursor.execute("INSERT INTO customer VALUES('john','doe',30,'perth','Australia')")
connection.commit()
connection.close()
After creating the database table, make sure to comment out that part if you added the insert query into the same python file. Otherwise, you’ll get an error.
Then run the sqlite_demo.py file. Now, let’s see our customer data is inserted to the table successfully. For that, we have to write a SELECT query.
Query the database table
After inserting the data, we can query the table. In the execute() method put the SELECT query.
import sqlite3
connection = sqlite3.connect('customer.db')
cursor = connection.cursor()
cursor.execute("""CREATE TABLE customer(
first_name text,
last_name text,
age integer,
city text,
country text
)""")
cursor.execute("INSERT INTO customer VALUES('john','doe',30,'perth','Australia')")
cursor.execute("SELECT * FROM customer WHERE city='perth'")
connection.commit()
connection.close()
After the execute() method, we get a query result so that we can iterate through to find the desired result. There are a few different methods to iterate the query result.
| Method | Functionality |
| fetchone() | fetchone() will get the next row in our result and return one record. If no more records available returns none |
| fetchmany(number) | fetchmany(number) will return a specified number of rows as a list. If there are no more records available return an empty list. |
| fetchall() | fetchall() return the remaining rows as a list that are left. If there are no rows, then it will return an empty list. |
Let’s use the fetchone() method and print the record.
import sqlite3
connection = sqlite3.connect('customer.db')
cursor = connection.cursor()
cursor.execute("""CREATE TABLE customer(
first_name text,
last_name text,
age integer,
city text,
country text
)""")
cursor.execute("INSERT INTO customer VALUES('john','doe',30,'perth','Australia')")
cursor.execute("SELECT * FROM customer WHERE city='perth'")
print(cursor.fetchone())
connection.commit()
connection.close()
Below is output of the above code.
You can see that it gives us the one entry/ record we just inserted to our table.
Let’s use the fetchall() method to get the record. You’ll see in the output that fetchall() return data as a list even though the table has one record.
Let’s add one more record to the table using the INSERT statement. After that, as a practise, make sure to commit that change. Then execute the SELECT query to get the desired result set.
import sqlite3
connection = sqlite3.connect('customer.db')
cursor = connection.cursor()
cursor.execute("""CREATE TABLE customer(
first_name text,
last_name text,
age integer,
city text,
country text
)""")
cursor.execute("INSERT INTO customer VALUES('john','doe',30,'perth','Australia')")
cursor.execute("INSERT INTO customer VALUES('sara','migel',25,'perth','Australia')")
connection.commit()
cursor.execute("SELECT * FROM customer WHERE city='perth'")
print(cursor.fetchall())
connection.commit()
connection.close()
Below is the output of the above query. We’ll get two records as the output since both records contain the city as “perth”.
Note: so far what we have done was typed the values that we are searching for directly into our SELECT statement. But most likely you’ll use a python variable to put as a value to the SELECT statement.
Utilizing the customer class
First, import the customer class to the sqlite_demo.py file. Now, we have to create an instance (object) of a customer class. I will create two instances and name them as customer_1 and customer_2.
import sqlite3
from customer import Customer
connection = sqlite3.connect('customer.db')
cursor = connection.cursor()
cursor.execute("""CREATE TABLE customer(
first_name text,
last_name text,
age integer,
city text,
country text
)""")
customer_1 = Customer('john', 'doe', 30, 'perth', 'Australia')
customer_2 = Customer('sara', 'migel', 25, 'perth', 'Australia')
connection.commit()
connection.close()
For example, let’s say I want to save the customer_1 object values to the database. If you’re use string formatting, what you’ll do is; use braces as placeholders. Then using the format() method, you’ll populate those values to the corresponding placeholder.
import sqlite3
from customer import Customer
connection = sqlite3.connect('customer.db')
cursor = connection.cursor()
cursor.execute("""CREATE TABLE customer(
first_name text,
last_name text,
age integer,
city text,
country text
)""")
customer_1 = Customer('john', 'doe', 30, 'perth', 'Australia')
customer_2 = Customer('sara', 'migel', 25, 'perth', 'Australia')
cursor.execute("INSERT INTO customer VALUES ('{}', '{}', {}, '{}', '{}')".format(
customer_1.first_name,
customer_1.last_name,
customer_1.age,customer_1.city,customer_1.country))
connection.commit()
connection.close()
Note: you might be eager to use the string format approach, but it is not a good practice since this is highly prone to SQL injection.
Proper ways to add an object to the database
There are two ways to insert objects to a database properly. Since the string formatting approach is prone to SQL injection, it’s better to use either one of them I mentioned below.
DB API placeholder via ?
Instead of using regular brace placeholders, we can use question marks.
# import statements
# create connection object
# create database table
customer_1 = Customer('john', 'doe', 30, 'perth', 'Australia')
cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?)",
(customer_1.first_name,customer_1.last_name,
customer_1.age,customer_1.city,customer_1.country))
# commit changes
# close the connection
One thing to note here, we don’t use format() function anymore. In the execute method, pass the second argument as the tuple of object values which we want to save in the database.
Let’s see another method to achieve the same functionality as before.
DB API placeholder via dictionary keys and values
Instead of using ? as a placeholder, we use the colon (:) with a name describing the placeholder. In the execute method, we have to pass a dictionary as the second argument. Dictionary keys are going to be the placeholder names, and the values would be whatever we pass from the object attributes.
| placeholder | key/value pair |
| :firstName | {‘firstName’: customer_1.first_name} |
We use curly brackets to denote a dictionary.
# import statements
# create connection object
# create database table
customer_1 = Customer('john', 'doe', 30, 'perth', 'Australia')
cursor.execute("INSERT INTO customer VALUES (:first, :last, :age, :city, :country)",
{'first':customer_1.first_name, 'last':customer_1.last_name, 'age':customer_1.age,
'city':customer_1.city, 'country':customer_1.country})
# commit changes
# close the connection
Executing the SELECT query
First, we’ll use the placeholder ?
Instead of hard coding values in the WHERE clause, let’s add the SELECT query with placeholder value. As the second argument, let’s pass a tuple. Even though the tuple value is single, still we have to put a comma after the first value. Otherwise, the python interpreter gives us an error.
# import statements
# create connection object
# create database table
customer_1 = Customer('john', 'doe', 30, 'perth', 'Australia')
cursor.execute("INSERT INTO customer VALUES (:first, :last, :age, :city, :country)",
{'first':customer_1.first_name, 'last':customer_1.last_name,
'age':customer_1.age, 'city':customer_1.city, 'country':customer_1.country})
cursor.execute("SELECT * FROM customer WHERE city=?", ('perth',))
# commit changes
# close the connection
Now, let’s see the use of dictionary placeholder approach to execute the SELECT query.
In the WHERE clause, we have to put the colon with a placeholder name, and for the second argument of execute() method, we’ve to pass a dictionary.
# import statements
# create connection object
# create database table
customer_1 = Customer('john', 'doe', 30, 'perth', 'Australia')
cursor.execute("INSERT INTO customer VALUES (:first, :last, :age, :city, :country)",
{'first':customer_1.first_name, 'last':customer_1.last_name,
'age':customer_1.age, 'city':customer_1.city, 'country':customer_1.country})
cursor.execute("SELECT * FROM customer WHERE city=:city", {'city':customer_1.city})
# commit changes
# close the connection
Both approaches will give us the same result. But I would prefer the dictionary approach.
Prototype the CRUD functions with Customer class
To demonstrate the Python SQLite CRUD Example , I will create four functions in the sqlite_demo.py file.
- create_customer(customer)
- get_customers(city)
- update_city(customer,city)
- delete_customer(customer)
Above functions are very straightforward, and they do as what they’ve defined.
Note: sometimes it’s tedious to commit our chances when we’ve done the CRUD operations to the database. A simple way to resolve that issue to use the python context managers. Context managers are used for setup and tear down resources automatically. For example; the connection object can be used as a context manager to commit and rollback transactions automatically.
The with keyword is used to define a context manger. Context managers can be written using classes or functions with the help of decorators.
create_customer(customer)
create_customer(customer) function will save a customer record into the database.
# import statements
# create connection object
# create database table
def create_customer(customer):
with connection:
cursor.execute("INSERT INTO customer VALUES (:first, :last, :age, :city, :country)",
{'first':customer.first_name, 'last':customer.last_name,
'age':customer.age, 'city':customer.city, 'country':customer.country})In the above code our execute() functions in wrap within the with block. So that we don’t need a commit() statement after that.
get_customers(city)
get_customers(city) function will accept the city of a customer and return a result set.
# import statements
# create connection object
# create database table
def get_customers(city):
cursor.execute("SELECT * FROM customer WHERE city=:city", {'city':city})
return cursor.fetchall()Note: you might be wondering why there is no with block here. Well for the SELECT query we don’t need the commit() function. Because it doesn’t need to be within a context manager, if it is INSERT, UPDATE, and DELETE then we need a with block.
update_city(customer,city)
Customer’s city is going to update based on the provided customer’s first name and last name.
# import statements
# create connection object
# create database table
def update_city(customer, city):
with connection:
cursor.execute("""UPDATE customer SET city=:city
WHERE first_name=:first AND last_name=:last""",
{'first':customer.first_name, 'last':customer.last_name, 'city':city})delete_customer(customer)
If the given customer’s first name and last name matched with the existing records of the database, then all the records with that name will be deleted from the database.
# import statements
# create connection object
# create database table
def delete_customer(customer):
with connection:
c.execute("DELETE FROM customer WHERE first_name=:first AND last_name=:last",
{'first':customer.first_name,'last':customer.last_name})All right, we have implemented all the CRUD functionality. Now let’s run our code to see the output.
First of all, we need to create customer objects. I will create two objects with sample values.
Then insert two customers to the database using create_customer(customer) method. Invoke the function two times.
# import statements
# create connection object
# create database table
def create_customer(customer):
with connection:
cursor.execute("INSERT INTO customer VALUES (:first, :last, :age, :city, :country)",
{'first':customer.first_name, 'last':customer.last_name,
'age':customer.age, 'city':customer.city, 'country':customer.country})
def get_customers(city):
cursor.execute("SELECT * FROM customer WHERE city=:city", {'city':city})
return cursor.fetchall()
customer_1 = Customer('john', 'doe', 30, 'perth', 'Australia')
customer_2 = Customer('sara', 'migel', 25, 'perth', 'Australia')
create_customer(customer_1)
create_customer(customer_2)
customers = get_customers('perth')
print(customers)
connection.close()
Let’s get customers by their city. For that invoke the get_customers(city) function.
Below is the output.
Now let’s see how to update a customer and delete a customer. You’ve to use the update_city(customer,city) and delete_customer(customer) functions for that.
# import statements
# create connection object
# create database table
def create_customer(customer):
with connection:
cursor.execute("INSERT INTO customer VALUES (:first, :last, :age, :city, :country)",
{'first':customer.first_name, 'last':customer.last_name,
'age':customer.age, 'city':customer.city, 'country':customer.country})
def get_customers(city):
cursor.execute("SELECT * FROM customer WHERE city=:city", {'city':city})
return cursor.fetchall()
def update_city(customer, city):
with connection:
cursor.execute("""UPDATE customer SET city=:city
WHERE first_name=:first AND last_name=:last""",
{'first':customer.first_name, 'last':customer.last_name, 'city':city})
def delete_customer(customer):
with connection:
cursor.execute("DELETE FROM customer WHERE first_name=:first AND last_name=:last",
{'first':customer.first_name,'last':customer.last_name})
customer_1 = Customer('john', 'doe', 30, 'perth', 'Australia')
customer_2 = Customer('sara', 'migel', 25, 'perth', 'Australia')
create_customer(customer_1)
create_customer(customer_2)
update_city(customer_1,'sydney')
delete_customer(customer_2)
print(get_customers('perth'))
print(get_customers('sydney'))
connection.close()In the update function, I have changed the city of customer_1 objects to sydney, so that new customer_1’s new city should be sydney.
Also, I have deleted the customer_2 from the database as well.
Below is the output
Conclusion
All right, in this article, we have learned about the SQLite module of python and how to perform CRUD operations with a database using SQLite. When you’re working with databases, always keep in mind to secure the database whenever the user submits some data. SQL injection is harmful and avoids it at all cost.
Keey it up
Thanks Sandunika.