python进阶（一）关联sql的算法操作

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

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

　　

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

# -*- coding: utf-8 -*-
#
# Copyright (C) 2014 NetEase SDC
#
# Author: LIJIAN <gzlijian@corp.netease.com>
# 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=["xEFxBBxBF",'用户ID, 礼包码, 使用时间, 服务器, 是否新增, 是否充值, 充值额, 
']
        for line in data:
            content.append(','.join([str(x) for x in line])+'
')
        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>