从数据库获取数据-接口开发

 1 #从数据库获取数据
 2 import pymysql,hashlib
 3 import flask,json
 4 
 5 def op_mysql(sql:str):
 6     mysql_info = {
 7         'host': 'xx.2xx.z.zz0',
 8         'port': 3306,
 9         'password': 'ssdfds6',
10         'user': 'xx',
11         'db': 'jxxx',
12         'charset': 'utf8',
13         'autocommit': True
14     }
15     result = '执行完成'
16     conn = pymysql.connect(**mysql_info)
17     cur = conn.cursor(pymysql.cursors.DictCursor) #建立游标
18     cur.execute(sql)
19     if sql.strip().lower().startswith('select'):
20         # result  = cur.fetchone()
21         result  = cur.fetchall()
22     cur.close()
23     conn.close()
24     return result
25 
26 server = flask.Flask(__name__)
27 
28 @server.route('/table_data')
29 def get_table_data():
30     table_name = flask.request.args.get('table_name')
31     limit = flask.request.args.get('limit')
32     sql = 'select * from %s limit %s'%(table_name,limit)
33     result = op_mysql(sql) #结果是一个列表
34  #[{"id": 422, "username": "glw", "passwd": "123455", "is_admin": 123, "error_count": 0},
35 # {"id": 424, "username": "glw1", "passwd": "123455", "is_admin": 123, "error_count": 0}, ]
36     return json.dumps(result,ensure_ascii=False) #list也可以转成json串
37 
38 #优化 要是传参的时候 不传limit或者不传table_name或者传不存在的表名称或者limit传的不是数字
39 @server.route('/table_data')
40 def get_table_data():
41     #没有实现校验表是否存在
42     #只能获取这几张表
43     tables = ['app_myuser','dsk_test app_student','pp_product']
44     table_name = flask.request.args.get('table_name')
45     limit = flask.request.args.get('limit','10')
46     if table_name not in tables:
47         return json.dumps({'msg':'没有获取这个表的权限！'})
48     if not table_name:
49         return json.dumps({'msg':'table_name是必填字段！'},ensure_ascii=False)
50     if limit.isdigit():
51         sql = 'select * from %s limit %s'%(table_name,limit)
52     else:
53         return json.dumps({'msg':'limit请传入一个整数！'},ensure_ascii=False)
54     result = op_mysql(sql)
55     return json.dumps(result, ensure_ascii=False)  # list也可以转成json串
56 
57 def md5(s,):
58     s = (str(s)+'#$%$%').encode()
59     m = hashlib.md5(s)#加密
60     return m.hexdigest()
61 
62 
63 #实现添加成员 #并且密码加密存数据库
64 @server.route('/add_mem',methods=['post'])
65 def add_mem():
66     username = flask.request.json.get('username')
67     password = flask.request.json.get('password')
68     if username and password:
69         sql = 'select * from app_myuser where username = "%s"' %username
70         if op_mysql(sql):
71             data = {'msg':'用户已存在！'}
72         else:
73             new_password = md5(password)
74             insert_sql = 'insert into app_myuser(username,passwd) value ("%s","%s")'%(username,new_password)
75             data = {'msg':'用户添加成功！'}
76             op_mysql(insert_sql)
77     else:
78         data = {'msg':'必填参数未填，请查看接口文档！'}
79     return json.dumps(data,ensure_ascii=False)

 

转载于:https://www.cnblogs.com/baiby/p/10972133.html