How can I get my threading script to run and access sql database

Jack Jones 0
Viewed 29

I have a python script that accesses a mysql database to get the most recent request added to the database and place that request in a queue. The way I want the script to run is that the function check_requests runs in an infinite while loop and constantly checks the mysql database for new requests that have been added, and then places those requests in a queue. The other part of the script then takes each request in the queue and processes them using some functions. I want both of these processes to run at the same time until there are no longer any requests in the queue after which both the script, and the thread that i've created for checking database requests stop. Right now nothing seems to happen and I get an error message. How can I fix it?

q = Queue()

def check_requests():
    global q
    global requests_queue

    while True:

        mydb = mysql.connector.connect(user='****',
                                    password='****',
                                    host='****',
                                    database='****',
                                    port ='****')

        mycursor = mydb.cursor()
        mycursor.execute("""
                SELECT request 
                FROM table
                ORDER BY id DESC LIMIT 1;
                """)

        myresult = mycursor.fetchall()

        for data in myresult:
            request = data[0]
            q.put(request)

if __name__ == "__main__":

    thread1 = Thread(target=check_requests)

    thread1.start()

    while not q.empty():
        time.sleep(3)
        item = q.get()
        print(item)

Exception in thread Thread-1 (check_requests):
Traceback (most recent call last):
  File "C:\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 263, in _open_connection
    self._cmysql.connect(**cnx_kwargs)
_mysql_connector.MySQLInterfaceError: Can't connect to MySQL server on '127.0.0.1:3306' (10048)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Python310\lib\threading.py", line 1009, in _bootstrap_inner
    self.run()
  File "C:\Python310\lib\threading.py", line 946, in run
    self._target(*self._args, **self._kwargs)
  File "c:\Users\Random\Desktop\container_content\checkdb.py", line 18, in check_requests
    mydb = mysql.connector.connect(user='root',
  File "C:\Python310\lib\site-packages\mysql\connector\pooling.py", line 286, in connect
    return CMySQLConnection(*args, **kwargs)
  File "C:\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 101, in __init__
    self.connect(**kwargs)
  File "C:\Python310\lib\site-packages\mysql\connector\abstracts.py", line 1095, in connect
    self._open_connection()
  File "C:\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 268, in _open_connection
    raise get_mysql_exception(
mysql.connector.errors.DatabaseError: 2003 (HY000): Can't connect to MySQL server on '127.0.0.1:3306' (10048)
multithreading mysql database python
1 Answers

You are connecting, mysql db with every request. Use a single connection and use that connection object everywhere. Please refer sample code.

def connect_db(con=None):
if con is not None:
    if not con._closed:
        return con
try:
    con = pymysql.connect(host=***,
                     user=***,
                     password=***,
                     db=***,
                     cursorclass=pymysql.cursors.DictCursor
    )
    return con
except:
    con = pymysql.connect(host=***,
                     user=***,
                     password=***,
                     db=***,
                     cursorclass=pymysql.cursors.DictCursor
    )
    return con

def func():
   global con
   con = connect_db(con)
   cur = con.cursor()
   query = """SELECT * FROM table WHERE condition"""
try:
    cur.execute(query)
    result = cur.fetchone()
    cur.close()
    return result
Sagar Lakhani0
Related