首页 > 代码库 > python进阶（一）关联sql的算法操作
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-18from django.shortcuts import render_to_responsefrom authority.sys.sys_views import get_menu_namefrom base.utils.request_utils import get_str, get_intfrom base.utils import mysql_utils_psafefrom django.http import HttpResponsefrom base.data.report_utils import get_menu_valid_timeimport datetimedef 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="http://www.mamicode.com/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="http://www.mamicode.com/static/product_center/specific/libao/js/query_libao.js?id=‘+Math.random()+‘"><\/script>‘);//Math.random()
</script>
<script type="text/javascript" src="http://www.mamicode.com/static/base/js/util.js"></script>
<script type="text/javascript" src="http://www.mamicode.com/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="http://www.mamicode.com/{{item.id}}">{{item.name}}</option>
{% endfor %}
</select>
<input type="button" class="btn" id="search_btn" value="http://www.mamicode.com/查询" />
<input type="button" class="btn-primary copy" id="download_data" value="http://www.mamicode.com/下载数据"/>
</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>
python进阶（一）关联sql的算法操作