1

I have these two classes and a (many-to-many) secondary table linking them:

association_table = db.Table('association_table',
    db.Column('project_id',db.Integer, db.ForeignKey('project.id'), primary_key=True),
    db.Column('usr_id',db.Integer, db.ForeignKey('usr.id'), primary_key=True)
)

class Usr(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    full_name = db.Column(db.String(150), unique=True, nullable=False)
    email = db.Column(db.String(150), unique=True, nullable=False)
    password = db.Column(db.String(150), nullable=False)
    projects = db.relationship('Project', secondary=association_table, lazy='subquery', backref=db.backref('usrs', lazy=True))

class Project(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(150), unique=True, nullable=False)
    description = db.Column(db.String(1000))
    creation_date = db.Column(db.DateTime(timezone=True), server_default=func.now())
    status = db.Column(db.Boolean, nullable=False)
    tickets = db.relationship('Ticket')
    owner = db.Column(db.Integer, db.ForeignKey('usr.id'))

I want to remove one user from the association_table with his id (user no longer assigned to the project instance) without removing all the other users. How can I do that using Flask-SQLAlchemy?

Example: how can I convert the following command in flask-sqlalchemy?

DELETE FROM association_table WHERE usr_id = 1;
davidism
  • 121,510
  • 29
  • 395
  • 339
Cohiba
  • 23
  • 6

2 Answers2

0

To delete a user by it's id you must do the following:

user = User.query.get(id)
db.session.delete(user)
db.session.commit()

For more information check out this link: How to delete a record by id in Flask-SQLAlchemy

P.D: Replace User by the name of your table

NoNameAv
  • 423
  • 2
  • 14
  • I don't want to delete the user itself, I want to delete the relation between this user and the Project class. In other words, delete the usr.id in the "association_table". – Cohiba May 23 '22 at 19:10
0

you can use delete method from sqlalchemy:

from sqlalchemy import delete
stmt = delete(association_table).where(association_table.c.usr_id == 1)
db.session.execute(stmt)
Vahid Rafael
  • 29
  • 1
  • 3