Python Forum
Using Pandas to save csv file into mysql database with for loop
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Using Pandas to save csv file into mysql database with for loop
#1
Hi All,

My python code is not working in the 'for' loop.

I am trying to save all the data from csv file to mysql database and want to save every row starting from the first row to the last row.

Thanks.

<python>
try:
for index, col in hdbrpi.iterrows():
quarter = col[0]
index = col[1]
data = {
'quarter': quarter,
'index' : index
}
query = ("INSERT INTO table_hdbrpi(quarter, index)"
"VALUES(%(quarter)s, %(index)s")
cursor.execute(query, data)
print("Adding row " + str(index))
cnx.commit()

print("All data inserted!")

except:
print("Unexpected error:", sys.exc_info()[0])
exit()
finally:
cursor.close()
cnx.close()
</python>
Reply
#2
Did you try .to_sql method of a DataFrame class?
Reply
#3
Hi scidam,

I have not learn how to do use .to_sql method.

I was only taught the basics.

So basically i have to load the csv file first.

Then save the data to a mysql database, so now im stuck the loop on how to save the data for every row in the csv.

And lastly i will have to call the data from the database to model them into graphs.
Reply
#4
From the code provided I can conclude that hdbrpi is a data frame;
So, you have successfully loaded data from the csv-file (e.g. using .read_csv method).
Now, you need to export the data into mysql db. This could be done using .to_sql method. Hitting the database in a loop will be not efficient alternative.
Reply
#5
Hi scidam,

Thanks for the reply, you were right haha, my lecturer told me not to use the loop but the to.sql method. Funny thing is cus all these template codes were given out during the lecture but in the real world I have to use different codes.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
Bug [for a h/w project] How to save and get back dictionary in a .json file in TinyDB. adithya_like_py 4 3,494 Feb-05-2021, 10:49 AM
Last Post: buran
  Parse text from a .txt file and save multiple output .txt rattlerskin 10 10,897 Aug-25-2019, 08:50 PM
Last Post: dwirsig

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020