SQLAlchemy: AttributeError: Could not locate column in row for column '_sa_instance

I have a many to many relation database with puzzles and categories. When I run it for the first time, everything works fabulously. All tables get created and filled with content. However, when I run it for a second time to further populate the database the following error pops up. AttributeError: Could not locate column in row for column '_sa_instance_state'

I've narrowed the issue down to knowing it crashes when there is a same category already found in the database. But that one can't be assigned to the new puzzle its categories, since that's where the crash happens.

I've made the following reproducible example. Run it once and it runs perfectly, run it again and it will crash.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///example.db'
db = SQLAlchemy(app)

category_identifier = db.Table('category_identifier',
    db.Column('categories_id', db.Integer, db.ForeignKey('categories.id')),
    db.Column('puzzles_id', db.Integer, db.ForeignKey('puzzles.id'))
)

class Puzzle(db.Model):
    __tablename__ = 'puzzles'
    id = db.Column(db.Integer, primary_key=True)
    categories = db.relationship("Category", secondary=category_identifier)


class Category(db.Model):
    __tablename__ = 'categories'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), unique=True, nullable=False)


db.create_all()
db.session.commit()

if __name__ == '__main__':

    myList = ['cat1', 'cat2, cat3']
    puzzle = Puzzle()

    for cat in myList:
        category = db.session.query(Category.name).filter_by(name=cat).first()
        
        if not category:
            category = Category(name=cat)

        puzzle.categories.append(category)

    db.session.add(puzzle)
    db.session.commit()

edit

I found that the type of the variable is different in both cases and thought maybe that could be a reason for it to fail. The first time, the type of the variable is <class 'models.Category'>and the second time, when it crashes, it's of the instance <class 'sqlalchemy.engine.row.Row'>.

Is there any way to typecast the variable to the class models.category and see if that might be a solution?

CodePudding user response：

The error message

AttributeError: Could not locate column in row for column '_sa_instance_state'

is rather obscure at first glance. What it means is that we are trying to perform some operation which is specific to a model instance, but the thing that the operation is being performed on is not a model instance.

In this particular case, in executing

session.add(puzzle)

we seem to be adding a Puzzle to the session, but the result of

db.session.query(Category.name).filter(name=cat).first()

will be a row (like ('cat1',), because we are retrieving an attribute, not a model instance.

Replacing the query with

db.session.query(Category).filter(name=cat).first()

will retrieve a model instance and fix the problem.