def load(self, select='', date=''):
colname = query('SHOW COLUMNS FROM `{}`'.format(select),
database=TABLE)
colname = [c[0] for c in colname if c[0] not in ['id', 'url']]
sql = 'select `{}` from `{}`'.format('`,`'.join(colname), select)
if date != '':
sql = "{} WHERE `date` >= '{}' ".format(sql, date)
data = query(sql, database=TABLE)
data = pd.DataFrame(list(data))
if len(data) > 0:
data.columns = colname
if self.select_variable in data.columns:
data = data.sort_values([self.select_variable, 'date'])
else:
data = data.sort_values('date')
data.index = range(len(data))
data['stock_id'] = select
return data
def load(self, select='', date=''):
colname = query('SHOW COLUMNS FROM `{}`'.format(TABLE),
database=DATABASE)
colname = [c[0] for c in colname if c[0] not in ['id', 'url']]
if isinstance(select, list) == False:
select = [select]
select = "','".join(select)
sql = 'select `{}` from `{}`'.format('`,`'.join(colname), TABLE)
sql = "{} WHERE `stock_id` IN ('{}') ".format(sql, select)
if date != '':
sql = "{} AND `date` >= '{}' ".format(sql, date)
data = query(sql, database=DATABASE)
data = pd.DataFrame(list(data))
if len(data) > 0:
data.columns = colname
if self.select_variable in data.columns:
data = data.sort_values([self.select_variable, 'date'])
else:
data = data.sort_values('date')
data.index = range(len(data))
return data
def load_all(self,status = 'package'):
colname = query( 'SHOW COLUMNS FROM {}'.format( TABLE ) )
colname = [ c[0] for c in colname if c[0] not in ['id','url'] ]
colname.remove('date')
sql = 'select `{}` from {}'.format( '`,`'.join( colname ) ,TABLE)
tem = query(sql)
data = pd.DataFrame( list(tem) )
data.columns = colname
if status == 'package':
sql = 'SHOW TABLES '
tem = query(sql,TABLE.replace('Info','Price'))
stock_id = [ te[0] for te in tem ]
bo = [ True if x in stock_id else False for x in data['stock_id'] ]
data = data[bo]
data.index = range(len(data))
return data
def load_all(self, status='package'):
colname = query('SHOW COLUMNS FROM {}'.format(TABLE))
colname = [c[0] for c in colname if c[0] not in ['id', 'url']]
sql = 'select `{}` from {}'.format('`,`'.join(colname), TABLE)
tem = query(sql)
data = pd.DataFrame(list(tem))
data.columns = colname
'''
if status == 'package':
sql = 'SELECT DISTINCT `stock_id` FROM `TaiwanStockPrice` '
#tem = query(sql,TABLE.replace('Info','Price'))
tem = query(sql)
stock_id = [ te[0].replace('_TW','') for te in tem ]
bo = [ True if x in stock_id else False for x in data['stock_id'] ]
data = data[bo]
data.index = range(len(data))
'''
return data
def load(self, date):
colname = query('SHOW COLUMNS FROM {}'.format(TABLE))
colname = [c[0] for c in colname if c[0] != 'id']
sql = 'select `{}` from {}'.format('`,`'.join(colname), TABLE)
if date != '':
sql = "{} WHERE `date` >= '{}'".format(sql, date)
data = query(sql)
data = pd.DataFrame(list(data))
if len(data) > 0:
data.columns = colname
data = data.sort_values('date')
data.index = range(len(data))
return data
def get_data_list(self):
tem = query('SHOW TABLES', database=TABLE)
return [te[0] for te in tem]
