Getting error You have an error in your SQL syntax;
Hi all i am like 99% finshed this pain in the but script but i am now getting a error mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use near
for the life of me i cant work it out and i tihnk i am going half blind just looking at the code can you please help me out
from requests_html import HTMLSessionimport mysql.connectormydb = mysql.connector.connect( host="localhost", user="root", passwd="*****", database="flightdata")mycursor = mydb.cursor()# create an HTML Session objectsession = HTMLSession()# Use the object above to connect to needed webpageresp = session.get("https://www.adelaideairport.com.au/flight-information/flight-search/?flt_no=&carrier=All&city=&dte=Current&leg=Departures")# Run JavaScript code on webpageresp.html.render()data = []airline_spans = resp.html.find('.SearchResultFlightListRow')print (airline_spans)airline_list = [span.text.split('\n') for span in airline_spans]for flight in airline_list: if len(flight) == 7: flightno, From, to, scheduled, estimated, gate, status = flight print ("This is a " + estimated) if estimated == "": print (" currently no dely ") print ("This is a " + estimated) estimated = 'IDEL' # print (f'Flight no {flightno} from {From} to {to} is scheduled to depart at {scheduled} from gate {gate} and flight status is {status}') elif len(flight) == 6: print (flight) status = "IDEL" print (status) flightno, From, to, scheduled, estimated, gate = flight print ("This is a " + estimated) if estimated == "": print (" currently no dely ") print ("This is a " + estimated) estimated = 'IDEL' # print (f'Flight no {flightno} from {From} to {to} is scheduled to depart at {scheduled} from gate {gate} ') elif len(flight) == 5: flightno, From, to, scheduled, estimated = flight origin = str(From) flight_id = str('na') airline = str('na') destination = str (to) flightNumbers = str(flightno) scheduledTime = str(scheduled) estimatedTime = str(estimated) scheduledDate = str('na') latestTime = str(estimated) status = str(status) gate = str(gate) print (From, flight_id, flightNumbers, airline, destination, scheduledTime, scheduledDate, latestTime, status, gate) sql = "INSERT INTO flightinfo (origin, id, airline, destinations, flightNumbers, scheduledTime, estimatedTime, scheduledDate, latestTime, status, gate) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s. %s)" val = (origin, flight_id, airline, destination, flightNumbers, scheduledTime, estimatedTime, scheduledDate, latestTime, status, gate) data.append(val)# doing a batch insertmycursor.executemany(sql, data)mydb.commit()print(mycursor.rowcount, "was inserted.")
I think ,You must append the values of the variables.
val = "{},{},{},{}....".format(origin,....)
data.append(val)
adbyits
ASKER
Ok mate can you pkeaaw give me a example
Juan Carlos
After val=(..... insert this lines:
t=tuple("{}{}{}{}{}{}{}{}{}{}{}".format(*val))
And in data append t
data.append(t)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Juan Carlos
If you have same error then ensure parameters are enclosed with ', and use same Type of variable as field type . You are passing all as string
Juan Carlos
scheduledDate = str('na')
If scheduledDate is a Date type then this is your error
adbyits
ASKER
it looks like the error is comming from gate = str(gate)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server v
ersion for the right syntax to use near '. '22'),('Adelaide', 'na', 'na', 'Sydney', 'JQ761', '12:40 pm', '12:42 pm', 'na'' at line 1
Unlimited question asking, solutions, articles and more.
Juan Carlos
Could you print data[] and table struct please?
Juan Carlos
There is a "". at the end of insert sentence %s.%s) change to ,: %s,%s)
Juan Carlos
The error show: '. '22'). List of values has a dot in INSERT VALUES(...)
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
adbyits
ASKER
ok mate now get this print ("This is date" + data)
TypeError: can only concatenate str (not "list") to str
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
val = "{},{},{},{}....".format(o
data.append(val)