Bottom Page

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>
Quote
#2
Did you try .to_sql method of a DataFrame class?
Quote
#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.
Quote
#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.
Quote
#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.
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Help with while loop creating an infinite loop. FWendeburg 3 197 Jan-30-2019, 08:28 PM
Last Post: FWendeburg
  Help : Inserting records into MySQL workbench Aresden 1 213 Jan-22-2019, 08:50 PM
Last Post: woooee
  Problem with quotes on MySQL request PierreSoulier 7 890 Jun-19-2018, 11:11 PM
Last Post: woooee
  Find a specific word on a webpage and save the url moses 0 542 May-16-2018, 08:29 PM
Last Post: moses
  Parse text from a .txt file and save multiple output .txt rattlerskin 9 2,926 Mar-17-2017, 01:45 PM
Last Post: Larz60+

Forum Jump:


Users browsing this thread: 1 Guest(s)