Why doesn't my sqlite3 in-memory table exist after running a CREATE query?

Question

I'm trying to create an in-memory sqlite3 database using Twisted's twisted.enterprise.adbapi.ConnectionPool class. My test-case is as follows:

#! /usr/bin/env python
from twisted.internet import task
from twisted.internet import defer
from twisted.enterprise.adbapi import ConnectionPool

sql_init = (
    "CREATE TABLE ajxp_index ( node_id INTEGER PRIMARY KEY AUTOINCREMENT );",
    "INSERT INTO ajxp_index (node_id) VALUES (9001);",
)


@task.react
@defer.inlineCallbacks
def main(reactor):
    cp = ConnectionPool("sqlite3", ":memory:", check_same_thread=False)
    for sql in sql_init:
        print(sql)
        yield cp.runQuery(sql)

Running the above script produces the following error: sqlite3.OperationalError: no such table: ajxp_index

Where things get strange is if we replace :memory: with a path to a file on persistent storage, e.g.: /tmp/foo.sqlite. Under this condition, the script executes as expected.

Further, running the same SQL queries using the sqlite3 module in the standard library runs as expected:

import sqlite3

conn = sqlite3.connect(":memory:")
for sql in sql_init:  # same commands as in above example
    conn.execute(sql)

What gives? Is this a bug in Twisted, or am I doing something wrong?

EDIT:

As per notorious.no's suggestion, I've updated my example to use cp.runInteraction, but the result remains the same:

@task.react
@defer.inlineCallbacks
def main(reactor):
    cp = ConnectionPool("sqlite3", ":memory:", check_same_thread=False)
    for sql in sql_init:
        print(sql)
        yield cp.runInteraction(lambda cursor: cursor.execute(sql))

EDIT 2

Okay, this seems to work:

def _interact(cursor, script):
    cursor.executescript(script)


@task.react
@defer.inlineCallbacks
def main(reactor):
    cp = ConnectionPool("sqlite3", ":memory:", check_same_thread=False)
    yield cp.runInteraction(_interact, "\n".join(sql_init))

Answer 1

Your code doesn't work because you're doing cp.runQuery, instead it should be runInteraction.

cp.runInteraction(lambda cursor, stmt: cursor.execute(stmt))

I wrote a post that might be helpful https://notoriousno.blogspot.com/2016/08/twisted-klein-database-usage.html?m=1