Forums

I'm using mysql.connector with Django. The credentials are working fine, since I'm able to connect occasionally. I suspect that it might be due to the number of connections at that moment. I do not wish to make a new connection and cursor everytime i call a function. Kindly advise. Thanks

2022-03-02 13:03:44,067: OK: /api/borrowed_shares/amc/ Traceback (most recent call last): File "/home/stocksera/.virtualenvs/mysite-virtualenv/lib/python3.8/site-packages/django/core/handlers/exception.py", line 47, in inner response = get_response(request) File "/home/stocksera/.virtualenvs/mysite-virtualenv/lib/python3.8/site-packages/django/core/handlers/base.py", line 181, in _get_response response = wrapped_callback(request, callback_args, callback_kwargs) File "./api/views.py", line 547, in borrowed_shares df = pd.read_sql_query(f"SELECT * FROM shares_available WHERE ticker='{ticker_selected.upper()}' " File "/home/stocksera/.virtualenvs/mysite-virtualenv/lib/python3.8/site-packages/pandas/io/sql.py", line 436, in read_sql_query return pandas_sql.read_query( File "/home/stocksera/.virtualenvs/mysite-virtualenv/lib/python3.8/site-packages/pandas/io/sql.py", line 2116, in read_query cursor = self.execute(args) File "/home/stocksera/.virtualenvs/mysite-virtualenv/lib/python3.8/site-packages/pandas/io/sql.py", line 2054, in execute cur = self.con.cursor() File "/home/stocksera/.virtualenvs/mysite-virtualenv/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 578, in cursor raise errors.OperationalError("MySQL Connection not available.") mysql.connector.errors.OperationalError: MySQL Connection not available.

That's probably because the connection has timed out. We have a help page on managing MySQL connections here: https://help.pythonanywhere.com/pages/ManagingDatabaseConnections/

Hi this is what I did... To prevent timeout, I checked if the existing connection is valid. If it has expired, I will update the global variables. However, mysql.connector.errors.OperationalError: MySQL Connection not available error is still raised occasionally. I need to refresh the same page a few times before the page loads. You may take a look at my code if you wish. This function is found in Stocksera/helpers.py

engine = create_engine(URL, encoding="utf-8") 
cnx = mysql.connector.connect(user="user",  password="password",  host="host", database="db") 
cur = cnx.cursor()

def connect_mysql_database():
     global engine
     global cnx
     global cur
     print("connect_mysql_database BEING CALLED...")
     if cnx.is_connected():
         print("SQL SERVER IS CONNECTED >>>")
     else:
         print("ERROR CONNECTING TO MYSQL... TRYING TO RECONNECT")
         engine = create_engine(URL, encoding="utf-8")
     cnx = mysql.connector.connect(user="user",  password="password",  host="host", database="db") 
     cur = cnx.cursor()
     return cnx, engine

Have you checked where that exception is raised from to make sure that you're actually using the new code there?