[pysqlite] How do I use pysqlite in a multi-threading env.?

[F. GEIGER]

In my wxPython-app a part of it gathers data, when a button is pressed, and
stores it into a db.

The GUI part should display the stuff being stored in the db.

When both parts work on the same connection, I get "SQL statements in
progress errors". Seems ok to me, you can't do that.

So, next step: Both parts get a separate connection. Now I get "Database
locked" errors. Hmm, yes, it's GUI stuff after all, being all in the same
thread.

So, next step: I put the data gathering part into a real thread, that's
started, wehn the "Scan" button is pressed. This way the "Database locked"-
errors should got away. But now I get "SQLite objects created in a thread
can only be used in that same thread.The object was created in thread id
3576 and this is thread id 1040". Hmm, true, I want to display db content,
that was stored by an other thread. But that's what multi-threading
capabilities are for!

I must be missing something here. Anyway, I'm a bit lost now, really. So,
how are you doing such stuff with pysqlite?

Kind regards
Franz GEIGER

 

[Gerhard Haering]

In my wxPython-app a part of it gathers data, when a button is pressed, and
stores it into a db.

The GUI part should display the stuff being stored in the db.

When both parts work on the same connection, I get "SQL statements in
progress errors". Seems ok to me, you can't do that.

You get that error message with which pysqlite call?

So, next step: Both parts get a separate connection. Now I get "Database
locked" errors. Hmm, yes, it's GUI stuff after all, being all in the same
thread.

One connection did not commit() or rollback() within the default
timeout (5 seconds). SQLite is not a good solution if you need
long transactions, because other SQLite connections are blocked, then.

So, next step: I put the data gathering part into a real thread, that's
started, wehn the "Scan" button is pressed. This way the "Database locked"-
errors should got away. But now I get "SQLite objects created in a thread
can only be used in that same thread.The object was created in thread id
3576 and this is thread id 1040". Hmm, true, I want to display db content,
that was stored by an other thread. But that's what multi-threading
capabilities are for!

Yes, but pysqlite's threadsafety level is 1, that means you cannot share
a connection object among multiple threads. In order to ensure that and
to avoid that you get random and unexplainable crashes, I implemented
this warning.

I must be missing something here. Anyway, I'm a bit lost now, really. So,
how are you doing such stuff with pysqlite?

Use multiple connections and commit/rollback early.

HTH,

-- Gerhard
--
Gerhard Häring - (e-mail address removed) - Python, web & database development

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFCjZBDdIO4ozGCH14RAjnnAJ9mc+xjol3Ya8AyhQKByb6EuKmrRwCfRsjQ
aH9eY/8sZk2VKt5ufoZwVX0=
=X36A
-----END PGP SIGNATURE-----

 

[F. GEIGER]

I've completely rewritten the db handling stuff, which formerly did too much
behind the scenes (explicit is better than implicit...). Now everything
looks much better. No unexpected errors and - most important - locking works
as expected. I have no idea yet, what went wrong with the former (probably
more pythonic) solution. It always did it with MySQL as backend.

Anyway, I'm quite happy now, that I can work on a db, that is incorporated
into the app itself.

Thanks for your help, Gerhard! And, sorry for the troubles...

Kind regards
Franz GEIGER