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?
from How to remove 1 record from a db.Table using Flask_SQLAlchemy?
No comments:
Post a Comment