I have encountered a problem in my code that is causing issues with the output of loops and inserting data into my database. Despite attempting to troubleshoot, I am unable to pinpoint the exact source of the problem. What I am striving for is to have each scraped line of data printed as an output and then inserted into a table in my database. However, the current result is a single incorrect entry being duplicated numerous times.
Current Output:
Ford C-MAX 2019 1.1 Petrol 0
Ford C-MAX 2019 1.1 Petrol 0
Ford C-MAX 2019 1.1 Petrol 0
...
Desired Output Based on Website Listings (example only as it varies):
Ford C-MAX 2019 1.1 Petrol 15950
Ford C-MAX 2014 1.6 Diesel 12000
Ford C-MAX 2011 1.6 Diesel 9000
...
Code:
from __future__ import print_function
import requests
import re
import locale
import time
from time import sleep
from random import randint
from currency_converter import CurrencyConverter
c = CurrencyConverter()
from bs4 import BeautifulSoup
import pandas as pd
from datetime import date, datetime, timedelta
import mysql.connector
import numpy as np
import itertools
locale.setlocale( locale.LC_ALL, 'en_US.UTF-8' )
pages = np.arange(0, 210, 30)
entered = datetime.now()
make = "Ford"
model = "C-MAX"
def insertvariablesintotable(make, model, year, liter, fuel, price, entered):
try:
cnx = mysql.connector.connect(user='root', password='', database='FYP', host='127.0.0.2', port='8000')
cursor = cnx.cursor()
cursor.execute('CREATE TABLE IF NOT EXISTS ford_cmax ( make VARCHAR(15), model VARCHAR(20), '
'year INT(4), liter VARCHAR(3), fuel VARCHAR(6), price INT(6), entered TIMESTAMP) ')
insert_query = """INSERT INTO ford_cmax (make, model, year, liter, fuel, price, entered) VALUES (%s,%s,%s,%s,%s,%s,%s)"""
record = (make, model, year, liter, fuel, price, entered)
cursor.execute(insert_query, record)
cnx.commit()
finally:
if (cnx.is_connected()):
cursor.close()
cnx.close()
for response in pages:
headers = {
'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.95 Safari/537.36'}
response = requests.get("https://www.donedeal.ie/cars/Ford/C-MAX?start=" + str(response), headers=headers)
soup = BeautifulSoup(response.text, 'html.parser')
cnx = mysql.connector.connect(user='root', password='', database='FYP', host='127.0.0.2', port='8000')
cursor = cnx.cursor()
for details in soup.findAll('ul', attrs={'class': 'card__body-keyinfo'}):
details = details.text
#print(details)
year = details[:4]
liter = details[4:7]
fuel = details[8:14] #exludes electric which has 2 extra
mileage = re.findall("[0-9]*,[0-9][0-9][0-9]..." , details)
mileage = ''.join(mileage)
mileage = mileage.replace(",", "")
if "mi" in mileage:
mileage = mileage.rstrip('mi')
mileage = round(float(mileage) * 1.609)
mileage = str(mileage)
if "km" in mileage:
mileage = mileage.rstrip('km')
mileage = mileage.replace("123" or "1234" or "12345" or "123456", "0")
for price in soup.findAll('p', attrs={'class': 'card__price'}):
price = price.text
price = price.replace("No Price", "0")
price = price.replace("123" or "1234" or "12345" or "123456", "0")
price = price.replace(",","")
price = price.replace("€", "")
if "p/m" in price:
#price = price[:-3]
price = price.rstrip('p/m')
price = "0"
if "£" in price:
price = price.replace("£", "")
price = c.convert(price, 'GBP', 'EUR')
price = round(price)
print(make, model, year, liter, fuel, price)
#insertvariablesintotable(make, model, year, liter, fuel, price, entered) #same result as above