def computeVolume(records):
count = len(records)
for i in range(0, count):
r10 = records[i:i + 10]
l = [r.volume for r in r10]
volume_avg_10 = reduce(lambda x, y: x + y, l) / len(l)
volume_p = float(records[i].volume) / volume_avg_10
#对volume进行分类
volume_level = 0
if volume_p >= 3:
volume_level = 31
elif volume_p < 3 and volume_p >= 2:
volume_level = 21
elif volume_p < 2 and volume_p >= 0.5:
volume_level = 19
elif volume_p < 0.5 and volume_p >= 0.33:
volume_level = 12
elif volume_p < 0.33:
volume_level = 13
else:
volume_level = 0
sql = 'update stock_daily set volume_avg_10=%s,volume_level=%s where pk_id=%s' % (
volume_avg_10, volume_level, records[i].pk_id)
dbw.query(sql)
def computeForecast(records, categories, allpp):
count = len(records)
for i in range(0, count):
if not records[i].trend_3:
continue
if not records[i].trend_5:
continue
if not records[i].candle_sort:
continue
if not records[i].up_or_down:
continue
if not records[i].volume_level:
continue
if not records[i].jump_level:
continue
if not records[i].ma_5_10:
continue
fields = {
'trend_3': records[i].trend_3,
'trend_5': records[i].trend_5,
'candle_sort': records[i].candle_sort,
#'up_or_down':records[i].up_or_down,
'volume_level': records[i].volume_level,
'jump_level': records[i].jump_level,
'ma_5_10': records[i].ma_5_10
}
#print fields
x = computeP.run(fields, categories, allpp)
sql = 'update stock_daily set forecast=%s where pk_id=%s' % (
x[2] / x[1], records[i].pk_id)
dbw.query(sql)
def computeForecast(records,categories,allpp):
count = len(records)
for i in range(0,count):
if not records[i].trend_3:
continue
if not records[i].trend_5:
continue
if not records[i].candle_sort:
continue
if not records[i].up_or_down:
continue
if not records[i].volume_level:
continue
if not records[i].jump_level:
continue
if not records[i].ma_5_10:
continue
fields = {'trend_3':records[i].trend_3,
'trend_5':records[i].trend_5,
'candle_sort':records[i].candle_sort,
#'up_or_down':records[i].up_or_down,
'volume_level':records[i].volume_level
,'jump_level':records[i].jump_level
,'ma_5_10':records[i].ma_5_10
}
#print fields
x = computeP.run(fields,categories,allpp)
sql = 'update stock_daily set forecast=%s where pk_id=%s' % (x[2]/x[1],records[i].pk_id)
dbw.query(sql)
def computeVolume(records):
count = len(records)
for i in range(0,count):
r10 = records[i:i+10]
l = [r.volume for r in r10]
volume_avg_10 = reduce(lambda x, y: x + y, l) / len(l)
volume_p = float(records[i].volume) / volume_avg_10
#对volume进行分类
volume_level = 0
if volume_p>=3:
volume_level = 31
elif volume_p<3 and volume_p>=2:
volume_level = 21
elif volume_p<2 and volume_p>=0.5:
volume_level = 19
elif volume_p<0.5 and volume_p>=0.33:
volume_level = 12
elif volume_p<0.33:
volume_level = 13
else:
volume_level = 0
sql = 'update stock_daily set volume_avg_10=%s,volume_level=%s where pk_id=%s' % (volume_avg_10,volume_level,records[i].pk_id)
dbw.query(sql)
def tmp():
fields = [
'open_price', 'high_price', 'low_price', 'close_price', 'volume',
'raise_drop', 'raise_drop_rate', 'volume_updown', 'volume_updown_rate'
]
for f in fields:
dbw.query('ALTER TABLE date_sum_infos ADD avg_%s decimal(8,2);' % (f))
return
def computeCandle(records):
count = len(records)
for i in range(0,count):
r = records[i]
result = comm.get_candle_2(r.open,r.close,r.high,r.low)
up_or_down = 2 if result[1]>0 else 1
sql = 'update stock_daily set candle_sort=%s,up_or_down=%s where pk_id=%s' % (result[4],up_or_down,records[i].pk_id)
dbw.query(sql)
def updateP(p,count,category,feature,featureField):
cfKey = "%s|%s" % (feature,category) if featureField else (category if category else 'none')
cfKey = "%s|%s" % (cfKey,featureField)
row = list(dbr.select('category_feature_probability',where='cfKey=$cfKey',vars=locals()))
sql = "insert into category_feature_probability set probability=%s,category='%s',feature='%s',cfKey='%s',count=%s,field='%s'" % (p,category,feature,cfKey,count,featureField)
if row:
sql = "update category_feature_probability set probability=%s,category='%s',feature='%s',count=%s,field='%s' where id=%s" % (p,category,feature,count,featureField,row[0].id)
dbw.query(sql)
def update_last_high_low():
trade_dates = load_trade_dates()
today = trade_dates[0].trade_date
last_day = trade_dates[1].trade_date
sql = """update stock_daily s,
(SELECT stock_no,high_low FROM `stock_daily` where trade_date='%s' ) as last
set s.last_high_low = last.high_low
where s.trade_date='%s' and s.stock_no=last.stock_no""" % (last_day,today)
dbw.query(sql)
def computeCandle(records):
count = len(records)
for i in range(0, count):
r = records[i]
result = comm.get_candle_2(r.open, r.close, r.high, r.low)
up_or_down = 2 if result[1] > 0 else 1
sql = 'update stock_daily set candle_sort=%s,up_or_down=%s where pk_id=%s' % (
result[4], up_or_down, records[i].pk_id)
dbw.query(sql)
def update_last_high_low():
trade_dates = load_trade_dates()
today = trade_dates[0].trade_date
last_day = trade_dates[1].trade_date
sql = """update stock_daily s,
(SELECT stock_no,high_low FROM `stock_daily` where trade_date='%s' ) as last
set s.last_high_low = last.high_low
where s.trade_date='%s' and s.stock_no=last.stock_no""" % (last_day,
today)
dbw.query(sql)
def update_v2(l):
dbw.delete('stock_daily_records_tmp',where="pk_id>0",vars=locals())
dbw.supports_multiple_insert = True
dbw.multiple_insert('stock_daily_records_tmp',l)
dbw.query('''update stock_daily_records a,stock_daily_records_tmp t set
a.raise_drop=t.raise_drop,
a.raise_drop_rate=t.raise_drop_rate,
a.volume_updown=t.volume_updown,
a.volume_updown_rate=t.volume_updown_rate,
a.last_update = t.last_update
where a.pk_id=t.pk_id''')
def replace_z_records(record):
# date,stock_no,open_price,high_price,low_price,close_price,volume,amount,adj_close,
# raise_drop,raise_drop_rate,is_traday,volume_updown,volume_updown_rate,create_date,last_update
strfields = "date,stock_no,open_price,high_price,low_price,close_price,volume,amount,adj_close,raise_drop,raise_drop_rate,is_traday,volume_updown,volume_updown_rate,create_date,last_update"
fields = strfields.split(",")
sql = "replace into forecast_backup.z_%s set %s" % (
record.stock_no,
",".join(["%s='%s'" % (k, v) for k, v in record.items() if k in fields and v is not None]),
)
dbw.query(sql)
def update_v2(l):
dbw.delete('stock_daily_records_tmp', where="pk_id>0", vars=locals())
dbw.supports_multiple_insert = True
dbw.multiple_insert('stock_daily_records_tmp', l)
dbw.query('''update stock_daily_records a,stock_daily_records_tmp t set
a.raise_drop=t.raise_drop,
a.raise_drop_rate=t.raise_drop_rate,
a.volume_updown=t.volume_updown,
a.volume_updown_rate=t.volume_updown_rate,
a.last_update = t.last_update
where a.pk_id=t.pk_id''')
def create_table_sql():
l = []
for fun in funs:
for field,ftype in fields.items():
for day in days:
field_final = '%s_%s_%s' % (fun,field,day)
segment = "`%s` %s DEFAULT NULL" % (field_final,ftype)
print segment
l.append(segment)
dbw.query(''' DROP TABLE IF EXISTS `stock_sums_info` ''')
dbw.query(sql_t % (','.join(l)))
def computeTrend(records):
count = len(records)
for i in range(0,count):
if count-i>2:
t3 = comm.get_trend(records[i:i+3])
sql = 'update stock_daily set trend_3=%s where pk_id=%s' % (t3,records[i].pk_id)
dbw.query(sql)
if count-i>4:
t5 = comm.get_trend(records[i:i+5])
sql = 'update stock_daily set trend_5=%s where pk_id=%s' % (t5,records[i].pk_id)
dbw.query(sql)
def replace_z_records(record):
#date,stock_no,open_price,high_price,low_price,close_price,volume,amount,adj_close,
#raise_drop,raise_drop_rate,is_traday,volume_updown,volume_updown_rate,create_date,last_update
strfields = 'date,stock_no,open_price,high_price,low_price,close_price,volume,amount,adj_close,raise_drop,raise_drop_rate,is_traday,volume_updown,volume_updown_rate,create_date,last_update'
fields = strfields.split(',')
sql = "replace into forecast_backup.z_%s set %s" % (
record.stock_no, ','.join([
"%s='%s'" % (k, v)
for k, v in record.items() if k in fields and v is not None
]))
dbw.query(sql)
def update_avg_volume_10():
trade_dates = load_trade_dates()
today = trade_dates[0].trade_date
begin_day = trade_dates[-1].trade_date
end_day = trade_dates[1].trade_date
sql = """update stock_daily s,
(SELECT stock_no,avg(volume) as avg_volume FROM `stock_daily` where trade_date BETWEEN '%s' and '%s' group by stock_no) as avg10
set s.volume_avg_10 = avg10.avg_volume
where s.trade_date='%s' and s.stock_no=avg10.stock_no""" % (begin_day,end_day,today)
dbw.query(sql)
def computeTrend(records):
count = len(records)
for i in range(0, count):
if count - i > 2:
t3 = comm.get_trend(records[i:i + 3])
sql = 'update stock_daily set trend_3=%s where pk_id=%s' % (
t3, records[i].pk_id)
dbw.query(sql)
if count - i > 4:
t5 = comm.get_trend(records[i:i + 5])
sql = 'update stock_daily set trend_5=%s where pk_id=%s' % (
t5, records[i].pk_id)
dbw.query(sql)
def update_avg_volume_10():
trade_dates = load_trade_dates()
today = trade_dates[0].trade_date
begin_day = trade_dates[-1].trade_date
end_day = trade_dates[1].trade_date
sql = """update stock_daily s,
(SELECT stock_no,avg(volume) as avg_volume FROM `stock_daily` where trade_date BETWEEN '%s' and '%s' group by stock_no) as avg10
set s.volume_avg_10 = avg10.avg_volume
where s.trade_date='%s' and s.stock_no=avg10.stock_no""" % (
begin_day, end_day, today)
dbw.query(sql)
def updateP(p, count, category, feature, featureField):
cfKey = "%s|%s" % (feature, category) if featureField else (
category if category else 'none')
cfKey = "%s|%s" % (cfKey, featureField)
row = list(
dbr.select('category_feature_probability',
where='cfKey=$cfKey',
vars=locals()))
sql = "insert into category_feature_probability set probability=%s,category='%s',feature='%s',cfKey='%s',count=%s,field='%s'" % (
p, category, feature, cfKey, count, featureField)
if row:
sql = "update category_feature_probability set probability=%s,category='%s',feature='%s',count=%s,field='%s' where id=%s" % (
p, category, feature, count, featureField, row[0].id)
dbw.query(sql)
def run_strategy_sum(strategy_id):
sql='''update trading_strategies s,
(select strategy_id,max(earn_rate) as max_earn_rate,
min(earn_rate) as min_earn_rate,
sum(earnings)/sum(input_output)*100 as avg_earn_rate,
count(*) as trade_count from `trading_records` where strategy_id=%s and buy_or_sell=1) as ss
set s.max_earn_rate=ss.max_earn_rate,
s.min_earn_rate = ss.min_earn_rate,
s.avg_earn_rate = ss.avg_earn_rate,
s.trade_count = ss.trade_count,
s.last_update = now()
where s.pk_id=ss.strategy_id;
''' % (strategy_id)
dbw.query(sql)
def computeMA(records):
count = len(records)
for i in range(0,count):
ma5 = ma10 = 0
if count-i>=5:
l5 = [r.close for r in records[i:i+5]]
ma5 = reduce(lambda x, y: x + y , l5) / 5
if count-i>=10:
l10 = [r.close for r in records[i:i+10]]
ma10 = reduce(lambda x, y: x + y , l10) / 10
ma_5_10 = 0
if ma5<>0 and ma10<>0:
ma_5_10 = 2 if ma5>ma10 else 1
sql = 'update stock_daily set ma_5=%s,ma_10=%s,ma_5_10=%s where pk_id=%s' % (ma5,ma10,ma_5_10,records[i].pk_id)
dbw.query(sql)
def run_avg_daily(startm, endm):
fields = [
'open_price', 'high_price', 'low_price', 'close_price', 'volume',
'raise_drop', 'raise_drop_rate', 'volume_updown', 'volume_updown_rate'
]
str_what = ','.join(
['avg(%s) as avg_%s' % (field, field) for field in fields])
sql = "SELECT date,%s FROM `stock_daily_records` where date>='%s' and date<'%s' and volume>0 GROUP BY date order by date desc;" % (
str_what, startm, endm)
l = list(dbr.query(sql))
for i in l:
dbw.query(
"update date_sum_infos set %s where date='%s'" %
(','.join(['%s=%s' % (k, v)
for k, v in i.items() if k != 'date']), i.date))
def computeMA(records):
count = len(records)
for i in range(0, count):
ma5 = ma10 = 0
if count - i >= 5:
l5 = [r.close for r in records[i:i + 5]]
ma5 = reduce(lambda x, y: x + y, l5) / 5
if count - i >= 10:
l10 = [r.close for r in records[i:i + 10]]
ma10 = reduce(lambda x, y: x + y, l10) / 10
ma_5_10 = 0
if ma5 <> 0 and ma10 <> 0:
ma_5_10 = 2 if ma5 > ma10 else 1
sql = 'update stock_daily set ma_5=%s,ma_10=%s,ma_5_10=%s where pk_id=%s' % (
ma5, ma10, ma_5_10, records[i].pk_id)
dbw.query(sql)
def import_date_sums(str_date):
sql = '''SELECT 'all' as stock_plate,count(*) as stock_count,
avg(open_price) as open,avg(high_price) as high,avg(low_price) as low,avg(close_price) as close,avg(volume) as volume,avg(amount) as amount
FROM `stock_daily_records` where date='%s' ''' % (str_date)
#rows = dbr.query(sql)
#insert_date_sum(str_date,rows)
sql = '''SELECT stock_market_no as stock_plate,count(*) as stock_count,
avg(open_price) as open,avg(high_price) as high,avg(low_price) as low,avg(close_price) as close,avg(volume) as volume,avg(amount) as amount
FROM `stock_daily_records` where date='%s' and stock_market_no is not null group by stock_market_no''' % (str_date)
rows = dbr.query(sql)
insert_date_sum(str_date,rows)
sql = '''SELECT stock_market_no as stock_plate,count(*) as stock_count
FROM `stock_daily_records` where date='%s' and stock_market_no is not null and raise_drop>0 group by stock_market_no ''' % (str_date)
rows = dbr.query(sql)
for r in rows:
stock_plate = r.stock_plate
dbw.update('date_sums',price_up_count=r.stock_count, where="trade_date=$str_date and stock_plate=$stock_plate",vars=locals())
dbw.query("update date_sums set price_up_percent=price_up_count/stock_count where trade_date='%s'" % (str_date) )
def create_table(stock_no):
dbw.query("DROP TABLE IF EXISTS forecast_backup.`z_%s`;" % (stock_no))
sql = """CREATE TABLE forecast_backup.`z_%s` (
`date` date NOT NULL DEFAULT '0000-00-00',
`stock_no` varchar(10) DEFAULT NULL,
`open_price` decimal(8,2) DEFAULT NULL,
`high_price` decimal(8,2) DEFAULT NULL,
`low_price` decimal(8,2) DEFAULT NULL,
`close_price` decimal(8,2) DEFAULT NULL,
`volume` bigint(11) DEFAULT NULL,
`amount` bigint(11) DEFAULT NULL,
`adj_close` decimal(8,2) DEFAULT NULL,
`create_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`raise_drop` decimal(8,2) DEFAULT NULL,
`raise_drop_rate` decimal(7,3) DEFAULT NULL,
`is_traday` int(11) DEFAULT NULL,
`volume_updown` bigint(11) DEFAULT NULL,
`volume_updown_rate` decimal(30,2) DEFAULT NULL,
PRIMARY KEY (`date`),
KEY `volume_idx` (`volume`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;""" % (stock_no)
dbw.query(sql)
def create_table(stock_no):
dbw.query("DROP TABLE IF EXISTS forecast_backup.`z_%s`;" % (stock_no))
sql = """CREATE TABLE forecast_backup.`z_%s` (
`date` date NOT NULL DEFAULT '0000-00-00',
`stock_no` varchar(10) DEFAULT NULL,
`open_price` decimal(8,2) DEFAULT NULL,
`high_price` decimal(8,2) DEFAULT NULL,
`low_price` decimal(8,2) DEFAULT NULL,
`close_price` decimal(8,2) DEFAULT NULL,
`volume` bigint(11) DEFAULT NULL,
`amount` bigint(11) DEFAULT NULL,
`adj_close` decimal(8,2) DEFAULT NULL,
`create_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`raise_drop` decimal(8,2) DEFAULT NULL,
`raise_drop_rate` decimal(7,3) DEFAULT NULL,
`is_traday` int(11) DEFAULT NULL,
`volume_updown` bigint(11) DEFAULT NULL,
`volume_updown_rate` decimal(30,2) DEFAULT NULL,
PRIMARY KEY (`date`),
KEY `volume_idx` (`volume`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;""" % (stock_no)
dbw.query(sql)
def compute_tf_idf():
#数据量非常小的情况下可以这样用，数据量大就需要编写hadoop脚本
dbw.query("""update terms as t, (SELECT term_id, count(*) as count FROM term_doc group by term_id) as tmp
set t.count_domain = tmp.count
where t.term_id=tmp.term_id;""")
#update term's idf
r = dbw.select('subjects',what="count(*) as count") #select count(*) as count from subjects;
doc_total_count = r[0].count
dbw.query("update terms set idf_domain=LOG(%s/(count_domain+1))" % doc_total_count )
#update term's tf
dbw.query("""update term_doc as t,
(SELECT doc_id, sum(term_count) as doc_term_count FROM term_doc group by doc_id) as tmp
set t.tf = t.term_count/tmp.doc_term_count
where t.doc_id = tmp.doc_id""")
#update term's tf-idf
dbw.query("""update term_doc as td,
terms as t
set td.tf_idf = td.tf*t.idf_domain
where td.term_id = t.term_id """)
def _______________________________update_date_sum_v2(plate=0):
#速度太慢，淘汰
sql = '''update date_sum_infos a,
(SELECT date,'%s' as plate, count(pk_id) as count FROM stock_daily_records WHERE raise_drop is not null and raise_drop>0 GROUP BY date) as b
set a.price_up_count=b.count
where a.date=b.date and a.plate=b.plate;''' % (plate)
dbw.query(sql)
sql = '''update date_sum_infos a,
(SELECT date,'%s' as plate, count(pk_id) as count FROM stock_daily_records WHERE volume_updown_rate is not null and volume_updown_rate>0 GROUP BY date) as b
set a.volumn_up_count=b.count
where a.date=b.date and a.plate=b.plate;''' % (plate)
dbw.query(sql)
sql = '''update date_sum_infos set price_up_percent = price_up_count/total_count,volumn_up_percent=volumn_up_count/total_count;'''
dbw.query(sql)
def compute_tf_idf():
#数据量非常小的情况下可以这样用，数据量大就需要编写hadoop脚本
dbw.query(
"""update terms as t, (SELECT term_id, count(*) as count FROM term_doc group by term_id) as tmp
set t.count_domain = tmp.count
where t.term_id=tmp.term_id;""")
#update term's idf
r = dbw.select(
'subjects',
what="count(*) as count") #select count(*) as count from subjects;
doc_total_count = r[0].count
dbw.query("update terms set idf_domain=LOG(%s/(count_domain+1))" %
doc_total_count)
#update term's tf
dbw.query("""update term_doc as t,
(SELECT doc_id, sum(term_count) as doc_term_count FROM term_doc group by doc_id) as tmp
set t.tf = t.term_count/tmp.doc_term_count
where t.doc_id = tmp.doc_id""")
#update term's tf-idf
dbw.query("""update term_doc as td,
terms as t
set td.tf_idf = td.tf*t.idf_domain
where td.term_id = t.term_id """)
def computeFuture(records):
for r in records:
i = records.index(r)
if i>1:
prate = int((records[i-2].close - records[i-1].close) *1000 / records[i-1].close)
frange = getFutureRange(prate)
sql = 'update stock_daily set future1_prate=%s,future1_range=%s where pk_id=%s' % (prate,frange,r.pk_id)
dbw.query(sql)
if i>2:
prate = int((records[i-3].close - records[i-1].close) *1000 / records[i-1].close )
frange = getFutureRange(prate)
sql = 'update stock_daily set future2_prate=%s,future2_range=%s where pk_id=%s' % (prate,frange,r.pk_id)
dbw.query(sql)
if i>3:
prate = int((records[i-4].close - records[i-1].close) *1000 / records[i-1].close )
frange = getFutureRange(prate)
sql = 'update stock_daily set future3_prate=%s,future3_range=%s where pk_id=%s' % (prate,frange,r.pk_id)
dbw.query(sql)
def computeFuture(records):
for r in records:
i = records.index(r)
if i > 1:
prate = int((records[i - 2].close - records[i - 1].close) * 1000 /
records[i - 1].close)
frange = getFutureRange(prate)
sql = 'update stock_daily set future1_prate=%s,future1_range=%s where pk_id=%s' % (
prate, frange, r.pk_id)
dbw.query(sql)
if i > 2:
prate = int((records[i - 3].close - records[i - 1].close) * 1000 /
records[i - 1].close)
frange = getFutureRange(prate)
sql = 'update stock_daily set future2_prate=%s,future2_range=%s where pk_id=%s' % (
prate, frange, r.pk_id)
dbw.query(sql)
if i > 3:
prate = int((records[i - 4].close - records[i - 1].close) * 1000 /
records[i - 1].close)
frange = getFutureRange(prate)
sql = 'update stock_daily set future3_prate=%s,future3_range=%s where pk_id=%s' % (
prate, frange, r.pk_id)
dbw.query(sql)
def create_tables():
rows = da.stockbaseinfos.load_all_stocks()
for r in rows:
dbw.query("ALTER TABLE forecast_backup.`z_%s` ENGINE=MyISAM;" %
(r.stock_no))
def update_high_low(stock_no,rows):
rows['high_low_update_date'] = datetime.datetime.now()
sql = 'update stock_base_infos set %s where stock_no=%s' %(','.join(["%s='%s'" % (k,v) for k,v in rows.items()]),stock_no)
dbw.query( sql )
def insert_trend_data(pkid, stock_no, h5, h3, l5, l3):
dbw.query(
'replace into trend_daily(pkid,stock_no,date,high5,high3,low5,low3)values'
)
def import_trade_record(stock_no, rows):
sql = "replace into forecast_backup.z_% (date,stock_no,open_price,high_price,low_price,close_price,volume,amount,adj_close)values()" % (
stock_no)
dbw.query(sql)
def update_high_low(stock_no, rows):
rows['high_low_update_date'] = datetime.datetime.now()
sql = 'update stock_base_infos set %s where stock_no=%s' % (','.join(
["%s='%s'" % (k, v) for k, v in rows.items()]), stock_no)
dbw.query(sql)
def import_history_rows(stock_no,rows):
fields = ['date', 'open_price','high_price','low_price','close_price', 'volume','amount','adj_close']
for r in rows:
str_field_value_list = ','.join(["%s='%s'" % (k,v) for k,v in r.items() if k in fields and v is not None])
dbw.query('replace into forecast_backup.z_%s set %s' % (stock_no,str_field_value_list))
def insertRealt(term_id, doc_id):
dbw.query(
'replace into term_doc set term_id=%s,doc_id=%s,last_update=sysdate()'
% (term_id, doc_id))
def update_marketcode(date):
dbw.query("update `stock_daily_records` a, stock_base_infos b set a.stock_market_no=b.market_code where a.date='"+ date.strftime('%Y-%m-%d') +"' and a.stock_no = b.stock_no")
def import_trade_record(stock_no,rows):
sql = "replace into forecast_backup.z_% (date,stock_no,open_price,high_price,low_price,close_price,volume,amount,adj_close)values()" % (stock_no)
dbw.query(sql)
def create_tables():
rows = da.stockbaseinfos.load_all_stocks()
for r in rows:
dbw.query("ALTER TABLE forecast_backup.`z_%s` ENGINE=MyISAM;" % (r.stock_no))
def insert_trend_data(pkid,stock_no,h5,h3,l5,l3):
dbw.query('replace into trend_daily(pkid,stock_no,date,high5,high3,low5,low3)values')
def insertRealt(term_id,doc_id):
dbw.query('replace into term_doc set term_id=%s,doc_id=%s,last_update=sysdate()'%(term_id,doc_id))
def remove_daily_records(date):
results = dbr.select('stock_daily_records',where="date=$date",vars=locals())
fields = ['date','stock_no','open_price','high_price','low_price','close_price','create_date','last_update', 'volume','amount','adj_close','raise_drop','raise_drop_rate','is_traday','volume_updown','volume_updown_rate']
for r in results:
str_field_value_list = ','.join(["%s='%s'" % (k,v) for k,v in r.items() if k in fields and v is not None])
dbw.query('replace into forecast_backup.z_%s set %s' % (r.stock_no,str_field_value_list))
def term_doc_insert(user_id,term_id,doc_id,term_count):
dbw.query("replace into term_doc(user_id,term_id,doc_id,term_count)values(%s,%s,%s,%s)" % (user_id,term_id,doc_id,term_count))
def term_doc_insert(user_id, term_id, doc_id, term_count):
dbw.query(
"replace into term_doc(user_id,term_id,doc_id,term_count)values(%s,%s,%s,%s)"
% (user_id, term_id, doc_id, term_count))
