0

My python 35 script needs at some point to run retrieve some data from .sqlite file (let's call it src.sqlite). It doesn't need to write just to read. But it can't access directly src.sqlite with sqlite3 because src.sqlite is locked by another program (sqlite3.OperationalError: database is locked)

So I made a copy of src.sqlite (copy.sqlite) with shutil.copy2 and I run my Select SQL query on copy.sqlite. After having retreived the data I need, I want to delete copy.sqlite and thus run shutil.rmtree(), but I get this error:

[WinError 32] The process cannot access the file because it is being used by another process.

When looking for a solution for this error I came accross the possibility of using temp file (yes, I am new to Python!). So I am now trying to create a temp file in which I will copy src.sqlite and then I should run my SQL query on this temp.sqlite. I have seen this question and that one but I have trouble adapting them to my needs. So far this is what I have done:

temp_dir = tempfile.gettempdir()
temp_path = os.path.join(temp_dir, 'temp_file_name')
shutil.copy2("C:\\src.sqlite", temp_path)
print('exists', os.path.isfile(temp_path))
print(temp_dir)

But when I run my SQL query on that temp I get this error sqlite3.OperationalError: no such table XXX.

Community
  • 1
  • 1
Sitak
  • 153
  • 8
  • This doesn't seem like a healthy setup. I assume you're attempting to have two processes write to the same DB at the same time? Even if you successfully copy the DB then you're missing updates being written by the other process(es). You should probably move to something that better supports concurrent writes e.g. MySQL/Postgres. – roganjosh Nov 26 '16 at 16:10
  • My python script will no write in the sqlite, just retrieve some data. There is no problem with the updates, as the retrieved data will not change. – Sitak Nov 26 '16 at 16:17
  • Then you can have multiple processes read from the DB at the same time. I'm not sure what you mean about " because src.sqlite is blocked by another program." in that case. – roganjosh Nov 26 '16 at 16:18
  • That's the message sqlite gives me: `sqlite3.OperationalError: database is locked`.When the python run, the .sqlite is open and modified "constantly" by another program but the modified data are not the one the python will retrieve. – Sitak Nov 26 '16 at 16:22
  • 1
    Copying an in-use SQLite database is not a good idea; you'll likely end up with either missing data or a corrupt database, and may even block the real user. Since your copy seems to be missing the table you expect it to have, I'm guessing that your other program creates it but never does a `commit`, holding the database open indefinitely. – Colonel Thirty Two Nov 26 '16 at 18:13
  • Well if I copy the .sqlite, there is not missing data/table. It's only when I use (or more exactly try to use) a temp that I get this error – Sitak Nov 26 '16 at 18:45
  • This answer might help resolve if the other program is working does not hold exclusive lock. https://stackoverflow.com/a/7857826 – balki Aug 08 '17 at 18:38

0 Answers0