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()
