How to save multiple scraped data to mysql using python-CodePudding

I have a data scraped from cars.com. And am trying to save them to MySQL database and I couldn't managed to do so. Here is my full code:

#ScrapeData.py

import requests
from bs4 import BeautifulSoup

URL = "https://www.cars.com/shopping/results/?dealer_id=&keyword=&list_price_max=&list_price_min=&makes[]=&maximum_distance=all&mileage_max=&page=1&page_size=100&sort=best_match_desc&stock_type=cpo&year_max=&year_min=&zip="
r = requests.get(URL)
soup = BeautifulSoup(r.content, 'html.parser')
cars = soup.find_all('div', class_='vehicle-card')

name = []
mileage = []
dealer_name = []
rating = []
rating_count = []
price = []


for car in cars:
    #name
    name.append(car.find('h2').get_text())
    #mileage
    mileage.append(car.find('div', {'class':'mileage'}).get_text())
    #dealer_name
    dealer_name.append(car.find('div', {'class':'dealer-name'}).get_text())
    #rate
    try:
        rating.append(car.find('span', {'class':'sds-rating__count'}).get_text())
    except:
        rating.append("n/a")
    #rate_count
    rating_count.append(car.find('span', {'class':'sds-rating__link'}).get_text())
    #price
    price.append(car.find('span', {'class':'primary-price'}).get_text())

#save_to_mysql.py

import pymysql
import scrapeData
import mysql.connector

connection = pymysql.connect(
    host='localhost',
    user='root',
    password='',
    db='cars',
)

name = scrapeData.name
mileage = scrapeData.mileage
dealer_name = scrapeData.dealer_name
rating = scrapeData.rating
rating_count = scrapeData.rating_count
price = scrapeData.price

try:
    mySql_insert_query = """INSERT INTO cars_details (name, mileage, dealer_name, rating, rating_count, price) 
                           VALUES (%s, %s, %s, %s, %s, %s) """

    records_to_insert = [(name, mileage, dealer_name, rating, rating_count, price)]

    print(records_to_insert)

    cursor = connection.cursor()
    cursor.executemany(mySql_insert_query, records_to_insert)
    connection.commit()
    print(cursor.rowcount, "Record inserted successfully into cars_details table")

except mysql.connector.Error as error:
    print("Failed to insert record into MySQL table {}".format(error))


    connection.commit()
finally:
    connection.close()

whenever I run this code I get this error message:

Traceback (most recent call last):
  File "c:\scraping\save_to_mysql.py", line 28, in <module>
    cursor.executemany(mySql_insert_query, records_to_insert)
  File "C:\Users\PC\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\cursors.py", line 173, in executemany     
    return self._do_execute_many(
  File "C:\Users\PC\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\cursors.py", line 211, in _do_execute_many    rows  = self.execute(sql   postfix)
  File "C:\Users\PC\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\cursors.py", line 148, in execute
    result = self._query(query)
  File "C:\Users\PC\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\cursors.py", line 310, in _query
    conn.query(q)
  File "C:\Users\PC\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\connections.py", line 548, in query       
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "C:\Users\PC\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\connections.py", line 775, in _read_query_result
    result.read()
  File "C:\Users\PC\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\connections.py", line 1156, in read       
    first_packet = self.connection._read_packet()
  File "C:\Users\PC\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\connections.py", line 725, in _read_packet    packet.raise_for_error()
  File "C:\Users\PC\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\protocol.py", line 221, in raise_for_error    err.raise_mysql_exception(self._data)
  File "C:\Users\PC\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1241, 'Operand should contain 1 column(s)')

Anybody have any idea of how to solve this? I want to insert multiple scraped data in MySQL at one execution. I will be glad for your help

CodePudding user response：

Firstly i wouldnt use seperated lists for all your data, but use a single list, with all the information about a single car gathered. So like nested within it. so instead of

millage = []
delar_name = []

i would create a single list called cars:

cars = []

Then i would create dirrerent variables for all the different pieces of scrape info you have on a car like this:

#brand
brand = car.find('h2').get_text()
#mileage
mileage = car.find('div', {'class':'mileage'}).get_text()

Then i would create the list for appending and append it to the list.

toAppend = brand, mileage, dealer_name, rating, rating_count, price
cars.append(toAppend)

Then the output would be:

[('2018 Mercedes-Benz CLA 250 Base', '21,326 mi.', '\nMercedes-Benz of South Bay\n', '4.6', '(1,020 reviews)', '$33,591'), ('2021 Toyota Highlander Hybrid XLE', '9,529 mi.', '\nToyota of Gastonia\n', '4.6', '(590 reviews)', '$47,869')]

I have made a small change to the mysql. Inserted into a function, then importing that function into the main script at just thrownin the list as a paramter. Works like a charm. I know this isnt an elaborate answer on why and how things are working, but it is a solution non the less.

import requests
from bs4 import BeautifulSoup
from scrapertestsql import insertScrapedCars

URL = "https://www.cars.com/shopping/results/?dealer_id=&keyword=&list_price_max=&list_price_min=&makes[]=&maximum_distance=all&mileage_max=&page=1&page_size=100&sort=best_match_desc&stock_type=cpo&year_max=&year_min=&zip="
r = requests.get(URL)
soup = BeautifulSoup(r.content, 'html.parser')
scrapedCars = soup.find_all('div', class_='vehicle-card')

cars = []
# mileage = []
# dealer_name = []
# rating = []
# rating_count = []
# price = []



for car in scrapedCars:
    #name
    brand = car.find('h2').get_text()
    #mileage
    mileage = car.find('div', {'class':'mileage'}).get_text()
    #dealer_name
    dealer_name = car.find('div', {'class':'dealer-name'}).get_text()
    #rate
    try:
        rating = car.find('span', {'class':'sds-rating__count'}).get_text()
    except:
        rating = "n/a"
    #rate_count
    rating_count = car.find('span', {'class':'sds-rating__link'}).get_text()
    #price
    price = car.find('span', {'class':'primary-price'}).get_text()
    toAppend = brand, mileage, dealer_name, rating, rating_count, price
    cars.append(toAppend)

insertScrapedCars(cars)
    
print(cars)

Next i would :

import pymysql
import mysql.connector

connection = pymysql.connect(
    host='127.0.0.1',
    user='test',
    password='123',
    db='cars',
    port=8889
)


def insertScrapedCars(CarsToInsert):
    try:
        mySql_insert_query = """INSERT INTO cars_details (name, mileage, dealer_name, rating, rating_count, price) 
                            VALUES (%s, %s, %s, %s, %s, %s) """

        cursor = connection.cursor()
        cursor.executemany(mySql_insert_query, CarsToInsert)
        connection.commit()
        print(cursor.rowcount, "Record inserted successfully into cars_details table")

    except mysql.connector.Error as error:
        print("Failed to insert record into MySQL table {}".format(error))

    finally:
        connection.close()