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