Python-web: Flask 应用、数据库查询数据、API接口
来源：互联网 发布：韦德体测数据 编辑：程序博客网 时间：2024/05/21 21:45
一个超简单的Flask应用helloword
from flask import Flaskapp = Flask(__name__)@app.route('/')def hello_world(): return 'Hello World!'if __name__ == '__main__': app.run()数据库查询、与数据API
关于对数据库查询操作可参考：Python:数据库操作模块SQLAlchemy
查询
#coding=utf-8from flask import Flask,request,url_for,g,render_template,session,redirect,flash,jsonifyfrom flask.ext.sqlalchemy import SQLAlchemyfrom threading import Threadfrom collections import defaultdictimport configimport jsonObjectsimport osimport sqlalchemyimport json#import datetime 与kbredisclient2 重复from jsonCoder import MyDecoder,MyEncoderfrom kbredisclient2 import *app = Flask(__name__)app.config['SQLALCHEMY_DATABASE_URI']='mysql://root:root@10.240.123.104:3306/db_news_news_report?charset=utf8'app.config.from_object(config)app.secret_key=app.config['SECERT_KEY']db = SQLAlchemy(app)engine = sqlalchemy.create_engine('mysql://root:root@10.240.123.104:3306/db_news_news_report?charset=utf8', echo=True)DB_Session =sqlalchemy.orm.sessionmaker(bind=engine)conn = DB_Session()opath='/data/wwwroot/htdocs/vitamin/reason_index'merge_reasons=defaultdict(list)for f in os.listdir(opath): p=opath+'/'+f if os.path.isfile(p): with open(p) as fin: for line in fin.readlines(): merge_reasons[f].append(line.strip())from models import contactfrom models import HourStatic,DayStatic,RHourStatic,RDayStatic,WholeStatic,HourWholeStatic,DayWholeStatic,RHourWholeStatic@app.route('/showdata/<ip>/<reason>/<start>/<end>/<tp>',methods=['POST','GET'])def showdata(ip,reason,start,end,tp): res={} ret=200 Message='Successful return' data={} title={} showstitle='展示数' clickstitle='点击数' ctrtitle='点击率' sratetitle='展示占比' title['shows']=showstitle title['clicks']=clickstitle title['ctr']=ctrtitle title['srate']=sratetitle obj=getQueryResult(ip,reason,start,end,tp) clicks=obj['data']['clicks'] ctr=obj['data']['ctr'] srate=obj['data']['srate'] times=obj['data']['times'] shows=obj['data']['shows'] if(len(ctr)<=0 and len(srate)<=0): ret=201 Message='Error' data['shows']=shows data['clicks']=clicks data['ctr']=ctr data['srate']=srate data['times']=times res['ret']=ret res['message']=Message res['data']=data res['title']=title maxv=1 if len(ctr)>0: if len(ctr)==1: maxv=max(maxv,ctr[0]) else: maxv=max(maxv,max(ctr)) if len(srate)>0: if len(clicks)==1: maxv=max(maxv,srate[0]) else: maxv=max(maxv,max(srate)) minv=0 maxv=maxv*1.2 gap=maxv/10 axis={} axis['max']=maxv axis['min']=minv axis['gap']=gap res['axis']=axis return json.dumps(res,indent=4,sort_keys=True)@app.route('/test/<ip>/<reason>/<start>/<end>/<tp>',methods=['POST','GET'])def getQueryResult(ip,reason,start,end,tp): Message='Successful return' title={} showstitle='展示数' clickstitle='点击数' ctrtitle='点击率' sratetitle='展示占比' title['shows']=showstitle title['clicks']=clickstitle title['ctr']=ctrtitle title['srate']=sratetitle data={} args=[] ip=ip.strip() reason=reason.strip() start=start.strip() start=start.replace('_',' ') end=end.strip() end=end.replace('_',' ') tp=tp.strip() ips=ip.split(',') if('all' not in ips): args.append(ip) if('all' not in reason): args.append(reason) args.append(start) args.append(end) args.append(tp) shows=[] clicks=[] ctr=[] srate=[] times=[] qres1=None qres2=None items=None data['ips']=ips if len(args)==5: if args[4]!='h' and args[4]!='d': Message='select type error' if(args[4]=='h' and Message=='Successful return'): if not merge_reasons.has_key(reason): qres1=db.session.query(HourStatic.static_time,sqlalchemy.sql.func.sum(HourStatic.shownum),sqlalchemy.sql.func.sum(HourStatic.clicknum)).filter(HourStatic.ip.in_(ips),HourStatic.reason==args[1],HourStatic.static_time.between(args[2],args[3])).group_by(HourStatic.static_time).order_by(sqlalchemy.asc(HourStatic.static_time)).all() else: qres1=db.session.query(HourStatic.static_time,sqlalchemy.sql.func.sum(HourStatic.shownum),sqlalchemy.sql.func.sum(HourStatic.clicknum)).filter(HourStatic.ip.in_(ips),HourStatic.reason.in_(merge_reasons[reason]),HourStatic.static_time.between(args[2],args[3])).group_by(HourStatic.static_time).order_by(sqlalchemy.asc(HourStatic.static_time)).all() qres2=db.session.query(HourWholeStatic.static_time,sqlalchemy.sql.func.sum(HourWholeStatic.shownum)).filter(HourWholeStatic.ip.in_(ips),HourWholeStatic.static_time.between(args[2],args[3])).group_by(HourWholeStatic.static_time).group_by(HourWholeStatic.static_time).order_by(sqlalchemy.asc(HourWholeStatic.static_time)).all() elif(args[4]=='d' and Message=='Successful return'): if not merge_reasons.has_key(reason): qres1=db.session.query(DayStatic.static_date,sqlalchemy.sql.func.sum(DayStatic.shownum),sqlalchemy.sql.func.sum(DayStatic.clicknum)).filter(DayStatic.ip.in_(ips),DayStatic.reason==args[1],DayStatic.static_date.between(args[2],args[3])).group_by(DayStatic.static_date).order_by(sqlalchemy.asc(DayStatic.static_date)).all() else: qres1=db.session.query(DayStatic.static_date,sqlalchemy.sql.func.sum(DayStatic.shownum),sqlalchemy.sql.func.sum(DayStatic.clicknum)).filter(DayStatic.ip.in_(ips),DayStatic.reason.in_(merge_reasons[reason]),DayStatic.static_date.between(args[2],args[3])).group_by(DayStatic.static_date).order_by(sqlalchemy.asc(DayStatic.static_date)).all() qres2=db.session.query(DayWholeStatic.static_date,sqlalchemy.sql.func.sum(DayWholeStatic.shownum)).filter(DayWholeStatic.ip.in_(ips),DayWholeStatic.static_date.between(args[2],args[3])).group_by(DayWholeStatic.static_date).order_by(sqlalchemy.asc(DayWholeStatic.static_date)).all() elif len(args)==4: if args[3]!='h' and args[3]!='d': Message='select type error' if(ip=='all'): if(args[3]=='h' and Message=='Successful return'): if not merge_reasons.has_key(reason): qres1=db.session.query(RHourStatic.static_time,RHourStatic.shownum,RHourStatic.clicknum).filter(RHourStatic.reason==args[0],RHourStatic.static_time.between(args[1],args[2])).order_by(sqlalchemy.asc(RHourStatic.static_time)).all() else: qres1=db.session.query(RHourStatic.static_time,sqlalchemy.sql.func.sum(RHourStatic.shownum),sqlalchemy.sql.func.sum(RHourStatic.clicknum)).filter(RHourStatic.reason.in_(merge_reasons[reason]),RHourStatic.static_time.between(args[1],args[2])).group_by(RHourStatic.static_time).order_by(sqlalchemy.asc(RHourStatic.static_time)).all() qres2=db.session.query(RHourWholeStatic.static_time,RHourWholeStatic.shownum).filter(RHourWholeStatic.static_time.between(args[1],args[2])).order_by(sqlalchemy.asc(RHourWholeStatic.static_time)).all() elif(args[3]=='d' and Message=='Successful return'): if not merge_reasons.has_key(reason): qres1=db.session.query(RDayStatic.static_date,RDayStatic.shownum,RDayStatic.clicknum).filter(RDayStatic.reason==args[0],RDayStatic.static_date.between(args[1],args[2])).order_by(sqlalchemy.asc(RDayStatic.static_date)).all() else: qres1=db.session.query(RDayStatic.static_date,sqlalchemy.sql.func.sum(RDayStatic.shownum),sqlalchemy.sql.func.sum(RDayStatic.clicknum)).filter(RDayStatic.reason.in_(merge_reasons[reason]),RDayStatic.static_date.between(args[1],args[2])).group_by(RDayStatic.static_date).order_by(sqlalchemy.asc(RDayStatic.static_date)).all() qres2=db.session.query(WholeStatic.static_date,WholeStatic.shownum).filter(WholeStatic.static_date.between(args[1],args[2])).order_by(sqlalchemy.asc(WholeStatic.static_date)).all() elif(reason=='all'): if(args[3]=='h' and Message=='Successful return'): items=db.session.query(HourWholeStatic.static_time,sqlalchemy.sql.func.sum(HourWholeStatic.shownum),sqlalchemy.sql.func.sum(HourWholeStatic.clicknum)).filter(HourWholeStatic.ip.in_(ips),HourWholeStatic.static_time.between(args[1],args[2])).group_by(HourWholeStatic.static_time).order_by(sqlalchemy.asc(HourWholeStatic.static_time)).all() elif(args[3]=='d' and Message=='Successful return'): items=db.session.query(DayWholeStatic.static_date,sqlalchemy.sql.func.sum(DayWholeStatic.shownum),sqlalchemy.sql.func.sum(DayWholeStatic.clicknum)).filter(DayWholeStatic.ip.in_(ips),DayWholeStatic.static_date.between(args[1],args[2])).group_by(DayWholeStatic.static_date).order_by(sqlalchemy.asc(DayWholeStatic.static_date)).all() elif len(args)==3: if args[2]=='d': items=db.session.query(WholeStatic.static_date,WholeStatic.shownum,WholeStatic.clicknum).filter(WholeStatic.static_date.between(args[0],args[1])).order_by(sqlalchemy.asc(WholeStatic.static_date)).all() elif args[2]=='h': items=db.session.query(RHourWholeStatic.static_time,RHourWholeStatic.shownum,RHourWholeStatic.clicknum).filter(RHourWholeStatic.static_time.between(args[0],args[1])).order_by(sqlalchemy.asc(RHourWholeStatic.static_time)).all() elif args[2]=='w': items=conn.execute("select DATE_FORMAT(static_date,'%%Y%%u') week,sum(shownum) as shownum,sum(clicknum) as clicknum from tb_day_whole_static where static_date>='%s' and static_date<='%s' group by week;"%(args[0],args[1])).fetchall() elif args[2]=='m': items=conn.execute("select DATE_FORMAT(static_date,'%%Y%%m') month,sum(shownum) as shownum,sum(clicknum) as clicknum from tb_day_whole_static where static_date>='%s' and static_date<='%s' group by month;"%(args[0],args[1])).fetchall() else: Message='the num of input parameter error!' time_format={'h':'%Y%m%d_%H','d':'%Y%m%d'} if (qres1 is not None) and (qres2 is not None): tdict={} for it in qres2: tdict[it[0]]=it[1] for it1 in qres1: if tdict.has_key(it1[0]): shows.append(long(it1[1])) clicks.append(long(it1[2])) ctr.append(float(it1[2])/float(it1[1])) srate.append(float(it1[1])/float(tdict[it1[0]])) times.append(it1[0].strftime(time_format[tp])) elif items is not None: for it1 in items: shows.append(long(it1[1])) clicks.append(long(it1[2])) ctr.append(float(it1[2])/float(it1[1])) if tp in time_format.keys(): times.append(it1[0].strftime(time_format[tp])) else: times.append(it1[0]) if isinstance(items, sqlalchemy.engine.result.ResultProxy): items.close() ret=200 if(len(ctr)>0): ctr=[round(100*e,2) for e in ctr] if(len(srate)>0): srate=[round(100*e,2) for e in srate] if(len(ctr)<=0 and len(srate)<=0): ret=201 data['shows']=shows data['clicks']=clicks data['ctr']=ctr data['srate']=srate data['times']=times res={} res['debug_num_merge_reasons']=len(merge_reasons) res['data']=data res['title']=title return res #return json.dumps(res,indent=4,sort_keys=True)@app.route('/get_2ips_data/<ip1>/<ip2>/<reason>/<start>/<end>/<tp>',methods=['POST','GET'])def get_2ips_data(ip1,ip2,reason,start,end,tp): data1=getQueryResult(ip1,reason,start,end,tp) data2=getQueryResult(ip2,reason,start,end,tp) set1=set(data1['data']['times']) set2=set(data2['data']['times']) un=list(set1 & set2) shows=[] clicks=[] ctr=[] srate=[] times=[] shows2=[] clicks2=[] ctr2=[] srate2=[] times2=[] if len(un)==0: ret=201 else: time1=data1['data']['times'] if time1!=un: for i in range(len(time1)): if time1[i] in un: shows.append(data1['data']['shows'][i]) clicks.append(data1['data']['clicks'][i]) ctr.append(data1['data']['ctr'][i]) if(len(data1['data']['srate'])>i): srate.append(data1['data']['srate'][i]) times.append(time1[i]) data1['data']['shows']=shows data1['data']['clicks']=clicks data1['data']['ctr']=ctr data1['data']['srate']=srate data1['data']['times']=times else: ctr=data1['data']['ctr'] srate=data1['data']['srate'] time2=data2['data']['times'] if time2!=un: for i in range(len(time2)): if time2[i] in un: shows2.append(data2['data']['shows'][i]) clicks2.append(data2['data']['clicks'][i]) ctr2.append(data2['data']['ctr'][i]) if(len(data2['data']['srate'])>i): srate2.append(data2['data']['srate'][i]) times2.append(time2[i]) data2['data']['shows']=shows2 data2['data']['clicks']=clicks2 data2['data']['ctr']=ctr2 data2['data']['srate']=srate2 data2['data']['times']=times2 else: ctr2=data2['data']['ctr'] srate2= data2['data']['srate'] res={} res['ip1']=data1 res['ip2']=data2 axis1={} axis2={} maxv=2 if len(ctr)>0: if len(ctr)==1: maxv=max(maxv,ctr[0]) else: maxv=max(maxv,max(ctr)) if len(ctr2)>0: if len(ctr2)==1: maxv=max(maxv,ctr2[0]) else: maxv=max(maxv,max(ctr2)) minv=0 maxv=maxv*1.2 gab=maxv/10 axis1['max']=maxv axis1['min']=minv axis1['gap']=gab maxv=2 if len(srate)>0: if len(srate)==1: maxv=max(maxv,srate[0]) else: maxv=max(maxv,max(srate)) if len(srate2)>0: if len(srate2)==1: maxv=max(maxv,srate2[0]) else: maxv=max(maxv,max(srate2)) maxv=maxv*1.6 axis2['max']=maxv axis2['min']=minv axis2['gap']=maxv/10 res['axis1']=axis1 res['axis2']=axis2 res['ret']=202 #两个ip都有数据的标识 return json.dumps(res,indent=4,sort_keys=True)@app.route('/getnewsinfo/<start>/<end>/<newsID>',methods=['POST','GET'])def getnewsinfo(start,end,newsID): rc=KBRedisClient(REDIS_MASTER,REDIS_DBNAME) newsID=newsID.strip() if newsID.endswith('00'): newsID=newsID[:len(newsID)-2] data=rc.getdata(newsID) res={} ret=data['ret'] newsid=data['title'] shows=data['data']['show'] clicks=data['data']['click'] ctr=data['data']['ctr'] srate=data['data']['srate'] times=data['data']['time'] title={} showstitle='展示数' clickstitle='点击数' ctrtitle='点击率' sratetitle='累计点击率' title['shows']=showstitle title['clicks']=clickstitle title['ctr']=ctrtitle title['srate']=sratetitle res['title']=title maxv=1 ctr=data['data']['ctr'] srate=data['data']['srate'] if(len(ctr)>0): ctr=[round(e,2) for e in ctr] if(len(srate)>0): srate=[round(e,2) for e in srate] if len(ctr)>0: if len(ctr)==1: maxv=max(maxv,ctr[0]) else: maxv=max(maxv,max(ctr)) if len(srate)>0: if len(clicks)==1: maxv=max(maxv,srate[0]) else: maxv=max(maxv,max(srate)) minv=0 maxv=maxv*1.2 gap=maxv/10 axis={} axis['max']=maxv axis['min']=minv axis['gap']=gap axis1={} axis2={} maxv=1 if len(shows)>0: if len(shows)==1: maxv=max(maxv,shows[0]) else: maxv=max(maxv,max(shows)) maxv=maxv*1.2 gab=maxv/10 axis1['max']=maxv axis1['min']=minv axis1['gap']=gab maxv=1 if len(clicks)>0: if len(clicks)==1: maxv=max(maxv,clicks[0]) else: maxv=max(maxv,max(clicks)) axis2['max']=maxv*1.2 axis2['min']=minv axis2['gap']=maxv*1.2/10 data['title']=title message="Successful return" if not ret==200: message='data empty' res['message']=message res['ret']=ret datas={} datas['shows']=shows datas['clicks']=clicks datas['times']=times datas['ctr']=ctr datas['srate']=srate res['data']=datas res['title']=title res['axis']=axis res['axis1']=axis1 res['axis2']=axis2 fout=open(os.getcwd()+'/data/newsinfo.json','w+') jsonobj=json.dumps(res,indent=4); fout.write(jsonobj) return jsonobj@app.route('/getinfo')def getinfo(): res={} ret=200 Message='Successful return' data={} qres1=db.session.query(sqlalchemy.distinct(DayStatic.ip)).all() qres2=db.session.query(sqlalchemy.distinct(DayStatic.reason)).all() ips=[] reasons=[] ch=[] num=[] if len(merge_reasons)>0: for key in merge_reasons.keys(): ch.append(key) for item in qres1: ips.append(item[0]) for item in qres2: try: int(item[0]) num.append(int(item[0])) except ValueError: ch.append(item[0]) ch.sort() num.sort() ips.sort() for e in num: ch.append(str(e)) data['ips']=ips data['reasons']=ch res['ret']=ret res['message']=Message res['data']=data fout=open(os.getcwd()+'/data/ipsandreasons.json','w+') jsonobj=json.dumps(res,indent=4); fout.write(jsonobj) return json.dumps(res,indent=4)@app.route('/news_report/<start>/<end>/<tp>')def report(start,end,tp): #获取从前一天零点开始到目前，CF、RCMT、VIDEO三个模块及整体的CTR及展示变化情况。 now=datetime.now() td=timedelta(days=1) END=now.strftime('%Y-%m-%d_%H:00:00') START=(now-td).strftime('%Y-%m-%d_00:00:00') if start==None or len(start)==0 or end==None or len(end)==0: start=START end=END names=['CF','RCMT','VIDEO','all'] ctr_vals=[] srate_vals=[] ctr_title=[] srate_title=[] ctr_axis={} srate_axis={} times=None ctr_maxv=1 srate_maxv=1 for it in names: res=getQueryResult('all',it,start,end,tp) ctr_vals.append(getModel(it+'-点击率',res['data']['ctr'])) srate_vals.append(getModel(it+'-展示占比',res['data']['srate'])) ctr_maxv=max(ctr_maxv,max(res['data']['ctr'])) ctr_title.append(it+'-点击率') srate_title.append(it+'-展示占比') if it =='all': times=res['data']['times'] else: srate_maxv=max(srate_maxv,max(res['data']['srate'])) ctr_axis['max']=ctr_maxv*1.2 ctr_axis['min']=0 ctr_axis['gap']=ctr_maxv*1.2/10 srate_axis['max']=srate_maxv*1.2 srate_axis['min']=0 srate_axis['gap']=srate_maxv*1.2/10 out={} out['ctr_title']=ctr_title out['ctr_axis']=ctr_axis out['ctr_series']=ctr_vals out['srate_title']=srate_title out['srate_axis']=srate_axis out['srate_series']=srate_vals out['times']=times out['ret']=202 return json.dumps(out,indent=4,sort_keys=True)#----------------------------------------------------------------------def getModel(name,data): model={ 'type':'line', 'markPoint': {'data': [{'type': 'max', 'name': '最大值'},{'type': 'min', 'name': '最小值','symbol':'arrow'},{'type': 'average', 'name': '平均值'},] } } model['name']=name model['data']=data return model数据库模型
from page import db#table1class HourStatic(db.Model):#指定数据库名 __tablename__ = 'tb_hour_reason_static'#一定要按照数据库各字段定义的顺序 ip= db.Column(db.String(32), index=True,primary_key=True) reason=db.Column(db.String(64), index=True,primary_key=True) static_time=db.Column(db.DateTime, index=True,primary_key=True) shownum=db.Column(db.BigInteger, nullable=False) clicknum= db.Column(db.BigInteger, nullable=False) def __init__(self,ip,reason,static_time,shownum,clicknum): self.ip=ip self.reason=reason self.static_time=static_time self.shownum=shownum self.clicknum=clicknum #---------------------------------------------------------------------- def __repr__(self): """""" return '<HourStatic ip:%s reason:%s time:%s>'%(self.ip,self.reason,self.static_time)#table2class DayStatic(db.Model): __tablename__='v_day_reason_static' ip=db.Column(db.String(32), index=True,primary_key=True) reason=db.Column(db.String(64), index=True,primary_key=True) static_date=db.Column(db.Date, index=True,primary_key=True) shownum=db.Column(db.BigInteger, nullable=False) clicknum= db.Column(db.BigInteger, nullable=False) def __init__(self,ip,reason,static_date,shownum,clicknum): self.ip=ip self.reason=reason self.static_date=static_date self.shownum=shownum self.clicknum=clicknum #---------------------------------------------------------------------- def __repr__(self): """""" return '<DayStatic ip:%s reason:%s date:%s>'%(self.ip,self.reason,self.static_date)#table3class RHourStatic(db.Model): __tablename__ = 'tb_hour_allip_reason_static' reason=db.Column(db.String(64), index=True,primary_key=True) static_time=db.Column(db.DateTime, index=True,primary_key=True) shownum=db.Column(db.BigInteger, nullable=False) clicknum= db.Column(db.BigInteger, nullable=False) def __init__(self,reason,static_time,shownum,clicknum): self.reason=reason self.static_time=static_time self.shownum=shownum self.clicknum=clicknum #---------------------------------------------------------------------- def __repr__(self): """""" return '<RHourStatic reason:%s time:%s>'%(self.reason,self.static_time)#table4class RDayStatic(db.Model): __tablename__='v_day_allip_reason_static' reason=db.Column(db.String(64), index=True,primary_key=True) static_date=db.Column(db.Date, index=True,primary_key=True) shownum=db.Column(db.BigInteger, nullable=False) clicknum= db.Column(db.BigInteger, nullable=False) def __init__(self,reason,static_date,shownum,clicknum): self.reason=reason self.static_date=static_date self.shownum=shownum self.clicknum=clicknum #---------------------------------------------------------------------- def __repr__(self): """""" return '<RDayStatic reason:%s date:%s>'%(self.reason,self.static_date)#table5class WholeStatic(db.Model): __tablename__='tb_day_whole_static' static_date=db.Column(db.Date, index=True,primary_key=True) shownum=db.Column(db.BigInteger, nullable=False) clicknum= db.Column(db.BigInteger, nullable=False) def __init__(tatic_date,shownum,clicknum): self.static_date=static_date self.shownum=shownum self.clicknum=clicknum #---------------------------------------------------------------------- def __repr__(self): """""" return '<WholeStatic date:%s>'%(self.static_date)#view1class HourWholeStatic(db.Model): __tablename__ = 'v_hour_allreason_static' ip=db.Column(db.String(32), index=True,primary_key=True) static_time=db.Column(db.DateTime, index=True,primary_key=True) shownum=db.Column(db.BigInteger, nullable=False) clicknum= db.Column(db.BigInteger, nullable=False) def __init__(self,ip,static_time,shownum,clicknum): self.ip=ip self.static_time=static_time self.shownum=shownum self.clicknum=clicknum #---------------------------------------------------------------------- def __repr__(self): """""" return '<HourWholeStatic ip:%s time:%s>'%(self.ip,self.static_time)#view2class DayWholeStatic(db.Model): __tablename__='v_day_allreason_static' ip=db.Column(db.String(32), index=True,primary_key=True) static_date=db.Column(db.Date, index=True,primary_key=True) shownum=db.Column(db.BigInteger, nullable=False) clicknum= db.Column(db.BigInteger, nullable=False) def __init__(self,ip,static_date,shownum,clicknum): self.ip=ip self.static_date=static_date self.shownum=shownum self.clicknum=clicknum #---------------------------------------------------------------------- def __repr__(self): """""" return '<DayWholeStatic ip:%s date:%s>'%(self.ip,self.static_date)#view3class RHourWholeStatic(db.Model): __tablename__ = 'v_hour_allip_allreason_static' static_time=db.Column(db.DateTime, index=True,primary_key=True) shownum=db.Column(db.BigInteger, nullable=False) clicknum= db.Column(db.BigInteger, nullable=False) def __init__(self,static_time,shownum,clicknum): self.static_time=static_time self.shownum=shownum self.clicknum=clicknum #---------------------------------------------------------------------- def __repr__(self): """""" return '<RHourWholeStatic time:%s>'%(self.static_time)if __name__ == "__main__": db.create_all()manage.py
#!/usr/bin/python# encoding=utf-8from flask.ext.script import Managerfrom page.page import appmanager = Manager(app)app.config['DEBUG'] = Trueif __name__ == '__main__': manager.run()数据库查询总结
1.查询指定字段，条件为某个字段多个值，并分组累计，结果排序：
#!/usr/bin/python# encoding=utf-8from flask.ext.sqlalchemy import SQLAlchemyimport sqlalchemy#查询语句：ips=["10.60.147.166", "10.60.147.167", "10.60.148.205", "10.60.148.5", "10.60.148.6", "10.60.148.71", "10.60.149.137", "10.60.149.158", "10.60.149.224", "10.60.149.225", "10.60.149.30", "10.60.150.170", "10.60.150.32", "10.60.151.92"]qres1=db.session.query(HourStatic.static_time,sqlalchemy.sql.func.sum(HourStatic.shownum),sqlalchemy.sql.func.sum(HourStatic.clicknum)).filter(HourStatic.ip.in_(ips),HourStatic.reason==args[1],HourStatic.static_time.between(args[2],args[3])).group_by(HourStatic.static_time).order_by(sqlalchemy.asc(HourStatic.static_time)).all() qres2=db.session.query(HourWholeStatic.static_time,sqlalchemy.sql.func.sum(HourWholeStatic.shownum)).filter(HourWholeStatic.ip.in_(ips),HourWholeStatic.static_time.between(args[2],args[3])).group_by(HourWholeStatic.static_time).group_by(HourWholeStatic.static_time).order_by(sqlalchemy.asc(HourWholeStatic.static_time)).all()#访问结果方法：if (qres1 is not None) and (qres2 is not None): tdict={} for it in qres2: tdict[it[0]]=it[1] for it1 in qres1: if tdict.has_key(it1[0]): shows.append(long(it1[1])) clicks.append(long(it1[2])) ctr.append(float(it1[2])/float(it1[1])) srate.append(float(it1[1])/float(tdict[it1[0]])) times.append(it1[0].strftime(time_format[tp])) 2.执行mysql语句：
import sqlalchemyengine = sqlalchemy.create_engine('mysql://root:root@10.240.123.104:3306/db_news_news_report?charset=utf8', echo=True)DB_Session =sqlalchemy.orm.sessionmaker(bind=engine)conn = DB_Session()items=conn.execute("select DATE_FORMAT(static_date,'%%Y%%u') week,sum(shownum) as shownum,sum(clicknum) as clicknum from tb_day_whole_static where static_date>='%s' and static_date<='%s' group by week;"%(args[0],args[1])).fetchall()if items is not None: for it1 in items: shows.append(long(it1[1])) clicks.append(long(it1[2])) ctr.append(float(it1[2])/float(it1[1])) if tp in time_format.keys(): times.append(it1[0].strftime(time_format[tp])) else: times.append(it1[0]) if isinstance(items, sqlalchemy.engine.result.ResultProxy): items.close()阅读全文
0 0
- Python-web: Flask 应用、数据库查询数据、API接口
- 数据挖掘之----使用 Python & Flask 实现 RESTful Web API
- python flask搭建web应用
- python flask api接口开发编程
- 初学Python之Flask开发API接口
- python flask api接口开发编程
- flask-api接口测试
- Flask： 应用编程接口（API）（一）
- Python flask post接口
- Python Flask 天气查询
- Flask之旅《Flask Web开发：基于Python的Web应用开发实战》学习笔记
- 第一部分 flask简介《Flask Web开发：基于Python的Web应用开发实战》
- flask web 应用部署
- Flask Web 开发 数据库
- 【001】基于Python的Flask Web应用框架（1）
- 【002】基于Python的Flask Web应用框架（2）
- 初识docker—创建WEB应用容器1python+flask
- Flask实现成绩查询接口
- python学习之路-线程-day08
- ACM训练日记—8月9日
- POJ3087 Shulle'm up 模拟
- 8月9日小结
- 小白算法练习 poj 1664 放苹果 整数拆分 递推
- Python-web: Flask 应用、数据库查询数据、API接口
- Xor Sum HDU
- POJ2251 Dungeon Master 三维BFSmd
- JfreeChart 生成图表-雷达图
- Android进阶--android自动化测试python+uiautomator
- 【NOIP2017】Day6
- JZOJ3418. 【NOIP动态规划专题】选课(2017.8DP&贪心专题)
- 8月9日总结
- 守护进程的实现
