How to Apply SQL in Python using sqlite3

In this short guide, you’ll see how to apply SQL in Python using sqlite3 by:

  • Creating a database and two tables
  • Inserting values into the tables
  • Joining the tables together

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_iditem_name
1Microwave
2Oven
3Refrigerator
4Toaster
5Blender

And the ‘prices‘ table would contain the information below:

item_idprice
1250
2700
31200
480
5300

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, [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

Next, 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, 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

Leave a Comment