1

I am executing a query against Mysql from a python script. I am using the anaconda distribution of python with the Oracle Mysql module. If I limit the query to ~500 rows it executes successfully. Anything above that results in an "Unread result found" exception. I can't understand what I am doing wrong. Any help is appreciated. The code is as follows:

import mysql.connector
import csv

cnx = mysql.connector.connect(user='user', password='password',
                          host='server',
                          port='3306',
                          database='db',
                          connect_timeout=2000,
                          buffered=True)

try:

    query = "...large query that returns > 500 records..."

    cursor = cnx.cursor()
    cursor.execute(query)

    print 'this will print'
    results = cursor.fetchall()
    print 'this will not print'

    with open("data.csv", "wb") as csv_file:

        csv_writer = csv.writer(csv_file)
        csv_writer.writerows(results)

finally:
    cursor.close()
    cnx.close()
David Morgan
  • 115
  • 2
  • 6
  • This is the equivalent in java: Class.forName("com.mysql.jdbc.Driver") ; Connection conn = DriverManager.getConnection("jdbc:mysql://server:3306/db", "user", "password") ; Statement stmt = conn.createStatement() ; String query = "...large query that returns > 500 records..." ; ResultSet rs = stmt.executeQuery(query); convertToCsv(rs); // loop over results and write to csv – David Morgan Oct 24 '14 at 07:54

1 Answers1

0

It seems that you run out of memory while buffering the rows on memory, your process is certainly killed.Try to use streaming result set instead of that( MySQLdb.cursors.SSCursor).

cnx = MySQLdb.connector.connect(user='user', password='password',
                          host='server',
                          port='3306',
                          database='db',
                          connect_timeout=2000,
                          buffered=True)

try:

    query = "...large query that returns > 500 records..."
    cursor = MySQLdb.SSCursor(conn)
    #cursor = cnx.cursor()
    cursor.execute(query)
 ..
user3378649
  • 5,154
  • 14
  • 52
  • 76