sqlalchemy 关联数据的添加和查询，转成json_sqlachemy tojson

类定义：

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import MetaData, Table, Column, ForeignKey, Integer, String, Boolean, DateTime

Base = declarative_base()

#region 角色-权限
groupRights = Table('t_db_group_rights', Base.metadata,
                    Column('groupId', Integer, ForeignKey('t_db_group.id')),
                    Column('rightsId', Integer, ForeignKey('t_db_rights.id'))
                    )
#endregion

# region 用户-角色
operatorGroup = Table('t_db_operator_group', Base.metadata,
                      Column('operatorId', Integer, ForeignKey('t_db_operator.id')),
                      Column('groupId', Integer, ForeignKey('t_db_group.id'))
                      )
# endregion

# region 用户-权限
operatorRights = Table('t_db_operator_rights', Base.metadata,
                       Column('operatorId', Integer, ForeignKey('t_db_operator.id')),
                       Column('rightsId', Integer, ForeignKey('t_db_rights.id'))
                       )


# endregion

#region 角色
class Group(Base):
    __tablename__ = 't_db_group'
    id = Column(Integer, primary_key=True)
    groupName = Column(String(30), unique=True)


    def toJson(self):
        dict = self.__dict__
        rights = []
        for r in self.rights:
            rights.append(r.toJson())
        dict["rights"] = rights
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        return dict
#endregion

#region 权限
class Rights(Base):
    __tablename__ = 't_db_rights'
    id = Column(Integer, primary_key=True)
    rightsName = Column(String(30), nullable=False)
    iconCls = Column(String(30))
    orderIndex = Column(Integer, default=0)
    fartherId = Column(Integer, default=0)
    urlPath = Column(String(255))
    rightType = Column(Integer, default=0)
    needLimit = Column(Boolean, default=0)
    groups = relationship('Group', secondary=groupRights, backref='t_db_rights')

    def toJson(self):
        dict = self.__dict__
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        return dict
#endregion

#region 操作用户
class Operator(Base):
    __tablename__ ='t_db_operator'
    id=Column(Integer,primary_key=True)
    userName = Column(String(30),nullable=False,unique=True)
    passWord = Column(String(60),nullable=False)
    userType = Column(Integer,nullable=False)
    groups = relationship('Group',secondary = operatorGroup,backref='t_db_operator')
    rights = relationship('Rights',secondary = operatorRights,backref = 't_db_operator')

    def toJson(self):
        dict = self.__dict__
        groups = []
        for g in self.groups:
            groups.append(g.toJson())
        dict["groups"] = groups
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        if "passWord" in dict:
            del dict["passWord"]
        return dict
#endregion
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

添加操作：

from testpro import config,  modelClass
from sqlalchemy.orm import sessionmaker

if __name__ == "__main__":
    session = sessionmaker(bind=config.engine)()

    g = modelClass.Group(groupName='管理组')

    r1 = modelClass.Rights(rightsName='后台首页', iconCls='x-fa fa-home', orderIndex=1, fartherId=0,
                           urlPath='admindashboard',
                           rightType=1, needLimit=1)
    r2 = modelClass.Rights(rightsName='系统设置', iconCls='x-fa fa-cogs', orderIndex=2, fartherId=0,
                           urlPath='#',
                           rightType=1, needLimit=1)
    g.rights = [r1, r2]

    u = session.query(modelClass.Operator).filter_by(userName='admin').one()
    u.groups = [g]
    u.rights = g.rights
    session.commit()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

查询操作：

u = session.query(modelClass.Operator).filter_by(userName='admin').one()
print(u.toJson().__str__())
1
2