Selenium List into MySQL Database Table

Question

I've written some code to scrape two pieces of data from a website:

from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
import pandas as pd
from numpy import nan
import mysql.connector

cnx = mysql.connector.connect(user='root', password='*mypassword*',
                          host='127.0.0.1',
                          database='racing')
#cnx.close()
cursor = cnx.cursor()

driver = webdriver.Chrome("/anaconda3/chromedriver")
driver.get("https://www.racingpost.com/results/2018-03-20")

timeout=10

expand = driver.find_element_by_xpath('/html/body/div[3]/div[1]/main/div/div/div/div/a[2]').click()

#all race results
 races_element = driver.find_elements_by_class_name('rp-timeView__raceName')
 races = []
 for x in races_element:
     races.append(x.text)

time_element = driver.find_elements_by_class_name('rp-timeView__time')
times = []
for x in time_element:
        times.append(x.text)

for race, time in zip(races,times):
    print(race + ': ' + time)

cursor.executemany("INSERT INTO racecard (course) VALUES (%s)", races)

driver.quit()

The code is successfully printing all the results on the page as 'coursename: time' but I'm unsure how I take that output and enter it into a single table on multiple rows with 1 column for 'coursename' and the second for 'time'.

The cursor line I entered was an attempt to enter the races list only but it hasn't worked.

Can anyone advise whether I should try output the 'races' and 'times' lists to the database or via the printed zip pair and in each situation, what code to use?

Thanks

Mahesh Kumaran · Accepted Answer · 2018-03-23 18:53:27Z

1

Assuming that your rest of the code works well and all you need is just to update the obtained values into the database , the first thing you need to do is to create a table before the for loop ,

cursor.execute("CREATE TABLE <table-name>(coursename varchar(255),time varchar(255));")

Once you've got the table ready , you can insert the values into the table, just by adding an INSERT statement inside the for loop where you are printing the values now

for race, time in zip(races,times):
    print(race + ': ' + time)   
    cursor.execute("INSERT INTO <table-name> VALUES(%s,%s);,(race,time)")

answered Mar 23, 2018 at 18:53

Mahesh Kumaran

9062 gold badges13 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

M. Sprout Over a year ago

Thanks for your help. The table created succesfully but the rest of the code returns: ProgrammingError: 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 '%s,%s);,(race,time)' at line 1

M. Sprout Over a year ago

Solved it with this: for race, time in zip(races,times): print(race + time) cursor.execute("""INSERT INTO racecard (coursename,time) VALUES (%s,%s)""", (race,time)) cnx.commit()

Collectives™ on Stack Overflow

Selenium List into MySQL Database Table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related