Inserting data into mysql using python

Question

I'm trying to execute the following code

for counter in range(0, len(weatherData) - 1):
 string = 'INSERT INTO weather VALUES(' + str((weatherData[counter])[0:]) +');'
 print(string)
 cur.execute(string)

All the the values and data are printed correctly and everything seems to work as there is no errors, however when I check the data base its empty.

• unutbu: Related: Database does not update automatically with MySQL and Python

Answer 1

Do commit after insertion complete.

for counter in range(0, len(weatherData) - 1):
 ....
connection_object.commit()

BTW, you'd better to use parameter-passing style instead of build query string yourself:

for data in weatherData:
 sql = 'INSERT INTO weather VALUES(%s)'
 cur.execute(sql, [data])

• akhilcjacob: the data in the weatherData variable is a list, so would I need to wrap everything with quotes?
• falsetru: @user2672738, I don't understand what you mean. Could you explain by showing the code?
• akhilcjacob: I have the code on github i cleaned up the code a bit but the concept hasn't changed :github.com/akhilcjacob/weather_station/blob/master/dataToSQL.py
• falsetru: @user2672738, Ah, I got it. You don't need to quote the string. If you pass the string with parameter passing style, db api will care of quoting.

Answer 2

The loop can be simplified as follows

for counter, row in weatherData:
 string = 'INSERT INTO weather VALUES(' + str(row) + ');'

and you'll need to commit afterwards

EDIT - added counter, which is a count of the for loop EDIT2 - using row EDIT3 - removed [0:] from end of string which doesn't do anything

• falsetru: row[0:] == row, no need to use [0:] :)