Python db_utility.db_command函数代码示例

def pulldown_menu(kpi_id):
    cdg_db = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics")
    sql = "select distinct(app_id) as app_id from mnl_kpi_" + kpi_id
    log = cdg_db.db_anyselect(sql)
    app_ids = []
    for v in log:
        app_ids.append(Applist(app_id=v["app_id"]))
    sql = "select distinct(os_type) as os_type from mnl_kpi_" + kpi_id
    log = cdg_db.db_anyselect(sql)
    os_types = []
    for v in log:
        os_types.append(Oslist(os_type=v["os_type"]))
    sql = "select distinct(year) as year from mnl_kpi_" + kpi_id
    log = cdg_db.db_anyselect(sql)
    year = []
    for v in log:
        year.append(Yearlist(year=v["year"]))
    month = []
    for v in range(1, 13):
        month.append(Monthlist(month=str(v)))
    date = []
    for v in range(1, 32):
        date.append(Datelist(date=str(v)))

    return app_ids, os_types, year, month, date

def get_rr(lower, upper):
    cdg_db = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics")
    sql = "select app_id, os_type, "
    sql += "avg(rr_001) as rr_001, avg(rr_007) as rr_007 "
    sql += "from mnl_kpi_rr where yyyymmdd >= "
    sql += str(lower) + " and yyyymmdd < " + str(upper) + " group by app_id, os_type"
    log = cdg_db.db_anyselect(sql)
    return log

def get_cvr(lower, upper):
    cdg_db = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics")
    sql = "select app_id, os_type, "
    sql += "avg(firstdaytrial) as ft, avg(firstdaysubscription) as fs, avg(subscription01) as s01, avg(trial2subscription) as t2s, "
    sql += "avg(trial2subscription02) as t2s02, avg(trial2subscription03) as t2s03 "
    sql += "from mnl_kpi_cvr where yyyymmdd >= "
    sql += str(lower) + " and yyyymmdd < " + str(upper) + " group by app_id, os_type"
    log = cdg_db.db_anyselect(sql)
    return log

def pulldown_menu_yyyymm(kpi_id):
    cdg_db = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics")
    sql = "select distinct(year) as year from mnl_kpi_" + kpi_id
    log = cdg_db.db_anyselect(sql)
    year = []
    for v in log:
        year.append(Yearlist(year=v["year"]))
    month = []
    for v in range(1, 13):
        month.append(Monthlist(month=str(v)))

    return year, month

import sys
sys.path.append('/home/takagi/git/analytics/analytics_tool')
import utility.db_utility as db_util
from datetime import datetime as dt
import commands

argvs = sys.argv
lower = int(argvs[1])

print lower
#DB

##########kidsnbooks
#access log
kidsnbooks_log = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics")
where = "date = " + str(lower)
access_log = kidsnbooks_log.db_select("*", "analytics_metalog", where, None)
summary = {}
for v in access_log:
    summary.setdefault(v["uid"], {})
    summary[v["uid"]].setdefault(v["app_id"], {})
    summary[v["uid"]][v["app_id"]].setdefault(v["os_type"], {})
    summary[v["uid"]][v["app_id"]][v["os_type"]].setdefault("regdate", v["regdate"])
    summary[v["uid"]][v["app_id"]][v["os_type"]].setdefault("moddate", v["date"])

#output
fname = "/home/takagi/git/analytics/analytics_tool/data_temp/lastsessionuu" + str(lower)
out = open(fname, 'w')

for uid, app in summary.items():

import utility.device_master as dev_master

user_data = {}
# DB
db = {
    "meta": {
        "server": "inhousedb.smartsrv.in",
        "db_name": "statistics",
        "table": ["mnl_metalog"],
        "colum": "id,created",
    }
}


