Morning Folks.

I am scraping some data from a local Property Company, data is then put into a local mysql DB. What I would like to do is compare one field (closing_date) to current date. Once closing_date is now or has passed either change the closing_date to 0000-00-00 or Null.

I plan at some point to display the data on a webpage local to me only, is the above the best way to do what I want within Python or would it be best achieved when displaying the data instead.

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


web_link_list = []


class House:
    def __init__(self, address, advert, postcode, area, prop_type, opening_date, closing_date, bedrooms, weblink):
    self.address = address
    self.advert = advert
    self.postcode = postcode
    self.area = area
    self.prop_type = prop_type
    self.opening_date = opening_date
    self.closing_date = closing_date
    self.bedrooms = bedrooms
    self.weblink = weblink

def insert_sql(self):
    my_db = mysql.connector.connect(host='localhost',
                                    user='test1',
                                    password='testpass',
                                    database='Houses')

    my_cursor = my_db.cursor()

    sql = "insert ignore into tb1 (address, advert, postcode, area, property_type, opening_date, closing_date, bedrooms, weblink) values (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
    val = (self.address, self.advert, self.postcode, self.area, self.prop_type, self.opening_date, self.closing_date, self.bedrooms, self.weblink)
    my_cursor.execute(sql, val)
    my_db.commit()
    print(my_cursor.lastrowid, " Inserted")


def get_links():

    url = 'https://angushomefinder.homeconnections.org.uk/my-cbl/property-search'
    page = requests.get(url)
    soup = BeautifulSoup(page.content, 'html.parser')

    for item in soup.find_all('a', attrs={'class': 'blue-btn'}, href=True):
        web_link_list.append('https://angushomefinder.homeconnections.org.uk'   item['href'])


def get_house():
    for item in web_link_list:
        page = requests.get(item)
        soup = BeautifulSoup(page.content, 'html.parser')
        result = soup.find('table', class_="tableproperty")

        new_house = House(soup.find('address', {'class': 'address-box'}).text.title(),
                      result.find('td', attrs={'data-title': 'Advert'} if result.find('td', attrs={'data-title': 'Advert'}) else None).text,
                      result.find('td', attrs={'data-title': 'Post code'} if result.find('td', attrs={'data-title': 'Post code'}) else None).text,
                      result.find('td', attrs={'data-title': 'Area'} if result.find('td', attrs={'data-title': 'Area'}) else None).text.title(),
                      result.find('td', attrs={'data-title': 'Property type'} if result.find('td', attrs={'data-title': 'Property type'}) else None).text.title(),
                      datetime.strptime(result.find('td', attrs={'data-title': 'Opening date'} if result.find('td', attrs={'data-title': 'Opening date'}) else None).text, '%d/%m/%Y'),
                      datetime.strptime(result.find('td', attrs={'data-title': 'Closing date'} if result.find('td', attrs={'data-title': 'Closing date'}) else None).text, '%d/%m/%Y'),
                      result.find('td', attrs={'data-title': 'Bedrooms'} if result.find('td', attrs={'data-title': 'Bedrooms'}) else None).text,
                      item)
    House.insert_sql(new_house)


get_links()
get_house()

CodePudding user response：

process existing rows. – pr0xibus

If you want to modify the values in the rows where the distance between the values is 8 days or more then

UPDATE table_name
SET closing_date = NULL
WHERE DATEDIFF(closing_date, opening_date) >= 8
-- AND closing_date IS NOT NULL

If I understand your criteria incorrectly then adjust the condition (use correct comparing operator).