How to use INSERT each row of a list to mySQL from Python?

Question

I have a list of data and need to insert each row back to MySQL. Below is an example of my data.

print(data[1:5])

[['Abilene, TX', 'Ahwatukee Foothills, AZ', 922.41258034], 
['Abilene, TX', 'Akron, OH', 1494.34762588], 
['Abilene, TX', 'Alafaya, FL', 1381.55219389], 
['Abilene, TX', 'Alameda, CA', 1680.9984747]]

My SQL columns are City, Destination, distance and so far I have:

for i in range(len(data)):
mycursor.execute("INSERT INTO table Shark (City, Destination, Distance) VALUES (:data)",[i])

which gives me an error "Not all parameters were used in the SQL Statment". How would I fix this?

arcee123 · Accepted Answer · 2020-04-21 04:58:27Z

1

try this:

data = [['Abilene, TX', 'Ahwatukee Foothills, AZ', 922.41258034], 
['Abilene, TX', 'Akron, OH', 1494.34762588], 
['Abilene, TX', 'Alafaya, FL', 1381.55219389], 
['Abilene, TX', 'Alameda, CA', 1680.9984747]]

for r in data:
    mycursor.execute("INSERT INTO table Shark (City, Destination, Distance) VALUES ({},{},{})".format(r[0], r[1], r[2])

answered Apr 21, 2020 at 4:58

arcee123

30514 gold badges61 silver badges139 bronze badges

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

3 Comments

Alex Over a year ago

It's still giving me issues. I think it's because there are commas between the city and the state.

Alex Over a year ago

so I changed it to r=0 for r in data: print('INSERT INTO mileage (SourceCity, Destination, Distance) VALUES ({''''},{''''},{});'.format(data[r][0], data[r][1], data[r][2])) to pick up r

Alex Over a year ago

and it's giving me a list indices must be integers or slices, not list error

Collectives™ on Stack Overflow

How to use INSERT each row of a list to mySQL from Python?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related