Good day, fellow reader.
There are a lot of options when it comes to saving the data you or your app generates:
Store it on memory: it will be lost when you close the app or reboot your machine.
Save it to a file: Easy to write, not that easy to read from it, and keeping consistency might be challenging.
Store it in a database: you guessed it, the way-to-go in this tutorial.
Save it to the cloud: a wise choice, if you know a blogger who has done a tutorial on this, please write a comment.
We are going to create our own database, schema, and insert and modify data over it!
Sheldon Cooper from The Big Bang Theory (TV Series)
Introduction
I'm a person who is not a big fan of the "foo, bar, baz" examples. I believe that real knowledge strikes in when you apply a concept to a real situation. So we're going that way. It's a simplified version of a real problem I solved a few months ago. This version uses no classes, so, if you are new to this, you can follow along with no problems.
We are going to use SQLite, a popular relational database management system that does not require a lot of configuration to start using, it is rather embedded into the final program. To perform actions over that set of data, we are going to use python.
Are you absolutely new to the SQL world? You can start reading "Essential SQL for beginners" by Shreyas Kulkarni.
If you also never coded a line a Python, the series in Layla's blog that starts with Python basics it's going to help you out. I'll wait here.
The problem
We are going to work with a small business that mainly needs to store its suppliers and the products they deliver.
- They need to store the supplier's name, its address is optional.
- They must store the product descriptions, and the quantity.
- Every product they have is only bought from one supplier.
The design
We can make a diagram of our database and it will look like this
Our database diagram and the relation between tables
We are going to allow the user the perform eight operations
- List suppliers
- List products
- Create supplier
- Create product
- Change product quantity
- Delete supplier
- Delete product
- Exit program
Of course, it could be less, for the sake of the tutorial I wanted to portrait all the basic CRUD operations (Create/Read/Update/Delete)
The Code
Setting up database
We need to start a connection with the SQLite database, which is nothing more than a file. We can do it directly, but we are going to get the connection from the main program calling the function create_connection that will receive db (the file path of the database) and return the connection
def create_connection(db):
conn = None
try:
conn = sqlite3.connect(db)
conn.execute("PRAGMA foreign_keys = 1")
except sqlite3.Error as err:
print(err)
return conn
Now we can in the main program, call it like this
def main():
DBFILE = "db.sqlite3"
conn = create_connection(DBFILE)
with conn:
Creating our tables
We may need to create our tables, that would be handled by the functions createProducts and createSuppliers
def createSuppliers(conn):
query = """CREATE TABLE IF NOT EXISTS suppliers (
id INTEGER PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
address TEXT
);"""
execute(conn, query)
def createProducts(conn):
query = """CREATE TABLE IF NOT EXISTS products (
id INTEGER PRIMARY KEY NOT NULL,
description TEXT NOT NULL,
quantity INTEGER NOT NULL,
supplier_id INTEGER NOT NULL,
FOREIGN KEY(supplier_id) REFERENCES suppliers(id)
);"""
execute(conn, query)
The "IF NOT EXISTS" will allow us to create them only if they already don't exist. That foreign key considers this part of the problem: "Every product they have is only bought from one supplier".
Now, what is that execute function at the end? We need to code that.
def execute(conn, query, args = None):
if args:
conn.execute(query, args)
conn.commit()
else:
conn.execute(query)
conn.commit()
Without this function we will be repeating the conn.execute and conn.commit every time we need to execute a query.
Inserting data into our database
So now to insert a supplier we will make a function like this
def insertSupplier(conn, supplier):
query = "INSERT INTO SUPPLIERS (name, address) VALUES (?,?);"
execute(conn, query, supplier)
The supplier argument should be a list with the value (name, address). See how tiny the function is? That's because we wrote execute and create_connection before. To insert a product you can follow along with that pattern.
Listing all the records
To read every record that is stored in a table, we will use the SQL Select statement
def selectAllProducts(conn):
query = "SELECT * FROM products;"
print (pd.read_sql_query(query, conn))
pd is the alias for pandas, a library that really helps when it comes to showing and manipulating data, we need to import it at the top of our python program like this import pandas as pd. The read_sql_query function returns a DataFrame corresponding to the result set of the query string. Think of the DataFrame as a table, a pretty formatted one.
Update a product
We will need to update the product's stock, so we will need a positive or negative quantity to add or subtract from the current stock.
def updateProductQuantity(conn, productId, quantity):
query = "UPDATE products SET quantity = (quantity + ?) WHERE id = ?;"
execute(conn, query, (quantity, productId))
Delete a record
In order to delete a record, we must call the DELETE statement with the id of the object we want to remove.
def deleteSupplier(conn, supplierId):
query = "DELETE FROM suppliers WHERE id = ?;"
execute(conn, query, supplierId)
Final thoughts
Yay! You got here. We performed all the basic operations for a CRUD program. You can allow the user to interact with the previous functions in different ways. The final example will show you just a basic input menu, but you can use them within a web app, a pyqt desktop, or even a Raspberry PI!
Here's the final code
See you around!
Please tell me, have you ever tried SQLite? And if you have, what was the last thing you used it for?
