Create a Database in Python using sqlite3

In this guide, you’ll see how to create a database in Python using sqlite3, including the steps to:

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

The Steps

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

Where the columns to be added to the 2 tables are:

Below is the script 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, [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

Next, insert the following values into the ‘products‘ table:

And then, insert the following values into the ‘prices‘ table:

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

Finally, 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)

Here are the results:

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

Additional Resources

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:

• Connect Python to SQL Server using pyodbc
• Connect Python to MS Access using pyodbc