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:

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

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