How can I create an in-memory database with sqlite?
You create a new connection each time you call the function. Each connection call produces a new in-memory database.
Create the connection outside of the function, and pass it into the function, or create a shared memory connection:
db = sqlite3.connect("file::memory:?cache=shared")
However, the database will be erased when the last connection is deleted from memory; in your case that'll be each time the function ends.
Rather than explicitly call db.commit()
, just use the database connection as a context manager:
try: with db: cur = db.cursor() # massage `args` as needed cur.execute(*args) return Trueexcept Exception as why: return False
The transaction is automatically committed if there was no exception, rolled back otherwise. Note that it is safe to commit a query that only reads data.
I created a dataframe and dumped it into a memory db with a shared cache:
#sql_write.pyimport sqlite3import pandas as pdconn = sqlite3.connect('file:cachedb?mode=memory&cache=shared')cur = conn.cursor()df DT Bid Ask0 2020-01-06 00:00:00.103000 1.11603 1.116051 2020-01-06 00:00:00.204000 1.11602 1.11605... ... ... ...13582616 2020-06-01 23:59:56.990000 1.11252 1.1125613582617 2020-06-01 23:59:58.195000 1.11251 1.11255[13582618 rows x 3 columns]df.to_sql("ticks", conn, if_exists="replace")
Read from the memory db in another thread / script:
#sql_read.pyimport sqlite3import pandas as pdconn = sqlite3.connect('file:cachedb?mode=memory&cache=shared')cur = conn.cursor()df = pd.read_sql_query("select * from ticks", conn)df DT Bid Ask0 2020-01-06 00:00:00.103000 1.11603 1.116051 2020-01-06 00:00:00.204000 1.11602 1.11605... ... ... ...13582616 2020-06-01 23:59:56.990000 1.11252 1.1125613582617 2020-06-01 23:59:58.195000 1.11251 1.11255[13582618 rows x 3 columns]
Note that it's a 15-second read from in memory, on 1.35 million rows (python 2.7). If I pickle the same dataframe and open it, the read takes only 0.3 seconds: that was very disappointing to discover, as I was hoping to dump a huge table into memory and pull it up anywhere I wanted instantly. But there you go, pickle it is.