# 課金データを取得
logdb = db_util.db_command("takagi", "7TgYiaI9", db["meta"]["server"], db["meta"]["db_name"])
start = datetime.datetime(2013, 6, 25, 0, 0, 0, 0)
one_day = datetime.timedelta(days=1)
end = start + one_day
user_data = {}
for i in range(130):
    s = start + i * one_day
    e = end + i * one_day
    month = s.strftime("%Y-%m")
    s = s.strftime("%Y%m%d")
    e = e.strftime("%Y%m%d")
    where = " where date = " + s + " and app_id = 'kidsnbooks' and sub_pay > 0"
    print where
    sql = "select uid, app_id, os_type, sub_pay from mnl_metalog" + where
    log = logdb.db_anyselect(sql)
    for v in log:

def growthrate(request):
    if not request.user.is_authenticated():
        return render_to_response('servicekpi/index.html', {},
                                  context_instance=RequestContext(request))
    else:
        if request.POST:
            wb = Workbook()
            c_border = xlwt.Borders()
            c_border.top    = xlwt.Borders.THICK
            c_border.bottom = xlwt.Borders.THICK
            c_border.left   = xlwt.Borders.THICK
            c_border.right  = xlwt.Borders.THICK
            font0 = xlwt.Font()
            font0.name = 'Osaka'
            c_style = xlwt.XFStyle()
            c_style.borders = c_border
            c_style.font = font0
            year, month = int(request.POST["sy"]), int(request.POST["sm"])
            start0 = str(year) + str(month).zfill(2)
            cdg_db = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics")
            sql = "select * "
            sql += "from mnl_kpi_monthly_subscription where date <= "
            sql += str(start0)
            log = cdg_db.db_anyselect(sql)
            data = {}
            for v in log:
                data.setdefault(v["app_id"], {})
                data[v["app_id"]].setdefault(v["os_type"], {})
                data[v["app_id"]][v["os_type"]].setdefault(v["regdate"], {})
                data[v["app_id"]][v["os_type"]][v["regdate"]].setdefault(v["date"], float(v["uu"]))
            ws = []
            i = 0
            for app_id, d0 in data.items():
                for os, d1 in d0.items():
                    j = 0
                    ws.append(wb.add_sheet(str(app_id) + "_" + str(os)))
                    total = {}
                    for regdate, d2 in sorted(d1.items()):
                        k = j
                        ws[i].write(j + 1, 0, regdate, c_style)
                        for date, uu in sorted(d2.items()):
                            if k == j:
                                ws[i].write(0, k + 1, date, c_style)
                            ws[i].write(j + 1, k + 1, uu, c_style)
                            total.setdefault(date, {})
                            total[date].setdefault("uu", 0) 
                            if k == j:
                                total[date].setdefault("col", k + 1) 
                            total[date]["uu"]+= uu
                            k += 1
                        j += 1
                    ws[i].write(j + 1, 0, u"合計", c_style)
                    ws[i].write(j + 2, 0, u"成長率", c_style)
                    p = 0
                    for key, value in sorted(total.items()):
                        try:
                            col = value["col"]
                        except:
                            col += 1
                        ws[i].write(j + 1, col, value["uu"], c_style)
                        g =  100 * value["uu"] / p if p != 0 else "NONE"
                        ws[i].write(j + 2, col, g, c_style)
                        p = value["uu"]
                    i += 1


            file_name = 'growthrate_' + str(start0) + '.xls'
            response = HttpResponse(mimetype='application/ms-excel')
            response['Content-Disposition'] = 'attachment; filename=%s' % file_name
            wb.save(response)
            return response
        else:
            year, month = udf.pulldown_menu_yyyymm("cvr")
            context = RequestContext(request, {
                    'user': User,
                    "year" : year,
                    "month" : month,
                    })
            return render_to_response('servicekpi/growthrate.html', {},
                                      context_instance=context)

argvs = sys.argv
install_date = int(argvs[1].replace("-", ""))
user_data = {}
#DB
db = {
    "meta"   : { "server" : "inhousedb.smartsrv.in",
                 "db_name" : "statistics",
                 "table" : ["mnl_subscriptionlog_fast"],
                 "colum" : "*"
                 },
    }


