3

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

1 Answer 1

1

First of all, are you sure that you want create Many-to-One relationship?

According to this relationship one "group" can contain only one user. This is something opposite to term "group". I think, you should create Many-to-Many relationship.

As for your first issue:

g = self.session.query(Group).\
filter(Group.group_name == group_name).one()
...
g.append(user) # append not found

When you make such query, the result is an instance of Group class. And this instance doesn't have append method. The right way to change user for this group is:

g.user = user

As for the second issue:

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()

Again, remove_user is an instance of Group now (so, you have chosen wrong name for it).

When you tried g = Group(); g.remove(r) you just created new instance of Group and tried to call method which doesn't exists. So, in order to delete this user from the group you should:

remove_user.user = None
self.session.commit()
Sign up to request clarification or add additional context in comments.

5 Comments

SEE ABOVE : Now a many-to-many table format with traceback error
Initially I wanted (as shown above): Many-to-One table Relationship where Many Users to One Group so that user1 could not belong to 2 or more groups.
Try remove_user.users.remove(user)
The _add_user method keeps replacing users and not adding a user. remove_user.users.remove(user) works!
Never mind, I used something similar to your recommendation: g.users.append(user) the append method works now. I am getting multiple adds for the same group. Thank you for all your help Alexsandr!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.