flask接口+ajax实现省市县查询

我们在网站表单里经常遇到省市县的选择,今天我们就用flask+ajax来简单的实现这个功能

效果图:

1.需求

提供三个选择框,分别提供省市县查询

2.数据库设计

具体数据和建表语句都是从网上下的.需要的同学可以去网上下载.这里给出建表语句做参考

-- 省份表
CREATE TABLE S_Province(
	ProvinceID bigint NOT NULL PRIMARY KEY ,
	ProvinceName VARCHAR( 50 ) NULL,
	DateCreated datetime NULL,
	DateUpdated datetime NULL
)ENGINE = MYISAM DEFAULT CHARSET=utf8 ;

-- 市级表
CREATE TABLE S_City(
	CityID bigint NOT NULL PRIMARY KEY ,
	CityName varchar(50) ,
	ZipCode varchar(50) ,
	ProvinceID bigint NULL ,
	DateCreated datetime NULL ,
	DateUpdated datetime NULL
)ENGINE = MYISAM DEFAULT CHARSET=utf8 ;

-- 县级表
CREATE TABLE S_District(
	DistrictID bigint NOT NULL PRIMARY KEY ,
	DistrictName nvarchar (50) ,
	CityID bigint NULL,
	DateCreated datetime NULL,
	DateUpdated datetime NULL
)ENGINE = MYISAM DEFAULT CHARSET=utf8 ;

3.前端

<html>
  <head>
     <meta charset="utf-8">
     <title>省市区县查询</title>
     <link rel="stylesheet" type="text/css" href = "{{ url_for('static',filename = 'css/bootstrap.min.css')}}">
	 <link rel="stylesheet" type="text/css" href = "{{ url_for('static',filename = 'css/demo.css')}}">
	<script src="https://apps.bdimg.com/libs/jquery/2.1.4/jquery.min.js"></script>
	<script type=text/javascript>
      $SCRIPT_ROOT = {{ request.script_root|tojson|safe }};
    </script>
  </head>
  <body>
    <!-- 导航栏 -->
    <nav class="navbar navbar-default" role="navigation">
    <div class="container-fluid">
    <div class="navbar-header">
      <a class="navbar-brand" href="#">中国地域查询系统</a>
    </div>
    <ul class="nav navbar-nav navbar-right">
      <li><a href="#"><span class="glyphicon glyphicon-user"></span> 注册</a></li>
      <li><a href="#"><span class="glyphicon glyphicon-log-in"></span> 登录</a></li>
    </ul>
    </div>
    </nav>
    
	 
	 
	 <div class="container">
            <div class="flip-container"><div class="flipper">
			<div class="front">
			<form class="contact" id="form" action="#" method="post">
                    
		    <div class="form-group">
		        <label for="name">选择列表</label>
		        <select class="form-control" id="s1">
			         
		        </select>
				<div class="span10 offset2" style="margin-left:auto" >
                    <input name="submit" class="submit" id="but" type="submit" value="点 击 开 始">
                </div>
		        
	        </div>
            </form>
			</div>
    </div>
	 
  </body>
</html>

页面就提供了一个简单的表单,样式用的是bootstrap.不过我在这里一开始只提供了一个选择框.另外两个选择框在第一次点击以后动态生成.

4.flask

这里写的是一个最小的项目,什么配置都没有

from flask import Flask,render_template,jsonify,request
import pymysql as MySQLdb

app = Flask(__name__)

config = {
          'host':'127.0.0.1',
          'port':3306,
          'user':'root',
          'password':'',
          'db':'test',
          'charset':'utf8mb4',
          'cursorclass':MySQLdb.cursors.DictCursor,
          }

connection = MySQLdb.connect(**config)

首先创建flask项目,导入pymysql,使用它来连接数据库

然后这里写了一个数据库查询的函数search(),以后的查询可以直接调用这个函数.

def search(sql):
    with connection.cursor() as cursor:
    # 执行sql语句，进行查询
            
        cursor.execute(sql)
    # 获取查询结果
        result = cursor.fetchall()
        
    # 没有设置默认自动提交，需要主动提交，以保存所执行的语句
    connection.commit()
    return result

第三步,直接写接口

#数据接口                   
@app.route("/num",methods=["GET"])
def add_numbers():
    #前端请求发过来的参数
    city = int(request.args.get("a"))
    district = int(request.args.get("b"))
    
    if city == -1:
        pass
    elif city == 0:
        sql = 'SELECT ProvinceID,ProvinceName FROM S_Province'
        result = search(sql)
    else:
        sql = 'SELECT CityID,CityName FROM S_City where ProvinceID='+repr(city)
        result = search(sql)

    if district == -1:
        pass
    else:
        sql = 'SELECT DistrictID,DistrictName FROM s_district where CityID='+repr(district)
        result = search(sql)
		
    return jsonify(result=result)

5.ajax展示数据

<script>
	    $(function(){
		alert("开始查询");
		  $("#but").click(function(){
		    $.getJSON($SCRIPT_ROOT + '/num',{
			   a:0,b:-1
			 },function(data){
			    var result = data.result;
			    make_up(result,"#s1",'ProvinceID','ProvinceName');
			 });
			  return false;
		  });
		  	
		  
		
		/*
		function make_up(data){
		    for( i of data){
			   console.log(i);
			}
		}
		*/
		//处理函数
		function make_up(data,where,cID,cName){
		  $(where).append("<option value=-1></option>");
		  $.each(data,function(){
		    var name = $(this)[0][cName];
			var ID = $(this)[0][cID];
			var lable = "<option value="+"'"+ID+"'"+">"+name+"</option>" ;
			
			$(where).append(lable);
		  })
		}
		
		//委托事件1
		
		$(document.body).delegate('#s1','change',function(){
		   var value = Number($("#s1 option:selected").val());
		   
		     $("#s2").empty();
			 $("#s3").empty();
			 
		   if($("#s2").length > 0){
		      $.getJSON($SCRIPT_ROOT + '/num',{
			   a:value,b:-1
			   },function(data){
			    var result = data.result;
			    make_up(result,"#s2",'CityID','CityName');
			});
		   }else{
		      $("#s1").after('<br/><select class="form-control" id="s2"></select>');
		      $.getJSON($SCRIPT_ROOT + '/num',{
			   a:value,b:-1
			  },function(data){
			    var result = data.result;
			    make_up(result,"#s2",'CityID','CityName');
			});
		   };
		  
		});
		
		//委托事件2
		
		$(document.body).delegate("#s2",'change',function(){
		    var value = Number($("#s2 option:selected").val());
			
			$("#s3").empty();
			
			if($("#s3").length > 0){
			  $.getJSON($SCRIPT_ROOT + '/num',{
			    b:value,a:-1
			  },function(data){
			    var result = data.result;
				make_up(result,"#s3",'DistrictID','DistrictName');
			  });
			}else{
			  $("#s2").after("<br/><select class='form-control' id='s3'></select>");
			  $.getJSON($SCRIPT_ROOT + '/num',{
			   b:value,a:-1
			  },function(data){
			    var result = data.result;
			    make_up(result,"#s3",'DistrictID','DistrictName');
			});
			};
		});
		
	})	

	   
	 </script>

posted @ 2018-05-14 13:29 数据菜鸟阅读(314) 评论(0) 收藏举报

刷新页面返回顶部

数据菜鸟