加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 (https://www.lidatong.com.cn/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 编程开发 > Python > 正文

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

发布时间:2020-12-20 10:55:51 所属栏目:Python 来源:网络整理
导读:环境准备 安装flask pip install? flask 项目结构如图 ? 1.新建配置文件conf.py #!/usr/bin/python # -*- coding:utf-8 -*- import logging,os from datetime import datetime # ============================ Global parameter ===========================

环境准备

安装flask

pip install? flask

项目结构如图

?

1.新建配置文件conf.py

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

import logging,os
from datetime import datetime


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

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

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

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

import xlrd
from conf import xlsPath
class ExcelUtil():
‘‘‘
返回格式为[(1,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 common.readexcel import ExcelUtil,xlsPath

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


def conn_db():
config = {
‘host‘: ‘127.0.0.1‘,
‘port‘: 3306,
‘user‘: ‘root‘,
‘passwd‘: ‘pwd‘,
‘charset‘: ‘utf8mb4‘,
‘cursorclass‘: pymysql.cursors.DictCursor
}

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)‘,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

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):
config = {
‘host‘: ‘127.0.0.1‘,
‘passwd‘: ‘123456‘,
‘cursorclass‘: pymysql.cursors.DictCursor
}

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

(编辑:李大同)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    推荐文章
      热点阅读