WindowsError moving sqlite3 database after closing

Question

Here's a simplified example of my problem:

import os
import sqlite3

with sqlite3.connect('test.db.temp') as db:
    db.executescript('CREATE TABLE foo (bar);')
os.rename('test.db.temp', 'test.db')

Output:

> python test.py
Traceback (most recent call last):
  File "test.py", line 7, in <module>
    os.rename('test.db.temp', 'test.db')
WindowsError: [Error 32] The process cannot access the file because it is being used by another process

Background: I'm attempting to create a database in an "atomic" way, the simplest way to do that is create the database at a temporary location and then move it.

score 4 · Accepted Answer · edited May 23 '17 at 11:43

The problem is that the context manager of the connection object seems not to work.

This way it works:

db = sqlite3.connect('test.db.temp')
db.executescript('CREATE TABLE foo (bar);')
db.close()
os.rename('test.db.temp', 'test.db')

It is worth investigating why it implements the __enter__/__exit__ in the first place...

UPDATE: From this answer it looks like the with when used with SQLite does a transaction, so your code is similar to:

db = sqlite3.connect('test.db.temp')
db.begin_transaction()
db.executescript('CREATE TABLE foo (bar);')
db.commit()
os.rename('test.db.temp', 'test.db')

So the db is still obviously opened when renamed. The recommended code would then be something like:

with contextlib.closing(sqlite3.connect('test.db.temp')) as db:
    db.executescript('CREATE TABLE foo (bar);')
os.rename('test.db.temp', 'test.db')

edited your answer to include `contextlib.closing` :) Real shame that the sqlite api doesn't close the file as part of their contextmanager. — anthony sottile, Jan 19 '15 at 20:52
@AnthonySottile: Nice, thanks! I didn't know about `contextlib`. — rodrigo, Jan 19 '15 at 22:50

score 0 · Answer 2 · answered Jan 19 '15 at 20:18

0

According to the documentation, executescript creates a cursor object. I am guessing the cursor object is not getting destroyed immediately. Try creating a cursor explicitly in the context manager:

with sqlite3.connect('test.db.temp') as db:
    cur = db.cursor()
    cur.execute('CREATE TABLE foo (bar);')

answered Jan 19 '15 at 20:18

Mike Driscoll

32,629
8
45
88

If this is the case, wouldn't the proper fix be to add a `with cur = `... instead of hoping it closes itself sooner? (Note: I don't know the Python bindings of SQLite.) – Cameron Jan 19 '15 at 20:19
I was going off of this example: http://zetcode.com/db/sqlitepythontutorial/ which seems to work – Mike Driscoll Jan 19 '15 at 20:20
Interestingly, this example works on Xubuntu 14.04 but not on Windows 7. You have to add the explicit **db.close()** for it to work on Windows. – Mike Driscoll Jan 19 '15 at 20:25
Interesting. The call to rename should succeed regardless of if the file is opened anyway, since Windows supports renaming a file that's currently in use (the process's file handle is not invalidated). – Cameron Jan 19 '15 at 20:29
Yeah sorry, had nothing to do with the cursor. It in fact repros with just `with sqlite3.connect('test.db.temp'): pass;; os.rename('test.db.temp', 'test.db')` – anthony sottile Jan 19 '15 at 20:57
@Cameron, the file isn't opened with sharing mode `FILE_SHARE_DELETE`, so it can't be renamed while the handle is open. Neither the C runtime nor Python's io or os modules provide a way to open a file with delete sharing. You need to use `_winapi.CreateFile` or ctypes, but that won't help with the sharing mode used by sqlite3. – Eryk Sun Jan 20 '15 at 07:04

WindowsError moving sqlite3 database after closing

2 Answers2