i have links.csv file and i use pd.read_csv to get this links one by one. My csv file looks something like this: https://im.ge/i/1i38PY
Except fourth link, i can scrap all infos from the links that took me to the site in real time based by my first codes. Datas directly saves in mysql CARFINAL table like this : https://im.ge/i/1igBaG
The error I got for the fourth link is this-> TypeError: not enough arguments for format string ///// print(df) looks like this -> https://im.ge/i/1i8tgT
this is my first codes at the bottom;
cursor = scrap_db.cursor()
# Drop table as per requirement
# cursor.execute('DROP TABLE IF EXISTS CARFINAL')
# Create table as per requirement
sql = """CREATE TABLE CARFINAL(
brand VARCHAR(120),
model VARCHAR(120),
model_version VARCHAR(120),
location VARCHAR(60),
price VARCHAR(80),
dealer VARCHAR(60),
contact_name VARCHAR(60),
tel_number VARCHAR(50),
mileage VARCHAR(50),
gearbox VARCHAR(60),
first_registration VARCHAR(30),
fuel_type VARCHAR(120),
power VARCHAR(60),
seller VARCHAR(60),
body_type VARCHAR(30),
type VARCHAR(10),
drivetrain VARCHAR(10),
seats int(11),
doors int(11),
country_version VARCHAR(20),
offer_number VARCHAR(20),
model_code int(11),
production_date int(11),
general_inspection int(11),
previous_owner int(11),
full_service_history VARCHAR(10),
non_smoker_vehicle VARCHAR(10),
engine_size VARCHAR(20),
gears VARCHAR(10),
cylinders VARCHAR(10),
fuel_consumption VARCHAR(60),
CO2_emissions VARCHAR(30),
energy_efficiency_class VARCHAR(10),
CO2_efficiency VARCHAR(80),
emission_class VARCHAR(20),
emissions_sticker VARCHAR(10),
colour_and_upholstery VARCHAR(60),
all_equipment VARCHAR(300),
vehicle_description VARCHAR(400),
car_picture_link VARCHAR(200),
link VARCHAR(200)
)"""
cursor.execute(sql)
#Save data to the table
#scrap_db = pymysql.connect(host='localhost',user='root',password='****',database='autoscout',charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
mySql_insert_query = """INSERT INTO CARFINAL
(brand,
model,
model_version,
location,
price,
dealer,
contact_name,
tel_number,
mileage,
gearbox,
first_registration,
fuel_type,
power,
seller,
body_type,
type,
drivetrain,
seats,
doors,
country_version,
offer_number,
model_code,
production_date,
general_inspection,
previous_owner,
full_service_history,
non_smoker_vehicle,
engine_size,
gears,
cylinders,
fuel_consumption,
CO2_emissions,
energy_efficiency_class,
CO2_efficiency,
emission_class,
emissions_sticker,
colour_and_upholstery,
all_equipment,
vehicle_description,
car_picture_link,
link
)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) """
for row_count in range(0, df.shape[0]): # range(0,1)
chunk = df.iloc[row_count:row_count + 1,:].values.tolist()
tuple_of_tuples = tuple(tuple(x) for x in chunk)
cursor = scrap_db.cursor()
cursor.executemany(mySql_insert_query, tuple_of_tuples)
scrap_db.commit()
print(cursor.rowcount, "Record inserted successfully into CARFINAL table")
scrap_db.close()
len_of_links = len(make_model_ads_data_latest)
number = np.arange(4,5)
j = 0
for i in tqdm(number):
ad_link = make_model_ads_data_latest['ad_link'][i]
#ad_link = make_model_ads_data_latest['ad_link'][i+1] #BAK
if ad_link not in make_model_ads_data['link'].values:
data = get_ad_data(ad_link = ad_link, sleep_time = 0)
j = j + 1
print("scraped ", j, " new ads")
By the way if i use my 2. codes; it works just for the fourth link. i have just replaced my firts codes to something like this ; .....
sql = """CREATE TABLE CAR2(
brand VARCHAR(120),
model VARCHAR(120),
model_version VARCHAR(120),
location VARCHAR(60),
price VARCHAR(80),
dealer VARCHAR(60),
contact_name VARCHAR(60),
tel_number VARCHAR(50),
mileage VARCHAR(50),
gearbox VARCHAR(60),
first_registration VARCHAR(30),
fuel_type VARCHAR(120),
power VARCHAR(60),
seller VARCHAR(60),
body_type VARCHAR(30),
type VARCHAR(10),
seats int(11),
doors int(11),
country_version VARCHAR(20),
model_code VARCHAR(20),
engine_size VARCHAR(20),
colour_and_upholstery VARCHAR(30),
all_equipment VARCHAR(300),
vehicle_description VARCHAR(400),
car_picture_link VARCHAR(200),
link VARCHAR(200)
)"""
cursor.execute(sql)
#Save data to the table
#scrap_db = pymysql.connect(host='localhost',user='root',password='1234',database='autoscout',charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
mySql_insert_query = """INSERT INTO CAR2
(brand,
model,
model_version,
location,
price,
dealer,
contact_name,
tel_number,
mileage,
gearbox,
first_registration,
fuel_type,
power,
seller,
body_type,
type,
seats,
doors,
country_version,
model_code,
engine_size,
colour_and_upholstery,
all_equipment,
vehicle_description,
car_picture_link,
link
)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) """
But i don't want to change the structure everytime when i scraping
i got stuck. please help