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

ProgrammingMySQL ServerSQL

Avatar of undefined
Last Comment
adbyits

8/22/2022 - Mon
Juan Carlos

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
Get an unlimited membership to EE for less than $4 a week.
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

here is a link to the code
https://codeshare.io/GAVX8x
ASKER CERTIFIED SOLUTION
Juan Carlos

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
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.
ask a question
adbyits

ASKER
we hav a dinner :) thanks amte