1

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?

Improve this question
5
  • 1
    Have you tried using .first() in place of .one(). Commented Jul 11, 2021 at 4:50
  • You can check for the previous answers for the same type of question here stackoverflow.com/questions/27158573/… Commented Jul 11, 2021 at 9: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'. Commented Jul 11, 2021 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. Commented Jul 11, 2021 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 <class 'int'>; type(book_rm) returns <class 'sqlalchemy.engine.row.Row'>. The error is occurring in the line of delete: db.session.delete(book_rm). Commented Jul 11, 2021 at 16:56

1 Answer 1

Reset to default
1
+50

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.

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

6 Comments

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.
I've added the code for the remove function.
I've also removed the SQLAlchemy example, in case it was confusing.
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.
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.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.