How to Import a pandas DataFrame Into a SQLite Database

In this tutorial, you will learn how to import a DataFrame into a SQLite database.

TLDR solution

conn = sqlite3.connect('path-to-database/db-file') 

df.to_sql('table_name', conn, if_exists="replace", index=False)

Step-by-Step Example

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

Step 1: Prepare the Database

Suppose you have created a database named fish_db on your desktop and it has a table names fishes with two columns: fish_name and egg_count.

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')

Next 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 1: Insert a DataFrame

Suppose you want to import the following DataFrame:

import pandas as pd

data = {
  'fish_name': ['salmon', 'pufferfish', 'shark'],
  'egg_count': [5000, 200, 2]
}

df = pd.DataFrame(data)

print(df)

    fish_name  egg_count
0      salmon       5000
1  pufferfish        200
2       shark          2

To import the DataFrame into your database use the to_sql method like this:

df.to_sql('fishes', conn, if_exists='replace', index=False)

Step 2: Verify the Import

Let's verify that everything is correctly imported by reading the table and printing all rows:

c = conn.cursor()

c.execute(
  """SELECT * FROM fishes"""
)

for row in c.fetchall():
    print(row)

Alternatively, you can also load the table into a DataFrame.

That's it! You just learned how to import a DataFrame into a SQLite database.