Geek Answers Handbook

SQLAlchemy removes association objects

I am trying to remove objects from an association table by filtering by column in one of the relationships. I use the following call in SQLAlchemy to remove

db.session.query(UserPaper).join(Paper, (UserPaper.paper_id ==
Paper.id)).filter(UserPaper.user_id == user.id).filter(Paper.journal_id 
== journal.id).delete()

and this leads to the following error

OperationalError: (OperationalError) (1054, "Unknown column 'papers.journal_id' 
in 'where clause'") 'DELETE FROM userpapers WHERE userpapers.user_id = %s AND
papers.journal_id = %s' (1L, 1L)

No deletion at the end of SQLAlchemy query

SELECT userpapers.user_id AS userpapers_user_id, userpapers.paper_id AS 
userpapers_paper_id, userpapers.created AS userpapers_created, 
userpapers.read_at AS userpapers_read_at, userpapers.score AS userpapers_score
FROM userpapers JOIN papers ON userpapers.paper_id = papers.id
WHERE userpapers.user_id = :user_id_1 AND papers.journal_id = :journal_id_1

what is right. From the error, I see that when adding delete()to the query, part of the SQL statement connection is lost, and the database does not know how to find the column papers.journal_id, obviously. Why don't I understand why this is happening?

This is the setup of my ORM objects

class User(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(255), unique=True)
    papers = db.relationship("UserPaper", backref=db.backref('users'), lazy='dynamic')

class Paper(db.Model):
    __tablename__ = 'papers'

    id = db.Column(db.Integer, primary_key = True)
    title = db.Column(db.String(1024))
    journal_id = db.Column(db.Integer, db.ForeignKey('journals.id'))

class UserPaper(db.Model):
    __tablename__ = 'userpapers'

    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), primary_key=True)
    paper_id = db.Column(db.Integer, db.ForeignKey('papers.id'), primary_key=True)
    paper = db.relationship("Paper", backref=db.backref('user_paper'))
    read_at = db.Column(db.DateTime)
    score = db.Column(db.Integer)

class Journal(db.Model):
    __tablename__ = 'journals'

    id = db.Column(db.Integer, primary_key = True)
    title = db.Column(db.String(100), index = True, unique = True)
    papers = db.relationship('Paper', backref = 'journal', lazy = 'dynamic')
+4
source share
1 answer

SQLALchemy 0.9 MySQL 5.6. /. , arround ( , ) - :

    paperQuery = db.session.query(Paper.id)\
                                    filter(Paper.journal_id == journal.id)


    baseQuery  = db.session.query(UserPaper)\
                                    .filter(UserPaper.paper_id.in_(paperQuery.subquery()))
                                    .filter(UserPaper.user_id == user.id).delete(synchronize_session='fetch')

, .

+3

More articles:


All Articles