Put retrieved data from MySQL query into DataFrame pandas by a for loop

Question

I have one database with two tables, both have a column called barcode, the aim is to retrieve barcode from one table and search for the entries in the other where extra information of that certain barcode is stored. I would like to have bothe retrieved data to be saved in a DataFrame. The problem is when I want to insert the retrieved data into DataFrame from the second query, it stores only the last entry:

 import mysql.connector
 import pandas as pd
 cnx = mysql.connector(user,password,host,database)
 query_barcode = ("SELECT barcode FROM barcode_store")
 cursor = cnx.cursor()
 cursor.execute(query_barcode)
 data_barcode = cursor.fetchall()

Up to this point everything works smoothly, and here is the part with problem:

 query_info = ("SELECT product_code FROM product_info WHERE barcode=%s")
 for each_barcode in data_barcode:
      cursor.execute(query_info % each_barcode)
      pro_info = pd.DataFrame(cursor.fetchall())

pro_info contains only the last matching barcode information! While I want to retrieve all the information for each data_barcode match.

Nhor · Accepted Answer · 2015-11-05 06:38:30Z

1

That's because you are consistently overriding existing pro_info with new data in each loop iteration. You should rather do something like:

query_info = ("SELECT product_code FROM product_info")
cursor.execute(query_info)
pro_info = pd.DataFrame(cursor.fetchall())

Making so many SELECTs is redundant since you can get all records in one SELECT and instantly insert them to your DataFrame.

@edit: However if you need to use the WHERE statement to fetch only specific products, you need to store records in a list until you insert them to DataFrame. So your code will eventually look like:

pro_list = []
query_info = ("SELECT product_code FROM product_info WHERE barcode=%s")
for each_barcode in data_barcode:
     cursor.execute(query_info % each_barcode)
     pro_list.append(cursor.fetchone())
pro_info = pd.DataFrame(pro_list)

Cheers!

edited Nov 5, 2015 at 6:38

answered Nov 4, 2015 at 13:43

Nhor

3,9806 gold badges31 silver badges41 bronze badges

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

3 Comments

techhighlands Over a year ago

I understand your point, but then the WHERE condition is gone, in this way I will receive all the product info and not only the ones which the barcodes match from the other table. Is my explanation clear?

techhighlands Over a year ago

Nhor thank you for the advice, at first it did not work, it was still writing only the last entry, then I thought I should change it to the following and now it works very well: pro_list.append(cursor.fetchone())

Nhor Over a year ago

ah yeah sorry, i didn't notice this one. ofc you did right thing to correct it and i'll correct my answer as well

Collectives™ on Stack Overflow

Put retrieved data from MySQL query into DataFrame pandas by a for loop

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related