CODEWITHSUNDEEP
pythonpostgresqlflaskflask-sqlalchemy
ARNON
ARNON

Reputation: 1217

How to remove 1 record from a db.Table using Flask_SQLAlchemy?

I have a very simple many-to-many table structure and I'm having problems removing records from the table that makes the association between the other two:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

user_book = db.Table('user_book',
                     db.Column('uid', db.Integer, db.ForeignKey('user.uid'), primary_key=True),
                     db.Column('bid', db.Text, db.ForeignKey('book.bid'), primary_key=True),
                     db.Column('date_added', db.DateTime(timezone=True), server_default=db.func.now())
                     )


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

    uid = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(25), nullable=False)
    hash = db.Column(db.String(), nullable=False)
    first_name = db.Column(db.String(30), nullable=True)
    last_name = db.Column(db.String(80), nullable=True)
    books = db.relationship('Book', secondary=user_book)

class Book(db.Model):
    __tablename__ = 'book'

    bid = db.Column(db.Text, primary_key=True)
    title = db.Column(db.Text, nullable=False)
    authors = db.Column(db.Text, nullable=False)
    thumbnail = db.Column(db.Text, nullable=True)
    users = db.relationship('User', secondary=user_book)

To make it even clearer, here is an excerpt from the table with some records:

table user_books

In the function that removes a record I did it this way:

def remove(book_id):
    # get the user id (uid)
    user_id = db.session.query(User).filter_by(email=session['email']).first().uid

    # match the user id with the book id on table 'user_book'
    book_rm = db.session.query(user_book).filter_by(uid=user_id, bid=book_id).one()

    db.session.delete(book_rm)
    db.session.commit()

When I call this function I get the following error on the console:

Class 'sqlalchemy.engine.row.Row' is not mapped

So after some research on Stack and documentation, I tried to do it like this:

db.session.execute(user_book.delete(user_id).where(bid=book_id))
db.session.commit()

And in this case I have received the following:

SQL expression for WHERE/HAVING role expected, got 2.

I really don't know how to go about solving this. I would like to delete only 1 record from the user_book table. Does anyone know how to do this?

Upvotes: 1

Views: 552

Answers (1)

snakecharmerb
snakecharmerb

Reputation: 55620

Given the a User instance and the Book instance to be deleted from the User's books, the Book can be removed like this:

user_instance.books.remove(book_instance)
db.session.commit()

So the remove function would look like this:

def remove(book_id):
    # get the user
    user = db.session.query(User).filter_by(email=session['email']).first()

    # Find the book by its id
    book_rm = Book.query.get(book_id)

    user.books.remove(book_rm)
    db.session.commit()

See the SQLAlchemy docs for more information.

Upvotes: 1

Related Questions