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

Question

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:

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?

You can check for the previous answers for the same type of question here https://stackoverflow.com/questions/27158573/how-to-delete-a-record-by-id-in-flask-sqlalchemy — Gaurang Delvadiya, Jul 11 '21 at 09:36
@charchit, yes. It returns me `Class 'sqlalchemy.engine.row.Row' is not mapped`. @Gaurang, I had check this before, but there is no option to query two columns, so I tried to make two queries like this: query_user = `user_book.query.filter_by(uid=your_user)` + `query_user.query.filter_by(bid=book_id).delete()` but it returns: `'Table' object has no attribute 'query'`. — ARNON, Jul 11 '21 at 15:59
try printing `user_id` and `book_rm`, and their type. `print(type(user_id),type(book_rm))`. what do they return , if the email doesn't match they may return none too. On which specific line this error `Class 'sqlalchemy.engine.row.Row' is not mapped` is occuring, can you paste the full traceback error. — charchit, Jul 11 '21 at 16:26
Printing -> `user_id` returns `2`; `book_rm` returns `(2, 'GL7BrQEACAAJ', datetime.datetime(2021, 7, 8, 9, 39, 2, 383391, tzinfo=datetime.timezone.utc))`; `type(user_id)` returns ``; `type(book_rm)` returns ``. The error is occurring in the line of delete: `db.session.delete(book_rm)`. — ARNON, Jul 11 '21 at 16:56

snakecharmerb · Accepted Answer · 2021-07-14T17:29:42.140

1

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.

edited Jul 14 '21 at 17:29

answered Jul 14 '21 at 08:24

snakecharmerb

47,570
11
100
153

It's a very interesting solution and seems to be very simple. However, I would have to start working with another package and that would make me have to change several other functions that exist in my code. Anyway, if no other answer comes up using `flask_sqlalchemy`, I'll probably use your solution. Thank you so much for your answer. – ARNON Jul 14 '21 at 08:39
1

I've added the code for the `remove` function. – snakecharmerb Jul 14 '21 at 16:57
1

I've also removed the SQLAlchemy example, in case it was confusing. – snakecharmerb Jul 14 '21 at 17:31
I'm impressed! You were surgical and managed to solve my problem very simply. I took the opportunity to research more about the `sqlalchemy` you had called "pure" and noticed that there is a lot of discussion in the community about which of these repositories to use. I got the impression that `sqlalchemy` is easier to work with than `flask_sqlalchemy` due to more functions and better documentation. But beyond that, is there any reason you prefer `sqlalchemy`? Thanks in advance. – ARNON Jul 14 '21 at 22:51
1

Flask-SQLAlchemy is just a wrapper around SQLAlchemy - it integrates session / connection management for Flask's request-handling, and exposes many SQLAlchemy attributes in the `db` namespace. So if you are going to go beyond the basics with Flask-SQLAlchemy you will have to learn more about SQLAlchemy anyway. So use whichever you prefer. I used SQLAlchemy for the initial example simply because it's quicker for me to that way than having to set up a Flask environment. – snakecharmerb Jul 15 '21 at 07:13
I got it. Really appreciate for you answer. Thanks! – ARNON Jul 15 '21 at 09:00

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

1 Answers1