CODEWITHSUNDEEP
pythonmysqlbigdatadatabase
wildcolor
wildcolor

Reputation: 574

Mysql/python fetchall() can't handle result because it is too big

I have a table of 2,760,000 rows. In mysqlworkbench, it takes 36 sec to select * from original table.

I want to create another table using this existing table in python (using my_func() to convert values).

But, when I run it in the command line, it never seems to finish.

sql = "SELECT ID, Eye, Values FROM my_original_table"
curQuery.execute(sql)

for row in curQuery.fetchall():        
    dat = list(row)
    id = dat.pop(0)
    eye = dat.pop(0)
    values = dat.pop(0)     
    v = my_func(values)
    if v != None :
        sql = "INSERT INTO new_table VALUES ( '%s', '%s', %d );" % (id, eye, v)
    print(sql)
    curExe.execute(sql)

db.commit()

However, if I added LIMIT 0,10 to my first select sql (like below), it runs fine. So, this means my program is right. But does this mean without the 'Limit', the data is too much for my computer to handle? How can I solve this?

sql = "SELECT ID, Eye, Values FROM ETCEpisodeVisualAcuity LIMIT 0,10"

Upvotes: 0

Views: 7161

Answers (2)

ndpu
ndpu

Reputation: 22561

Use cursor as iterator (without calling fetchall):

sql = "SELECT ID, Eye, Values FROM my_original_table"
curQuery.execute(sql)

for row in curQuery:
    # ...

above is equivalent to process a query using while loop with fetchone:

curQuery.execute("SELECT ID, Eye, Values FROM my_original_table")
row = curQuery.fetchone()
while row is not None:
  # do something with data...
  row = curQuery.fetchone()

Upvotes: 5

Umang Agarwal
Umang Agarwal

Reputation: 33

Per the documentation:

db.store_result() returns the entire result set to the client immediately. If your result set is really large, this could be a problem. One way around this is to add a LIMIT clause to your query, to limit the number of rows returned. The other is to use use_result(), which keeps the result set in the server and sends it row-by-row when you fetch. This does, however, tie up server resources, and it ties up the connection: You cannot do any more queries until you have fetched all the rows. Generally I recommend using store_result() unless your result set is really huge and you can't use LIMIT for some reason.

db = MySQLdb.connect(yourhost,yourname,yourpw,yourdb)

db.query("SELECT ID, Eye, Values FROM my_original_table")

r=db.use_result()

>>> r.fetch_row()
(('3','2','0'),)

Upvotes: 1

Related Questions