Link to home
Start Free TrialLog in
Avatar of adbyits
adbyits

asked on

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

Avatar of Juan Carlos
Juan Carlos
Flag of Peru image

I think ,You must append the values of the variables.
val = "{},{},{},{}....".format(origin,....)
data.append(val)
Avatar of adbyits
adbyits

ASKER

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)
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
Avatar of 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
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(...)
Avatar of adbyits

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Juan Carlos
Juan Carlos
Flag of Peru image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of adbyits

ASKER

we hav a dinner :) thanks amte