How to Create a Database in Python using sqlite3

In this tutorial, you will learn how to create database in Python using sqlite3.

TLDR solution

create_db.py

import sqlite3

conn = sqlite3.connect('db_name') 

c = conn.cursor()

c.execute('''
          CREATE TABLE IF NOT EXISTS table_name
          ([column_a] INTEGER,
           [column_b] TEXT)
          ''')

conn.commit()

c.execute('''
          INSERT INTO table_name (column_a, column_b)
          VALUES (1,'b1'),
                 (2,'b2')
          ''')

conn.commit()

Step-by-Step Example

Sqlite3 comes included with Python. So no need to install any package.

Step 1: Create an Empty Database

Let's create a database named fish_db on your desktop. You can achieve this with the following code:

create_db.py

import os
import sqlite3

desktop_path = os.path.expanduser("~/Desktop")

# Connects to the database if it exists. If not, creates it.
conn = sqlite3.connect(desktop_path + '/fish_db')

Step 2: Create an Empty Table

Suppose, you want to insert the following table into your database:

fish_name	egg_count
salmon	5,000
pufferfish	200
shark	2

You first have to create an empty table that can hold the data, i.e., one text column and one integer column:

create_table.py

# Connect to db
conn = sqlite3.connect(desktop_path + '/fish_db') 

# Create a cursor object which allows you to read from and write to the db.
c = conn.cursor()

# Stage the table creation. Need to commit to "save" the table in the db.
c.execute('''
          CREATE TABLE IF NOT EXISTS fishes
          ([fish_name] TEXT,
           [egg_count] INTEGER)
          ''')
                   
# Commit staged actions
conn.commit()

Step 3: Insert Data

Finally, fill the table by inserting the rows as tuples:

insert_data.py

conn = sqlite3.connect(desktop_path + '/fish_db') 

c = conn.cursor()

# Stage the data insert.
c.execute('''
          INSERT INTO fishes (fish_name, egg_count)
          VALUES ('salmon',5000),
                 ('pufferfish',200),
                 ('shark',2)
          ''')
                   
conn.commit()

Step 4: Read the Table

Let's verify by loading the table into a pandas DataFrame:

insert_data.py

import pandas as pd

conn = sqlite3.connect(desktop_path + '/fish_db') 

c = conn.cursor()
                   
c.execute('''
          SELECT
          fish_name,
          egg_count
          FROM fishes
          ''')

df = pd.DataFrame(c.fetchall(), columns=['fish_name','egg_count'])
print (df)

You should see the following output:

    fish_name  egg_count
0      salmon       5000
1  pufferfish        200
2       shark          2

That's it! You just created a database, inserted data, and read from it using sqlite3.