flask sqlalchemy序列化查询结果

发表于 | 分类于 | 阅读次数

在flask开发运维后台的时候, 比较多的需求是, 从数据库取得结果, 需要序列化之后传递给前端的表格插件.

下面我们创建一个基类, 在定义model的时候, 继承就可以达到想要的结果了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
class SerializeMixin(object):
    'Mixin for retrieving public fields of model in json-compatible format'
    __public__ = None
    __exclude__ = None
    def to_json(self, exclude=[], include=[], only=[], convert=True, replace=False, extend={}, **kwargs):
        """
        :param exclude: 在转化时指定需要排除的字段列表
        :param include: 在转化时指定需要包含的字段列表
        :param only:    在转化时指定仅仅包含的字段列表
        :param convert: 附加转换结果显示,当需要转换值的显示成指定的状态值使用, 状态值需要在kwargs指定.\
                     {'status': {'0': u'正常', '1': u'故障',...}}, 将会在返回的字段中附加一个同名的且前面加'_'的字段,\
                     当status的值为'0'时, 那返回值会附加的'_status'字段,且'_status'字段值为u'正常'.
                    当存在外键的时候, 会获取外键中是否有'_get_public'方法,有的化将显示一个友好的值.
        :param replace: 当为True时,将不会产生_下划线的转换字段,而是直接替换
        :param extend: 扩充值, 想格式化的json字符串扩充值,主要是解决外键引用而又想显示值
        :param kwargs:
        :return:
        """
        data = {}
        items = self._sa_instance_state.attrs.items()
        column_name = self._sa_instance_state.attrs.keys()
        self.convert = convert
        self.kwargs = kwargs
        if only:
            """ If you specify the parameters of only """
            _public = set(only)
            _exclude = set([])
        else:
            if self.__public__ and self.__exclude__ is None:
                """ __public__ is True and __exclude__ is False """
                _public = self.__public__
                _exclude = []
            elif self.__public__ is None and self.__exclude__:
                """ __public__ is False and __exclude__ is True """
                _public = []
                _exclude = self.__exclude__
            elif self.__public__ and self.__exclude__:
                """ __public__ is True and __exclude__ is True """
                _exclude = self.__exclude__
                _public = set(self.__public__) - set(_exclude)
            else:
                """ __public__ is False and __exclude__ is False """
                _public = column_name
                _exclude = []
            if exclude:
                """ If you specify the parameters of exclude """
                _exclude = set(_exclude + exclude)
            if include:
                """ If you specify the parameters of include """
                _public = set(_public + include) - set(_exclude)
        for key, field in items:
            if _public and key not in _public: continue
            if key in _exclude: continue
            value = self._serialize(field.value)
            data[key] = value
            if convert and kwargs and kwargs.has_key(key):
                if replace is True:
                    _key = '%s' % key
                else:
                    _key = '_%s' % key
                data[_key] = kwargs.get(key, value)
                # data[_key] = kwargs[key].get(value, value)
        if extend:
            data.update(extend)
        return data
    @classmethod
    def _serialize(cls, value, follow_fk=False):
        ret = value
        if isinstance(value, datetime.datetime) or isinstance(value, datetime.date):
            ret = str(value)
        elif isinstance(value, uuid.UUID):
            ret = str(value)
        elif BaseModelMixin in value.__class__.__bases__ or SerializeMixin in value.__class__.__bases__:
            # 一对多或者多对一
            try:
                ret = value._get_public()
            except:
                pass
        elif AppenderMixin in value.__class__.__bases__:
            # 多对多
            try:
                ret = [x._get_public() for x in value.all()]
            except Exception, ex:
                print 'many to many : ', ex
        elif hasattr(value, '__iter__'):
            # 迭代对象
            # Don't set True
            if follow_fk:
                ret = []
                for v in value:
                    ret.append(cls._serialize(v))
        return ret
class BaseModelMixin(SerializeMixin):
    """ 定义基础的Model用于继承 """
    __table_args__ = {
        'mysql_engine': u'InnoDB',
        'mysql_default_charset': u'utf8'
    }
    def __repr__(self):
        if hasattr(self, 'name'):
            return '<%s %s>' % (self.__class__.__name__, self.name)
        else:
            return '<%s %s>' % (self.__class__.__name__, self.id )
    def _get_public(self):
        """ 当存在外键的时候, 一个友好的显示名称 """
        ret = self
        try:
            if hasattr(self, 'name'):
                rt = self.name
            elif hasattr(self, 'id'):
                rt = self.id
        except Exception, ex:
            print '_get_public error:%s' % str(ex)
        return rt
    def save(self):
        db.session.add(self)
        db.session.commit()
    def delete(self):
        db.session.delete(self)
        db.session.commit()

在model定义时, 可以选指定在子类中指定需要序列化的字段public 或者 exculde 指定需要排除的字段, 注意当model中定义了外键的时候,稍后在做解释.

1
2
3
4
5
6
7
8
9
10
class Test(db.Model, BaseModelMixin):
    __tablename__ = 'test'
    __public__ = ['id', 'name', 'email']
 
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(100), nullable=False, unique=True)
 
    def __repr__(self):
        return '<Test %r>' % self.name

当然也可以不用指定public. 这样就会序列化Test 的全部的表字段, 下面做一些测试

