Daniel,
Apologize for not posting a subject in the original request.
Thanks for offering to help; I do not think is a connection related
problem, but I could be wrong. Here are pertinent code segments:
# Method getDBConnection() is called, which performs the following:
....
# create a connection property
self.con = sqlite.connect( path + file,timeout=self.dbdefs.DB_TIMEOUT,
detect_types=sqlite.PARSE_DECLTYPES|sqlite.PARSE_COLNAMES,
isolation_level='EXCLUSIVE',check_same_thread=False)
# config some extensions: row_factory, text_factory
self.con.row_factory = sqlite.Row # access by index or col names
self.con.text_factory = sqlite.OptimizedUnicode # uncode/bytestr
#create a general cursor property
self.cur = self.con.cursor()
....
# Then the following method is called to create the tables if they do not exist:
# create the database structure if does not exist
errBool = not self._DBHandler__createDBTables()
In this case it is a NOOP since the tables do exist; working with an existing
sqlite file.
# Then method deleteData(self, **kwargs) is called, which ends up executing the
deletes
# on each table:
for table in kwargs.keys():
...
cmd, errBool = self.__buildDeleteSQL(table, kwargs[table]) # build a delete
sql statement
...
errBool, err = self.__execSQLCmd(cmd, self.cur)
Method __execSQLCmd(self, cmd, cur), verifies for complete sql statement and:
....
cur.execute(cmd) # execute sql command
....
Every DELETE executes correctly.
# Then method insertData(self, **kwargs) is called:
for table in kwargs.keys():
...
for val in kwargs[table].keys():
...
row = kwargs[table][val]
cmd, errBool = self.__buildInsertSQL(table, row) # build insert sql
statements
...
errBool, err = self.__execSQLCmd(cmd, self.cur) # same method called
above to exec sql
# The cur.execute(cmd) succeeds the insert with the first two tables,
fails on the third table, only if
# the record of the first table is not the
last record.
# Delete and insert loops finish thru every db table; whenever errBool is true,
breaks out of the loop
# with an exception:
except err_handler.DBInsertFail:
errBool = True
errMsg += "\nInsert statement structure:\n" + str(kwargs)
except:
errBool = True # something
else wrong; check args
errMsg += "\nGeneral exception at insertData; structure:\n" + str(kwargs)
....
# If error is returned attempts a rollback; else attempts a commit:
if errBool:
try: self.cur.execute('ROLLBACK;') # rollback on error
except: pass
else:
try: self.cur.execute('COMMIT;')
except: pass
The same connection object is maintained throughout; it is never closed
until the program ends. Again the same code is used for successful and
failed results as outlined before.
Thanks,
Boris
________________________________
From: Daniel Watrous <[email protected]>
To: General Discussion of SQLite Database <[email protected]>
Sent: Saturday, January 31, 2009 5:42:40 PM
Subject: Re: [sqlite] (no subject)
why don't you send us some code. It sounds like you might have an
issue managing your connections.
On Sat, Jan 31, 2009 at 3:09 PM, Boris Arloff <[email protected]> wrote:
> Hi,
>
> I am having a small problem with an sqlite3 v3.5.6 database being accessed
> from within python 2.5 (import sqlite3 as sqlite).
>
> The database has been working correctly with the exception of the following
> issue:
> There are 17 tables with the first table being a dataset index table with
> three columns (index, name, datetimestamp). All other records have various
> columns one being a field that stores the index reference to this first
> table. Therefore we can access different datasets.
>
> If we create several datasets each with an incremental index (1,2,3, ...n) in
> the first table, we can then delete the last dataset n and recreate a new one
> with same name and index number; no problem.
>
> The problem results when we delete a dataset less than n (last created).
> When any dataset 1 thru n-1 is deleted and we attempt to reenter its
> information, the following happens:
>
> 1. Delete all records from all tables where the dataset id is lets say 3
> (with n > 3)
> 2. Then insert all data related to dataset id 3.
> 3.. The data set index table entry is successful with index=3, name set to
> the original name and new datetimestamp.
> The second table to be updated gets the its first record inserted correctly,
> corresponding to this dataset.
> The third and subsequent tables however fail to accept the insert and sqlite
> declares an "sqlite3.OperationalError" with database is locked.
>
> This operation using the last set of data entered does not report this same
> error and completes correctly. For example if we had only three datasets in
> the case above, then it would have completed successfully. As a special case
> if we only have one single dataset (i.e. n=1), then we can repeat the
> operation successfully until we drop dead. We can insert all the original
> data in every table, delete the data, then recreate the same data, and
> repeat. As soon as a second dataset is created we can repeat the process
> with records that pertain to dataset 2 only, any attempts to do the same to
> dataset 1 causes the "database is locked" error.
>
> Does anyone can help with this? It seems to me that sqlite corrupts the
> index when it is deleted, hence perhaps we cannot reuse the same index number.
>
> Thanks,
> Boris
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
