How to Apply SQL in Python using sqlite3

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