How to Apply SQL in Python using sqlite3

September 25, 2021

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
Home » Python  »  How to Apply SQL in Python using sqlite3