python进阶(一)关联sql的算法操作

  上次我们讲了一个很简单的python程序,这次我们复杂一点,通过python操作更加复杂点并配合具有更多算法功能的sql语句。

  首先,我们这个程序的功能为,通过python操作sql获取数据,并通过关联算法在服务器端对数据进行处理和筛选。然后返回前端,前端我们再用js通过ajax操作获取数据,并在前端再对数据进行二次处理。首先上一张效果图吧:

  技术分享

这里顺带用了日期插件跟分页插件。好了,废话不多说,还是直接上代码:

# -*- coding: utf-8 -*-
#
# Copyright (C) 2014 NetEase SDC
#
# Author: LIJIAN <[email protected]>
# Version: 1.0 2014-03-18
from django.shortcuts import render_to_response
from authority.sys.sys_views import get_menu_name
from base.utils.request_utils import get_str, get_int
from base.utils import mysql_utils_psafe
from django.http import HttpResponse
from base.data.report_utils import get_menu_valid_time
import datetime

def query_libao_inter(request):
    u""" 
    """
    para = {}
    game_id = get_int(request,game_id,None)
    para[game_id] = game_id
    from base.utils.data_utils import get_dimen_info
    para[dimen_info] = get_dimen_info([143])[143]
    para[valid_time] = {start:20140430,end:str(datetime.datetime.now())[0:10].replace(-,‘‘)}
    template_name = /specific/libao/query_libao.html
    return render_to_response(template_name, para)

def query_libao_use_data(request):
    game_id = get_int(request,game_id,None)
    sn = get_str(request,sn,‘‘)
    date_s = get_str(request,date_s,‘‘)
    date_e = get_str(request,date_e,‘‘)
    os = get_int(request,os,None)
    page_conditions_str = get_str(request, page_conditions,‘‘)
        
    total_sql = """
        select  count(*)
        from report_ods.wscs_libao a left join info.game_host b on a.hostnum=b.id and b.game_id=%s 
    """%game_id
    sql = """
        select  user_id,sn,registertime,if(b.host_name,b.host_name,hostnum),if(is_trade=1,‘是‘,‘否‘),if(is_valid_user=1,‘是‘,‘否‘),total_trade_cash
        from report_ods.wscs_libao a left join info.game_host b on a.hostnum=b.id and b.game_id=%s 
    """%game_id
    where_sql = []
    if sn:
        where_sql.append("sn like ‘%%%s%%‘"%sn)
    if date_s and date_e:
        where_sql.append("registertime between ‘%s-%s-%s 00:00:00‘ and ‘%s-%s-%s 23:59:59‘"%(date_s[0:4],date_s[4:6],date_s[6:8],date_e[0:4],date_e[4:6],date_e[6:8]))
    if os:
        where_sql.append( os_name=%s%os)
    if where_sql:
        sql += where + and .join(where_sql)
        total_sql += where + and .join(where_sql)
    if page_conditions_str:
        import json
        page_conditions = json.loads(page_conditions_str)
        sql +=  limit %d,%d%((int(page_conditions[cursor])-1)*int(page_conditions[size]),page_conditions[size])
    print sql
    data = mysql_utils_psafe.query_listlist(sql,value_decorator_list=str)
    total_num = mysql_utils_psafe.query_one(total_sql)
    return {data:data,total_num:total_num}


def file_os(request):
    data = query_libao_use_data(request)[data]
    response = None
    try:
        content=["\xEF\xBB\xBF",用户ID, 礼包码, 使用时间, 服务器, 是否新增, 是否充值, 充值额, \n]
        for line in data:
            content.append(,.join([str(x) for x in line])+\n)
        print data        
        response = HttpResponse(content, mimetype=application/octet-stream)
        response[Content-Disposition] = attachment; filename=%s%礼包码.csv
    except Exception,ex:
        print_error()
    finally:
        return response
    
    
    
    
    js代码:
