Re: [sqlalchemy] SQLAlchemy Many-to-Many Users, Groups, Roles

[Leslie Luyt]

This is fantastic, I didn't realise you could define a table using a sql 
expression. I am always amazed by how amazing and configurable SQLAlchemy 
is.

Thank you so much for the help with this.

PS. I am a huge fan. Please give Dilly a pat for me =).

On Saturday, October 21, 2017 at 5:14:28 AM UTC+2, Mike Bayer wrote:
>
> note I inadvertently used a previous form for the first GroupProfile 
> example.   The two forms are: 
>
>
> class GroupProfile(Base): 
> __table__ = ( 
> select([Group.group_id, Profile]). 
> select_from(join(Group, ProfileGroupRole).join(Profile)). 
> distinct().alias() 
> ) 
> ... 
>
> and the potentially more efficient one: 
>
> distinct_profile_groups = select( 
> [ProfileGroupRole.group_id, ProfileGroupRole.profile_id]).\ 
> distinct().alias() 
>
>
> class GroupProfile(Base): 
> # the GroupProfile itself is then based on Group/Profile rows 
> # joined to our distinct group_id/profile_id pairs 
> __table__ = ( 
> select([Group.group_id, Profile]). 
> select_from(join(Group, distinct_profile_groups).join(Profile)). 
> alias() 
> ) 
>
>
>
> The second form applies DISTINCT to only two columns, whereas the 
> first applies DISTINCT to all columns in Profile, which can be 
> wasteful.   A third form could use Postgresql DISTINCT ON to limit the 
> scope of the DISTINCT without using a second subquery. 
>
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] SQLAlchemy Many-to-Many Users, Groups, Roles

[Leslie Luyt]

Here is my example:

class Role(Base):
__tablename__ = 'role'
role_id = Column(BigInteger, primary_key=True)
role_name = Column(String, nullable=False)
role_description = Column(String)


class Group(Base):
__tablename__ = 'group'
group_id = Column(BigInteger, primary_key=True)
group_name = Column(String, nullable=False)
group_description = Column(String)

profiles = association_proxy('_profile_group_roles', 'profile')


class Profile(Base):
__tablename__ = 'profile'
profile_id = Column(BigInteger, primary_key=True)
profile_username = Column(String, nullable=False, unique=True)

groups = association_proxy('_profile_group_roles', 'group')
roles = association_proxy('_profile_group_roles', 'role')


class ProfileGroupRole(Base):
__tablename__ = 'profile_group_role_assoc'
profile_id = Column(BigInteger, ForeignKey('profile.profile_id'), 
primary_key=True)
group_id = Column(BigInteger, ForeignKey('group.group_id'), 
primary_key=True)
role_id = Column(BigInteger, ForeignKey('role.role_id'), 
primary_key=True)

group = relationship(Group, backref=backref('_profile_group_roles', 
cascade='all, delete-orphan'))
role = relationship(Role)
profile = relationship(Profile, backref=backref('_profile_group_roles', 
cascade='all, delete-orphan'))


Example data:
s = db.connect(engine_config)
profile1 = db.Profile(profile_username='test_user')
group1 = db.Group(group_name='group1', group_description='')
role1 = db.Role(role_name='role1')
prg1 = db.ProfileGroupRole(profile=profile1, group=group1, 
role=role1)
group2 = db.Group(group_name='group2', group_description='')
role2 = db.Role(role_name='role2')
prg2 = db.ProfileGroupRole(profile=profile1, group=group2, 
role=role1)
prg3 = db.ProfileGroupRole(profile=profile1, group=group2, 
role=role2)
s.add_all([prg1, prg2, prg3])
s.commit()

Querying:
db.Group.query.filter(db.Group.group_name == 
'group1').one().profiles[0].roles

produces:
[Role{'role_id': 4, 'role_name': 'role1', 'role_description': None}, 
Role{'role_id': 4, 'role_name': 'role1', 'role_description': None}, 
Role{'role_id': 5, 'role_name': 'role2', 'role_description': None}]

and now what I expected to get back was just one the one role associated 
with 'group1':
[Role{'role_id': 4, 'role_name': 'role1', 'role_description': None}]

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] SQLAlchemy Many-to-Many Users, Groups, Roles

[Leslie Luyt]

 

I am trying to integrate with an existing user-group-role table structure 
where a user can belong to many groups and have multiple roles on each 
group.


I found a similar question to this, however it does not allow for multiple 
roles: Many-to-many declarative SQLAlchemy definition for users, groups, 
and roles 





I have the following table structure and would like to be able to access 
the roles in the following sort of manner: group.users[0].roles

It would also be nice to be able to access it from the other directions but 
is not required i.e. user.groups[0].roles or role.groups[0].users


class Role(Base):
__tablename__ = 'roles'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(Unicode(16), unique=True)
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(Unicode(16), unique=True)
class Group(Base):
__tablename__ = 'groups'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(Unicode(16), unique=True)
class UserGroupRole(Base):
__tablename__ = 'user_group_role'
id = Column(Integer, primary_key=True, autoincrement=True)
user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), 
nullable=False)
group_id = Column(Integer, ForeignKey('groups.id', ondelete='CASCADE'), 
nullable=False)
role_id = Column(Integer, ForeignKey('roles.id', ondelete='CASCADE'), 
nullable=False)


-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.