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:
how-to-export-pandas-dataset-to-sqlite-database.py
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
export_sqlalchemy_engine.py
import sqlalchemy
db = sqlalchemy.create_engine('sqlite:///timeseries.db')and export the DataFrame to the database:
df_to_sql.py
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
export_to_sqlite_complete.py
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")If this post helped you, please consider buying me a coffee or donating via PayPal to support research & publishing of new posts on TechOverflow