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]