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.

Related: Database does not update automatically with MySQL and Python — unutbu
– unutbu, Commented Jul 30, 2015 at 1:43

falsetru · Accepted Answer · 2015-07-30 01:46:07Z

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])

edited Jul 30, 2015 at 1:46

answered Jul 30, 2015 at 1:40

falsetru

371k69 gold badges769 silver badges659 bronze badges

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

4 Comments

akhilcjacob Over a year ago

the data in the weatherData variable is a list, so would I need to wrap everything with quotes?

falsetru Over a year ago

@user2672738, I don't understand what you mean. Could you explain by showing the code?

akhilcjacob Over a year ago

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 Over a year ago

@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.

acutesoftware · Accepted Answer · 2015-07-30 02:20:23Z

0

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

edited Jul 30, 2015 at 2:20

answered Jul 30, 2015 at 1:42

acutesoftware

1,0913 gold badges15 silver badges33 bronze badges

1 Comment

falsetru Over a year ago

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

Collectives™ on Stack Overflow

Inserting data into mysql using python

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related