112 lines
3.6 KiB
Python
112 lines
3.6 KiB
Python
#
|
|
# Title: Scraping Alpha
|
|
# Version: 1.0
|
|
# Author: Ben Goldsworthy <b.goldsworthy@lancaster.ac.uk>
|
|
#
|
|
# This file is a part of Scraping Alpha, a series of scripts to scrape
|
|
# earnings call transcripts from seekingalpha.com and present them as useful
|
|
# SQL.
|
|
#
|
|
# This file takes the `transcripts.sql` file exported from the database
|
|
# created using the output file of `JSONtoSQL.py` after the following query:
|
|
# SELECT `id`, `execs`, `analysts` FROM `transcripts`
|
|
# It creates from this two `execs.sql` and `analysts.sql` for creating linking
|
|
# tables in the database.
|
|
#
|
|
# This file should be located in the same directory as `transcripts.sql`, and
|
|
# is run via 'python execsAndAnalysts'.
|
|
#
|
|
|
|
import sys
|
|
import codecs
|
|
import os
|
|
from shutil import copyfile
|
|
import fileinput
|
|
|
|
sys.stdout=codecs.getwriter('utf-8')(sys.stdout)
|
|
|
|
start = 0
|
|
|
|
# Creates a temporary copy in case something goes Pete Tong.
|
|
copyfile("transcripts.sql", "transcripts.sql.tmp")
|
|
|
|
# Trims everything from the export except for the INSERT statement.
|
|
for line in fileinput.FileInput("transcripts.sql.tmp",inplace=1):
|
|
if start == 0:
|
|
if "INSERT INTO `transcripts`" in line:
|
|
start = 1
|
|
print "INSERT INTO `execs_to_transcripts` (`exec_id`, `transcript_id`) VALUES"
|
|
else:
|
|
if line == "\n":
|
|
start = 0
|
|
else:
|
|
print "\t"+line,
|
|
|
|
# Copies the produced file to create both the output files, then deletes the
|
|
# temporary file.
|
|
copyfile("transcripts.sql.tmp", "execs.sql")
|
|
copyfile("transcripts.sql.tmp", "analysts.sql")
|
|
os.remove("transcripts.sql.tmp")
|
|
|
|
# Converts each line "(x, '0;...;n')" in the file to n separate INSERTs, one
|
|
# for each executive.
|
|
start = 0
|
|
nL = ""
|
|
for line in fileinput.FileInput("execs.sql",inplace=1):
|
|
if start == 0:
|
|
start = 1
|
|
else:
|
|
bits = line.split(', ')
|
|
tID = bits[0].strip('\t').strip('(')
|
|
execs = bits[1].split(';')
|
|
newLines = ""
|
|
for execID in execs:
|
|
newLines = newLines + nL + "\t("+tID+", "+execID.strip('\'')+"),"
|
|
line = line.replace(line, newLines)
|
|
nL = "\n"
|
|
print line,
|
|
|
|
# Does the same for the analysts.
|
|
start = 0
|
|
nL = ""
|
|
for line in fileinput.FileInput("analysts.sql",inplace=1):
|
|
if start == 0:
|
|
start = 1
|
|
line = line.replace(line, "INSERT INTO `analysts_to_transcripts` (`analyst_id`, `transcript_id`) VALUES\n")
|
|
else:
|
|
bits = line.split(', ')
|
|
tID = bits[0].strip('\t').strip('(')
|
|
# As it is possible for there to be no analysts in a call, this ignores
|
|
# blank results.
|
|
if "''" not in bits[2]:
|
|
analysts = bits[2].split(';')
|
|
newLines = ""
|
|
for analystID in analysts:
|
|
# This stops the final transcript from getting an additional,
|
|
# `analyst_id`-less INSERT
|
|
if analystID != '\n':
|
|
newLines = newLines + nL + "\t("+tID+", "+analystID.strip('\'').strip('\'),\n')+"),"
|
|
line = line.replace(line, newLines)
|
|
nL = "\n"
|
|
else:
|
|
line = ""
|
|
print line,
|
|
|
|
# Replace the final comma at the end of each file with a semicolon, to make it
|
|
# valid SQL
|
|
with open("execs.sql", 'rb+') as filehandle:
|
|
filehandle.seek(-1, os.SEEK_END)
|
|
filehandle.truncate()
|
|
filehandle.write(";")
|
|
with open("analysts.sql", 'rb+') as filehandle:
|
|
filehandle.seek(-1, os.SEEK_END)
|
|
filehandle.truncate()
|
|
filehandle.write(";")
|
|
# `analysts.sql` then performs some cleanup on the database.
|
|
filehandle.write("\n\nALTER TABLE `transcripts`\n\tDROP COLUMN `execs`,\n\tDROP COLUMN `analysts`;\n\n")
|
|
filehandle.write("DELETE FROM `transcripts` WHERE `id` = 0;\n")
|
|
filehandle.write("DELETE FROM `execs` WHERE `id` = 0;\n")
|
|
filehandle.write("DELETE FROM `analysts` WHERE `id` = 0;\n")
|
|
filehandle.write("DELETE FROM `execs_to_transcripts` WHERE `transcript_id` = 0;\n")
|
|
filehandle.write("DELETE FROM `analysts_to_transcripts` WHERE `transcript_id` = 0;\n")
|