def refresh_stock_concept(): engine = get_pg_engine() code_df = ts.get_concept_classified() code_df.to_sql(constants.STOCK_CONCEPT, engine, if_exists='replace') engine.dispose() runner = SqlRunner() runner.execute("CREATE INDEX on %s(code)" % constants.STOCK_CONCEPT) runner.dispose()
def refresh_stock_list(): engine = get_pg_engine() code_df = ts.get_stock_basics() code_df.to_sql(constants.STOCK_INFO, engine, if_exists='replace') engine.dispose() runner = SqlRunner() runner.execute("CREATE INDEX on %s(code)" % constants.STOCK_INFO) runner.dispose()
class StockIncreaseRatio(object): def __init__(self, interval, end_day=None): self.runner = SqlRunner() self.interval = interval self.end_day = end_day if end_day else stock_latest_day(self.runner) self.start_day = stock_open_day(self.runner, self.end_day, self.interval) def create_table(self): if not dbutil.table_exists(self.runner, constants.STOCK_INCREASE): dbutil.create_table(self.runner, constants.STOCK_INCREASE, """ CREATE TABLE {name} ( code text, start_day date, end_day date, percent float8, high_percent float8, name TEXT, c_name TEXT ); CREATE INDEX on {name} (code); CREATE INDEX on {name} (start_day, end_day); """.format(name=constants.STOCK_INCREASE)) def get_ratio(self): self.create_table() print self.start_day, self.end_day sql = """ DELETE FROM {name} WHERE start_day = %(start_day)s and end_day = %(end_day)s; INSERT INTO {name} SELECT s.code, t.date as start_day, s.date as end_day, (s.close - t.close) / t.close as percent, r.high_percent, c.name, c.c_name FROM ( SELECT code, date, close FROM {stock_daily} WHERE date = %(end_day)s ) s, ( SELECT code, date, close FROM {stock_daily} WHERE date = %(start_day)s ) t, ( SELECT code, (max(high) - min(low)) / min(low) as high_percent FROM {stock_daily} WHERE date BETWEEN %(start_day)s and %(end_day)s GROUP BY code ) r, {stock_concept} c WHERE s.code = t.code and s.code = r.code and r.code = c.code """.format(stock_daily=constants.STOCK_DAILY, name=constants.STOCK_INCREASE, stock_concept=constants.STOCK_CONCEPT) params = { 'start_day': self.start_day, 'end_day': self.end_day } print sql, params self.runner.execute(sql, params) self.dispose() def dispose(self): self.runner.dispose()
def delete_stock_data(): runner = SqlRunner() runner.execute("DELETE FROM {name} WHERE date = %(day)s".format(name=STOCK_DAILY), {"day": stock_latest_day(runner)}) runner.dispose()
class TickData(object): def __init__(self): self.runner = SqlRunner() def drop_stock_tick_table(self, name): self.runner.execute("DROP TABLE IF EXISTS %s" % name) def create_stock_tick_table(self, name): dbutil.create_table( self.runner, name, """ CREATE TABLE {name} ( index BIGINT, name TEXT, open FLOAT8, pre_close FLOAT8, price FLOAT8, high FLOAT8, low FLOAT8, bid FLOAT8, ask FLOAT8, volume FLOAT8, amount FLOAT8, b1_v TEXT, b1_p TEXT, b2_v TEXT, b2_p TEXT, b3_v TEXT, b3_p TEXT, b4_v TEXT, b4_p TEXT, b5_v TEXT, b5_p TEXT, a1_v TEXT, a1_p TEXT, a2_v TEXT, a2_p TEXT, a3_v TEXT, a3_p TEXT, a4_v TEXT, a4_p TEXT, a5_v TEXT, a5_p TEXT, date DATE, time TIME, code TEXT ); CREATE INDEX on {name} (code) """.format(name=name)) def get_stock_tick_by_code(self, table_name, stock_code): try: df = ts.get_realtime_quotes(stock_code) if df is None or df.empty: print "No need refresh %s" % stock_code else: df["code"] = stock_code df.to_sql(table_name, self.runner.engine, if_exists='append') df.to_sql(STOCK_TICK_HIST_DATA, self.runner.engine, if_exists='append') print "generating,", stock_code except Exception as e: print "wrong code", stock_code print e def dispose(self): self.runner.dispose()