Problem with inserting records and the for loop. Only a few records are saved in the database, not a-CodePudding

After a successful scraping (only and exclusively for study and personal purposes), I try to insert the records into a database. But I have a problem:

not all records are inserted in the database table, but only one (1) for each for loop. I would like to enter the entire list of 20 clubs from this link into the table, but only 1 record is saved and not 20. In my case, 40 records should be entered (20 for Serie A and 20 for Serie B), but only 2 are entered in database. Instead the scrape in the console happens successfully and shows everything

I know what the error is and where I am wrong (needless to comment on the error), but I can't solve it. Can you show me how to solve please? I'm just starting out with Python, if you can please show me the code, otherwise I probably wouldn't understand. Thank

#SCRAPING
#SerieA
driver.minimize_window()
driver.get("https://www.diretta.it/serie-a/classifiche/")
time.sleep(20)

for SerieA in driver.find_elements(By.CSS_SELECTOR, "a[href^='/squadra'][class^='tableCellParticipant__name']"):
    SerieA_text = SerieA.text
    print(SerieA_text)
driver.close

#SerieB
driver.minimize_window()     
driver.get("https://www.diretta.it/serie-b/classifiche/")
time.sleep(15)

for SerieB in driver.find_elements(By.CSS_SELECTOR, "a[href^='/squadra'][class^='tableCellParticipant__name']"):
    SerieB_text = SerieB.text
    print(SerieB_text)
driver.close


#INSERT IN DATABASE
con = sqlite3.connect('DATABASE.db')
cursor = con.cursor()
records_added_Risultati = 0
    
Values = ((SerieA_text,), (SerieB_text,))
sqlite_insert_query = 'INSERT INTO Squadre_Campionato (Nome_Squadra) VALUES (?);'
count = cursor.executemany(sqlite_insert_query, Values)
con.commit()
    
print("Record inserted successfully ", cursor.rowcount)
records_added_Risultati = records_added_Risultati   1
cursor.close()

CodePudding user response：

The Values argument that you are passing into the executemany operator has just two elements:

Values = ((SerieA_text,), (SerieB_text,))

See the following link for an example of how to use executemany: https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-executemany.html

The second argument should be a list of all rows that need to be inserted into the table.

The code should start by initializing Values to an empty list [], and then in a for-loop insert tuples that you are extracting from: https://www.diretta.it/serie-a/classifiche/ https://www.diretta.it/serie-b/classifiche/

#SCRAPING
#SerieA
Values = []

driver.minimize_window()
driver.get("https://www.diretta.it/serie-a/classifiche/")

for SerieA in driver.find_elements(By.CSS_SELECTOR, "a[href^='/squadra'][class^='tableCellParticipant__name']"):
    SerieA_text = SerieA.text
    print(SerieA_text)
    Values.append(tuple([SerieA_text]))
driver.close

#SerieB
driver.minimize_window()     
driver.get("https://www.diretta.it/serie-b/classifiche/")
time.sleep(15)

for SerieB in driver.find_elements(By.CSS_SELECTOR, "a[href^='/squadra'][class^='tableCellParticipant__name']"):
    SerieB_text = SerieB.text
    print(SerieB_text)
    Values.append(tuple([SerieB_text]))
driver.close

for i in range(len(Values)):
    print(Values[i])

#INSERT IN DATABASE
con = sqlite3.connect('DATABASE.db')
cursor = con.cursor()
records_added_Risultati = 0

sqlite_insert_query = 'INSERT INTO Squadre_Campionato (Nome_Squadra) VALUES (?);'
count = cursor.executemany(sqlite_insert_query, Values)
con.commit()

print("Record inserted successfully ", cursor.rowcount)
records_added_Risultati = records_added_Risultati   1
cursor.close()