def remove_v2():
while True:
results = list(dbr.query("select date FROM `stock_daily_records` order by date limit 300;"))
print results[-1].date
if results[-1].date == datetime.date(2012, 6, 1):
break
dbr.query("delete FROM `stock_daily_records` order by date limit 300;")
def remove_v2():
while True:
results = list(
dbr.query(
"select date FROM `stock_daily_records` order by date limit 300;"
))
print results[-1].date
if results[-1].date == datetime.date(2012, 6, 1):
break
dbr.query(
"delete FROM `stock_daily_records` order by date limit 300;")
def load_subjects(user_id,terms,offset,size):
terms = load_terms(terms)
total_weight = sum([r.idf_domain for r in terms])
for r in terms:
r.weight = r.idf_domain / total_weight
term_ids = [str(r.term_id) for r in terms]
r = dbr.query("""select s.pk_id,user_id,s.body,s.created_date,s.last_update,s.app_created_date,s.local_id,s.is_delete,s.is_todo,s.is_remind,s.plan_start_date,s.task_status,s.remind_datetime,s.remind_frequency,s.remind_next,s.closed_date from
(SELECT doc_id,sum(tf_idf) as tf_idf FROM term_doc where user_id=%s and term_id in (%s) group by doc_id) as t
left join subjects s on t.doc_id = s.pk_id
order by t.tf_idf desc limit 0,200""" %(str(user_id),','.join(term_ids)) )
return list(r)
doc_terms = list(dbr.select('term_doc',what="doc_id,term_id,tf,tf_idf",
where="user_id=$user_id and term_id in $term_ids",vars=locals()))
max_tf_idf = max([t.tf_idf for t in doc_terms])
doc_ids = list(set([t.doc_id for t in doc_terms]))
doc_weights = {}
for dt in doc_terms:
term_weight = [t.weight for t in terms if t.term_id==dt.term_id][0]
dweight = (dt.tf_idf / max_tf_idf) * (term_weight/total_weight)
if dt.doc_id in doc_weights:
doc_weights[dt.doc_id] = doc_weights[dt.doc_id] + dweight
else:
doc_weights[dt.doc_id] = dweight
subjects = list(dbr.select('subjects',
what="pk_id,user_id,body,created_date,last_update,local_id",
where="user_id=$user_id and pk_id in $doc_ids",vars=locals()))
for s in subjects:
s.weight = doc_weights[s.pk_id]
#tmp = [(k,v) for k,v in doc_weights.items()]
subjects.sort(cmp=lambda x,y:cmp(y.weight,x.weight))
print subjects
return subjects
for k,v in result.items():
result[k].weight = v.idf_domain / total_weight
#term_ids = [str(k.term_id) for k in result.values()]
#compute 匹配度
rows = list(dbr.select('term_doc',what="doc_id",where="user_id=$user_id and term_id in $term_ids",vars=locals()))
doc_ids = [r.doc_id for r in rows]
subjects = list(dbr.select('subjects',
what="pk_id,user_id,body,created_date,last_update,local_id",
where="user_id=$user_id and pk_id in $doc_ids",vars=locals()))
return subjects
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 POST(self):
i = web.input(sql="")
rows = list(dbr.query(i.sql))
l = []
for r in rows:
code = comm.get_market_codes(r.stock_no)
if not code['pinyin'] : continue
l.append(web.storage(stock_no=r.stock_no,pinyin2=code['pinyin']) )
r = web.storage(stocks=l,query=i.sql,count = len(l))
return render.index(r)
def POST(self):
i = web.input(sql="")
rows = list(dbr.query(i.sql))
l = []
for r in rows:
code = comm.get_market_codes(r.stock_no)
if not code['pinyin']: continue
l.append(web.storage(stock_no=r.stock_no, pinyin2=code['pinyin']))
r = web.storage(stocks=l, query=i.sql, count=len(l))
return render.index(r)
def getCategories(): #where %s is not null
sql = """select %s,count(*) as count from %s group by %s """ % (categoryField,tableName,categoryField)
l = list(dbr.query(sql))
print l
count_sum = sum([r.count for r in l])
d = {}
for i in l:
if not i[categoryField]:
continue #none的情况
probability = float(i.count)/count_sum
cat = i[categoryField]
d[cat] = web.storage(category=cat,count=i.count,probability=probability)
updateP(probability,i.count,cat,'','')
return d
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 computeOneFeatue(categories,field):
#where category is not null and feature1 is not null
sql="select %s,%s,count(*) as count from %s where %s is not null and %s is not null group by %s,%s" % (field,categoryField,tableName,field,categoryField,field,categoryField)
l = list(dbr.query(sql))
total_count = sum([r.count for r in l])
features = list(set([r[field] for r in l]))
d={}
for cat,catV in categories.items():
for f in features:
if not f:
continue #none的情况?
f_count = sum([r.count for r in l if r[categoryField]==cat and r[field]==f])
probability = float(f_count)/catV.count
d['%s|%s'%(f,cat)] = web.storage(probability=probability,count=f_count)
updateP(probability,f_count,cat,f,field)
return d
def getCategories(): #where %s is not null
sql = """select %s,count(*) as count from %s group by %s """ % (
categoryField, tableName, categoryField)
l = list(dbr.query(sql))
print l
count_sum = sum([r.count for r in l])
d = {}
for i in l:
if not i[categoryField]:
continue #none的情况
probability = float(i.count) / count_sum
cat = i[categoryField]
d[cat] = web.storage(category=cat,
count=i.count,
probability=probability)
updateP(probability, i.count, cat, '', '')
return d
def computeOneFeatue(categories, field):
#where category is not null and feature1 is not null
sql = "select %s,%s,count(*) as count from %s where %s is not null and %s is not null group by %s,%s" % (
field, categoryField, tableName, field, categoryField, field,
categoryField)
l = list(dbr.query(sql))
total_count = sum([r.count for r in l])
features = list(set([r[field] for r in l]))
d = {}
for cat, catV in categories.items():
for f in features:
if not f:
continue #none的情况?
f_count = sum([
r.count for r in l if r[categoryField] == cat and r[field] == f
])
probability = float(f_count) / catV.count
d['%s|%s' % (f, cat)] = web.storage(probability=probability,
count=f_count)
updateP(probability, f_count, cat, f, field)
return d
def get_plate_cout():
sql = "SELECT market_code,count(*) as count FROM `stock_base_infos` where high_date_188=trade_date and market_code<>'sb' group by market_code order by count(*) desc;"
return list(dbr.query(sql))
def get_last_count(field_name):
sql = "SELECT count(*) as count FROM `stock_base_infos` where %s=trade_date;" % (field_name)
return list(dbr.query(sql))[0].count
def load_subjects(user_id, terms, offset, size):
terms = load_terms(terms)
total_weight = sum([r.idf_domain for r in terms])
for r in terms:
r.weight = r.idf_domain / total_weight
term_ids = [str(r.term_id) for r in terms]
r = dbr.query(
"""select s.pk_id,user_id,s.body,s.created_date,s.last_update,s.app_created_date,s.local_id,s.is_delete,s.is_todo,s.is_remind,s.plan_start_date,s.task_status,s.remind_datetime,s.remind_frequency,s.remind_next,s.closed_date from
(SELECT doc_id,sum(tf_idf) as tf_idf FROM term_doc where user_id=%s and term_id in (%s) group by doc_id) as t
left join subjects s on t.doc_id = s.pk_id
order by t.tf_idf desc limit 0,200""" %
(str(user_id), ','.join(term_ids)))
return list(r)
doc_terms = list(
dbr.select('term_doc',
what="doc_id,term_id,tf,tf_idf",
where="user_id=$user_id and term_id in $term_ids",
vars=locals()))
max_tf_idf = max([t.tf_idf for t in doc_terms])
doc_ids = list(set([t.doc_id for t in doc_terms]))
doc_weights = {}
for dt in doc_terms:
term_weight = [t.weight for t in terms if t.term_id == dt.term_id][0]
dweight = (dt.tf_idf / max_tf_idf) * (term_weight / total_weight)
if dt.doc_id in doc_weights:
doc_weights[dt.doc_id] = doc_weights[dt.doc_id] + dweight
else:
doc_weights[dt.doc_id] = dweight
subjects = list(
dbr.select('subjects',
what="pk_id,user_id,body,created_date,last_update,local_id",
where="user_id=$user_id and pk_id in $doc_ids",
vars=locals()))
for s in subjects:
s.weight = doc_weights[s.pk_id]
#tmp = [(k,v) for k,v in doc_weights.items()]
subjects.sort(cmp=lambda x, y: cmp(y.weight, x.weight))
print subjects
return subjects
for k, v in result.items():
result[k].weight = v.idf_domain / total_weight
#term_ids = [str(k.term_id) for k in result.values()]
#compute 匹配度
rows = list(
dbr.select('term_doc',
what="doc_id",
where="user_id=$user_id and term_id in $term_ids",
vars=locals()))
doc_ids = [r.doc_id for r in rows]
subjects = list(
dbr.select('subjects',
what="pk_id,user_id,body,created_date,last_update,local_id",
where="user_id=$user_id and pk_id in $doc_ids",
vars=locals()))
return subjects
def __get_plate_cout(field_name):
sql = """SELECT market_code,count(*) as %s_count FROM `stock_base_infos`
where %s=trade_date and %s
group by market_code
order by count(*) desc;""" % (field_name, field_name, comm_codition)
return list(dbr.query(sql))
def load_trade_dates():
sql = "SELECT DISTINCT trade_date FROM `stock_daily` ORDER BY trade_date desc limit 0,10"
return list(dbr.query(sql))
def get_plate_cout():
sql = "SELECT market_code,count(*) as count FROM `stock_base_infos` where high_date_188=trade_date and market_code<>'sb' group by market_code order by count(*) desc;"
return list(dbr.query(sql))
def get_all_avg():
sql = "select 'ALL' as market_code,count(*) as count,%s from `stock_base_infos` where %s ;" % (
sql_seg, comm_codition)
return list(dbr.query(sql))
def close_high_than_open():
sql="select market_code,count(*) as cho_count from `stock_base_infos` where %s and close>open group by market_code;" % (comm_codition)
return list(dbr.query(sql))
def get_all_avg():
sql="select 'ALL' as market_code,count(*) as count,%s from `stock_base_infos` where %s ;" % (sql_seg,comm_codition)
return list(dbr.query(sql))
def get_market_avg():
sql="select market_code,count(*) as count,%s from `stock_base_infos` where %s group by market_code;" % (sql_seg,comm_codition)
return list(dbr.query(sql))
def getFeatures(featureField):
sql = "select distinct %s from %s " % (featureField, tableName)
return [r[featureField] for r in dbr.query(sql)]
def load_trade_dates():
sql = "SELECT DISTINCT trade_date FROM `stock_daily` ORDER BY trade_date desc limit 0,10"
return list(dbr.query(sql))
def get_market_avg():
sql = "select market_code,count(*) as count,%s from `stock_base_infos` where %s group by market_code;" % (
sql_seg, comm_codition)
return list(dbr.query(sql))
def getFeatures(featureField):
sql = "select distinct %s from %s " % (featureField,tableName)
return [r[featureField] for r in dbr.query(sql)]
def get_last_count(field_name):
sql = "SELECT count(*) as count FROM `stock_base_infos` where %s=trade_date;" % (
field_name)
return list(dbr.query(sql))[0].count
def __get_plate_cout(field_name):
sql="""SELECT market_code,count(*) as %s_count FROM `stock_base_infos`
where %s=trade_date and %s
group by market_code
order by count(*) desc;""" % (field_name,field_name,comm_codition)
return list(dbr.query(sql))
def close_high_than_open():
sql = "select market_code,count(*) as cho_count from `stock_base_infos` where %s and close>open group by market_code;" % (
comm_codition)
return list(dbr.query(sql))