For a while I have been looking at how to read/write to a sqlite database from different threads, I found lots of answers and documents describing what needed to happen however I was nowhere near able to achieve what was needed so I decided to use an existing class I found made by someone else.

Ashamed to admit but figuring out how to get this class to work has taken a few hours despite me now not knowing why however I am unable to get variables inside of the execution function

I would normally do it like this:

c.execute("SELECT codeID FROM users WHERE codeID=:code", {'code':tag_attempt})

This worked fine however when I try the same thing with the multithread class it will not work (The class has a 'select' function which fetches and returns the data, which was the only way I have been able to fetch data from the database, however the execute function has the exact same issue) (I also tried this method of using variables)

for q in sql.select(("select codeID from users where codeID=?", (tag_attempt)), 0):
    print(q)
# TypeError: 'NoneType' object is not iterable

This is the class used but I will also included the link

class MultiThreadOK(Thread):
    def __init__(self, db):
        super(MultiThreadOK, self).__init__()
        self.db = db
        self.reqs = Queue()
        self.start()

    def run(self):
        cnx = sqlite3.Connection(self.db)
        cursor = cnx.cursor()
        while True:
            req = self.reqs.get()
            if req == '--close--':
                break
            elif req == '--commit--':
                cnx.commit()
            try:
                cursor.executescript(
                    req) if ';' in req else cursor.execute(req)
            except sqlite3.OperationalError as err:
                self.escribir_error(
                    '{0} - Error {1}\n'.format(datetime.now(), err))
                self.escribir_error('{0} - {1}\n'.format(datetime.now(), req))
            except:
                self.escribir_error('{0} - Salida'.format(datetime.now()))
        cnx.close()

    def execute(self, req):
        self.reqs.put(req)

    def queries(self):
        return self.reqs.qsize()

    def empty(self):
        return self.reqs.empty()

    def select(self, req, results=0):
        cnx = sqlite3.Connection(self.db)
        cursor = cnx.cursor()
        try:
            if results == 0:
                cursor.execute(req)
                ret = [x for x in cursor.fetchall()]
                cnx.close()
                return ret
            else:
                cursor.execute(req)
                ret = [x for x in cursor.fetchall()[:results]]
                cnx.close()
                return ret
        except:
            print("Unexpected error: {0}".format(sys.exc_info()[0]))
            cnx.close()

    def commit(self):
        self.execute("--commit--")

    def close(self):
        self.execute('--close--')

    def escribir_error(self, texto):
        #with open(os.path.dirname(os.path.abspath(__file__))   '\\errores.txt', 'a') as archivo:
        #    archivo.write(texto)
        print(texto)

Summary I would like to be able to fetch data while in a separate thread which is possible with this class I have just not been able to include variables at any stage

Credit https://gist.github.com/User001501/3053f26100ddf281600668fed347e518

CodePudding user response：

The method execute receive a single parameter.

Looks like you can use the string named placeholders like

c.execute("SELECT codeID FROM users WHERE codeID='{code}'".format(**{'code': ag_attempt})

CodePudding user response：

I found a solution and would like to post it just in case it benefits anyone in the future

I passed a new argument which was just the dictionary into the functions in the class, I will comment on the changed lines. This new code works with the lines I was trying to use in the question so the input to the function is the same and works like regular sqlite.

class MultiThreadOK(Thread):
    def __init__(self, db):
        super(MultiThreadOK, self).__init__()
        self.db = db
        self.reqs = Queue()
        self.start()

    def run(self):
        cnx = sqlite3.Connection(self.db)
        cursor = cnx.cursor()
        while True:
            req, arg, res = self.reqs.get() # Add arg and res here
            if req == '--close--':
                break
            elif req == '--commit--':
                cnx.commit()
            try:
                cursor.executescript(
                    req, arg) if ';' in req else cursor.execute(req, arg) # Add arg in two places
            except sqlite3.OperationalError as err:
                self.escribir_error(
                    '{0} - Error {1}\n'.format(datetime.now(), err))
                self.escribir_error('{0} - {1}\n'.format(datetime.now(), req))
            except:
                self.escribir_error('{0} - Salida'.format(datetime.now()))
        cnx.close()

    def execute(self, req, arg=None, res=None):
        self.reqs.put((req, arg or tuple(), res)) # Add this

    def queries(self):
        return self.reqs.qsize()

    def empty(self):
        return self.reqs.empty()

    def select(self, req, arg, results=0): # Add arg here
        cnx = sqlite3.Connection(self.db)
        cursor = cnx.cursor()
        try:
            if results == 0:
                cursor.execute(req, arg) # Add arg here
                ret = [x for x in cursor.fetchall()]
                cnx.close()
                return ret
            else:
                cursor.execute(req, arg) # Add arg here
                ret = [x for x in cursor.fetchall()[:results]]
                cnx.close()
                return ret
        except:
            print("Unexpected error: {0}".format(sys.exc_info()[0]))
            cnx.close()

    def commit(self):
        self.execute("--commit--")

    def close(self):
        self.execute('--close--')

    def escribir_error(self, texto):
        #with open(os.path.dirname(os.path.abspath(__file__))   '\\errores.txt', 'a') as archivo:
        #    archivo.write(texto)
        print(texto)