Getting 2013 lost connection error when trying to connect to mysql with Python

Question

I am trying to connect to mysql database to retrieve some id for some users and use those id to retrieve another set of data from another table. It should be easy but I am getting mysql errors. Here's a snippet of what I am trying to do.

import MySQLdb
from langdetect import detect

my_db = MySQLdb.connect(
                    host="localhost", 
                    port = 3306,
                    user="user", 
                    passwd="password",
                    db="mydb",
                    charset = 'utf8'
                    )

sql1 = """SELECT id, comment FROM users WHERE usertype = 5 LIMIT 100"""

users = []
db_cursor = my_db.cursor()
db_cursor.execute(sql1)
users = db_cursor.fetchall()

sql2 = """SELECT first_name, last_name, email FROM user_contact WHERE id = %s"""

user_contact =[]
for user in users:
    comment = user[1]
    if detect(comment) == 'en': 
        id = user[0]
        db_cursor = my_db.cursor()
        db_cursor.execute(sql2, (id))
        temp = db_cursor.fetchall()
        user_contact . append (temp)

print (user_contact)

This is the error message I get when I try to run this query.

_mysql_exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query')

The first part of the query will normally go through but it usually fails when it tries to connect to mysql again for the second part. I tested with just 100 records just to check if it's an issue with the query running too long but it's still the same even with 10 records.

Answer 1

For your second part, you might not execute sql;)

Try to change

for user in users:
    comment = user[1]
    if detect(comment) == 'en': 
        id = user[0]
        db_cursor = my_db.cursor()
        temp = db_cursor.fetchall()
        user_contact . append (temp)

to

for user in users:
    comment = user[1]
    if detect(comment) == 'en': 
        id = user[0]
        db_cursor = my_db.cursor()
        db_cursor.execute(sql1, (id))
        temp = db_cursor.fetchall()
        user_contact . append (temp)