Python-web: Flask 應用、資料庫查詢資料、API介面

一個超簡單的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)>