SQLite3 - The Basics

 SQLite3 

 

SQLite is already a preinstalled library in Python3. To use SQLite3, you’ll need to create two databases: 

• DMS.py - A python file for running and managing the database. 
• test.db - The database file, must have the extension .db 
• 
  

You will need to make sure these files are in the same folder. 

For simplicity, I will be creating one table. You are already expected to know how a database works and the objective of primary keys. 

Open that folder in your IDE of choice, I recommend Visual Studio Code. 

 

Let’s start writing some code. To import the SQLite3 module, we need to write: 

import sqlite3 

To connect our database, we need to use the connect() function: 

con = sqlite3.connect(“test.db”) 

We also need to open a cursor to execute SQL queries: 

cur = con.cursor() 

 

Our setup is now completed, we can execute SQL queries for our database. However, our database is empty, so we need to add a table and columns. To do this, I personally use SQLite Studio which is a GUI based viewing software for SQLite databases. Download SQLite Studio from here: 

https://sqlitestudio.pl/ 

Once installed, open a new database by following these screenshots: 

 

 

Now create a new table: 

 

 

For this example, I'm going to make a table about cars. Once the table has been created, we are going to create some columns. These are kind of like attributes, for example a car would have a certain colour, make, model and price. It will also have a carID to uniquely identify each value. Let’s make some columns based off these parameters: 

 

 

CarID is our primary key. This should be an autonumber value: 

 

 

Now our carID column is created. Create the other columns as well following a similar process: 

 

 

 

 

 

 

 

All our columns are now created, we can start to create, manage and delete data. 

 

Let's insert some values into the table. To do this we use an SQL statement to INSERT data: 

cur.execute("""INSERT INTO  

            cars(colour, make, model, price) 

            VALUES("red", "BMW", "Z4", 43000)""") 

Here the statement is colour coded: 

• Commands 
• Table name 
• Columns 
• Values 

The values relate to the order in which we specified the columns. The price column stores integer values, so we don’t need quotation marks. 

 

 

To add these values to the database, we need to use: 

con.commit() 

By running that code, we see that data has been added to our cars table: 

 

Let’s delete this value from the table. To do this we use: 

cur.execute("""DELETE FROM 

            cars WHERE 

            carID=(1)""") 

con.commit() 

Here the statement is colour coded: 

• Commands 
• Table name 
• Columns 
• Values 

We are deleting the row in cars where the carID is equal to 1. It is best to delete using primary keys so there are no duplicate values.  

 

We should now see that our data has been removed from the database. 

Finally, we are going to search for data. First, we will need to add more data because we deleted it... Let’s use the same script as before to insert some more data: 

cur.execute("""INSERT INTO  

            cars(colour, make, model, price) 

            VALUES("red", "BMW", "Z4", 43000)""") 

con.commit() 

Now we have data, we can search: 

To search for data we need to use the SELECT keyword: 

cur.execute("""SELECT * FROM cars WHERE colour=("red")""") 

print(cur.fetchall()) 

Here the statement is colour coded: 

• Commands 
• Wildcard 
• Table name 
• Columns 
• Values 

The wildcard * means everything in the table. We are selecting everything (*) from cars where the colour value is red. 

We use cur.fetchall() to fetch every value and format it in an array. 

If we run this code: 

 

We get this output: 

 

That is the basics of SQLite! Of course, it gets a lot more complicated as you go along but here, we have learnt the simple techniques and queries we need to manage a database.