def main():
database = 'Financial_DataSet'
CSID = CrawlerStockID(Key.host, Key.user, Key.password, database)
CSID.run()
C2S = BasedClass.Crawler2SQL('StockInfo', 'Financial_DataSet')
try:
C2S.create_table(CSID.data.columns)
except:
123
# upload stock info
BasedClass.execute_sql2(database, 'TRUNCATE table `StockInfo` ')
CSID.upload_stock_info2sql()
def get_curr_id_name(self):
self.data_name = []
curr_id = BasedClass.execute_sql2(
self.database,
'SELECT DISTINCT `curr_id` FROM `EnergyFuturesPrices` WHERE 1')
self.curr_id = [c[0] for c in curr_id]
for c in self.curr_id:
#c = country[0]
sql_text = (
'SELECT DISTINCT `data_name` FROM `EnergyFuturesPrices` ' +
'WHERE `curr_id` = "' + c + '"')
value = BasedClass.execute_sql2(self.database, sql_text)
value = [d[0] for d in value]
[self.data_name.append(v) for v in value]
def get_stock_id_set(self):
data = BasedClass.execute_sql2(
database = self.database,
sql_text = 'SELECT distinct `stock_id` FROM FinancialStatements')
self.stock_id_set = [ da[0] for da in data]
def __init__(self):
self.host = Key.host
self.user = Key.user
self.password = Key.password
self.database = 'python'
tem = BasedClass.execute_sql2(self.database, 'SHOW TABLES')
tem = np.concatenate(tem, axis=0)
self.datatable = [te for te in tem]
self.datatable.remove('new')
def get_curr_id_name(self):
self.data_name = []
curr_id = BasedClass.execute_sql2(
self.database,
'SELECT DISTINCT `curr_id` FROM `GovernmentBonds` WHERE 1')
self.curr_id = [c[0] for c in curr_id]
country = BasedClass.execute_sql2(
self.database,
'SELECT DISTINCT `country` FROM `GovernmentBonds` WHERE 1')
country = [c[0] for c in country]
for c in country:
#c = country[0]
sql_text = ('SELECT DISTINCT `data_name` FROM `GovernmentBonds` ' +
'WHERE `country` = "' + c + '"')
value = BasedClass.execute_sql2(self.database, sql_text)
value = [d[0] for d in value]
[self.data_name.append(c + ' ' + v) for v in value]
def start(self):
date = []
for stock in self.stock:
print(stock)
sql = "SELECT MAX(`date`) FROM `StockPrice` WHERE `stock` = '" + stock + "'"
tem = BasedClass.execute_sql2(database='Financial_DataSet',
sql_text=sql)
date.append(tem[0][0])
start = str(max(date))
return start
def CrawlerStatus(self):
self.cdate = pd.DataFrame()
for dt in self.datatable:
text = "SELECT name,CrawlerDate FROM `" + dt + "` where id = ( SELECT max(id) FROM `" + dt + "` )"
tem = BasedClass.execute_sql2(self.database, text)
if len(tem) != 0:
tem = pd.DataFrame(np.concatenate(tem, axis=0))
self.cdate = self.cdate.append(tem.T)
self.cdate.index = range(len(self.cdate))
self.cdate.columns = ['name', 'date']
date = [d.date() for d in self.cdate['date']]
self.cdate['date'] = date
def get_yearquar(self,stock):
sql_text = "SELECT year,quar FROM FinancialStatements WHERE stock_id = "
sql_text = sql_text + stock
tem = BasedClass.execute_sql2(
database = self.database,
sql_text = sql_text)
year,quar = [],[]
for te in tem:
year.append(te[0]-1911)
quar.append(te[1])
return year,quar
def select_new_data(self):
new_data = pd.DataFrame()
for stock in self.stock:
sql = "SELECT MAX(`date`) FROM `StockPrice` WHERE `stock` = '" + stock + "'"
tem = BasedClass.execute_sql2(database='Financial_DataSet',
sql_text=sql)
max_date = tem[0][0]
data = self.data[self.data['stock'] == stock]
date = [
datetime.datetime.strptime(d, '%Y-%m-%d').date() > max_date
for d in data['date']
]
new_data = new_data.append(data[date])
self.new_data = new_data
def create_stock(self):
sql = 'SELECT DISTINCT `stock` FROM `StockPrice` WHERE 1'
tem = BasedClass.execute_sql2(database='Financial_DataSet',
sql_text=sql)
self.stock = [t[0] for t in tem]
def get_data_id(self):
sql_text = "SELECT id FROM `StockDividend` WHERE `meeting_data` = '"
sql_text = sql_text + str(self.new_date) + "' AND `stock_id` LIKE "
sql_text = sql_text + self.new_data['stock_id']
self.data_id = BasedClass.execute_sql2(self.database,sql_text)[0][0]