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()
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 www.einblick.ai and follow us on LinkedIn and Twitter.