AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of adbyits
adbyits
 asked on

i am only getting one record inserted in to mysql database

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.")
DatabasesPythonMySQL Server
Avatar of undefined
Last Comment
noci
8/22/2022 - Mon
Kimputer
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.
adbyits
ASKER
@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
Kimputer
As I said, everything belonging to the loop, give it the correct spaces or tabs, whatever you use to ident
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 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,
noci
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

adbyits
ASKER
hi mate thanks for the help with that code there is nothin going in to the database
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
noci
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.
adbyits
ASKER
File "sa_new.py", line 72
    print ('Old: '.sqlold."\n")
                          ^
SyntaxError: invalid syntax
ASKER CERTIFIED SOLUTION
noci
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent