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]