Reputation: 145
This happens with python 2.6.6, sqlite3. I have a db class that uses sqlite. Below is part of its init.
def _init_db(self):
"init the database tables and indices"
print '\n'.join(DROP_TABLES[0:1])
print '\n'.join(CREATE_TABLES[0:1])
print '\n'.join(CREATE_INDEXES[0:1])
print '\n'.join(CREATE_TRIGGERS[0:1])
for query in DROP_TABLES:
self.connection.execute(query)
# self.connection.commit()
for query in CREATE_TABLES:
self.connection.execute(query)
# self.connection.commit()
for query in CREATE_INDEXES:
self.connection.execute(query)
# self.connection.commit()
for query in CREATE_TRIGGERS:
self.connection.execute(query)
self.connection.commit()
Here is a printout of the queries. (its not very important in my opinion, here for completeness)
DROP TABLE IF EXISTS graph_T
CREATE TABLE IF NOT EXISTS graph_T
(v1 int,
v2 int,
step_start int,
step_end int DEFAULT 2147483647,
value int DEFAULT 1,
path_kind int DEFAULT 0,
path_id long,
partial_path_id long)
CREATE INDEX IF NOT EXISTS idxgraph_T
ON graph_T(v1,v2)
CREATE TRIGGER IF NOT EXISTS trig_graph_T_path_id
AFTER INSERT ON graph_T
BEGIN
UPDATE graph_T SET
path_id = (10000 * 10000 * max(new.v1, new.v2) +
10000 * min(new.v1, new.v2) + 0 ) ,
partial_path_id = 10000 * 10000 * max(new.v1, new.v2) +
10000 * min(new.v1, new.v2)
WHERE rowid = new.rowid;
END;
I get sqlite3.OperationalError: unable to open database file on one of the self.connection.execute lines. sometimes the third or fourth (it happens also in other places in my program).
I work on windows. I am not sure why this is happening and what i am doing wrong. Would appreciate any suggestions.
More Information (due to questions asked): - I am not using concurrent access. no threads or anything of that sort.
edit- More info: i have added a timed retry on all of the connection.execute lines and it usually fails once or twice, and then works. I am guessing that maybe the data wasnt actually written to disk when the execute command returns.
Upvotes: 2
Views: 1238
Reputation: 401
The concurrent access answer helped me solve my issue. My solution was essentially to revert back to a previous un-corrupted db file from before concurrent access occurred (https://stackoverflow.com/a/57729827/3869714).
Upvotes: 0
Reputation: 19317
My intuition tells me there's got to be something suspicious going on in the filesystem, not in the software. Maybe one of these:
:-)
Upvotes: 3
Reputation: 375484
SQLite is not good with concurrent access. If you have multiple threads or processes accessing the same database file, you will run into this problem.
Upvotes: 2