Variables in Sqlite execution when being passed into a function (Python3)

Question

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

Gonzalo Odiard · Accepted Answer · 2022-03-05 22:46:39Z

1

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

edited Mar 5, 2022 at 22:46

answered Mar 5, 2022 at 22:36

Gonzalo Odiard

1,3951 gold badge13 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Will Jordan Over a year ago

Thank you for your answer, I also found an answer and was typing it when you posted yours. I will check to see if your suggestion works aswell

Will Jordan Over a year ago

I changed the class back to the way that it was in the question and tried your method and it gave the same error as before, however I appreciate your answer and help

Gonzalo Odiard Over a year ago

I modified the answer for the case that the code is a string, myy be that is your case. Anyway, this is not the proper way to manage parameters in sql because is unsafe.

Will Jordan Over a year ago

Yes, I can see how it makes sense, I was happy when I saw it because it seemed more robust and variable. When you say the proper way to manage parameters are you talking about it not being the standard method? Not sure what unsafe means (I will test again with the string)

Will Jordan Over a year ago

I'm pleased to say with the string change you made it now works as expected, thank you :)

Will Jordan · Accepted Answer · 2022-03-05 22:38:32Z

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)

I agree that this is a better solution. You can make the arg parameter on the select() method optional too.

Collectives™ on Stack Overflow

Variables in Sqlite execution when being passed into a function (Python3)

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related