Support sqlite databases about jupyterlab-sql HOT 5 CLOSED

Now fixed in jupyterlab-sql 0.1.8.

from jupyterlab-sql.

Is there a specific case (e.g. a unit test) highlighting the current reason for calling it unsupported? At first glance it seems like SQL alchemy is aware of this and tries to solve it: https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#threading-pooling-behavior

from jupyterlab-sql.

Quoting the relevant parts from the link for posterity:

SQLAlchemy sets up pooling to work with Pysqlite’s default behavior:
- When a :memory: SQLite database is specified, the dialect by default will use SingletonThreadPool. This pool maintains a single connection per thread, so that all access to the engine within the current thread use the same :memory: database - other threads would access a different :memory: database.
- When a file-based database is specified, the dialect will use NullPool as the source of connections. This pool closes and discards connections which are returned to the pool immediately. SQLite file-based connections have extremely low overhead, so pooling is not necessary. The scheme also prevents a connection from being used again in a different thread and works best with SQLite’s coarse-grained file locking.

from jupyterlab-sql.

Thanks for looking into it!

I've tried changing the connection pool to both the SingletonThreadPool and the NullPool, but these don't fix the problem.

Basically, in most web apps, you have a single connection pool that is defined at start-up and whose lifetime matches that of the app. For jupyterlab-sql, we don't have this: the user can change what database to connect to between queries.

We therefore create a new engine in response to every execution. Since these are created asynchronously, they are in general on different threads.

I think the right solution would be to have a store mapping connection strings to sqlalchemy engines (maybe just for sqlite). We could then re-use the engine if it's present in the cache.

from jupyterlab-sql.

Having a cache of connection string to sqlalchemy engine that persists across requests seems to work. I have been prototyping with this:

import tornado.ioloop
import tornado.web
import tornado.options
from tornado.log import app_log as log
from tornado.escape import json_decode

from sqlalchemy import create_engine


cache = dict()


class MainHandler(tornado.web.RequestHandler):
    def initialize(self, cache):
        self.cache = cache

    def post(self):
        data = json_decode(self.request.body)
        query = data['query']
        connection_string = 'sqlite://'
        try:
            engine = self.cache[connection_string]
        except KeyError:
            engine = create_engine(connection_string)
            self.cache[connection_string] = engine
        response = engine.connect().execute(query)
        log.info(response)
        if response.returns_rows:
            log.info(list(response))
        else:
            log.info('no rows')
        self.write('done')


def make_app():
    return tornado.web.Application([
        (r'/', MainHandler, dict(cache=cache))
    ], debug=True)


if __name__ == '__main__':
    tornado.options.parse_command_line()
    app = make_app()
    app.listen(8866)
    tornado.ioloop.IOLoop.current().start()

Then, httpie commands like:

http post 127.0.0.1:8866 query="create table t (i int)"
http post 127.0.0.1:8866 query="insert into t values (1), (2)"
http post 127.0.0.1:8866 query="select * from t"

... work as expected.

from jupyterlab-sql.