One of the many things I like about SQLAlchemy is the feature of reflected tables, this means I can develop dashboards for existing applications with my current favorite framework(Pylons). So I had to create an authorization mechanism for my dashboard and this recipe from the Pylons cookbook was just what I needed.
The only thing I had to change was the way my tables were being mapped since the users table schema was already defined. This is what I did:
user_table = schema.Table('users', Base.metadata, autoload=True, autoload_with=engine)
user_group_table = schema.Table('user_groups',Base.metadata,autoload=True,autoload_with=engine)
group_permission_table = schema.Table('group_permission',Base.metadata,autoload=True,autoload_with=engine)
permission_table = schema.Table('permissions', Base.metadata,autoload=True,autoload_with=engine)
group_table = schema.Table('groups', Base.metadata,autoload=True,autoload_with=engine)
orm.mapper(User,user_table, properties={
'groups':orm.relation(Group, secondary=user_group_table),
})
orm.mapper(Permission,permission_table,properties={
'groups':orm.relation(Group, secondary=group_permission_table),
})
orm.mapper(Group,group_table,properties={
'permissions':orm.relation(Permission, secondary=group_permission_table),
'users':orm.relation(User, secondary=user_group_table),
})
orm.mapper(UGT,user_group_table)
orm.mapper(GPT,group_permission_table)
And here’s the schema I had to create, except for the users table:
CREATE TABLE user_groups(
id serial NOT NULL,
user_id integer,
group_id integer,
CONSTRAINT user_groups_pkey PRIMARY KEY (id),
CONSTRAINT gid_fkey FOREIGN KEY (group_id) REFERENCES groups (id)
CONSTRAINT uid_fkey FOREIGN KEY (user_id) REFERENCES users (id)
)
CREATE TABLE group_permission(
id serial NOT NULL,
gid integer,
permid integer,
CONSTRAINT group_permission_pkey PRIMARY KEY (id),
CONSTRAINT groupid_fkey FOREIGN KEY (gid) REFERENCES groups (id),
CONSTRAINT permid_fkey FOREIGN KEY (permid) REFERENCES permissions (id)
)
CREATE TABLE permissions(
id serial NOT NULL,
pname character varying(50),
CONSTRAINT permissions_pkey PRIMARY KEY (id)
)
CREATE TABLE groups(
id serial NOT NULL,
gname character varying(50),
CONSTRAINT groups_pkey PRIMARY KEY (id)
)
CREATE TABLE users(
id serial NOT NULL,
username character varying(50),
email character varying(50),
upassword character varying(150),
CONSTRAINT users_pkey PRIMARY KEY (id)
)
And that’s all there’s to it. I was very surprise on how flexible SQLAlchemy is.