1

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.

Cryssie
  • 3,047
  • 10
  • 54
  • 81
  • What does the error log of the server say? You might need to activate is or increase log level. – Klaus D. May 13 '16 at 03:31
  • @KlausD. I dont have the error log. I dont have admin rights for the database. I am just wondering if it's an issue with using the same connection the second time to connect to mysql. – Cryssie May 13 '16 at 03:47
  • Then you should get in contact with the admin. – Klaus D. May 13 '16 at 03:49

1 Answers1

0

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)
Blank
  • 12,308
  • 1
  • 14
  • 32
  • Thanks. Actually that was typo on my part. I was rewriting the codes in the question and missed that part out. It still doesn't work with the execute line added in. – Cryssie May 13 '16 at 03:54
  • How many records in your `user` table? And how many records in your `user_contact` table with a user `id`? – Blank May 13 '16 at 03:57
  • This is a rough estimate but maybe 30 millions. The same code still fails even when I limit the number per record retrieved to only 100 and this takes less than 1 second when query it using mysqlyog. – Cryssie May 13 '16 at 03:59