#インストールユーザーを抽出する
logdb = db_util.db_command("takagi", "7TgYiaI9", db["meta"]["server"], db["meta"]["db_name"])
sql = "select * from mnl_subscriptionlog_fast where sub_pay > 0 and date = " + str(install_date) 
log = logdb.db_anyselect(sql)
for v in log:
     month = round(float(v["is_sub"]) / 30)
     #created異常のユーザー回避
     if month == 0:
          month = 1
     price = float(v["sub_pay"]) / float(month)
     user_data.setdefault(v["uid"], {})
     user_data[v["uid"]].setdefault(v["app_id"], {})
     user_data[v["uid"]][v["app_id"]].setdefault("month", month)
     user_data[v["uid"]][v["app_id"]].setdefault("price", price)
     user_data[v["uid"]][v["app_id"]].setdefault("date", [])

     for i in range(0, int(month)):

                   },
     "access"   : { "server" : "apps.db.smartsrv.jp",
                    "db_name" : "smartapps_log",
                    "table" : ["Access_201310",],
                    "where" : "application = 122"
                    },    
     "play"   : { "server" : "apps.db.smartsrv.jp",
                    "db_name" : "smartapps_log",
                    "table" : ["Play_201310","Play_201311"],
                    "where" : "(application = 122 or application = 121) and is_start = 0 and date > '2013-10-22'"
                    },
    }


#インストールユーザーを抽出する
logdb = db_util.db_command("takagi", "7TgYiaI9", db["users"]["server"], db["users"]["db_name"])
for table in db["users"]["table"]:
     sql = "select user_id from "
     sql += table + " where " + db["users"]["where"]
     log = logdb.db_anyselect(sql)
     for v in log:
          user_data.setdefault(v["user_id"], {})
          user_data[v["user_id"]].setdefault("iOS", [])
          user_data[v["user_id"]].setdefault("Android", [])



#プレイログを読む
logdb = db_util.db_command("takagi", "7TgYiaI9", db["play"]["server"], db["play"]["db_name"])
for table in db["play"]["table"]:
     sql = "select user_id, content_id, type, extra_val_01, application from "

           "Goccozoo-iOS" : {"app_id" : "goccozoo",
                             "os_type" : "os_type != 'Android'",
                             "application" : "goccozoo",
                             "pay" : "and (name = 'sales_contents')"},
           }


#全体の売上
rec = {"売上" : 0,
       "サブスクリプション売上" : 0,
       "従量売上" : 0,
       "当月累計売上" : 0,
       "先月同日比" : 0,
       "当月着地見込み" : 0
       }
logdb = db_util.db_command("takagi", "7TgYiaI9", db["revenue"]["server"], db["revenue"]["db_name"])
#今日の売上
sql = "select name, sum(cnt) as cnt from analyze_summary "
sql += "where "
sql += "date = '" + date + "' "
sql += "and name like '%sales%' and country = ''"
log = logdb.db_anyselect(sql)
for v in log:
     rec["売上"] += float(v["cnt"])

#今日のサブスクリプション売上
sql = "select name, sum(cnt) as cnt from analyze_summary "
sql += "where "
sql += "date = '" + date + "' "
sql += "and name = 'sales_subscription' and country = ''"
log = logdb.db_anyselect(sql)

sys.path.append("/home/takagi/git/analytics/analytics_tool")
import utility.db_utility as db_util
from datetime import datetime as dt
import commands
import utility.device_master as dev_master

argvs = sys.argv
lower = "2013-07-28"
upper = "2013-08-05"

print lower, upper
# DB

##########kidsnbooks

kidsnbooks = db_util.db_command("takagi", "7TgYiaI9", "kidsnbooksdb.smartsrv.jp", "kidsnbooks")

