Python MySQL requests a timeout where Workbench MySQL runs

Question

Recently, my site switched to a new server. I have some basic python scripts with access data in a MySQL database. We had no problems on the old server. On the new server:

• MySQLWorkbench cannot connect without problems and execute all queries
• The same SELECT queries with python work in 5% of cases, and the remaining 95% of the time when they time out or connection are lost during the request
• For example, a table contains 100,000 rows, and selecting the whole object in MySQLWorkbench works great. He returned after 3 seconds.
• In python, the same query never works when the LIMIT 2999 query is running, but only the LIMIT 3010 forces it to be delayed.
• same effect observed when running script locally or remotely

Within a few days, we found out if there are any settings in the database, the database server, the server itself, which prevents python from working (but not MySQLWorkbench).

Request and code in case they are interesting:

query = "SELECT * FROM wordpress.table;"

conn = MySQLConnection(**mysqlconfig)
cursor = conn.cursor()
cursor.execute(query)
rows = cursor.fetchall()

I do not have data on the server, but it has enough power for MySQLWorkbench to work, just python, it seems, can not make it work.

**** EDIT ****

To find out if this problem is caused by queries returning too much data for python processing, I thought about using OFFSET and LIMIT to process a larger query in parts, using 10 lines for each query.

total_rows = 100000
interval = 10
data = []

for n in range(0, total_rows / interval):
    q = "SELECT * FROM wordpress.table LIMIT %s OFFSET %s" % (interval, n * interval)
    cursor.execute(q)
    returned = cursor.fetchall()
    rows = [[i for i in j] for j in list(returned)]

    for row in rows:
        data.append(row)

    print n, len(data)

: : 3000 , , . , script 10 , n .

Answer 1

:

import MySQLdb.cursor

con = MySQLdb.connect(host=host,
                  user=user,
                  passwd=pwd,
                  charset=charset,
                  port=port,
                  cursorclass=MySQLdb.cursors.SSDictCursor);
cur = con.cursor()
cur.execute("SELECT * FROM wordpress.table")
for row in cur:
    print row

:

MySQL ResultSet python

SQLDB SScursor?