查询接口---flask+python+mysql

环境准备

安装flask

pip install  flask

项目结构如图

 

1.新建配置文件conf.py

#!/usr/bin/python
# -*- coding:utf-8 -*-

import pymysql,os


# ============================ Global parameter ==============================
proDir = os.path.split(os.path.realpath(__file__))[0]
print(proDir)
xlsPath = os.path.join(proDir, 'testFile')
#============================ DB Config ==============================

config = {

    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'passwd': 'pwd',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}

2.新建目录testFile，将excel表格放到此目录下

 

3.原始数据处理，excel表数据导入mysql库

新建readexcel.py，读取excel数据，返回格式为[(1,2,3),(3,4,5)]

import xlrd
from conf import xlsPath
class ExcelUtil():
    '''
    返回格式为[(1,2,3),(3,4,5)]
    '''
    def __init__(self, excelPath, sheetIndex=0):
        self.data = xlrd.open_workbook(excelPath)
        self.table = self.data.sheet_by_index(sheetIndex)
        # 获取第一行作为key值
        self.keys = self.table.row_values(0)
        # 获取总行数
        self.rowNum = self.table.nrows
        # 获取总列数
        self.colNum = self.table.ncols

    def dict_data(self):
        r = []
        j = 1
        for i in list(range(self.rowNum-1)):
            values = self.table.row_values(j)
            r.append(tuple(values))
            j += 1
        return r

if __name__ == "__main__":
    filepath = xlsPath+'/testsalary.xlsx'
    sheetIndex = 0
    data = ExcelUtil(filepath, sheetIndex).dict_data()
    print(data)

4.操作数据库connDB.py,将excel数据批量入库

#!/usr/bin/python
# -*- coding:utf-8 -*-


import pymysql
from conf import config
from common.readexcel import ExcelUtil, xlsPath

filepath = xlsPath + '/testsalary.xlsx'
sheetIndex = 0
data = ExcelUtil(filepath, sheetIndex).dict_data()


def conn_db():
    conn = pymysql.connect(**config)
    conn.autocommit(1)
    cursor = conn.cursor()

    try:
        # 创建数据库
        DB_NAME = 'test'
        cursor.execute('DROP DATABASE IF EXISTS %s' % DB_NAME)
        cursor.execute('CREATE DATABASE IF  NOT EXISTS %s ' % DB_NAME)
        conn.select_db(DB_NAME)

        # 创建表
        TABLE_NAME = 'user'
        cursor.execute(
            'CREATE TABLE %s(company varchar(30),Account varchar(30) primary key,'
            'name varchar(30), Duties varchar(30), Jobwages varchar(30),'
            'Rankwages varchar(30),'
            'workyears varchar(30),70percent varchar(30),'
            '30percent varchar(30),'
            'totoal_wages varchar(30),housing_fund varchar(30),'
            'Medical_insurance varchar(30),Pension varchar(30),'
            'Career_Annuities varchar(30),Taxes varchar(30),'
            'total_Deduction varchar(30),Actual_wages varchar(30))' % TABLE_NAME)

        # 批量插入纪录
        values = []
        for i in data:
            values.append(i)
        cursor.executemany('INSERT INTO user VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)', values)
        # 查询数据条
        cursor.execute('SELECT * FROM %s' % TABLE_NAME)
        print('total records:', cursor.rowcount)
        result = cursor.fetchall()
        return result

    except:
        import traceback
        traceback.print_exc()
        # 发生错误时会滚
        conn.rollback()
    finally:
        # 关闭游标连接
        cursor.close()
        # 关闭数据库连接
        conn.close()


if __name__ == "__main__":
    print(conn_db())

5.数据准备好，开始写接口，新建api.py

from flask import Flask, request
import json
import pymysql
from conf import config

app = Flask(__name__)


# 只接受get方法访问
@app.route("/select/salary/", methods=["GET"])
def check():
    # 默认返回内容
    return_dict = {'code': '200', 'msg': '处理成功', 'result': False}
    # 判断入参是否为空
    if request.args is None:
        return_dict['return_code'] = '504'
        return_dict['return_info'] = '请求参数为空'
        return json.dumps(return_dict, ensure_ascii=False)
    # 获取传入的参数
    get_data = request.args.to_dict()
    Account = get_data.get('Account')
    # age = get_data.get('age')
    # 对参数进行操作
    return_dict['result'] = sql_result(Account)

    return json.dumps(return_dict, ensure_ascii=False)


# 功能函数
def sql_result(Account):
    conn = pymysql.connect(**config)
    conn.autocommit(1)
    conn.select_db('test')
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM test.user WHERE Account= %s' % Account)
    # print('total records:', cursor.rowcount)
    result = cursor.fetchall()
    conn.close()
    return result[0]


if __name__ == "__main__":
    app.run(host='127.0.0.1',port=5000)

 6.浏览器访问

http://127.0.0.1:5000/select/salary/?Account=62268200113006149