def get_access_token(user_id,device_no,device_type,os_type,channel,version):
result = list(dbr.select('user_devices',
what="user_id,device_no,access_token,device_type,os_type",
where="user_id=$user_id and device_no=$device_no",vars=locals()))
access_token = generate_access_token()
if not result:
dbw.insert('user_devices',user_id=user_id,device_no=device_no,
device_type=device_type,os_type=os_type,
access_token=access_token,
channel=channel,
version=version,
creation_date=web.SQLLiteral('now()'),
last_update=web.SQLLiteral('now()'))
result = list(dbr.select('user_devices',
what="user_id,device_no,access_token,device_type,os_type",
where="user_id=$user_id and device_no=$device_no",vars=locals()))
else:
dbw.update('user_devices',
device_no=device_no,
device_type=device_type,os_type=os_type,
access_token=access_token,
channel=channel,
version=version,
last_update=web.SQLLiteral('now()'),
where="user_id=$user_id and device_no=$device_no",vars=locals())
result[0].access_token = access_token
return load_user(result[0].user_id)
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 load_user(user_id):
users = list(dbr.select(tname,what="pk_id,mobile,email,nick_name,password",where="pk_id=$user_id",vars=locals()))
tokens = list(dbr.select('user_devices',
what="user_id,device_no,access_token,device_type,os_type",
where="user_id=$user_id",vars=locals()))
if users and tokens:
users[0].device_no = tokens[0].device_no
users[0].access_token = tokens[0].access_token
users[0].device_type = tokens[0].device_type
users[0].os_type = tokens[0].os_type
users[0].password =u'high'
users[0].user_id = tokens[0].user_id
return users[0]
return False
def load_dates(plate=0):
return [
r.date for r in dbr.select('date_sum_infos',
what="distinct date",
where="plate=$plate",
vars=locals())
]
def load_all_last_5():
#加载过去5天内股票
#SELECT * FROM `stock_daily_records` WHERE TO_DAYS(NOW())-TO_DAYS(date) < 8 and volume<>0 order by stock_no,date desc
results = dbr.select('stock_daily_records',
where="TO_DAYS(NOW())-TO_DAYS(date) < 8 and volume<>0", #考虑到排除周末情况？
order="date desc",vars=locals())
return list(results)
def load_all_stocks():
return list(dbr.select('stock_base_infos',
what='pk_id,stock_no,market_code,market_code_yahoo,pinyin2,trade_date',
where="days<>0 and market_code<>'sb'",
#where="market_code_yahoo in ('ss','sz')",
#offset=0,limit=1,
order="market_code,stock_no"))
def load_daily_records():
#SELECT * FROM `stock_daily_records` order by date asc limit 0,1000 ;
results = dbr.select('stock_daily_records',
order='date asc',
limit=1000,
offset=0)
return list(results)
def load_trade_dates_range():
r = dbr.select(
'stock_daily_records',
what=
'max(date) as max_date,min(date) as min_date, (TO_DAYS(max(date)) - TO_DAYS(min(date))) as days '
)
return r[0]
def load_stock_dates(stock_no):
rows = dbr.select('stock_daily_records',
what='date',
where="stock_no=$stock_no",
vars=locals())
dates = [row.date.strftime('%Y-%m-%d') for row in rows]
return dates
def load_dates(stock_no):
return list(
dbr.select('stock_daily',
what="trade_date",
where='stock_no=$stock_no',
order='trade_date desc',
vars=locals()))
def load_terms(terms):
rows = list(
dbr.select('terms',
what="term_id,term,idf_domain",
where="term in $terms",
vars=locals()))
return rows
def load_doc_ids(term_id):
rows = list(
dbr.select('term_doc',
what="doc_id",
where="term_id=$term_id",
vars=locals()))
return [r.doc_id for r in rows]
def run():
stocks = dbr.select('stock_base_infos',what='stock_no,market_code,market_code_yahoo,pinyin2')
for s in stocks:
try:
update_stock_high_low(s.stock_no)
except Exception ,e:
loger.error(s.stock_no + " " + str(e))
def validate_token(user_id, access_token):
result = list(
dbr.select('user_devices',
what="user_id,device_no,device_type,os_type",
where="user_id=$user_id and access_token=$access_token",
vars=locals()))
return result
def load_stocks_date(date):
li = list(
dbr.select('stock_daily_records',
what="stock_no,raise_drop,volume_updown_rate",
where="date=$date and volume>0",
order='stock_no',
vars=locals()))
print li
total_count = len(li)
if total_count == 0:
return False
print total_count
price_raise_sum = sum([r.raise_drop for r in li if r.raise_drop > 0
]) / total_count * 100
price_up_count = len([stock for stock in li if stock.raise_drop > 0])
volumn_up_count = len(
[stock for stock in li if stock.volume_updown_rate > 0])
price_up_percent = float(price_up_count) / float(total_count) * 100
volumn_up_percent = float(volumn_up_count) / float(total_count) * 100
print price_up_count, volumn_up_count, price_up_percent, volumn_up_percent
return web.storage(date=date,
total_count=total_count,
price_up_count=price_up_count,
volumn_up_count=volumn_up_count,
price_up_percent=price_up_percent,
volumn_up_percent=volumn_up_percent,
price_raise_sum=price_raise_sum)
def load_by_name(name):
result = list(
dbr.select(tname,
what="pk_id,nick_name,password",
where="mobile=$name or email=$name",
vars=locals()))
return result[0] if result else False
def _________________load_all_dates():
#查询速度太慢
return dbr.select('stock_daily_records',
what="date,count(pk_id) as count",
where="volume>0",
group="date",
order="date desc")
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 load_page3(cust_id,min_pk_id,limit):
rows = list(dbr.select(table_name,
what= list_fields,
where="user_id=$cust_id and pk_id>$min_pk_id",
order="pk_id",
offset=0,limit=limit,
vars=locals()))
return rows
def load_page2(cust_id,offset,limit):
rows = list(dbr.select(table_name,
what= list_fields,
where="user_id=$cust_id",
order="pk_id asc",
offset=offset,limit=limit,
vars=locals()))
return rows
def term_insert(term):
tname = 'terms'
result = list(dbr.select(tname, where="term=$term", vars=locals()))
if not result:
return dbw.insert(tname,
term=term,
term_len=len(term),
last_update=web.SQLLiteral('now()'))
return result[0].term_id
def load_for_buy(date):
return list(
dbr.select(
'stock_daily',
what="stock_no",
where=
"trade_date=$date and volume<>0 and trend_3=321 and range_3>5",
order="range_3 desc",
vars=locals()))
def auth(name,password,deive_no,device_type):
result = list(dbr.select(tname,what="pk_id,nick_name,password",where="email=$name",vars=locals()))
if result:
if result[0].password == password:
return result[0]
#user_devices, user_id=$user_id and device_no=$deive_no
#是否存在，
#如果存在，accesss_token是否过期
return False
def load_all_stocks():
return list(
dbr.select(
'stock_base_infos',
what=
'pk_id,stock_no,market_code,market_code_yahoo,pinyin2,trade_date',
where="market_code<>'sb'",
#where="market_code_yahoo in ('ss','sz')",
#offset=0,limit=1,
order="market_code,stock_no"))
def check_code(user_id,val_sort,val_account,val_code):
result = list(dbr.select(tname,what="pk_id,user_id,validate_sort,validate_account",
where="user_id=$user_id and validate_sort=$val_sort and validate_code=$val_code and code_status=1",
vars=locals()))
if result:
pk_id = result[0].pk_id
if result[0].validate_account == val_account:
update_code_status(pk_id,2)
return result[0]
return False
def GET(self):
rows = list(dbr.select('stock_daily',what='candle_sort,range_1,range_2,range_3',
where="volume>0 and stock_no='002639'",
offset=0,limit=2000,order="trade_date asc",vars=locals()))
for r in rows:
r.updown = "up" if r.range_2>0 else "down"
print r.range_2
r = web.storage(rows=rows,query='',count=len(rows))
return render.candle(r)
def load_user(user_id):
users = list(
dbr.select(tname,
what="pk_id,mobile,email,nick_name,password",
where="pk_id=$user_id",
vars=locals()))
tokens = list(
dbr.select('user_devices',
what="user_id,device_no,access_token,device_type,os_type",
where="user_id=$user_id",
vars=locals()))
if users and tokens:
users[0].device_no = tokens[0].device_no
users[0].access_token = tokens[0].access_token
users[0].device_type = tokens[0].device_type
users[0].os_type = tokens[0].os_type
users[0].password = u'high'
users[0].user_id = tokens[0].user_id
return users[0]
return False
def load_max_min(stock_no,days):
result = {}
high = list(dbr.select('stock_daily_records',
what="date,high_price",
where="stock_no=$stock_no and volume<>0 and TO_DAYS(NOW())-TO_DAYS(date) < $days",
offset=0,limit=1,
order="high_price desc",
vars=locals()))
if high:
result['high_date_%s'%(days)]=high[0].date
result['high_price_%s'%(days)]=high[0].high_price
low = list(dbr.select('stock_daily_records',
what="date,low_price",
where="stock_no=$stock_no and volume<>0 and TO_DAYS(NOW())-TO_DAYS(date) < $days",
offset=0,limit=1,
order="low_price asc",
vars=locals()))
if low:
result['low_date_%s'%(days)]=low[0].date
result['low_price_%s'%(days)]=low[0].low_price
high_v = list(dbr.select('stock_daily_records',
what="date,volume",
where="stock_no=$stock_no and volume<>0 and TO_DAYS(NOW())-TO_DAYS(date) < $days",
offset=0,limit=1,
order="high_price desc",
vars=locals()))[0]
low_v = list(dbr.select('stock_daily_records',
what="date,volume",
where="stock_no=$stock_no and volume<>0 and TO_DAYS(NOW())-TO_DAYS(date) < $days",
offset=0,limit=1,
order="low_price asc",
vars=locals()))[0]
return result
def get_access_token(user_id, device_no, device_type, os_type, channel,
version):
result = list(
dbr.select('user_devices',
what="user_id,device_no,access_token,device_type,os_type",
where="user_id=$user_id and device_no=$device_no",
vars=locals()))
access_token = generate_access_token()
if not result:
dbw.insert('user_devices',
user_id=user_id,
device_no=device_no,
device_type=device_type,
os_type=os_type,
access_token=access_token,
channel=channel,
version=version,
creation_date=web.SQLLiteral('now()'),
last_update=web.SQLLiteral('now()'))
result = list(
dbr.select(
'user_devices',
what="user_id,device_no,access_token,device_type,os_type",
where="user_id=$user_id and device_no=$device_no",
vars=locals()))
else:
dbw.update('user_devices',
device_no=device_no,
device_type=device_type,
os_type=os_type,
access_token=access_token,
channel=channel,
version=version,
last_update=web.SQLLiteral('now()'),
where="user_id=$user_id and device_no=$device_no",
vars=locals())
result[0].access_token = access_token
return load_user(result[0].user_id)
def auth(name, password, deive_no, device_type):
result = list(
dbr.select(tname,
what="pk_id,nick_name,password",
where="email=$name",
vars=locals()))
if result:
if result[0].password == password:
return result[0]
#user_devices, user_id=$user_id and device_no=$deive_no
#是否存在，
#如果存在，accesss_token是否过期
return False
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 check_code(user_id, val_sort, val_account, val_code):
result = list(
dbr.select(
tname,
what="pk_id,user_id,validate_sort,validate_account",
where=
"user_id=$user_id and validate_sort=$val_sort and validate_code=$val_code and code_status=1",
vars=locals()))
if result:
pk_id = result[0].pk_id
if result[0].validate_account == val_account:
update_code_status(pk_id, 2)
return result[0]
return False
def GET(self):
rows = list(
dbr.select('stock_daily',
what='candle_sort,range_1,range_2,range_3',
where="volume>0 and stock_no='002639'",
offset=0,
limit=2000,
order="trade_date asc",
vars=locals()))
for r in rows:
r.updown = "up" if r.range_2 > 0 else "down"
print r.range_2
r = web.storage(rows=rows, query='', count=len(rows))
return render.candle(r)
def load_page(cust_id,offset,limit):
rows = list(dbr.select(table_name,
what= list_fields,
where="user_id=$cust_id and is_delete=0",
order="pk_id desc",
offset=offset,limit=limit,
vars=locals()))
r = {}
for row in rows:
day = row.created_date.strftime('%Y-%m-%d')
if day in r:
r[day].append(row)
else:
r[day]=[row]
return sorted(r.iteritems(), key=lambda k:k[0], reverse=True)
def update_date_sum(date, data, plate=0):
rows = list(
dbr.select('date_sum_infos',
where="date=$date and plate=$plate",
vars=locals()))
if len(rows) == 0:
dbw.insert('date_sum_infos',
date=date,
plate=plate,
create_date=web.SQLLiteral("NOW()"),
last_update=web.SQLLiteral("NOW()"))
dbw.update(
'date_sum_infos',
total_count=data.total_count,
price_up_count=data.price_up_count,
volumn_up_count=data.volumn_up_count,
price_up_percent=data.price_up_percent,
volumn_up_percent=data.volumn_up_percent,
# price_raise_sum = data.price_raise_sum,
where="date=$date and plate=$plate",
vars=locals())
def validate_token(user_id,access_token):
result = list(dbr.select('user_devices',
what="user_id,device_no,device_type,os_type",
where="user_id=$user_id and access_token=$access_token",
vars=locals()))
return result
def load_all(): return dbr.select(tname, what="pk_id,term,idf",where="CHAR_LENGTH(term)>1",order="count desc")
def load_best_terms(): return list(dbr.select(tname, what="pk_id,term",where="count>1 and sogou_tf_idf is not null",order="sogou_tf_idf desc"))
def load_by_name(name):
result = list(dbr.select(tname,what="pk_id,nick_name,password",where="mobile=$name or email=$name",vars=locals()))
return result[0] if result else False
def load_sogou_terms(): return dbr.select(tname,where="CHAR_LENGTH(term)>1 and sogou_ix_count is null")
def load_term_id(term): rows = list(dbr.select(tname,what="pk_id",where="term=$term",vars=locals())) return rows[0].pk_id if rows else 0
def load_all(offset,limit=100):
return list(dbr.select(table_name,order="pk_id desc",offset=offset,limit=limit))
def load_by_date(user_id,date):
return list(dbr.select(table_name,what="pk_id,subject,body,task_status",
where='user_id=$user_id and date(plan_start_date)=$date',
order="pk_id",vars=locals()))
def load_by_ids(pk_ids):
rows = list(dbr.select(table_name,what="pk_id,body",where='pk_id in $pk_ids' , vars=locals()))
return rows
def load_by_id(pk_id):
rows = list(dbr.select(table_name,where='pk_id=$pk_id' , vars=locals()))
return rows[0] if rows else False
def load_count(user_id):
r = dbr.select(table_name,what="count(*) as count",where="user_id=$user_id",vars=locals())
return r[0].count
#update subjects a, subjects_old b set a.terms = b.terms where a.pk_id=b.pk_id and b.terms is not null;
def load_has_sogou_terms(): return list(dbr.select(tname,where="sogou_ix_count is not null"))
def load_all(): return list(dbr.select(tname,what="term,idf"))
def load_all_stock_nos():
results = dbr.select('stock_base_infos',what='stock_no')
l = [r.stock_no for r in results]
return l
def load_daily_records():
# SELECT * FROM `stock_daily_records` order by date asc limit 0,1000 ;
results = dbr.select("stock_daily_records", order="date asc", limit=1000, offset=0)
return list(results)
def load_doc_ids(term_id):
rows = list(dbr.select('term_doc',what="doc_id",where="term_id=$term_id",vars=locals()))
return [r.doc_id for r in rows]