Python sqlite3: How to use sqlite with dataframes

Paul Yang - November 9th, 2022

Python's baked-in sqlite3 package makes it easy to read and write from a sqlite database.

Whether you already have use cases for sqlite, or if you're actually trying to figure out how to use SQL in conjunction with dataframes and are happier to do complex data manipulations using SQL syntax, this tutorial is for you.

We have prepared a small example that shows how easy it is to write to SQL from a dataframe and then read it back out from SQL to a dataframe.

import sqlite3
#connect to a database, creating it if it doesn't exist 
conn = sqlite3.connect("demo.db")

#write our dataframe to the sqlite database
df.to_sql("demo_data", conn, if_exists="replace")

#let's select data out of the table we just wrote
query = """select * from demo_data"""
res = conn.execute(query)

#get one row from the result
print(res.fetchone()) #to get all, use res.fetchall()

#now let's get a sqlite table into a dataframe 
query = """select * from demo_data"""
df2 = pd.read_sql_query(query, conn)

df2.head()

#let's insert into this table one row of new data
query = """INSERT INTO demo_data VALUES (0, 51.4028287306306, 'married', 'Bachelor''s degree', 0, 821, 36, 21079.571367776967, 25.79275296304541, 14991, 600, 1, 217, 1, 0)"""
conn.execute(query)

##We must commit the transaction
conn.commit()


# close out the connection
conn.close()

Start using Einblick

Pull all your data sources together, and build actionable insights on a single unified platform.

  • All connectors
  • Unlimited teammates
  • All operators