Issue at hand: Removing a User from Group and adding User to a Group
Many-to-One table Relationship
(Many Users to One Group)
Using: SQLAlchemy 1.0.9, Python 2.7.9
DB operates off of CRUD operations:
1. Create a group (e.g. 'admin')
2. Retrieve group (using either group name or id)
3. Update:
a) Update : add_user to group ('joe' to 'admin')
b) Update : remove_user from group ('bob' from 'editor')
4. Delete group
I am a newbie trying to get my hands dirty, but smartly here.
1. I am attempting to remove a .user from Group() in the function: update_remove_user without deleting the user from User(). I found something similar to what I want to do on Stacks here. and in the sqlalchemy docs
2. Adding a user to an existing group. Right now, the function is creating a group and user instead of retrieving a group and adding the user to it.
model: base.py
class Group(Base):
__tablename__ = 'groups'
id = Column(Integer, primary_key=True)
group_name = Column(String(50), unique=True)
created_on = Column(DateTime, default=datetime.utcnow)
modified_on = Column(DateTime, onupdate=datetime.utcnow) #new
user_id = Column(Integer, ForeignKey('users.id'))
user = relationship('User', backref='groups')
def __init__(self, group_name, *args):
self.group_name = group_name
self.args = args
def __repr__(self):
return "<Group(group_name='%s')>" % (self.group_name)
object.py
def create_group(self, group_name):
new_group = Group(group_name)
self.session.add(new_group)
self.session.commit()
return new_group
print 'this is the new group: ', new_group
def update_group_add_user(self, group_name, user):
g = self.session.query(Group).\
filter(Group.group_name == group_name).one()
if g:
g.append(user) # append not found
self.session.commit()
def update_group_remove_user(self, group_name, user):
remove_user = self.session.query(Group).\
filter(Group.group_name == group_name).\
filter(Group.user == user).one()
if remove_user:
r = remove_user.user
print 'test r : ', r
g = Group() # does not work
g.remove(r)
self.session.commit()
Changes per suggestions below:
Works except add_user REPLACES a user with new user when added to the SAME group (as indicated by Alex) with a Many-to-One relationship. So added a Many-to-Many relationship and the same issue is occurring. A user is being replace with another in the many-to-many relationship.
Changes are reflected with # below of working code that has resolved the issues.Code is working for a N-N relationship.
object.py
def update_group_add_user(self, group_name, user):
g = self.session.query(Group).\
filter(Group.group_name == group_name).one()
if g:
found_name = g.group_name
g.users = [user] # < replaced with working: g.users.append(user)
print 'found_name and user: ', found_name, user
self.session.commit()
def update_group_remove_user(self, group_name, user):
remove_user = self.session.query(Group).\
filter(Group.group_name == group_name).one()
if remove_user:
remove_user.users = None # < replaced with working remove_user.users.remove(user)
self.session.commit()
New Many to Many design:
association_t = Table('group_user_link', Base.metadata,
Column('group_id', Integer, ForeignKey('groups.id')),
Column('user_id', Integer, ForeignKey('users.id')))
class Group(Base):
__tablename__ = 'groups'
id = Column(Integer, primary_key=True)
name = Column(String(50), unique=True)
user = relationship('User', secondary='group_user_link', backref='groups')
# more...
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
username = Column(String(40), unique=True)
# more info