'sqlite:///:memory:' is OK while `sqlite:///db.sqlite` fails with `Cannot operate on a closed database.`

[vlcinsky]

Run

$ pytest -sv tests/test_transactions.py

and see, that when the test runs with in-memory sqlite database, all cases are passing, but if it is using real file, it fails in all but one case.

The failure shows tracebacks such as:

Traceback (most recent call last):
  File "/home/javl/sandbox/playwith_records/records/.tox/py36/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 1186, in fetchone
    row = self._fetchone_impl()
  File "/home/javl/sandbox/playwith_records/records/.tox/py36/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 1067, in _fetchone_impl
    return self.cursor.fetchone()
sqlite3.ProgrammingError: Cannot operate on a closed database.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/javl/sandbox/playwith_records/records/.tox/py36/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1288, in _safe_close_cursor
    cursor.close()
sqlite3.ProgrammingError: Cannot operate on a closed database.

[vlcinsky]

Doublechecking my test modifications to make sure, it is not introduced by test only:

• all fixures used (db, foo_table) use default scope, which is function. Thus the values provided shall be created new for each test.
• the db fixture yields the database object, which is then used in the test. The code after the yield in the fixture is executed after the test case is completed (teardown).
• I did not find any global state related to database within the fixtures or test code which would survive across multiple tests.

By "test" I mean one test case (function call, e.g. test_plain_db or test_plain_conn).

My estimation is, the close on file based sqlite database is somehow surviving across multiple test calls and later attempts are failing due to database being already closed.

[vlcinsky]

Queries can be run against:

• database object (e.g. test_plain_db test case)
• connection object (e.g. test_plain_con test case.

The problems are with db.query('SELECT count(*) AS n FROM foo')[0].n.

Fetching from queries run via connection are fine, e.g. conn.query('SELECT count(*) AS n FROM foo')[0].n.

Narrowing the problem definition: When database object is created from file-based sqlite database, any attempt to fetch query results from query run on such database fails with an error sqlalchemy.exc.ProgrammingError: (sqlite3.ProgrammingError) Cannot operate on a closed database. (Background on this error at: http://sqlalche.me/e/f405)

If the sqlite database is memory based, the problem does not happen.

Original test suite tested queries only against in-memory sqlite database. Refactored test suite preserves all original cases and adds test variant with file-based sqlite database. All tests run against in-memory database are passing. Test cases with file-based sqlite database which do not fetch from query run directly on database are fine too (these queries are run against connection).

To run one failing case:

$ pytest -sv tests/test_transactions.py::test_plain_db[sqlite_file]

or all of them:

$ pytest -sv tests/test_transactions.py -k sqlite_file

[jraper3]

In my research/testing, I've found that the issue is the context manager in Database.query() is closing the Connection object before you've had a chance to retrieve the records. I tried remove the with block, instead going with: conn = self.get_connection() return conn.query(query, fetchall, **params) This change currently fixed the bug, at least in context of SQLite databases. I'm not quite familiar enough with SQLAlchemy to say whether this would have a negative effect on other database types. I'm going to to a little further testing locally, then pull my changes into a pull request.

[dannguyen]

Not sure if this issue has been fixed with the May 7/12 merges, but just wanted to note that this error arises when using the records command-line tool:

$ records 'select * from things' csv --url sqlite:///mythings.sqlite

Error closing cursor
Traceback (most recent call last):
  File "/Users/dan/.pyenv/versions/anaconda3-5.3.1/lib/python3.7/site-packages/sqlalchemy/engine/result.py", line 1186, in fetchone
    row = self._fetchone_impl()
  File "/Users/dan/.pyenv/versions/anaconda3-5.3.1/lib/python3.7/site-packages/sqlalchemy/engine/result.py", line 1067, in _fetchone_impl
    return self.cursor.fetchone()
sqlite3.ProgrammingError: Cannot operate on a closed database.

During handling of the above exception, another exception occurred:

# .....

line 1186, in fetchone
    row = self._fetchone_impl()
  File "/Users/dan/.pyenv/versions/anaconda3-5.3.1/lib/python3.7/site-packages/sqlalchemy/engine/result.py", line 1067, in _fetchone_impl
    return self.cursor.fetchone()
sqlalchemy.exc.ProgrammingError: (sqlite3.ProgrammingError) Cannot operate on a closed database. (Background on this error at: http://sqlalche.me/e/f405)

[felciano]

Has this fix been released? It looks like the patch was integrated on May 7 2019 but I just installed the master branch via pipenv install git+https://github.com/kennethreitz/records.git@master#egg=records and I'm still seeing this bug when trying to access a local sqlite3 database:

db = records.Database("sqlite:////absolute/path/to/db.sqlite3")
rows = db.query("SELECT * FROM TASK")
for r in rows:
    print(r)

[felciano]

Thank you!