SQLite database contents is not preserved between reconnections

[rutsky]

I'm trying to use aioodbc for working with SQLite database and struggling with issue that database content is not preserved after closing connection.

Take a look at the following example:

import asyncio
import aioodbc


loop = asyncio.get_event_loop()


async def test_example():
    dsn = 'Driver=SQLite;Database=test_db.sqlite'
    async with aioodbc.connect(dsn=dsn, loop=loop) as conn:
        async with await conn.cursor() as cur:
            await cur.execute("CREATE TABLE test (id INTEGER PRIMARY KEY)")
            await cur.execute("INSERT INTO test VALUES (1)")
            await cur.execute("SELECT * FROM test")
            r = await cur.fetchall()
            print(r)

    async with aioodbc.connect(dsn=dsn, loop=loop) as conn:
        async with await conn.cursor() as cur:
            # This line fails:
            # pyodbc.Error: ('HY000', '[HY000] [SQLite]no such table: test (1) (1) (SQLExecDirectW)')
            await cur.execute("SELECT * FROM test")
            r = await cur.fetchall()
            print(r)

loop.run_until_complete(test_example())

in this example I create table, insert value, close DB, then reopen DB and try to read inserted value. This fails with:

$ python test_odbc.py 
[(1, )]
Traceback (most recent call last):
  File "test_odbc.py", line 24, in <module>
    loop.run_until_complete(test_example())
  File "/usr/lib/python3.5/asyncio/base_events.py", line 387, in run_until_complete
    return future.result()
  File "/usr/lib/python3.5/asyncio/futures.py", line 274, in result
    raise self._exception
  File "/usr/lib/python3.5/asyncio/tasks.py", line 239, in _step
    result = coro.send(None)
  File "test_odbc.py", line 20, in test_example
    await cur.execute("SELECT * FROM test")
  File "/usr/lib/python3.5/asyncio/futures.py", line 361, in __iter__
    yield self  # This tells Task to wait for completion.
  File "/usr/lib/python3.5/asyncio/tasks.py", line 296, in _wakeup
    future.result()
  File "/usr/lib/python3.5/asyncio/futures.py", line 274, in result
    raise self._exception
  File "/usr/lib/python3.5/concurrent/futures/thread.py", line 55, in run
    result = self.fn(*self.args, **self.kwargs)
pyodbc.Error: ('HY000', '[HY000] [SQLite]no such table: test (1) (1) (SQLExecDirectW)')

Perhaps I'm missing some configuration parameter?
test_db.sqlite file is created, but empty.

I'm running on Ubuntu 16.04 with Python 3.5 in virtualenv.

$ pip list
aioodbc (0.0.3)
pip (8.1.1)
pkg-resources (0.0.0)
pyodbc (3.0.10)
setuptools (20.7.0)

[asvetlov]

I suspect SQLite doesn't use autocommit mode by default.

[rutsky]

@asvetlov thanks for the comment. Indeed this looks like autocommit issue, if I add

await cur.execute("COMMIT")

before closing first cursor data is being stored permanently.

Still this is counterintuitive and IMO unexpected default behavior, which is different to Python sqlite3 module behavior.

[asvetlov]

autocommit=False is a default mode for pyodbc, aioodbc inherits the behavior.

You may pass autocommit=True into aioodbc.connect() call though.

[rutsky]

Python's sqlite3 module will not save changes made without calling commit() if connection is closed (so this behavior is the same as in aioodbc), but if sqlite3's connection is used as context manager it automatically calls commit()/rollback() on scope exit (this behavior is different from aioodbc).

I think it's not worth to change semantics here, so this issue is resolved.

@asvetlov thanks, settings autocommit=True resolves this issue too.