Sqlalchemy transaction not working as expected

Question

I read about sqlalchemy transaction that transactions automatically determine which objects are to be saved first when objects have relations

But when I tried

>> user = User('demo user')
>> post = Post('Sample post')
>> post.user_id = user.id
>> db.session.add_all([post, user])
>> db.session.commit()

It creates new post with user_id = None which is not expected.

Below are my model implementations

class User(db.Model):

    __tablename__ = 'users'

    id = db.Column(db.Integer(), primary_key=True)
    username = db.Column(db.String(40),nullable=False, unique=True, index=True)
    posts = db.relationship(
        'Post',
        backref='user',
        lazy='dynamic'
    )

    def __init__(self, username):
        self.username = username

    def __repr__(self):
        return f'<User {self.username}>'



class Post(db.Model):

    __tablename__ = 'posts'

    id = db.Column(db.Integer(), primary_key=True)
    title = db.Column(db.String(50), index=True)
    user_id = db.Column(db.Integer(), db.ForeignKey('users.id'))
    date = db.Column(db.DateTime(), default=datetime.datetime.now)

    def __init__(self, title):
        self.title = title

    def __repr__(self):
        return f'<Post {self.title}>'

can someone please explain why transaction not saving user_id value?

Answer 1

The issue that you are facing is because id is generated by the database after the commit. So at the time of assignment (post.user_id=user.id) user.id is None.

If you append the post to posts on the user object SQLAlchemy handles adding the correct id.

user = User('demo user')
post = Post('Sample post')
user.posts.append(post)
db.session.add(user)
db.session.commit()