In this short guide, you’ll see how to apply SQL in Python using sqlite3.
More specifically, you’ll observe how to:
- Create a database and two tables using sqlite3
- Insert values into the tables
- Join the table together and display the results
Steps to Apply SQL in Python using sqlite3
Step 1: Create a database and tables using sqlite3
In this step, you’ll see how to create:
- A new database called: ‘test_database‘
- 2 tables called: items and prices
Where the ‘items‘ table would contain the following columns and data:
item_id | item_name |
1 | Microwave |
2 | Oven |
3 | Refrigerator |
4 | Toaster |
5 | Blender |
While the ‘prices‘ table would contain the information below:
item_id | price |
1 | 250 |
2 | 700 |
3 | 1200 |
4 | 80 |
5 | 300 |
Here is the complete syntax to create the above database and 2 tables using sqlite3:
import sqlite3 conn = sqlite3.connect('test_database') c = conn.cursor() c.execute(''' CREATE TABLE IF NOT EXISTS items ([item_id] INTEGER PRIMARY KEY, [item_name] TEXT) ''') c.execute(''' CREATE TABLE IF NOT EXISTS prices ([item_id] INTEGER PRIMARY KEY, [price] INTEGER) ''') conn.commit()
Once you run the above script in Python, a new file, called the ‘test_database,’ would be created at the same location where you saved your Python script.
Step 2: Insert values into the tables
You may now apply the syntax below in order to insert the values into the 2 tables:
import sqlite3 conn = sqlite3.connect('test_database') c = conn.cursor() c.execute(''' INSERT INTO items (item_id, item_name) VALUES (1,'Microwave'), (2,'Oven'), (3,'Refrigerator'), (4,'Toaster'), (5,'Blender') ''') c.execute(''' INSERT INTO prices (item_id, price) VALUES (1,250), (2,700), (3,1200), (4,80), (5,300) ''') conn.commit()
Step 3: Join the tables together
For the final step, let’s join the ‘items‘ table with the ‘prices‘ table using the item_id column which is present in both tables:
import sqlite3 import pandas as pd conn = sqlite3.connect('test_database') c = conn.cursor() c.execute(''' SELECT a.item_name, b.price FROM items a LEFT JOIN prices b ON a.item_id = b.item_id ''') df = pd.DataFrame(c.fetchall(), columns = ['item_name','price']) print (df)
The results would be displayed using Pandas DataFrame:
item_name price
0 Microwave 250
1 Oven 700
2 Refrigerator 1200
3 Toaster 80
4 Blender 300