user_data = {}
# access log
where = "created >= '" + lower + " 00:00:00' and created < '" + upper + " 00:00:00'"
access_log = kidsnbooks.db_select(
    "user_id,subscription_id,application,status,created", "subscription_users", where, None
)
user_data = {}
for v in access_log:
    dev = "Android" if v["application"] == "31" else "iOS"
    tdt = dt.strptime(v["created"], "%Y-%m-%d %H:%M:%S")
    tdtf = tdt.strftime("%Y%m%d")
    user_data.setdefault(v["user_id"], {})
    user_data[v["user_id"]].setdefault("device", dev)
    user_data[v["user_id"]].setdefault("status", v["status"])

    "user"   : { "server" : "kidsnbooksdb.smartsrv.jp",
                 "db_name" : "kidsnbooks",
                 "table" : ["users"],
                 "colum" : "id,created"
                 },
    "play"   : { "server" : "log.kidsnbooksdb.smartsrv.jp",
                 "db_name" : "kidsnbooks_log",
                 "table" : ["play_log"],
                 "colum" : "id,is_start,book_id,created"
             },

    }


#インストールデータを日別に取得する
logdb = db_util.db_command("smartedu", "nakadama", db["user"]["server"], db["user"]["db_name"])
sql = "select id,created,platform_full from users where created >= '2013-06-01 00:00:00'"
log = logdb.db_anyselect(sql)
for v in log:
     created = dt.strptime(v["created"], '%Y-%m-%d %H:%M:%S')
     created = created.strftime('%Y%m%d')
     user_data.setdefault(v["id"], {})
     user_data[v["id"]].setdefault("regdate", created)
     dev = dev_master.os_name(v["platform_full"])
     user_data[v["id"]].setdefault("device", dev)
     user_data[v["id"]].setdefault("book_start", None)
     user_data[v["id"]].setdefault("book_end", None)
     user_data[v["id"]].setdefault("start", None)
     user_data[v["id"]].setdefault("end", None)
     user_data[v["id"]].setdefault("book_num", 0)

def get_data(sql):
    cdg_db = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics")
    log = cdg_db.db_anyselect(sql)
    return log

db = {
    "smartapps"   : { "server" : "apps.db.smartsrv.jp",
                      "db_name" : "smartapps_user",
                      "table" : ["Users_0", "Users_1", "Users_2", "Users_3", "Users_4",
                                  "Users_5", "Users_6", "Users_7", "Users_8", "Users_9",],
                      "colum" : "user_id as id, application, created"
                      },
    
    }

kdmode = {"server" : "apps.db.smartsrv.jp", "db_name" : "kdmode" }
user_data = {}

#ユーザーデータの取得
for app_id, server in db.items():
    logdb = db_util.db_command("smartedu", "nakadama", server["server"], server["db_name"])
    tables = server["table"]
    for table in tables:
        where = "application = 65"
        log = logdb.db_select(server["colum"], table, where, None)
        am = app_master.app_master()
        for v in log:
            if v["created"] != "NULL" and am.has_key(v["application"]):
                created = dt.strptime(v["created"], '%Y-%m-%d %H:%M:%S')
                created = created.strftime('%Y%m%d')
                user_data.setdefault(v["id"], {})
                user_data[v["id"]].setdefault("regdate", created)
                user_data[v["id"]].setdefault("apps", {})
                user_data[v["id"]].setdefault("install", {})
                user_data[v["id"]].setdefault("add_on_video", {})

                     },
    
    "smartapps"   : { "server" : "apps.db.smartsrv.jp",
                      "db_name" : "smartapps_user",
                      "table" : ["Users_0", "Users_1", "Users_2", "Users_3", "Users_4",
                                  "Users_5", "Users_6", "Users_7", "Users_8", "Users_9",],
                      "colum" : "user_id as id, application, created"
                      },
    
    }


##########kidsnbooks

for app_id, server in db.items():
    logdb = db_util.db_command("smartedu", "nakadama", server["server"], server["db_name"])
    tables = server["table"]
    for table in tables:
        user_data = {}
    #subscription log
        log = logdb.db_select(server["colum"], table, None, None)
        am = app_master.app_master()
        for v in log:
            if app_id != "smartapps":
                if v["created"] != "NULL":
                    created = dt.strptime(v["created"], '%Y-%m-%d %H:%M:%S')
                    created = created.strftime('%Y%m%d')
                    user_data.setdefault(v["id"], {})
                    user_data[v["id"]].setdefault(app_id, {})
                    user_data[v["id"]][app_id].setdefault("regdate", created)
            else: