关于flask入门教程-记录集转jsonify

python与大数据分析

发布于 2022-03-11 17:15:15

1.3K00

代码可运行

文章被收录于专栏：python与大数据分析python与大数据分析

运行总次数：0

代码可运行

Flask 框架里，可以用 jsonify 返回 json 数据，但是为什么不用 Python 自带的 json 模块返回 JSON 数据呢？

其实两者是差不多的，jsonify指明了Content-Type 是 application/json ，这样做是符合 HTTP 协议的规定的，这就是使用 jsonify 的原因之一。

但是从数据集转jsonify有很多坑需要踩，本文就是数据集转jsonify踩坑指南和数据集转jsonify处理的几种办法，互联网上零零散散的也有，但都是语焉不详。

import config
from exts import db
from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
from flask import jsonify, make_response

app = Flask(__name__)
# 从config.py文件中获取数据库配置信息
app.config.from_object(config)
db = SQLAlchemy(app)

@app.route('/weather', methods=['GET', 'POST'])
def getjson():
pass
if __name__ == '__main__':
    app.run()

下面代码封装了一个将记录集和数据字段名称转换为字典列表的函数，通过cursor.description获取数据字段元数据，通过zip将字段名和结果封装起来。

def rows_as_dicts(cursor):
    # 将游标集转换为字典类型列表
    col_names = [i[0] for i in cursor.description]
    return [dict(zip(col_names, row)) for row in cursor]

下面代码是通过mappings方法将结果集转换为字典列表

sql = "select userid,username,password,personalname,birthday,sex,phone,postcard,address from userinfo "
result = db.session.execute(sql)
# 1、mappings方式，最简单直接
results_as_dict = result.mappings().all()
# [{'userid': 1, 'username': 'admin', 'password': '1qaz!QAZ', 'personalname': 'adminstrator', 'birthday': '1976-11-08', 'sex': '男', 'phone': 'eeee', 'postcard': 'dddd', 'address': '44444444'},
# {'userid': 2, 'username': 'admin1', 'password': '1qaz!QAZ', 'personalname': 'adminstrator', 'birthday': '1976-11-03', 'sex': '男', 'phone': '', 'postcard': '', 'address': '423'},
# ...
# {'userid': 17, 'username': 'admin33333', 'password': '1qaz!QAZ', 'personalname': '333', 'birthday': '2022-02-17', 'sex': '男', 'phone': '3333', 'postcard': '333', 'address': '333333'}]

下面是数据集操作返回的数据类型

result = db.session.execute(sql)
print('result=', result)
# result= <sqlalchemy.engine.cursor.CursorResult object at 0x0000025FF5B36438>
print('type(result)=', type(result))
# type(result)= <class 'sqlalchemy.engine.cursor.CursorResult'>
result = db.session.execute(sql).fetchall()
print('result=', result)
# result= [(1, 'admin', '1qaz!QAZ', 'adminstrator', '1976-11-08', '男', 'eeee', 'dddd', '44444444'),
# (2, 'admin1', '1qaz!QAZ', 'adminstrator', '1976-11-03', '男', '', '', '423'),
print('type(result)=', type(result))
# type(result)= <class 'list'>
result = db.session.execute(sql).cursor
print('result=', result)
# result= <sqlite3.Cursor object at 0x0000025FF5AC6B20>
print('type(result)=', type(result))
# type(result)= <class 'sqlite3.Cursor'>

通过rows_as_dicts转换为字典列表

# 2、自定义函数，将游标集转换为字典列表
cursor = db.session.execute(sql).cursor
result = rows_as_dicts(cursor)
# [{'userid': 1, 'username': 'admin', 'password': '1qaz!QAZ', 'personalname': 'adminstrator', 'birthday': '1976-11-08', 'sex': '男', 'phone': 'eeee', 'postcard': 'dddd', 'address': '44444444'},
# {'userid': 2, 'username': 'admin1', 'password': '1qaz!QAZ', 'personalname': 'adminstrator', 'birthday': '1976-11-03', 'sex': '男', 'phone': '', 'postcard': '', 'address': '423'},
# ...
# {'userid': 17, 'username': 'admin33333', 'password': '1qaz!QAZ', 'personalname': '333', 'birthday': '2022-02-17', 'sex': '男', 'phone': '3333', 'postcard': '333', 'address': '333333'}]

下面是分步骤获取数据操作返回的数据和数据类型

