Problems with "for" inserting into a database

Issue

After scraping with Selenium (a list of 20 names for Test1 and a list of 20 names for Test2), I would like to save these in the database: in a single vertical column called "name". So I would like to save 40 different rows, all in the same name column, so 40 different names. Scraping happens correctly. He scrapes all 40 names

There is some problem in inserting into the database: only a few names are saved (for example only 5, 6, 7 names), but all are scraped regularly. How to solve? Should i use append? if so how? what should i change to my code? Obviously also print the result in the console as I did

Can you show me where I am wrong in entering the database? Thank you

    #Test 1
    driver.minimize_window()
    driver.get("link")
    for Test1 in driver.find_elements(By.CSS_SELECTOR, "a[href^='/xxxx'][class^='xxxx']"):
        Test1_text = Test1.text
        print(Test1_text)
    driver.close


    #Test 2
    driver.minimize_window()
    driver.get("link")
    for Test2 in driver.find_elements(By.CSS_SELECTOR, "a[href^='/xxxx'][class^='xxxx']"):
        Test2_text = Test2.text
        print(Test2_text)
    driver.close


# INSERT IN DATABASE
    con = sqlite3.connect('/home/blablabla/Desktop/Database.db')
    cursor = con.cursor()

    Values = ((Test1_text,), (Test2_text,))
    sqlite_insert_query = 'INSERT INTO NewTest (Name) VALUES (?);'
    count = cursor.executemany(sqlite_insert_query, Values)
    con.commit()
    print(" ")
    print("Record inserted successfully ", cursor.rowcount)
    records_added_Risultati = records_added_Risultati + 1
    cursor.close()

Solution

The issue would appear to be that Test1_text and Test2_text get updated each time through the loop, so when the loop ends they are holding only the last value found. So your SQL Insert is going to insert just those two last values (I cannot explain how you would get 5, 6, or 7 unless maybe it’s interpreting James D. Com as 3 different names). There would be 2 ways to address this. I am sketching here, not writing flawless code because I cannot easily test it.

Way 1: Write name to database immediately. So you would bring this part up before your first test:

con = sqlite3.connect('/home/blablabla/Desktop/Database.db')
cursor = con.cursor()
sqlite_insert_query = 'INSERT INTO NewTest (Name) VALUES (?);'
records_added = 0

and where you have your print(Test1_text) add in:

count = cursor.execute(sqlite_insert_query, (Test1_text,))
records_added = records_added + 1# or say + count, should be the same

(no need to executemany).

Thus:

Test1_text = Test1.text
print(Test1_text)#put first so in case of error, you know where it happened
count = cursor.execute(sqlite_insert_query, (Test1_text,))
records_added = records_added + 1

and similarly where you have your print(Test2_text)

Test2_text = Test2.text
print(Test2_text)#put first so in case of error, you know where it happened
count = cursor.execute(sqlite_insert_query, (Test2_text,))
records_added = records_added + 1

I think your commit and close (the close for sure stays put) can stay put, but you can experiment with commit after each insertion. Also, no Values= or executes happen below outside the loops. And after your con.commit, you could now say:

print("Records inserted successfully: ", records_added)

Way 2. Accumulate the values

tests1_and2_text=[]

somewhere before the first for loop. After you print test1_text do a tests1_and2_text.append((Test1_text,)) and do similarly where you print Test2_text.
Later, set your Values = tests1_and2_text and see if that works (it should; you may need to tinker with exactly what you are appending, I am using the syntax you had) when you do your executemany.

Answered By – Jeremy Kahan

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published