How to Create a Database in Python using sqlite3

In this guide, you’ll see a complete example with the steps to create a database in Python using sqlite3.

More specifically, you’ll learn how to:

Create a database and tables using sqlite3
Insert values into the tables
Display the results in a DataFrame

But before we begin, here is a simple template that you can use to create your database using sqlite3:

import sqlite3
sqlite3.connect('database_name')

Steps to Create a Database in Python using sqlite3

Step 1: Create the Database and Tables

In this step, you’ll see how to create:

A new database called: test_database
2 tables called: products, and prices

Here are the columns to be added for the 2 tables:

Table Name	Column Name	Column Format
products	product_id	Integer – Primary Key
products	product_name	Text
prices	product_id	Integer – Primary Key
prices	price	Integer

Below is the script that you can use in order to create the database and the 2 tables using sqlite3:

import sqlite3

conn = sqlite3.connect('test_database') 
c = conn.cursor()

c.execute('''
          CREATE TABLE IF NOT EXISTS products
          ([product_id] INTEGER PRIMARY KEY, [product_name] TEXT)
          ''')
          
c.execute('''
          CREATE TABLE IF NOT EXISTS prices
          ([product_id] INTEGER PRIMARY KEY, [price] INTEGER)
          ''')
                     
conn.commit()

Once you run the above script in Python, a new file, called test_database, would be created at the same location where you saved your Python script.

Step 2: Insert values into the tables

For this step, let’s insert the following data into the ‘products‘ table:

product_id	product_name
1	Computer
2	Printer
3	Tablet
4	Desk
5	Chair

Let’s also insert the following data into the ‘prices‘ table:

product_id	price
1	800
2	200
3	300
4	450
5	150

Here is the complete code to insert the values into the 2 tables:

import sqlite3

conn = sqlite3.connect('test_database') 
c = conn.cursor()
                   
c.execute('''
          INSERT INTO products (product_id, product_name)

                VALUES
                (1,'Computer'),
                (2,'Printer'),
                (3,'Tablet'),
                (4,'Desk'),
                (5,'Chair')
          ''')

c.execute('''
          INSERT INTO prices (product_id, price)

                VALUES
                (1,800),
                (2,200),
                (3,300),
                (4,450),
                (5,150)
          ''')

conn.commit()

Step 3: Display the results

For the final step, let’s join the ‘products‘ table with the ‘prices‘ table using the product_id column which is present in both tables.

You can then run the following code to display the results in Pandas DataFrame:

import sqlite3
import pandas as pd

conn = sqlite3.connect('test_database') 
c = conn.cursor()
                   
c.execute('''
          SELECT
          a.product_name,
          b.price
          FROM products a
          LEFT JOIN prices b ON a.product_id = b.product_id
          ''')

df = pd.DataFrame(c.fetchall(), columns=['product_name','price'])
print (df)

You’ll then get the following results:

  product_name  price
0     Computer    800
1      Printer    200
2       Tablet    300
3         Desk    450
4        Chair    150

Additional Sources

You just saw how to create a database in Python using the sqlite3 package. You may want to check the following articles to learn more about connecting Python with different database applications: