File locks in SQLite

Question

I am writing my first SQLAlchemy (0.6.8) / Python (2.7.1) program, sitting on top of SQLite (3.7.6.3, I think), running on Windows Vista.

To do unit testing, I point SQLite to the test database, and my unit test scripts usually delete the database file, so I constantly work with the known initial state.

Sometimes my (single-threaded) unit-tests cannot delete a file:

WindowsError: [Error 32] The process cannot access the file because it is being used by another process 

The only process using this file is the unit-test wiring harness. Obviously, some locks are not released by one of my completed unit tests, which prevents the removal of the next block in the same process from the file.

I looked at all the places that the session created and confirmed that the corresponding session.commit () or session.rollback () exists.

I looked for all calls to session.commit () and session.rollback () in my code and immediately added a call to session.close () to explicitly release any transactional locks, but hasn’t helped.

Are there any secrets so that the remaining locks are removed at the end of the transaction to allow file deletion?

Answer 1

Someone had a similar problem: http://www.mail-archive.com/ [e-mail] /msg20724.html

You should use NullPool when establishing a connection to ensure that there is no active connection after session.close()

 from sqlalchemy import create_engine from sqlalchemy.pool import NullPool to_engine = create_engine('sqlite:///%s' % temp_file_name, poolclass=NullPool) 

Link: http://www.sqlalchemy.org/docs/06/core/pooling.html?highlight=pool#sqlalchemy.pool

This is only required in SQLAlchemy prior to 0.7.0. After 0.7.0, this became the default for SQLite. Link: http://www.sqlalchemy.org/docs/core/pooling.html?highlight=pool#sqlalchemy.pool

Answer 2

Do you need database sharing during unit tests? If not, use the in-memory SQLite database for these tests. From the SQLAlchemy documentation :

Sqlite identifier: memory: default if there is no file path. Specify sqlite: // and nothing:

 # in-memory database e = create_engine('sqlite://') 

There is no need to manage temporary files, do not use locking semantics, guarantee clean slate between unit tests, etc.