i am only getting one record inserted in to mysql database

adbyits
adbyits used Ask the Experts™
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()


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:
        flightno, From, to, scheduled, estimated, gate = flight
        status = 'IDEL'
        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
        gate = 'IDEL'
        status = 'IDEL'
        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 ')


sql = "INSERT INTO flightinfo (origin, airline, destinations, flightNumbers, scheduledTime, estimatedTime, status) VALUES (str(From), str(to), str(flightno), str(scheduled), str(estimated), str(status), str(gate)"

val = (str(From), str(to), str(flightno), str(scheduled), str(estimated), str(status), str(gate))
#data.append(val)
print (val)


# doing a batch insert
#mycursor.executemany(sql, val)
mycursor.executemany(sql,())
mydb.commit()

print(mycursor.rowcount, "was inserted.")
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The code passed the SQL insert once. You probably wanted it in the for loop, so ident it, so it becomes part of the loop.

Author

Commented:
@Kimputer you might be right mate but i so raw with this i have no idea how to do it can you help at all
As I said, everything belonging to the loop, give it the correct spaces or tabs, whatever you use to ident
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
ok so i changed the formatting and put it here https://codeshare.io/5zWKLb so you can see it and its still the same issue mate,
nociSoftware Engineer
Distinguished Expert 2018
Why not insert it in a code block here:
from requests_html import HTMLSession
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="****@0401",
  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()


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:
        flightno, From, to, scheduled, estimated, gate = flight
        status = 'IDEL'
        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
        gate = 'IDEL'
        status = 'IDEL'
        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 ')

    sql = "INSERT INTO flightinfo (origin, airline, destinations, flightNumbers, scheduledTime, estimatedTime, status) VALUES (str(From), str(to), str(flightno), str(scheduled), str(estimated), str(status), str(gate)"

    val = (str(From), str(to), str(flightno), str(scheduled), str(estimated), str(status), str(gate))
#data.append(val)
    print (val)


# doing a batch insert
#mycursor.executemany(sql, val)
    mycursor.executemany(sql,())
    mydb.commit()

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

Open in new window

Author

Commented:
hi mate thanks for the help with that code there is nothin going in to the database
nociSoftware Engineer
Distinguished Expert 2018
The sql statement doesn't look valid..

sql = "INSERT INTO flightinfo (origin, airline, destinations, flightNumbers, scheduledTime, estimatedTime, status) VALUES (str(From), str(to), str(flightno), str(scheduled), str(estimated), str(status), str(gate)"

Open in new window


i doubt you want to use str(From) as a value... , it needs a closing ")" ... Maybe you meant:

sql = 'INSERT INTO flightinfo (origin, airline, destinations, flightNumbers, scheduledTime, estimatedTime, status) VALUES ("{}","{}","{}","{}","{}","{}","{}")'.format(str(From), str(to), str(flightno), str(scheduled), str(estimated), str(status), str(gate))

Open in new window


With actual values from strings in the SQL statment.
Replace:
   print (f'Flight no {flightno} from  {From} to {to} is scheduled to depart at {scheduled} from gate ')

    sql = "INSERT INTO flightinfo (origin, airline, destinations, flightNumbers, scheduledTime, estimatedTime, status) VALUES (str(From), str(to), str(flightno), str(scheduled), str(estimated), str(status), str(gate)"

    val = (str(From), str(to), str(flightno), str(scheduled), str(estimated), str(status), str(gate))

Open in new window

with:
   print (f'Flight no {flightno} from  {From} to {to} is scheduled to depart at {scheduled} from gate ')

    sql_old = "INSERT INTO flightinfo (origin, airline, destinations, flightNumbers, scheduledTime, estimatedTime, status) VALUES (str(From), str(to), str(flightno), str(scheduled), str(estimated), str(status), str(gate)"
    sql = 'INSERT INTO flightinfo (origin, airline, destinations, flightNumbers, scheduledTime, estimatedTime, status) VALUES ("{}","{}","{}","{}","{}","{}","{}")'.format(str(From), str(to), str(flightno), str(scheduled), str(estimated), str(status), str(gate))

   print ('Old: '.sqlold."\n")
   print('New: '.sql."\n")
    val = (str(From), str(to), str(flightno), str(scheduled), str(estimated), str(status), str(gate))

Open in new window


And observe the difference.

Author

Commented:
File "sa_new.py", line 72
    print ('Old: '.sqlold."\n")
                          ^
SyntaxError: invalid syntax
Software Engineer
Distinguished Expert 2018
Oops php -ism in python code...  replace . (dot) with + (plus)
  print ('Old: '+sqlold+"\n")
   print('New: '+sql+"\n")
 

Open in new window

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