python MySQL Connector

Question

I'm playing with big data in Python and MySQL.

I've a got a huge table, I need to insert new rows while I'm fetching query's results.

I've got this error:

Traceback (most recent call last):
  File "recsys.py", line 53, in <module>
    write_cursor.executemany(add_sim, data_sims)
  File "/Library/Python/2.7/site-packages/mysql/connector/cursor.py", line 603, in executemany
    self._connection.handle_unread_result()
  File "/Library/Python/2.7/site-packages/mysql/connector/connection.py", line 1057, in handle_unread_result
    raise errors.InternalError("Unread result found")
mysql.connector.errors.InternalError: Unread result found

The code is the following one:

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import logging
import mysql.connector

import numpy
import scipy
from scipy.spatial import distance

logging.basicConfig(filename='recsys.log', level=logging.INFO)

cnx = mysql.connector.connect(user='...', password='...', database='...')

cursor = cnx.cursor(dictionary=True)
write_cursor = cnx.cursor()


query = ("...")

cursor.execute(query)

while True:
    rows = cursor.fetchmany(100)
    if not rows:
        break

    add_sim = ("...")
    data_sims = []

    for row in rows:
        f1 = row['f1']
        f2 = row['f2']
        v1 = [[row['a1'], row['b1'], row['c1']]]
        v2 = [[row['a2'], row['b2'], row['c2']]]

        c = 1 - scipy.spatial.distance.cdist(v1, v2, 'cosine')

        if c > 0.8:

            data_sim = (f1, f2, c)
            data_sims.append(data_sim)

    write_cursor.executemany(add_sim, data_sims)
    cnx.commit()

cursor.close()

cnx.close()

I know I could use a buffered connection to mysql, but it's not a good choice in my case because of how really big my table is!

Eugene Yarmash · Accepted Answer · 2016-04-01 15:12:17Z

4

This is a documented behaviour of cursor.fetchmany() :

You must fetch all rows for the current query before executing new statements using the same connection.

To overcome this issue, you can establish a new connection to be used by write_cursor:

cnx_write = mysql.connector.connect(...)
write_cursor = cnx_write.cursor()

answered Apr 1, 2016 at 15:12

Eugene Yarmash

152k44 gold badges346 silver badges391 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wolf Head · Accepted Answer · 2017-01-22 09:30:06Z

0

I had a similar problem when I was threading, and just had to close and open a new connection in each thread separately... instead of leaving the connection open.

answered Jan 22, 2017 at 9:30

Wolf Head

452 silver badges5 bronze badges

Comments

Chris Noh · Accepted Answer · 2018-10-23 03:57:10Z

0

Limit 100 in your query.

query = ("..."),  //limit 100 here

Then use fetchall() instead of fetchmany(100)

answered Oct 23, 2018 at 3:57

Chris Noh

479 bronze badges

Comments

jellycsc · Accepted Answer · 2023-04-04 20:07:47Z

0

In my case, I accidentally closed the mysql connection before the query.

answered Apr 4, 2023 at 20:07

jellycsc

12.5k2 gold badges20 silver badges41 bronze badges

Collectives™ on Stack Overflow

python MySQL Connector

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related