我有從cars.com 上抓取的資料。我試圖將它們保存到 MySQL 資料庫中，但我無法做到。這是我的完整代碼：
#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()
每當我運行此代碼時，都會收到此錯誤訊息：
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)')
有人知道如何解決這個問題嗎？我想在一次執行中在 MySQL 中插入多個抓取的資料。我會很高興你的幫助
uj5u.com熱心網友回復：
首先，我不會為您的所有資料使用單獨的串列，而是使用單個串列，收集有關一輛車的所有資訊。所以就像嵌套在其中。所以而不是
millage = []
delar_name = []
我會創建一個名為汽車的串列：
cars = []
然后，我將為您在汽車上的所有不同刮擦資訊創建 dirrerent 變數，如下所示：
#brand
brand = car.find('h2').get_text()
#mileage
mileage = car.find('div', {'class':'mileage'}).get_text()
然后我會創建用于附加的串列并將其附加到串列中。
toAppend = brand, mileage, dealer_name, rating, rating_count, price
cars.append(toAppend)
那么輸出將是：
[('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')]
我對mysql做了一個小改動。插入到一個函式中，然后將該函式匯入到主腳本中作為引數。奇跡般有效。我知道這不是關于事情為什么以及如何運作的詳細答案，但它仍然是一個解決方案。
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)
接下來我會：
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()
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/388642.html
上一篇：SELECTMySQLwith02countsinthesametablewithWHEREandGROUPBYinthesameQuery