Блог

Я использую программу очистки веб-страниц для сбора данных из truecar.com в моей базе данных есть 3 столбца, и когда я запускаю программу, я получаю сообщение об ошибке следующего содержания: список indext вне диапазона вот что я сделал до сих пор:

 import mysql.connector
from bs4 import BeautifulSoup
import requests
import re

# take the car's name
requested_car_name = input()

# inject the car's name into the URL

my_request = requests.get('https://www.truecar.com/used-cars-for-sale/listings/'  
                          requested_car_name   '/location-holtsville-ny/?sort[]=best_match')


my_soup = BeautifulSoup(my_request.text, 'html.parser')

# ************ car_model column in database ******************
car_model = my_soup.find_all(
    'span', attrs={'class': 'vehicle-header-make-model text-truncate'})

# we have a list of car models
car_list = []
for item in range(20):
    # appends car_model to car_list
    car_list.append(car_model[item].text)

car_string = ', '.join('?' * len(car_list))


# ************** price column in database *****************************
price = my_soup.find_all(
    'div', attrs={'data-test': 'vehicleCardPricingBlockPrice'})
price_list = []
for item in range(20):
    # appends price to price_list
    price_list.append(price[item].text)

price_string = ', '.join('?' * len(price_list))


# ************** distance column in database ***************************
distance = my_soup.find_all('div', attrs={'data-test': 'vehicleMileage'})
distance_list = []
for item in range(20):
    # appends distance to distance_list
    distance_list.append(distance[item].text)

distance_string = ', '.join('?' * len(distance_list))

# check the connection
print('CONNECTING ...')

mydb = mysql.connector.connect(
    host="xxxxx",
    user="xxxxxx",
    password="xxxxxx",
    port='xxxxxx',
    database='xxxxxx'
)

print('CONNECTED')

# checking the connection is done

my_cursor = mydb.cursor(buffered=True)
insert_command = 'INSERT INTO car_name (car_model, price, distance) VALUES (%s, %s, %s);' % (car_string, price_string, distance_string)
# values = (car_string, price_string, distance_string)
my_cursor.execute(insert_command, car_list, price_list, distance_list)
mydb.commit()

print(my_cursor.rowcount, "Record Inserted")

mydb.close()
 

и у меня есть еще одна проблема, заключающаяся в том, что я не могу вставить список в свои столбцы, и я перепробовал много способов, но, к сожалению, я не смог заставить его работать

Я думаю, что проблема в этой строке:

 IndexError                                Traceback (most recent call last)
<ipython-input-1-4a3930bf0f57> in <module>
     23 for item in range(20):
     24     # appends car_model to car_list
---> 25     car_list.append(car_model[item].text)
     26 
     27 car_string = ', '.join('?' * len(car_list))

IndexError: list index out of range
 

Я не хочу, чтобы он вставлял весь список в 1 строку в базе данных. Я хочу, чтобы цена, модель, пробег первых 20 автомобилей в truecar.com в моей базе данных

Да, вы жестко кодируете длину. Измените способ перебора элементов супа. Итак:

 import mysql.connector
from bs4 import BeautifulSoup
import requests


# take the car's name
requested_car_name = input('Enter car name: ')

# inject the car's name into the URL

my_request = requests.get('https://www.truecar.com/used-cars-for-sale/listings/'  
                          requested_car_name   '/location-holtsville-ny/?sort[]=best_match')


my_soup = BeautifulSoup(my_request.text, 'html.parser')

# ************ car_model column in database ******************
car_model = my_soup.find_all(
    'span', attrs={'class': 'vehicle-header-make-model text-truncate'})

# we have a list of car models
car_list = []
for item in car_model:
    # appends car_model to car_list
    car_list.append(item.text)




# ************** price column in database *****************************
price = my_soup.find_all(
    'div', attrs={'data-test': 'vehicleCardPricingBlockPrice'})
price_list = []
for item in price:
    # appends price to price_list
    price_list.append(item.text)




# ************** distance column in database ***************************
distance = my_soup.find_all('div', attrs={'data-test': 'vehicleMileage'})
distance_list = []
for item in distance:
    # appends distance to distance_list
    distance_list.append(item.text)




# check the connection
print('CONNECTING ...')

mydb = mysql.connector.connect(
    host="xxxxx",
    user="xxxxxx",
    password="xxxxxx",
    port='xxxxxx',
    database='xxxxxx'
)

print('CONNECTED')

# checking the connection is done

