Python-web:flask application, database query data, API interface

a super simple flask application Helloword

From flask import flask
app = Flask (__name__)

@app. Route ('/")
def Hello_world (): Return
    ' Hello world! '

if __name__ = = ' __main__ ':
    app.run ()

database queries, and data APIs

Reference to database query operations: Python: Database Operations Module SQLAlchemy

Inquire

#coding =utf-8 from flask import flask,request,url_for,g,render_template,session,redirect,flash,jsonify Flask.ext.sqlalchemy Import SQLAlchemy from threading import Thread from collections import defaultdict Import config Impo RT jsonobjects Import OS Import sqlalchemy import JSON #import datetime and Kbredisclient2 repeat from Jsoncoder import mydecoder , Myencoder from kbredisclient2 import * app = Flask (__name__) app.config[' Sqlalchemy_database_uri ']= ': 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) to 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 contacts from MO Dels 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= ' Show number ' clickstitle= ' clicks ' ctrtitle= ' Ctr ' sratetitle= ' show accounted for ' title[' shows ']=showstitle title[' C 
    Licks ']=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 ' d ata[' 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 axis={} axis[' Max ']=maxv axis[' min ']=minv axis[' gap ']=gap res[' axis ']=axis return Json.dumps (res,in Dent=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={} shows Title= ' Show number ' clickstitle= ' clicks ' ctrtitle= ' Ctr ' sratetitle= ' show accounted for ' 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 Mer Ge_reasons.has_key (reason): Qres1=db.session.query (Hourstatic.static_time,sqlalchemy.sql.func.sum (HourStat Ic.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 Hour Wholestatic.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.se Ssion.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)). fil ter (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 a RGS[3]!= ' h ' and args[3]!= ' d ': message= ' Select Type error ' if (ip== ' all '): if (args[3]== ' h ' D message== ' successful return '): If not merge_reasons.has_key (reason): Qres1=db.sessi On.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), Sqla Lchemy.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,RH Ourwholestatic.shownum). Filter (RHourWholeStatic.static_time.between (args[1),ARGS[2]). Order_by (SQLALCHEMY.ASC (Rhourwholestatic.static_time)). All () elif (args[3]== ' d ' and message== ' Success Ful 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,wholesta Tic.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 (Rhourwholesta Tic.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 St Atic_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 are not none) and (Qres2 are not none): tdict={} fo
                R 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 (Tim E_FORMAT[TP]) elif The items are not none:for it1 in Items:shows.append (Long (it1[1)) CL
                Icks.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[' click S ']=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/&LT;IP1&GT;/&LT;IP2&GT;/&LT;REASON&GT;/&LT;START&GT;/&LT;END&GT;/&LT;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[' d 
    ATA ' [' 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 ']=ti Mes 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 (data 2[' 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 '] [' clic KS ']=clicks2 data2[' data ' [' Ctr ']=ctr2 data2[' data '] [' srate ']=srate2 data2[' data '] [' Ti
    Mes ']=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
    .2GAB=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 ' a Xis2 ']=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 ('): Newsid=newsid[:le N (NewsID)-2] data=rc.getdata (NewsID) res={} ret=data[' ret '] newsid=data[' title '] shows=data[' data ' [' s How '] clicks=data[' data ' [' click ']
    ctr=data[' data '] [' CTR '] srate=data[' data ' [' srate '] times=data[' data '] [' Time '] title={} showstitle= ' Show Number ' clickstitle= ' clicks ' ctrtitle= ' hits ' sratetitle= ' cumulative click ' title[' shows ']=showstitle title[' ' clicks ']=clic Kstitle title[' Ctr ']=ctrtitle title[' srate ']=sratetitle res[' title ']=title maxv=1 ' data ' ['] ct R '] srate=data[' data ' [' srate '] if (Len (CTR) >