测试2中情况:

  1. 指定public 字段 或者 指定exclude 字段;
  2. 什么都不指定, 在查询的时候, 才指定
  3. 下面是指定了public 字段时的结果, exclude 就不在演示了
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
In [5]: db.session.execute(Test.__table__.insert(), [dict(name='name_%s'%x, email='%s@qq.com'% x) for x in range(1,10)])
Out[5]: <sqlalchemy.engine.result.ResultProxy at 0x4737e50>
 
In [6]: db.session.commit()
In [7]: Test.query.all()
Out[7]: 
[<Test u'name_1'>,
 <Test u'name_1'>,
 <Test u'name_1'>,
 <Test u'name_2'>,
 <Test u'name_3'>,
 <Test u'name_4'>,
 <Test u'name_5'>,
 <Test u'name_6'>,
 <Test u'name_7'>,
 <Test u'name_8'>,
 <Test u'name_9'>]
 
In [8]: [ x.to_json() for x in Test.query.all() ]
Out[8]: 
[{'id': 1L, 'name': u'name_1'},
 {'email': u'%s@qq.com', 'id': 3L, 'name': u'name_1'},
 {'email': u'1@qq.com', 'id': 5L, 'name': u'name_1'},
 {'email': u'2@qq.com', 'id': 6L, 'name': u'name_2'},
 {'email': u'3@qq.com', 'id': 7L, 'name': u'name_3'},
 {'email': u'4@qq.com', 'id': 8L, 'name': u'name_4'},
 {'email': u'5@qq.com', 'id': 9L, 'name': u'name_5'},
 {'email': u'6@qq.com', 'id': 10L, 'name': u'name_6'},
 {'email': u'7@qq.com', 'id': 11L, 'name': u'name_7'},
 {'email': u'8@qq.com', 'id': 12L, 'name': u'name_8'},
 {'email': u'9@qq.com', 'id': 13L, 'name': u'name_9'}]
  1. 当不指定时, 修改之前定义的model. 去掉 public
1
2
3
4
5
6
7
8
9
10
11
class Test(db.Model, AutoSerialize):
    __tablename__ = 'test'
    # __public__ = ['id', 'name', 'email']
 
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(100), nullable=False, unique=True)
 
    def __repr__(self):
        return '<Test %r>' % self.name

重新测试, 同样会遍历出所有字段

1
2
3
4
5
6
7
8
9
10
11
12
13
In [1]: [ x.to_json() for x in Test.query.all() ]
Out[1]: 
[{'id': 1L, 'name': u'name_1'},
 {'email': u'%s@qq.com', 'id': 3L, 'name': u'name_1'},
 {'email': u'1@qq.com', 'id': 5L, 'name': u'name_1'},
 {'email': u'2@qq.com', 'id': 6L, 'name': u'name_2'},
 {'email': u'3@qq.com', 'id': 7L, 'name': u'name_3'},
 {'email': u'4@qq.com', 'id': 8L, 'name': u'name_4'},
 {'email': u'5@qq.com', 'id': 9L, 'name': u'name_5'},
 {'email': u'6@qq.com', 'id': 10L, 'name': u'name_6'},
 {'email': u'7@qq.com', 'id': 11L, 'name': u'name_7'},
 {'email': u'8@qq.com', 'id': 12L, 'name': u'name_8'},
 {'email': u'9@qq.com', 'id': 13L, 'name': u'name_9'}]

我们在查询的时候, 也可以指定排除的字段exclude, 或者仅包含指定的字段only

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
In [2]: [ x.to_json(exclude=['id']) for x in Test.query.all() ] 
Out[2]: 
[{'name': u'name_1'},
 {'email': u'%s@qq.com', 'name': u'name_1'},
 {'email': u'1@qq.com', 'name': u'name_1'},
 {'email': u'2@qq.com', 'name': u'name_2'},
 {'email': u'3@qq.com', 'name': u'name_3'},
 {'email': u'4@qq.com', 'name': u'name_4'},
 {'email': u'5@qq.com', 'name': u'name_5'},
 {'email': u'6@qq.com', 'name': u'name_6'},
 {'email': u'7@qq.com', 'name': u'name_7'},
 {'email': u'8@qq.com', 'name': u'name_8'},
 {'email': u'9@qq.com', 'name': u'name_9'}]
 
In [3]: [ x.to_json(only=['name']) for x in Test.query.all() ]                  
Out[3]: 
[{'name': u'name_1'},
 {'name': u'name_1'},
 {'name': u'name_1'},
 {'name': u'name_2'},
 {'name': u'name_3'},
 {'name': u'name_4'},
 {'name': u'name_5'},
 {'name': u'name_6'},
 {'name': u'name_7'},
 {'name': u'name_8'},
 {'name': u'name_9'}]

当然可以根据自己的需求, 去修改序列化的基类. 上面定义的基类, 在逻辑判断中没有考虑指定多个条件时的情况, 所以根据自己的需求, 自己修改;

题外话, 当我们model中有定义外键的时候, 那序列化的字段, 就会变成引用表的id值(通常是这样的)

1
2
3
4
5
elif BaseModelMixin in value.__class__.__bases__ or SerializeMixin in value.__class__.__bases__:
    try:
        ret = value._get_public()
    except:
        pass

而处理的方式就在上面, 直接或者引用表的的’_get_public’方法, 而当没有定义的时候, 在就取默认值了

分享即快乐，谢谢你请思哲小朋友吃糖!