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)


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

##We must commit the transaction

# close out the connection


Einblick is an AI-native data science platform that provides data teams with an agile workflow to swiftly explore data, build predictive models, and deploy data apps. Founded in 2020, Einblick was developed based on six years of research at MIT and Brown University. Einblick is funded by Amplify Partners, Flybridge, Samsung Next, Dell Technologies Capital, and Intel Capital. For more information, please visit and follow us on LinkedIn and Twitter.

Start using Einblick

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

  • All connectors
  • Unlimited teammates
  • All operators