How to export Pandas dataset to SQLite database

In our previous post we showed how to connect to an SQLite database using sqlalchemy.

In this blogpost, we’ll show how you can export a pandas DataFrame - for example, our time series example dataset - to the SQLite database.

First, we’ll load the example data frame:

import pandas as pd
# Load pre-built time series example dataset
df = pd.read_csv("https://datasets.techoverflow.net/timeseries-example.csv", parse_dates=["Timestamp"])
df.set_index("Timestamp", inplace=True)

Now we can open the SQLite database as shown in our previous post

import sqlalchemy
db = sqlalchemy.create_engine('sqlite:///timeseries.db')

and export the DataFrame to the database:

df.to_sql('timeseries', db, if_exists="replace")

I always recommend using if_exists="replace" (i.e. if the table already exists, replace it) for a quicker development process.

The database looks like this when viewed in an SQLite viewer like HeidiSQL:

Complete code example

import pandas as pd
# Load pre-built time series example dataset
df = pd.read_csv("https://datasets.techoverflow.net/timeseries-example.csv", parse_dates=["Timestamp"])
df.set_index("Timestamp", inplace=True)

import sqlalchemy
db = sqlalchemy.create_engine('sqlite:///timeseries.db')

df.to_sql('timeseries', db, if_exists="replace")