How can I create an in-memory database with sqlite?

Question

I'm trying to create an in-memory database using sqlite3 in Python.

I created a function to create a db database file and store information in to it and that is working 100%.

But trying to connect with :memory: I've faced some problems.

What I'm doing is:

import sqlite3

def execute_db(*args):
    db = sqlite3.connect(":memory:")
    cur = db.cursor()
    data = True
    try:
        args = list(args)
        args[0] = args[0].replace("%s", "?").replace(" update "," `update` ")
        args = tuple(args)
        cur.execute(*args)
        arg = args[0].split()[0].lower()
        if arg in ["update", "insert", "delete", "create"]: db.commit()
    except Exception as why:
        print why
        data = False
        db.rollback()
    db.commit()
    db.close()
    return data

create name table

execute_db("create table name(name text)")

which returned True

insert some information to this table

execute_db("insert into name values('Hello')")

which returned

no such table: name
False

Why doesn't this work? It works when I use a file:

db = sqlite3.connect("sqlite3.db")

Why the double db.commit() when altering data?

Martijn Pieters
– Martijn Pieters

2016-07-30 17:37:40 +00:00
Commented Jul 30, 2016 at 17:37 — Martijn Pieters
– Martijn Pieters, Commented Jul 30, 2016 at 17:37
@MartijnPieters Ohh, my bad thank u.

Deounix
– Deounix

2016-07-30 17:44:52 +00:00
Commented Jul 30, 2016 at 17:44 — Deounix
– Deounix, Commented Jul 30, 2016 at 17:44

Martijn Pieters · Accepted Answer · 2016-07-30 17:44:50Z

31

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 True
except 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.

edited Jul 30, 2016 at 17:44

answered Jul 30, 2016 at 17:39

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Deounix Over a year ago

but if i create shared memory connection is this connection Fast as memory connection ?

ajsp · Accepted Answer · 2020-11-18 17:34:15Z

I created a dataframe and dumped it into a memory db with a shared cache:

#sql_write.py
import sqlite3
import pandas as pd

conn = sqlite3.connect('file:cachedb?mode=memory&cache=shared')
cur  = conn.cursor()

df
          DT      Bid      Ask
0         2020-01-06 00:00:00.103000  1.11603  1.11605
1         2020-01-06 00:00:00.204000  1.11602  1.11605
...                              ...      ...      ...
13582616  2020-06-01 23:59:56.990000  1.11252  1.11256
13582617  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.py
import sqlite3
import pandas as pd

conn = sqlite3.connect('file:cachedb?mode=memory&cache=shared')
cur  = conn.cursor()

df = pd.read_sql_query("select * from ticks", conn)

df
          DT      Bid      Ask
0         2020-01-06 00:00:00.103000  1.11603  1.11605
1         2020-01-06 00:00:00.204000  1.11602  1.11605
...                              ...      ...      ...
13582616  2020-06-01 23:59:56.990000  1.11252  1.11256
13582617  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.

Collectives™ on Stack Overflow

How can I create an in-memory database with sqlite?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related