Python write data to DB in one row-CodePudding

I am adding the following data, which I am scraping from a website, to my database.

carnames = driver.find_elements(By.XPATH,'//span[@data-bind="text: vehicle.name"]')
seats = driver.find_elements(By.XPATH,'//li[@data-title="seats"]')
doors = driver.find_elements(By.XPATH,'//li[@data-title="doors"]')
transmission = driver.find_elements(By.XPATH,'//span[@data-bind="text: drive.value"]')
car_type = driver.find_elements(By.XPATH,'//span[@name="vehicleCategory"]')
prices = driver.find_elements(By.XPATH,'//p[@data-bind="text:priceController.price.onlineTotalDisplayPrice"]')
images = driver.find_elements(By.XPATH,'//img[@data-bind="event: {error: $root.placeholder}, attr: {src: vehicle.imageMedium, alt: vehicle.tooltip, title: vehicle.tooltip}"]')

for car, cartype, image, price, trans, seat, door, in zip(carnames, car_type, images, prices, transmission, seats, doors):
    cursor.execute(
        "insert into cardata (carname, carmodel, imageurl, fullprice, location, seating, features, transmission, Fro, T, companyid) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        (car.text, cartype.text, image.get_attribute('src'), price.text, location, seat.text, door.text, trans.text, pickup_d, return_d, Rental_ID)
    )
connection.commit()

This gives me 3 search results, but I also click on each of these 3 results and collect some extra data. But for this I have to click on the webpage and it brings me to another page where I scrape some extra data.

choose_car = driver.find_element(By.XPATH,'//a[@id="Choose car"]').click()
insurance = driver.find_elements(By.XPATH,'//div[@]')
additional_driver = driver.find_elements(By.XPATH,'//li[@id="extra_price"]')

for extra1, extra2 in zip(insurance, additional_driver):
    cursor.execute(
        "insert into cardata (insurance, additional) values (?, ?)",
        (extra1.text, extra2.text)
    )
connection.commit()

How can I get all the above data in one row ? Because now I have 5 rows in the database, the extra data is obviously coming in separate rows

CodePudding user response：

I don't think it is possible to fetch data which are in different web-pages, I think you should ideally load web-pages and query individually from them!

CodePudding user response：

Each time you do a insert it will make a new row. What you can do is create a variable for the data to insert, append the new information to that variable, and then insert once you're done.

Note: here I'm assuming that these "extra" values should be paired with all of the cars you get in your first section. If not, you'll need to figure out how to pair the correct 'extra' information with each of those cars.

carnames = driver.find_elements(By.XPATH,'//span[@data-bind="text: vehicle.name"]')
seats = driver.find_elements(By.XPATH,'//li[@data-title="seats"]')
doors = driver.find_elements(By.XPATH,'//li[@data-title="doors"]')
transmission = driver.find_elements(By.XPATH,'//span[@data-bind="text: drive.value"]')
car_type = driver.find_elements(By.XPATH,'//span[@name="vehicleCategory"]')
prices = driver.find_elements(By.XPATH,'//p[@data-bind="text:priceController.price.onlineTotalDisplayPrice"]')
images = driver.find_elements(By.XPATH,'//img[@data-bind="event: {error: $root.placeholder}, attr: {src: vehicle.imageMedium, alt: vehicle.tooltip, title: vehicle.tooltip}"]')

car_data = []
for car, cartype, image, price, trans, seat, door, in zip(carnames, car_type, images, prices, transmission, seats, doors):
    car_data.append((carname, carmodel, imageurl, fullprice, location, seating, features, transmission, Fro, T, companyid))

choose_car = driver.find_element(By.XPATH,'//a[@id="Choose car"]').click()
insurance = driver.find_elements(By.XPATH,'//div[@]')
additional_driver = driver.find_elements(By.XPATH,'//li[@id="extra_price"]')

for extra1, extra2, car_data_item in zip(insurance, additional_driver, car_data):
    car, cartype, image, price, trans, seat, door = car_data_item
    cursor.execute(
        "insert into cardata (carname, carmodel, imageurl, fullprice, location, seating, features, transmission, Fro, T, companyid, insurance, additional) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        (extra1.text, extra2.text, car.text, cartype.text, image.get_attribute('src'), price.text, location, seat.text, door.text, trans.text, pickup_d, return_d, Rental_ID)
    )
connection.commit()