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()About
Einblick is an agile data science platform that provides data scientists with a collaborative 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 customers include Cisco, DARPA, Fuji, NetApp and USDA. Einblick is funded by Amplify Partners, Flybridge, Samsung Next, Dell Technologies Capital, and Intel Capital. For more information, please visit www.einblick.ai and follow us on LinkedIn and Twitter.