SQLalchemy查询数据对象转化为字典_sqlalchemy 结果转换字典类型

ORM技术：Object-Relational Mapping，把关系数据库的表结构映射到对象上

但是由谁来做这个转换呢？所以ORM框架应运而生。

在Python中，最有名的ORM框架是SQLAlchemy。

当时用sqlalchemy时，查询到的是一个对象，而不是列表。解决方法。
一.查询单条数据。
model

from sqlalchemy import Column, String, Integer, ForeignKey

# db = SQLAlchemy()
from sqlalchemy.orm import relationship

from app.models.base import Base


class Test(Base):
    id = Column(Integer,primary_key=True,autoincrement=True)
    # fourtest= relationship('Fourtest')
    # uid = Column(Integer,ForeignKey('fourtest.id'))
    name = Column(String(50),nullable = False)
    age = Column(String(50), nullable=False)
    # 查询单条数据
    def to_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}
    #第二种方法
    def to_dict(self):
        model_dict = dict(self.__dict__)
        del model_dict['_sa_instance_state']
        return model_dict
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

web

import math
from flask import render_template, request

from app.models.base import db
from app.models.test import Test
from app.models.test4 import Fourtest
from . import web_blue

@web_blue.route('/helo')
def hello():
    list = Test.query.filter_by(id=1).first()
    print(type(list))#<class 'app.models.test.Test'>
    a = list.to_dict()
    print(a)#{'age': '18', 'id': 1, 'stats': '1', 'name': '猪猪'}
    return 'helloworld'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

查询多条数据。

from sqlalchemy import Column, String, Integer, ForeignKey

# db = SQLAlchemy()
from sqlalchemy.orm import relationship

from app.models.base import Base


class Test(Base):
    id = Column(Integer,primary_key=True,autoincrement=True)
    # fourtest= relationship('Fourtest')
    # uid = Column(Integer,ForeignKey('fourtest.id'))
    name = Column(String(50),nullable = False)
    age = Column(String(50), nullable=False)
  	 
  	def dobule_to_dict(self):
        result = {}
        for key in self.__mapper__.c.keys():
            if getattr(self, key) is not None:
                result[key] = str(getattr(self, key))
            else:
                result[key] = getattr(self, key)
        return result
    #配合todict一起使用
    def to_json(all_vendors):
        v = [ven.dobule_to_dict() for ven in all_vendors]
        return v

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

web.py

import math
from flask import render_template, request

from app.models.base import db
from app.models.test import Test
from app.models.test4 import Fourtest
from . import web_blue

@web_blue.route('/helo')
def hello():
    list = Test.query.filter().all()
    data = Test.to_json(list)
    print(data)
    #[{'stats': '1', 'id': '1', 'name': '猪猪', 'age': '18'}, {'stats': '1', 'id': '2', 'name': '小飞', 'age': '19'}, {'stats': '1', 'id': '3', 'name': '小春', 'age': '18'}]
    return 'helloworld'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17