Flask-SQLAlchemy - How to append a value to a column?

Question

My total lack of knowledge in SQL queries is biting me hard right now.

In flask-Sqlalchemy, how to you append a value to a column that already has one without replacing the one already present?

I tried db.session.merge(user) and db.session.add(user) but both end up replacing the value in the column.

To be more precise, the Users table has a column named classes_id that is used to record the "id" field of the gym classes he attended. So that field needs to be updated with many other classes "id" as time goes.

Just in case, here are the models

class Classes(db.Model):
    __tablename__= 'classes'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    date = db.Column(db.DateTime)
    participants =  db.relationship('User', backref='classes', lazy='dynamic')
    status = db.Column(db.Integer)  #1 = open, 0 = closed

class User(UserMixin,db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), unique=True, index=True)
    email = db.Column(db.String(64),unique=True,index=True)
    firstname = db.Column(db.String(64))
    lastname = db.Column(db.String(64))
    fullname = db.Column(db.String(64))
    role_id = db.Column(db.Integer, db.ForeignKey('roles.id'))
    password_hash = db.Column(db.String(128))
    member_since = db.Column(db.DateTime(), default=datetime.utcnow)
    last_seen = db.Column(db.DateTime(), default=datetime.utcnow)
    notes = db.Column(db.Text())
    classes_id = db.Column(db.Integer, db.ForeignKey("classes.id"))

And the views.py

@main.route('/add_attendees/<int:class_id>',methods=['GET','POST'])
@login_required
def add_attendees(class_id):
    form = Find_member()
    if form.validate_on_submit():
        fullname = form.member_name.data
        find_username = fullname.find('-')
        username = fullname[find_username + 2:]
        user = User.query.filter_by(username=username).first()
        user.classes_id = class_id          
        db.session.merge(user)
        db.session.commit()
    return redirect(url_for('.add_attendees',class_id = class_id))
return render_template('members_list.html',form=form)

Aleksandr K. · Accepted Answer · 2015-04-28 03:51:50Z

2

Your models definitions are not what you really want. User.classes_id defines exactly one value. Right answer depends of requirements. What type of relationships must be there? Many User to many Classes (M:M)? In this case you would create models like:

user_to_classes = Table('user_to_classes', Base.metadata,
    Column('class_id', Integer, ForeignKey('class.id')),
    Column('user_id', Integer, ForeignKey('user.id'))
)

class Class(Base):
    ...
    # your old wrong code
    # participants = ...
    # here is right definition
    participants = relationship(
        'User', secondary=user_to_classes, backref='classes')

# In the User class you should not write any foreign keys and relations.
# There will be `classes` field defined in backref of `Class.participants

You can add a user to class in the following manner:

user = ...# some code that creates or selects a user
class_ = ...# some code that creates or selects a class
# here is the addition
class_.participants.append(user)
session.commit()

Look at Sqlalchemy docs about relationships.

answered Apr 28, 2015 at 3:51

Aleksandr K.

5282 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kinwolf Over a year ago

A huge thank you. You put me on the right track there. I configured the many-to-many relationship and used the .append like you described and it's working wonderfully now!(I had to modify the code a bit since I'm using flask-sqlalchemy, but now I knew at what part of the doc to look at!) Another table was created holding the classe.id and the user.id and I can now query who went to which class, etc. 2 thumbs-up!

Aleksandr K. Over a year ago

You're welcome. It seems, that the problem is not specific to SQLAlchemy or flask. It refers to common relational databases theory.

Collectives™ on Stack Overflow

Flask-SQLAlchemy - How to append a value to a column?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related