Running a python with MySQL insert command getting an error on parameters

Question

I am trying to do an insert from Python to MySQL and I am getting

mysql.connector.errors.ProgrammingError: Not enough parameters for the SQL statement;

I have had a bit of a look online and I know its got something to do with tuble but I can't work out what to change, I have looked around and at my code, there are 10 items in the responce_data, 10 in the SQL and I have 10 %s so I don't know where I am going wrong

 import urllib.parse
import requests
import mysql.connector


mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="**",
  database="flightdata"
)

mycursor = mydb.cursor()


main_api = 'https://www.sydneyairport.com.au/_a/flights/?query=&flightType=departure&terminalType=domestic&date=2019-11-10&sortColumn=scheduled_time&ascending=true&showAll=true'

address = 'lhr'
url = main_api + urllib.parse.urlencode({address: address})

response_data = requests.get(url).json()
for element in response_data['flightData']:
    flight_id = element['id']
    airline = element['airline']
    destination = element['destinations']
    flightNumbers = element['flightNumbers']
    scheduledTime = element['scheduledTime']
    estimatedTime = element['estimatedTime']
    scheduledDate = element['scheduledDate']
    latestTime = element['latestTime']
    status = element['status']
    statusColor = element['statusColor']

    sql = "INSERT INTO flightinfo (id, airline, destinations, flightNumbers, scheduledTime, estimatedTime, scheduledDate, latestTime, status, statusColor) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

    #sql = "INSERT INTO flightinfo (flight_id, airline, destination, flightNumbers, scheduledTime, estimatedTime, estimatedTime, scheduledDate, latestTime, status, statusColor ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s ,%s))"
    val = [(flight_id, airline, " ".join(destination), ", ".join(flightNumbers), scheduledTime, estimatedTime,
            scheduledDate, latestTime, status, statusColor)]

mycursor.executemany(sql, val)

mydb.commit()

print(mycursor.rowcount, "was inserted.")


print(airline, destination, flightNumbers, scheduledTime, "Estimated Time:" + " " + estimatedTime, "Scheduled Date:" + " " + scheduledDate, "Latest Time:" + " " + latestTime, "Status:" + " " +status, "Status Color:" + " " + statusColor)

You sql is wrong it has `11 table columns` instead of `10` because of duplicate entry of `estimatedTime` — dennohpeter, Nov 16 '19 at 06:43

dennohpeter · Accepted Answer · 2019-11-16T07:40:53.127

1

Remove the trailing , in val and change val to be

val = [(flight_id, airline, destination, flightNumbers, scheduledTime, estimatedTime, scheduledDate, latestTime, status, statusColor)]

And don't forget to fix your sql to

sql = "INSERT INTO flightinfo (flight_id, airline, destination, flightNumbers, scheduledTime, estimatedTime, scheduledDate, latestTime, status, statusColor) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

UPDATE: And to avoid getting

TypeError: Python 'list' cannot be converted to a MySQL type

You can convert the relevant fields to string for instance in your case you can do.

val = [(flight_id, airline, " ".join(destination), ", ".join(flightNumbers), scheduledTime, estimatedTime, scheduledDate, latestTime, status, statusColor)]

edited Nov 16 '19 at 07:40

answered Nov 16 '19 at 07:10

dennohpeter

391
4
16

with new code i am getting File "Sydneyimport.py", line 40, in mycursor.executemany(sql, response_data) File "C:\Users\User\AppData\Local\Programs\Python\Python38\lib\site-packages\mysql\connector\cursor.py", line 668, in executemany stmt = self._batch_insert(operation, seq_params) File "C:\Users\User\AppData\Local\Programs\Python\Python38\lib\site-packages\mysql\connector\cursor.py", line 613, in _batch_insert raise errors.ProgrammingError( mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement new code in question – Adam Wolarczuk Nov 16 '19 at 07:38
check the update and don't forget to mark as best if solved your problem. – dennohpeter Nov 16 '19 at 07:41
its kida worked but only inserted one row of data when there is around 500 for it to entered, changes in code https://codeshare.io/alWVLd so you can edit if you want – Adam Wolarczuk Nov 16 '19 at 07:59
Thank you mate that worked thank so much , one last question , i need to rerun the code every 5 mins, is there a way to run the query to update all the data that is there and not delete it, i will do another one to delete all data after midnight – Adam Wolarczuk Nov 16 '19 at 11:00
also mate i ran the code for today and i only fgot 3 records back and i should be seeing 300+ has that got something to do with address = '1hr' – Adam Wolarczuk Nov 16 '19 at 11:20
For re-runing the code every five minutes check out **apscheduler**, and about updating or create check this https://stackoverflow.com/questions/696190/create-if-an-entry-doesnt-exist-otherwise-update – dennohpeter Nov 17 '19 at 07:24
about the last question I can't say coz I really don't know – dennohpeter Nov 17 '19 at 07:25

Running a python with MySQL insert command getting an error on parameters

1 Answers1