How to pass a sqlite Connection Object through multiprocessing

Question

I'm testing out how multiprocessing works and would like an explanation why I'm getting this exception and if it is even possible to pass the sqlite3 Connection Object this way:

import sqlite3
from multiprocessing import Queue, Process

def sql_query_worker(conn, query_queue):
    # Creating the Connection Object here works...
    #conn = sqlite3.connect('test.db')
    while True: 
        query = query_queue.get()
        if query == 'DO_WORK_QUIT':
            break
        c = conn.cursor()
        print('executing query: ', query)
        c.execute(query)
        conn.commit()

if __name__ == "__main__":
    connection = sqlite3.connect('test.db')
    commands = ( '''CREATE TABLE IF NOT EXISTS test(value text)''',
        '''INSERT INTO test VALUES('test value')''',
        '''INSERT INTO test VALUES('test value')''',
        '''INSERT INTO test VALUES('test value')''',
        '''INSERT INTO test VALUES('test value')''',
        '''INSERT INTO test VALUES('test value')''',
        '''INSERT INTO test VALUES('test value')''',
        '''INSERT INTO test VALUES('test value')''',
        '''INSERT INTO test VALUES('test value')''',
        '''INSERT INTO test VALUES('test value')''',
        '''INSERT INTO test VALUES('test value')''',
        '''INSERT INTO test VALUES('test value')''',
        '''INSERT INTO test VALUES('test value')''',
        '''INSERT INTO test VALUES('test value')''',
        '''INSERT INTO test VALUES('test value')''',
        '''DO_WORK_QUIT''',
        )
    cmd_queue = Queue()
    sql_process = Process(target=sql_query_worker, args=(connection, cmd_queue))
    sql_process.start()
    for x in commands:
        cmd_queue.put(x)
    sql_process.join()
    print('Done.')

I'm getting this exception thrown:

Process Process-1:
Traceback (most recent call last):
  File "C:\Python33\lib\multiprocessing\process.py", line 258, in _bootstrap
    self.run()
  File "C:\Python33\lib\multiprocessing\process.py", line 95, in run
    self._target(*self._args, **self._kwargs)
  File "testmp.py", line 11, in sql_query_worker
    c = conn.cursor()
sqlite3.ProgrammingError: Base Connection.__init__ not called.
Done.

Sorry if this has been asked before but google couldn't seem to find a decent answer to the exception above.

It looks like multiprocessing Process's pass data back and forth by pickling the objects. So it looks like its not possible to pass a sqlite3 Connection object over. Looks like I'll just have to create the connection inside the Process instead. — David, Aug 22 '13 at 23:19
Even if it were possible, it's a bad idea. Just open a new connection in the new process. — mata, Aug 22 '13 at 23:58

score 1 · Answer 1 · answered May 27 '16 at 12:11

You can try this on UNIX only:

import sqlite3
import multiprocessing as mp
from multiprocessing import Process, Queue


def get_a_stock(queue, cursor, symbol):
    cursor.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)
    queue.put(cursor.fetchall())


if __name__ == '__main__':
    """
    multiprocessing supports three ways to start a process, one of them is fork:
    The parent process uses os.fork() to fork the Python interpreter.
    The child process, when it begins, is effectively identical to the parent process.
    All resources of the parent are inherited by the child process.
    Note that safely forking a multithreaded process is problematic.
    Available on Unix only. The default on Unix.
    """
    mp.set_start_method('fork')

    conn = sqlite3.connect(":memory:")
    c = conn.cursor()

    # Create table
    c.execute('''CREATE TABLE stocks
                 (date text, trans text, symbol text, qty real, price real)''')

    # Insert a row of data
    c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','USD',100,35.14)")

    # Save (commit) the changes
    conn.commit()

    q = mp.Queue()
    p = mp.Process(target=get_a_stock, args=(q, c, 'USD', ))
    p.start()
    result = q.get()
    p.join()

    for r in result:
        print(r)

    c.close()
    # We can also close the connection if we are done with it.
    # Just be sure any changes have been committed or they will be lost.
    conn.close()

How to pass a sqlite Connection Object through multiprocessing

1 Answers1

Linked