html_to_sqlite.py

"""Script that converts an HTML table into a SQLite database."""

import random
import sqlite3
import time

import requests
from bs4 import BeautifulSoup

BASE_URL = "https://coinmarketcap.com/currencies/{0}/historical-data/"

COINS = ["litecoin", "bitcoin"]

USER_AGENTS = [
    "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2228.0 Safari/537.36",
    "Opera/9.80 (Windows NT 6.2; Win64; x64) Presto/2.12 Version/12.16",
    "Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko",
    "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)",
    "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0)",
    "Mozilla/5.0 (Windows NT 6.3; WOW64; rv:45.0) Gecko/20100101 Firefox/45.0",
    "Mozilla/5.0 (Windows NT 6.1; Trident/7.0; rv:11.0) like Gecko",
    "Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.1; WOW64; Trident/6.0)",
    "Mozilla/5.0 (Windows NT 6.3; WOW64; rv:32.0) Gecko/20100101 Firefox/32.0"
]


def load_data(coin):
    """Loads the coin info and saves it to a fresh SQLite db."""

    headers = {"User-Agent": random.choice(USER_AGENTS)}

    with requests.get(BASE_URL.format(coin), headers=headers) as coin_data:

        db_connection = sqlite3.connect("{0}.db".format(coin))
        db_cursor = db_connection.cursor()
        db_cursor.execute(
            """CREATE TABLE IF NOT EXISTS stocks (date TEXT, open REAL, high REAL, low REAL, close REAL, volume REAL, marketcap REAL)""")

        soup = BeautifulSoup(coin_data.text, "html.parser")
        table = soup.find("tbody")
        queries = list()

        for item in table.find_all("tr"):

            coin_values = item.find_all("td")

            coin_date = coin_values[0].text
            coin_open = coin_values[1].text
            coin_high = coin_values[2].text
            coin_low = coin_values[3].text
            coin_close = coin_values[4].text
            coin_volume = coin_values[5].text
            coin_market_cap = coin_values[6].text

            queries.append((coin_date, coin_open, coin_high,
                            coin_low, coin_close, coin_volume, coin_market_cap))

        db_cursor.executemany(
            "INSERT INTO stocks VALUES (?,?,?,?,?,?,?)", queries)
        db_connection.commit()
        db_connection.close()
        print("Successfully saved: {0}".format(coin))


if __name__ == "__main__":

    for coin_name in COINS:
        load_data(coin_name)
        time.sleep(5)