I am new to Python and recently started exploring web crawling. The code below parses the S&P 500 List Wikipedia page and writes the data of a specific table into a database.
While this script is hardcoded and I would certainly be interested in some thoughts on performing the same task in a slightly more generic way (perhaps with beautifulsoup
), this is not my primary concern. What I really wondered was if there is a less verbose or more "pythonic" way of doing it.
import urllib.request
import re
import pymysql
# Open Website and get only the table on the page with the relevant data. In this hardcoded case
table = urllib.request.urlopen("https://en.wikipedia.org/wiki/List_of_S%26P_500_companies#S.26P_500_Component_Stocks").read().decode("utf-8")
table = table.split("<table")[1]
table = table.split("\n")
# Define regex used for parsing and initialise list containers
tick_ident_nasdaq = 'href=\"http:\/\/www\.nasdaq\.com\/symbol\/'
tick_ident_nyse = 'href=\"https:\/\/www.nyse.com\/quote\/'
name_grab = '\">(.+)<\/a></td>'
cigs_grab = '^<td>(.+)</td>'
ticker, exchange, names, cigs, cigs_sub = ( [] for i in range(5))
match = False
# Parse HTML output and write relevant td data to lists.
# The list is "hardcoded", meaning after each match of either NASDAQ or NYSE ident,
# the matching <td> as well as the next, the fourth and fifth <td> after that one get parsed.
for i in range(len(table)):
if bool(re.search(pattern = tick_ident_nasdaq, string = table[i])):
ticker.append(re.search(pattern = name_grab, string = table[i]).group(1))
exchange.append("NASDAQ")
match = True
elif bool(re.search(pattern = tick_ident_nyse, string = table[i])):
ticker.append(re.search(pattern = name_grab, string = table[i]).group(1))
exchange.append("NYSE")
match = True
if match == True:
names.append(re.search(pattern = name_grab, string = table[i + 1]).group(1))
names[-1] = re.sub(pattern = "&", repl = "&", string = names[-1])
cigs.append(re.search(pattern = cigs_grab, string = table[i + 3]).group(1))
cigs[-1] = re.sub(pattern = "&", repl = "&", string = cigs[-1])
cigs_sub.append(re.search(pattern = cigs_grab, string = table[i + 4]).group(1))
cigs_sub[-1] = re.sub(pattern = "&", repl = "&", string = cigs_sub[-1])
match = False
# Format Data in tuple format for database export
company_data = zip(ticker, exchange, names, cigs, cigs_sub)
# Establish database connection, empty companies table and rewrite list data to table
try:
conn = pymysql.connect(host = "localhost", user = "root", passwd = "pw", db = "db", charset = "utf8", autocommit = True, cursorclass=pymysql.cursors.DictCursor)
cur = conn.cursor()
cur.execute("DELETE FROM companies")
cur.executemany("INSERT INTO companies (tickersymbol, exchange, name, cigs, cigs_sub) VALUES (\"%s\", \"%s\", \"%s\", \"%s\", \"%s\")", (company_data))
finally:
cur.close()
conn.close()