Mysql error message: Lost connection to MySQL server during query

[tomtom]

I want to connect to a database in a remote server (namecheap Cpanel)
Os: wisdows 11

Here's my code:

def connect_to_db(): # function connect to database
    return mysql.connector.connect(host='server.web-hosting.com',
                                   user="me_tomtom",
                                   passwd="*****",
                                   database= "db_name",
                                   port = 2083 ) 

def mydb_data(table_name, loop, user_id, index):  # function returns a specified index data in db
    mydp = connect_to_db()
    my_cursor = mydp.cursor(buffered=True)
    my_cursor.execute(f"SELECT * FROM {table_name}")
    for data in my_cursor:
        print(data)
        if data[loop] == user_id:
            return data[index]

then I call mydb_data function

print(mydb_data('demo_bot_users',1,'123r24gte3221', 3))

Error I'm having:

Error:
  File "C:\Users\elect\AppData\Local\Programs\Python\Python38\lib\site-packages\mysql\connector\network.py", line 264, in recv_plain
    raise errors.InterfaceError(errno=2013)
mysql.connector.errors.InterfaceError: 2013: Lost connection to MySQL server during query
PS C:\Project\exe> 

Please what is the cause of this error and how can i fix it? Support would be highly appreciated👍

[ibreeden]

You are not the first one to encounter this error. MySql has this information: B.3.2.7 MySQL server has gone away. There are a lot of possible reasons, but the timout of 8 hours exceeded is unlikely. The other possibility is a big query. I believe they mean: a big query result. So they say you have to enlarge the "max_allowed_packet" on the server. But if this is the cause I have a better solution.

Like many Pythonists you do a full table scan and then use Python to select the data you need. But it is better to have the database do its work of selecting data. That is what a database is made for.
So I would do it this way:

def mydb_data(table_name, loop, user_id, index):  
    """ Function returns a specified index data in db.
    table_name: the name of the table
    loop: the name of the column containing the query data
    user_id: the data to be found in column "loop"
    index: the column name containing the data to be returned.
    """
    mydp = connect_to_db()
    my_cursor = mydp.cursor(buffered=True)
    my_cursor.execute(f"SELECT {index} FROM {table_name} where {loop} = %s", (user_id,))
    for data in my_cursor:
        print(data)
        result = data  # Question: is only one result possible? Or should this be a list?
    my_cursor.close()
    my_dp.close()
    return result

Note that I changed the column indexes to column names.

[tomtom]

You are not the first one to encounter this error. MySql has this information: B.3.2.7 MySQL server has gone away. There are a lot of possible reasons, but the timout of 8 hours exceeded is unlikely. The other possibility is a big query. I believe they mean: a big query result. So they say you have to enlarge the "max_allowed_packet" on the server. But if this is the cause I have a better solution.

Like many Pythonists you do a full table scan and then use Python to select the data you need. But it is better to have the database do its work of selecting data. That is what a database is made for.
So I would do it this way:

def mydb_data(table_name, loop, user_id, index):  
    """ Function returns a specified index data in db.
    table_name: the name of the table
    loop: the name of the column containing the query data
    user_id: the data to be found in column "loop"
    index: the column name containing the data to be returned.
    """
    mydp = connect_to_db()
    my_cursor = mydp.cursor(buffered=True)
    my_cursor.execute(f"SELECT {index} FROM {table_name} where {loop} = %s", (user_id,))
    for data in my_cursor:
        print(data)
        result = data  # Question: is only one result possible? Or should this be a list?
    my_cursor.close()
    my_dp.close()
    return result

Note that I changed the column indexes to column names.

Question: is only one result possible? Or should this be a list?
Anwser: it is one result from the row of that column

milewhile let me tweak your code and see

[tomtom]

You are not the first one to encounter this error. MySql has this information: B.3.2.7 MySQL server has gone away. There are a lot of possible reasons, but the timout of 8 hours exceeded is unlikely. The other possibility is a big query. I believe they mean: a big query result. So they say you have to enlarge the "max_allowed_packet" on the server. But if this is the cause I have a better solution.

Like many Pythonists you do a full table scan and then use Python to select the data you need. But it is better to have the database do its work of selecting data. That is what a database is made for.
So I would do it this way:

def mydb_data(table_name, loop, user_id, index):  
    """ Function returns a specified index data in db.
    table_name: the name of the table
    loop: the name of the column containing the query data
    user_id: the data to be found in column "loop"
    index: the column name containing the data to be returned.
    """
    mydp = connect_to_db()
    my_cursor = mydp.cursor(buffered=True)
    my_cursor.execute(f"SELECT {index} FROM {table_name} where {loop} = %s", (user_id,))
    for data in my_cursor:
        print(data)
        result = data  # Question: is only one result possible? Or should this be a list?
    my_cursor.close()
    my_dp.close()
    return result

Note that I changed the column indexes to column names.

Question: is only one result possible? Or should this be a list?
Anwser: it is one result from the row of that column

milewhile let me tweak your code and see

I used your code but still have the same error

[tomtom]

The fact is that the request does not even connect to db

def connect_to_db(): # function connect to database
    return mysql.connector.connect(host='server.web-hosting.com',
                                   user="me_tomtom",
                                   passwd="*****",
                                   database= "db_name",
                                   port = 2083 ) 

this code does not even connect to thedatabase so i can make a query
when i tried this

print(connect_to_db())

it still show the same error

[tomtom]

I now find out the the host does not allow connecting to db using a remote computer on their shared hosting

[ibreeden]

Thank you for letting us know. It is a lesson for all of us. In your first post you did not show the full error stack. The complete error stack always shows in which line of the code the error occurred. It would have shown the error occurred in the connect(), while all of us thought it occurred in the cursor.execute(). So a message to everyone: always show the complete error stack.