Understanding Python sqlite mechanics in multi-module environments

Question

First off, I have no idea if "Ownership" is the correct term for this, it's just what I am calling it in Java.

I am currently building a Server that uses SQLite, and I am encountering errors concerning object "ownership":

I have one Module that manages the SQLite Database. Let's call it "pyDB". Simplified:

import threading
import sqlite3

class DB(object):
    def __init__(self):
        self.lockDB = threading.Lock()
        self.conn = sqlite3.connect('./data.sqlite')
        self.c = self.conn.cursor()
        [...]

    def doSomething(self,Param):
        with self.lockDB:
            self.c.execute("SELECT * FROM xyz WHERE ID = ?", Param)

(Note that the lockDB object is there because the Database-Class can be called by multiple concurrent threads, and although SQLite itself is thread-safe, the cursor-Object is not, as far as I know).

Then I have a worker thread that processes stuff.

import pyDB
self.DB = pyDB.DB()

class Thread(threading.Thread):
    [omitting some stuff that is not relevant here]
    def doSomethingElse(self, Param):
        DB.doSomething(Param)

If I am executing this, I am getting the following exception:

self.process(task)
File "[removed]/ProcessingThread.py", line 67, in process
  DB.doSomething(Param)
File "[removed]/pyDB.py", line 101, in doSomething
  self.c.execute(self,"SELECT * FROM xyz WHERE ID = ?", Param)
ProgrammingError: SQLite objects created in a thread can only be used in that same
  thread.The object was created in thread id 1073867776 and this is thread id 1106953360

Now, as far as I can see, this is the same problem I had earlier (Where Object ownership was given not to the initialized class, but to the one that called it. Or so I understand it), and this has led me to finally accept that I generally don't understand how object ownership in Python works. I have seached the Python Documentation for an understandable explanation, but have not found any.

So, my Questions are:

• Who owns the cursor object in this case? The Processing Thread or the DB thread?
• Where can I read up on this stuff to finally "get" it?
• Is the term "Object ownership" even correct, or is there an other term for this in Python? (Edit: For explanations concerning this, read the comments of the main question)

I will be glad to take specific advice for this case, but am generally more interested in the whole concept of "what belongs to who" in Python, because to me it seems pretty different to the way Java handles it, and since I am planning to use Python a lot in the future, I might as well just learn it now, as this is a pretty important part of Python.

Answer 1

ProgrammingError: SQLite objects created in a thread can only be used in that same

The problem is that you're trying to conserve the cursor for some reason. You should not be doing this. Create a new cursor for every transaction; or if you're not totally sure where transactions start or end, a new cursor per query.

import sqlite3

class DB(object):
    def __init__(self):
        self.conn_uri = './data.sqlite'
        [...]

    def doSomething(self,Param):
        conn = sqlite.connect(self.conn_uri)
        c = conn.cursor()
        c.execute("SELECT * FROM xyz WHERE ID = ?", Param)

Edit, Re comments in your question: What's going on here has very little to do with python. When you create a sqlite resource, which is a C library and totally independent of python, sqlite requires that resource be used only in the thread that created it. It verifies this by looking at the thread ID of the currently running thread, and not at all attempting to coordinate the transfer of the resource from one thread to another. As such, you are obligated to create sqlite resources in each thread that needs them.

In your code, you create all of the sqlite resources in the DB object's __init__ method, which is probably called only once, and in the main thread. Thus these resources are only permitted to be used in that thread, threading.Lock not withstanding.

Your questions:

• Who owns the cursor object in this case? The Processing Thread or the DB thread?

The thread that created it. Since it looks like you're calling DB() at the module level, it's very likely that it's the main thread.

• Where can I read up on this stuff to finally "get" it?

There's not really much of anything to get. Nothing is happening at all behind the scenes, except what SQLite has to say on the matter, when you are using it.

• Is the term "Object ownership" even correct, or is there an other term for this in Python?

Python doesn't really have much of anything at all to do with threading, except that it allows you to use threads. It's on you to coordinate multi-threaded applications properly.

EDIT again:

Objects do not live inside particular threads. When you call a method on an object, that method runs in the calling thread. ten threads can call the same method on the same object; all will run concurrently (or whatever passes for that re the GIL), and it's up to the caller or the method body to make sure nothing breaks.

Answer 2

I'm the author of an alternate SQLite wrapper for Python (APSW) and very familiar with this issue. SQLite itself used to require that objects - the database connection and cursors could only be used in the same thread. Around SQLite 3.5 this was changed and you could use objects concurrently although internally SQLite did its own locking so you didn't actually get concurrent performance. The default Python SQLite wrapper (aka pysqlite) supports even old versions of SQLite 3 so it continues to enforce this restriction even though it is no longer necessary for SQLite itself. However the pysqlite code would need to be modified to allow concurrency as the way it wraps SQLite is not safe - eg handling error messages is not safe because of SQLite API design flaws and requires special handling.

Note that cursors are very cheap. Do not try to reuse them or treat them as precious. The actual underlying SQLite objects (sqlite3_stmt) are kept in a cache and reused as needed.

If you do want maximum concurrency then open multiple connections and use them simultaneously.

The APSW doc has more about multi-threading and re-entrancy. Note that it has extra code to allow the actual concurrent usage that pysqlite does not have, but the other tips and info apply to any usage of SQLite.