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