+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: Homework (https://python-forum.io/forum-9.html)
+--- Thread: Using Pandas to save csv file into mysql database with for loop (/thread-15846.html)
Using Pandas to save csv file into mysql database with for loop - kirito85 - Feb-03-2019
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>
RE: Using Pandas to save csv file into mysql database with for loop - scidam - Feb-03-2019
Did you try .to_sql method of a DataFrame class?
RE: Using Pandas to save csv file into mysql database with for loop - kirito85 - Feb-03-2019
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.
RE: Using Pandas to save csv file into mysql database with for loop - scidam - Feb-04-2019
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.
RE: Using Pandas to save csv file into mysql database with for loop - kirito85 - Feb-05-2019
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.