my_cursor = mydb.cursor(buffered=True)
insert_command = 'INSERT INTO car_name (car_model, price, distance) VALUES (%s, %s, %s)'
values = list(zip(car_list, price_list, distance_list))
my_cursor.executemany(insert_command, values)
mydb.commit()

print(my_cursor.rowcount, "Record Inserted")

mydb.close()
 

АЛЬТЕРНАТИВНЫЙ:

существует также API, где вы можете извлекать данные:

 import mysql.connector
import requests
import math


# take the car's name
requested_car_name = input('Enter car name: ')

# inject the car's name into the URL

url = 'https://www.truecar.com/abp/api/vehicles/used/listings'
payload = {
'city': 'holtsville',
'collapse': 'true',
'fallback': 'true',
'include_incentives': 'true',
'include_targeted_incentives': 'true',
'make_slug': requested_car_name,
'new_or_used': 'u',
'per_page': '30',
'postal_code': '',
'search_event': 'true',
'sort[]': 'best_match',
'sponsored': 'true',
'state': 'ny',
'page':'1'}


jsonData = requests.get(url, params=payload).json()
total = jsonData['total']
total_pages = math.ceil(total/30)

total_pages_input = input('There are %s pages to iterate.nEnter the number of pages to go through or type ALL: ' %total_pages)
if total_pages_input.upper() == 'ALL':
    total_pages = total_pages
else:
    total_pages = int(total_pages_input)

values = []
for page in range(1,total_pages 1):
    if page == 1:
        car_listings = jsonData['listings']
    else:
        payload.update({'page':'%s' %page})
        jsonData = requests.get(url, params=payload).json()
        car_listings = jsonData['listings']
        
    for listing in car_listings:
        vehicle = listing['vehicle']
        
        ex_color = vehicle['exterior_color']
        in_color = vehicle['interior_color']
        location = vehicle['location']
        price = vehicle['list_price']
        make = vehicle['make']
        model = vehicle['model']
        mileage = vehicle['mileage']
        style = vehicle['style']
        year = vehicle['year']
        
        engine = vehicle['engine']
        accidentCount = vehicle['condition_history']['accidentCount']
        ownerCount = vehicle['condition_history']['ownerCount']
        isCleanTitle = vehicle['condition_history']['titleInfo']['isCleanTitle']
        isFrameDamaged = vehicle['condition_history']['titleInfo']['isFrameDamaged']
        isLemon = vehicle['condition_history']['titleInfo']['isLemon']
        isSalvage = vehicle['condition_history']['titleInfo']['isSalvage']
        isTheftRecovered = vehicle['condition_history']['titleInfo']['isTheftRecovered']
        
        values.append((ex_color, in_color,location,price,make,model,mileage,
        style,year,engine,accidentCount,ownerCount,isCleanTitle,isFrameDamaged,
        isLemon, isSalvage,isTheftRecovered))
    print('Completed: Page %s of %s' %(page,total_pages))
        
        
# check the connection
print('CONNECTING ...')

mydb = mysql.connector.connect(
    host="xxxxx",
    user="xxxxxx",
    password="xxxxxx",
    port='xxxxxx',
    database='xxxxxx'
)

print('CONNECTED')

# checking the connection is done

my_cursor = mydb.cursor(buffered=True)

# create_command = ''' create table car_information (exterior_color varchar(255), interior_color varchar(255),location varchar(255),price varchar(255),make varchar(255),model varchar(255),mileage varchar(255),
#         style varchar(255),year varchar(255),engine varchar(255),accidentCount varchar(255),ownerCount varchar(255),isCleanTitle varchar(255),isFrameDamaged varchar(255),
#         isLemon varchar(255), isSalvage varchar(255),isTheftRecovered varchar(255))'''

# my_cursor.execute(create_command)
# print('created')


insert_command = '''INSERT INTO car_name (exterior_color, interior_color,location,price,make,model,mileage,
        style,year,engine,accidentCount,ownerCount,isCleanTitle,isFrameDamaged,
        isLemon, isSalvage,isTheftRecovered) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'''
my_cursor.executemany(insert_command, values)
mydb.commit()

print(my_cursor.rowcount, "Record Inserted")

mydb.close()             
 

проблема, похоже, в том, что список моделей автомобилей содержит менее 20 записей.

 for item in range(20):
  car_list.append(car_model[item].text)
 

это всегда пытается добавить ровно 20 элементов в список car. если у вас меньше 20 записей, возникает ошибка, потому что car_model[20].text не существует, когда есть только 10 записей. вы можете попробовать

 for item in range(len(car_model)):
  car_list.append(car_model[item].text)