require([jquery, pt, bootstrap,table, datepicker], function() {
    var $ = require(jquery);
      var pt = require(pt);
      var table = require(table);
      var datepicker = require(datepicker);
      var day_s=$ns.report_valid_times[start],day_e=$ns.report_valid_times[end];
      $ns.settings = {"size":15,"cursor":1,"sortingStatus":[]};
      $(document).ready(function() {
          // 初始化
          // 数据条件
          $ns.data_conditions = {};
          // 分页条件
          $ns.table_conditions = {};
          $ns.player_list = [];
          
          //生成日期插件
          $ns.start_dp = new datepicker.Datepicker({
            datepicker : {
                renderTo : query_date,
                onpick : function(type, start, end) {
                    console.log(type, start, end, this);
                    day_s=start;
                    day_e=end;
                }
            },
            datetype : {
                onpick : function(type) {
                }
            },
            date : {
                day : {
                    start : day_s,
                    end : day_e,
                    initial:[-60]
                }
            }
        });
        
        $("#search_btn").click();
      });
      
      
    //查询事件
    $("#search_btn").click(function(){
        query_libao_use_data();
    });
        
    function query_libao_use_data(){
        var sn = $("#sn").val();
        var os = $(#os).val();
        var para = new Object();
        var QUERY_URL = "/product_center/ajax_query/"; 
        para.query_type = "query_libao_use_data";
        para.date_s = day_s;
        para.date_e = day_e;
        para.game_id = $ns.game_id;
        para.sn = sn;
        para.os = os;
        para.page_conditions = JSON.stringify($ns.settings);
        para.random = Math.random();
        console.log(day_s)
        $.ajax({
                type:get,url:QUERY_URL,data:para,dataType:json,async:false,
                success:function(data){
                    $ns.total_num = data[total_num];
                    for(var i in data[data]){
                        data[data][i][2]=data[data][i][2].replace(/T/," ");
                    }
                    tables([]);
                    $(".navbar").css("margin-bottom","0");
                }, 
                complete:function(){
                    
                },error:function(){
                    tables([]);
                    alert(查询出错!);
                }
        });
    }    
        
        
        //复制数据
        $("#download_data").click(function(){
            var para = new Object();
            var query_url = "/product_center/specific/download_libao/"; 
            query_url += ?date_s=+day_s;
            query_url += &date_e=+day_e;
            query_url += &game_id=+$ns.game_id;
            query_url += &sn=+$("#sn").val();;
            query_url += &os=+$("#os").val();;
            para.random = Math.random();
            window.open (query_url);
        });
        
    //加载表格
      function tables(item){
          $(.table).table({
            head : [用户ID, 礼包码, 使用时间,服务器,是否新增,是否充值,充值额],
            body : {
                   rows:item,
                   paging:{
                        enabled : true,
                          size : $ns.settings[size],
                          availableSizes : [15,20,50],
                          totalNum : $ns.total_num,
                          action : function(settings, allrows, comparers, body) {
                              $ns.settings = settings;
                              var sn = $("#sn").val();
                            var os = $(#os).val();
                            var para = new Object();
                            var QUERY_URL = "/product_center/ajax_query/"; 
                            para.query_type = "query_libao_use_data";
                            para.date_s = day_s;
                            para.date_e = day_e;
                            para.game_id = $ns.game_id;
                            para.sn = sn;
                            para.os = os;
                            para.page_conditions = JSON.stringify($ns.settings);
                            para.random = Math.random();
                            $.ajax({
                                    type:get,url:QUERY_URL,data:para,dataType:json,async:false,
                                    success:function(data){
                                        $ns.total_num = data[total_num];
                                        for(var i in data[data]){
                                            data[data][i][2]=data[data][i][2].replace(/T/," ");
                                        }
                                        item = data[data];
                                    }, 
                                    complete:function(){
                                        
                                    },error:function(){
                                        return [];
                                    }
                            });
                            return item;
                          }
                   },
            }
        });
      }
});

html代码:

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>礼包码使用情况</title>
{% include "base/head_report.html" %}
<link rel="stylesheet" href="/static/product_center/specific/libao/css/query_libao.css?v=1.0" type="text/css" />
<script>
var $ns = {};
$ns.game_id = {{game_id|default:"‘‘"}};
$ns.report_valid_times = {{valid_time|safe|default:‘{}‘}};
document.write(‘<script type="text/javascript" src="/static/product_center/specific/libao/js/query_libao.js?id=‘+Math.random()+‘"><\/script>‘);//Math.random()
</script>
<script type="text/javascript" src="/static/base/js/util.js"></script>
<script type="text/javascript" src="/static/base/js/filter.js"></script>
</head>
<body>
<div>
<div class="wrapper">
<div class="row" style="">
<div class="conditions">
<div class="tips-options">
<div id="query_date"></div>
<div id="sel">
<input type="text" class="search_text" id="sn" placeholder="输入礼包码查询" />
<select class="input-mini" id="os">
<option value="">---请选择操作系统---</option>
{% for item in dimen_info %}
<option value="{{item.id}}">{{item.name}}</option>
{% endfor %}
</select>
<input type="button" class="btn" id="search_btn" value="查询" />
<input type="button" class="btn-primary copy" id="download_data" value="下载数据"/>
</div>
</div>

</div>
</div>
<div class="row" style="margin-bottom:20px;">
<div class="span12" style="">

<div>
<table id="player_list"
class="table table-condensed table-bordered table-fixed table-hover table-column-hover"
style="border-radius: 0">


</table>
</div>
</div>
</div>

</div>
</div>
</body>
</html>


 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。