SQL Alchemy AttributeError: 'str' object has no attribute '_sa_instance_state'

Question

I'm trying to add a new element to a table of a database but I'm keeping getting this error. My database is this:

class User(UserMixin, db.Model):
    __tablename__ = "users"
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(100), unique=True)
    password = db.Column(db.String(100))
    name = db.Column(db.String(100))
    posts = relationship("BlogPost", back_populates="author")


class BlogPost(db.Model):
    __tablename__ = "blog_posts"
    id = db.Column(db.Integer, primary_key=True)

    # Create Foreign Key, "users.id" the users refers to the tablename of User.
    author_id = db.Column(db.Integer, db.ForeignKey("users.id"))
    # Create reference to the User object, the "posts" refers to the posts property in the User class.
    author = relationship("User", back_populates="posts")

    title = db.Column(db.String(250), unique=True, nullable=False)
    subtitle = db.Column(db.String(250), nullable=False)
    date = db.Column(db.String(250), nullable=False)
    body = db.Column(db.Text, nullable=False)
    img_url = db.Column(db.String(250), nullable=False)

and the function I'm using to create a new blog post is this:

    form = CreatePostForm()
    if form.validate_on_submit():
        new_post = BlogPost(
            title=form.title.data,
            subtitle=form.subtitle.data,
            body=form.body.data,
            img_url=form.img_url.data,
            author=current_user.name,
            author_id=current_user.id,
            date=date.today().strftime("%B %d, %Y")
        )
        db.session.add(new_post)
        db.session.commit()
        return redirect(url_for("get_all_posts"))
    return render_template("make-post.html", form=form)

I specify that before linking the two tables, the code was working just fine. The problem occured when I created a relationship among the two tables.

Thanks for your kind help in advance!

You have defined a relationship to reference the user (author). However, within your route you pass on the name of the user and the ID of the user. Just pass the current_user as the author or alternatively just the current_user.id as the author_id, then it should work. — Detlef
– Detlef, Commented Nov 16, 2021 at 19:41
It worked!! Thank you very much, but could you explain to me why was I getting that error? I'm new to relational databases topic and I'm not sure I understood the logic. — giovanni sergi
– giovanni sergi, Commented Nov 16, 2021 at 19:56
The relationship is a virtual mapping to another model. Instead of an instance of an object, you passed a character string. The mapping could not be carried out with this information. A relationship is based on the ForeignKey which is stored in a column of the table. From now on, the columns of the referenced object can be read from another table. So you only have to set the attribute that represents the ForeignKey, or the object that expects the relationship. If you set the object, the ForeignKey will be set automatically and vice versa. — Detlef
– Detlef, Commented Nov 16, 2021 at 20:11
Ok, so if I understand correctly with a relational database either you put the Foreign Key or the "variable" that link the relationship with the parent table. Is that correct? — giovanni sergi
– giovanni sergi, Commented Nov 16, 2021 at 21:01

Detlef · Accepted Answer · 2021-11-16 21:46:52Z

The relational database uses the foreign key to reference related records. This always refers to the unique primary key of another table. So you define a link in which you define a column foreign key, which points to the primary key of another table. This means that the referenced data record can be queried with an SQL JOIN statement.

You can also define a relationship in SQLAlchemy. This automatically loads the columns of the referenced dataset and creates an instance of an object of the respective model class. You can request one or more objects directly from the model, depending on the relationship (ONE <-> ONE, ONE <-> MANY, MANY <-> ONE, MANY <-> MANY). This is the virtual relationship. It is only there to do the class mapping and is optional and not a property of the database.

When creating an object using SQLAlchemy, as you did in your route, you can either set the referencing id (foreign key; author_id), or you pass over the entire referenced object (author / current_user). If you use the first variant, you set the foreign key directly. If the second solution is chosen, the primary key (id) column is automatically assigned to the column that is defined as the foreign key (author_id) in the background.

The actual relationship is defined by the link between the foreign key and the primary key. This is a property of the database. The lookup of the objects is defined by the SQLAlchemy relationship and reflects part of the ORM (Object Relational Mapping). It is a property of SQLAlchemy.

Your mistake was that you passed a string to the constructor instead of an object from your model class. So the mapping could not work.

Collectives™ on Stack Overflow

SQL Alchemy AttributeError: 'str' object has no attribute '_sa_instance_state'

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related