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.

Answer 1

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()