# 关于cursor和cursor.description介绍
cursor = db.session.execute(sql).cursor
# cursor.description返回元祖类型元数据
# print('cursor.description=',cursor.description)
# (('userid', None, None, None, None, None, None),
# ('username', None, None, None, None, None, None),
# ('password', None, None, None, None, None, None),
# ...
# ('address', None, None, None, None, None, None))
# [i[0] for i in cursor.description]，通过迭代获取元数据信息
# print('[i[0] for i in cursor.description]=',[i[0] for i in cursor.description])
# ['userid', 'username', 'password', 'personalname', 'birthday', 'sex', 'phone', 'postcard', 'address']
# print('[row for row in cursor]=',[row for row in cursor])
#  [(1, 'admin', '1qaz!QAZ', 'adminstrator', '1976-11-08', '男', 'eeee', 'dddd', '44444444'),
# (2, 'admin1', '1qaz!QAZ', 'adminstrator', '1976-11-03', '男', '', '', '423'),
# ...
# (17, 'admin33333', '1qaz!QAZ', '333', '2022-02-17', '男', '3333', '333', '333333')]
# db.session.execute返回CursorResult
r = db.session.execute(sql)
# db.session.execute(sql)= <sqlalchemy.engine.cursor.CursorResult object at 0x0000020353A069B0>
# 通过fetchall()获取记录集
result = r.fetchall()
# [(1, 'admin', '1qaz!QAZ', 'adminstrator', '1976-11-08', '男', 'eeee', 'dddd', '44444444'),
# (2, 'admin1', '1qaz!QAZ', 'adminstrator', '1976-11-03', '男', '', '', '423')
# ...
# (17, 'admin33333', '1qaz!QAZ', '333', '2022-02-17', '男', '3333', '333', '333333')]

下面是结合cursor.description和结果集生成字典列表

# 3、通过
resultproxy = db.session.execute(sql)
resultdict = []
# resultproxy= <sqlalchemy.engine.cursor.CursorResult object at 0x000001D48DB93B00>
# resultproxy.keys= <bound method _WithKeys.keys of <sqlalchemy.engine.cursor.CursorResult object at 0x000001D48DB93B00>>
colname = [i[0] for i in cursor.description]
print('colname=', colname)
for rowproxy in resultproxy:
    rowresult = dict(zip(colname, rowproxy))
    # rowproxy= (1, 'admin', '1qaz!QAZ', 'adminstrator', '1976-11-08', '男', 'eeee', 'dddd', '44444444')
    # rowproxy type= <class 'sqlalchemy.engine.row.Row'>
    # rowresult= {'userid': 1, 'username': 'admin', 'password': '1qaz!QAZ', 'personalname': 'adminstrator', 'birthday': '1976-11-08', 'sex': '男', 'phone': 'eeee', 'postcard': 'dddd', 'address': '44444444'}
    resultdict.append(rowresult)
# rowresult =
# [{'userid': 1, 'username': 'admin', 'password': '1qaz!QAZ', 'personalname': 'adminstrator', 'birthday': '1976-11-08', 'sex': '男', 'phone': 'eeee', 'postcard': 'dddd', 'address': '44444444'},
# {'userid': 2, 'username': 'admin1', 'password': '1qaz!QAZ', 'personalname': 'adminstrator', 'birthday': '1976-11-03', 'sex': '男', 'phone': '', 'postcard': '', 'address': '423'},
# ...
# {'userid': 17, 'username': 'admin33333', 'password': '1qaz!QAZ', 'personalname': '333', 'birthday': '2022-02-17', 'sex': '男', 'phone': '3333', 'postcard': '333', 'address': '333333'}]

下面是讲字典列表json化，并通过response响应回前端的两种方式

# jsonify及返回响应的两种方法
# 1、通过构造response方式
response = jsonify(resultdict)
response.status_code = 200  # or 400 or whatever
# ---------------response.data----------------
print('response.data',response.data)
# response.data b'[{"address":"44444444","birthday":"1976-11-08","password":"1qaz!QAZ","personalname":"adminstrator","phone":"eeee","postcard":"dddd","sex":"\\u7537","userid":1,"username":"admin"},
# {"address":"423","birthday":"1976-11-03","password":"1qaz!QAZ","personalname":"adminstrator","phone":"","postcard":"","sex":"\\u7537","userid":2,"username":"admin1"},
# ...
# {"address":"333333","birthday":"2022-02-17","password":"1qaz!QAZ","personalname":"333","phone":"3333","postcard":"333","sex":"\\u7537","userid":17,"username":"admin33333"}]\n'
# ---------------response.json----------------
# response.json [{'address': '44444444', 'birthday': '1976-11-08', 'password': '1qaz!QAZ', 'personalname': 'adminstrator', 'phone': 'eeee', 'postcard': 'dddd', 'sex': '男', 'userid': 1, 'username': 'admin'}, 
# {'address': '423', 'birthday': '1976-11-03', 'password': '1qaz!QAZ', 'personalname': 'adminstrator', 'phone': '', 'postcard': '', 'sex': '男', 'userid': 2, 'username': 'admin1'}, 
# ... 
# {'address': '333333', 'birthday': '2022-02-17', 'password': '1qaz!QAZ', 'personalname': '333', 'phone': '3333', 'postcard': '333', 'sex': '男', 'userid': 17, 'username': 'admin33333'}]
# response.headers Content-Type: application/json
# Content-Length: 3012
# ---------------response.headers----------------
# response.headers Content-Type: application/json
# ---------------response.status_code----------------
# response.status_code 200
# ---------------response.content_length----------------
# Content-Length: 3012
return response

# 2、通过make_response方式
# return make_response(jsonify(resultdict), 200)

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2022-02-22，如有侵权请联系 cloudcommunity@tencent.com 删除

css