Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
"""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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment