python multiprocessing error when use sqlite3

Question

I'm having trouble using multi-process in sqlite3. The code:

import multiprocessing
import sqlite3
class DealProcess(multiprocessing.Process):
    def __init__(self):
        super(DealProcess, self).__init__()
        '''
        CREATE TABLE people (id text, name text)
        INSERT INTO people (id, name) VALUES ('1', 'jack')
        '''
        self.conn = sqlite3.connect('people.sqlite')

    def __get_people(self):
        cur = self.conn.cursor()  # This not work, the following is OK,Why?
        # conn = sqlite3.connect('people.sqlite')
        # cur = conn.cursor()
        cur.execute('SELECT * FROM people')
        return cur.fetchall()

    def run(self):
        for people in self.__get_people():
            print people

if __name__ == '__main__':
    p = DealProcess()
    p.start()

When I init the self.conn in __init__, it does not work in __get_people. Error msg is:

cur.execute('SELECT * FROM people')
OperationalError: no such table: people

I do not know what is causing it. When I directly use the raw connect like the annotations, it works well. Thanks.

Answer 1

Your problem appears to be that you forgot to actually execute your CREATE TABLE... since it is simply a Multi line comment at the top of your __init__ function.

Try moving it after your self.conn is created and run cursor().execute on it.

import multiprocessing
import sqlite3
class DealProcess(multiprocessing.Process):
    def __init__(self):
        super(DealProcess, self).__init__()
        self.conn = sqlite3.connect('people.sqlite')
        self.conn.cursor().execute('''
            CREATE TABLE people (id text, name text)
            INSERT INTO people (id, name) VALUES ('1', 'jack')
        ''')

    def __get_people(self):
        cur = self.conn.cursor()  # This not work, the following is OK,Why?
        # conn = sqlite3.connect('people.sqlite')
        # cur = conn.cursor()
        cur.execute('SELECT * FROM people')
        return cur.fetchall()

    def run(self):
        for people in self.__get_people():
            print people

if __name__ == '__main__':
    p = DealProcess()
    p.start()

On a speeding train and I don't have Python on the laptop, so syntax might be off.
Or there might be a glitch in the way i use the function .execute, if so please do correct me if I don't make it home before that. But this gives you an idea of what's wrong.