Deounix Deounix - 4 months ago 13
Python Question

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

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



  1. create name table

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


    which returned
    True

  2. 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")

Answer

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.