Getting error You have an error in your SQL syntax;

adbyits
adbyits used Ask the Experts™
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 HTMLSession
import mysql.connector



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




mycursor = mydb.cursor()

# create an HTML Session object
session = HTMLSession()

# Use the object above to connect to needed webpage
resp = session.get("https://www.adelaideairport.com.au/flight-information/flight-search/?flt_no=&carrier=All&city=&dte=Current&leg=Departures")

# Run JavaScript code on webpage
resp.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 insert
mycursor.executemany(sql, data)

mydb.commit()

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

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I think ,You must append the values of the variables.
val = "{},{},{},{}....".format(origin,....)
data.append(val)

Author

Commented:
Ok mate can you pkeaaw give me a example
After val=(..... insert this lines:

t=tuple("{}{}{}{}{}{}{}{}{}{}{}".format(*val))

And in data append t

data.append(t)
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

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
scheduledDate = str('na')

If  scheduledDate  is a Date type then this is your error

Author

Commented:
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
Could you print data[] and table struct please?
There is a "". at  the end of insert sentence %s.%s)  change to ,: %s,%s)
The error show: '. '22'). List of values has a dot in INSERT  VALUES(...)

Author

Commented:
ok mate now get this     print ("This is date" + data)
TypeError: can only concatenate str (not "list") to str

here is a link to the code
https://codeshare.io/GAVX8x
I remove one line and modify print
 Please try.

Author

Commented:
we hav a dinner :) thanks amte

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial