TDM 40100: Project 11 — 2022

import uuid
import os

def link_to_blob(link: str) -> bytes:
    def _load_tables(driver):
        """
        Given the driver, scroll through the cards
        so that they all load.
        """
        # find price and tax history element using xpath
        table = driver.find_element(...)

        # scroll the table into view
        driver.execute_script(...)

        # sleep 2 seconds
        time.sleep(2)

        try:
            # find the "See complete tax history" button (if it exists)
            see_more = driver.find_element(...)

            # click the button to reveal the rest of the history (if it exists)
            see_more.click()

        except NoSuchElementException:
            pass

        try:
            # find the "See complete price history" button (if it exists)
            see_more = driver.find_element(...)

            # click the button to reveal the rest of the history (if it exists)
            see_more.click()

        except NoSuchElementException:
            pass

    # create a .html file with a random name using the uuid package so there aren't collisions
    filename = f"{uuid.uuid4()}.html"

    # open the file
    with open(filename, 'w') as f:
        firefox_options = Options()
        # Headless mode means no GUI
        firefox_options.add_argument("--headless")
        firefox_options.add_argument("--disable-extensions")
        firefox_options.add_argument("--no-sandbox")
        firefox_options.add_argument("--disable-dev-shm-usage")

        driver = webdriver.Firefox(options=firefox_options)
        driver.get(link)
        time.sleep(2)
        _load_tables(driver)

        # write the page source to the file
        f.write(...)
        driver.quit()

    # open the file in read binary mode
    with open(filename, 'rb') as f:
        # read the binary contents that are ready to be inserted into a sqlite BLOB
        blob = f.read()

    # remove the file from the filesystem -- we don't need it anymore
    os.remove(filename)

    return blob

import lxml.html

tree = lxml.html.fromstring(blob_to_html(my_blob))
tree.xpath("blah")

my_blob = link_to_blob("https://www.zillow.com/homedetails/2180-N-Brentwood-Cir-Lecanto-FL-34461/43641432_zpid/")
get_price_history(blob_to_html(my_blob))

def blob_to_html(blob: bytes) -> str:
    return blob.decode("utf-8")

[['11/9/2022', 'Price change', 275000],
 ['11/2/2022', 'Listed for sale', 289900],
 ['1/13/2000', 'Sold', 19000]]

my_blob = link_to_blob("https://www.zillow.com/homedetails/2180-N-Brentwood-Cir-Lecanto-FL-34461/43641432_zpid/")
get_tax_history(blob_to_html(my_blob))

[[2021, 1344, 124511],
 [2020, 1310, 122792],
 [2019, 1290, 120031],
 [2018, 1260, 117793],
 [2017, 1260, 115370],
 [2016, 1252, 112997],
 [2015, 1262, 112212],
 [2014, 1277, 113120],
 [2013, 1295, 112920],
 [2012, 1389, 124535],
 [2011, 1557, 134234],
 [2010, 1495, 132251],
 [2009, 1499, 128776],
 [2008, 1483, 128647],
 [2007, 1594, 124900],
 [2006, 1608, 121900],
 [2005, 1704, 118400],
 [2004, 1716, 115000],
 [2003, 1624, 112900],
 [2002, 1577, 110300],
 [2000, 288, 15700]]

def get_price_history(html: str):
    tree = lxml.html.fromstring(html)
    # xpath to find the price and tax history table
    # then, you can use the xpath `following-sibling::div` to find the `div` that directly follows the
    # price and tax history div (hint, look for "Price-and-tax-history" in the id attribute of a div element
    # after the "following-sibling::div" part, look for <tr> elements with an id attribute
    trs = tree.xpath(...)
    values = []
    for tr in trs:
        # xpath on the "tr" to find td with an inner span. Use string methods to remove the $ and remove the ",", and to remove trailing whitespace
        price = tr.xpath(...)[2].text.replace(...).replace(...).strip()

        # if price is empty, make it None
        if price == '':
            price = None

        # append the values
        values.append([tr.xpath(...)[0].text, tr.xpath(...)[1].text, price])

    return values

def get_tax_history(html: str):
    tree = lxml.html.fromstring(html)
    try:
        # find the 'Price-and-tax-history' div, then, the following-sibling::div, then a table element, then a tbody element
        tbody = tree.xpath("//div[@id='Price-and-tax-history']/following-sibling::div//table//tbody")[1]
    except IndexError:
        return None
    values = []
    # get the trs in the tbody
    for tr in tbody.xpath(".//tr"):
        # replace the $, ",", and "-", strip whitespace
        prop_tax = tr.xpath(...)[1].text.replace(...).replace(...).replace(...).strip()
        # if prop_tax is empty set to None
        if prop_tax == '':
            prop_tax = None
        # add the data, for the last item in the list, remove $ and ","
        values.append([int(tr.xpath(...)[0].text), prop_tax, int(tr.xpath(...)[2].text.replace(...).replace(...))])

    return values

import sqlite3
from urllib.parse import urlsplit
from tqdm.notebook import tqdm

links = get_links("47933")

# connect to database
con = sqlite3.connect(...)
for link in tqdm(links): # this shows a progress bar for assistance

    # use link_to_blob to get the blob

    # use urlsplit to extract the zpid from the link

    # add values to a tuple for insertion into the database
    to_insert = (linkid, blob)

    # get a cursor
    cur = con.cursor()

    # insert the data into the houses table using the cursor

    # get price history data to insert
    to_insert = get_price_history(blob_to_html(blob))

    # insert id into price history data
    for val in to_insert:
        val.insert(0, linkid)

    # insert the data into the price_history table using the cursor

    # prep the tax history data in the exact same way as price history

    # if there is tax history data, insert the ids just like before

    # insert the data into the tax_history table using the cursor

    # commit the changes
    con.commit()

# close the connection
con.close()