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

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?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source