How to write a Pandas dataframe to an SQLite table

You can use SQLite to create a permanent copy of your dataframe. This can be useful if you ever need to come back to your code at a later date.

#Python
#SQL
#HowTo

Pandas dataframes can be imported and exported to and from SQLite tables.

What is SQLite?

SQLite is a self-contained database management system.

It runs on your local file system and needs minimal administration and resources. Unlike other systems such as PostgreSQL or MySQL, which need a server process in order to run.

Exporting a dataframe to SQLite

First let’s import sqlite3 and pandas:

import sqlite3
import pandas as pd

And create our sample Pandas dataframe:

df = pd.DataFrame({'id': [100, 101, 102, 103], 'name': ['Tom', 'Jerry', 'Sooty', 'Sweep']})

Next, let’s set up a database connection using sqlite3:

conn = sqlite3.connect('cartoon_characters.db')

If the database cartoon_characters.db doesn’t already exist then it will be created.

Now let’s create a table called names in the database:

df.to_sql('names', conn, if_exists='replace')

We need the if_exists='replace' option above as the default option is if_exists='fail', which will raise a ValueError.

Now you can see that the cartoon_characters.db file has been created in your working directory.

You can use DB Browser for SQLite to open up your database.

Now you can see that the table names has now been created in the cartoon_characters.db database.

Importing an SQLite table to a dataframe

To read this data back into a Pandas dataframe, simply use the read_sql() method to select all records from the table.

conn = sqlite3.connect('cartoon_characters.db')
df = pd.read_sql('SELECT * FROM names', conn)

And there you have it, importing and exporting dataframes into SQLite is as simple as that!

Check out this post if you’ve been working with Python lists and would like to get them into a Pandas dataframe for your analysis.