Sqlalchemy 1.4 create model with None relationship field

Viewed 151

everyone. I have updated sqlalchemy version from 1.3 to 1.4 and now have strange errors that I have not in 1.3. I have two models with one-to-one relationship. This is my models:

@as_declarative()
class Base:
    __name__: str

    @declared_attr
    def __tablename__(cls) -> str:
        return cls.__name__.lower()

    id = Column(UUID(as_uuid=True), primary_key=True, nullable=False, default=uuid4)

class User(Base):
    name = Column(String(255))
    permissions = relationship('Permissions', uselist=False, cascade="all, delete-orphan")


class Permissions(Base):
    user_id = Column(UUID(as_uuid=True), ForeignKey('user.id'))

My crud method is look like:

async def create_user_async(async_db: AsyncSession) -> User:
    user = User(name='test')
    async_db.add(user)
    await async_db.flush()
    permissions = Permissions(user_id=user.id)
    async_db.add(permissions)
    await async_db.commit()
    return user

After I do await async_db.flush() I get in user.permissions traceback with error instead of None. But if I do like this:

async def create_user_async(async_db: AsyncSession) -> User:
    user = User(name='test')
    user.id = uuid4()
    user.permissions = Permissions(user_id=user.id)
    async_db.add(user)
    await async_db.commit()
    return user

Everything is allright. It seems that in 1.4 version I can't commit to db with None relation model. I don't know is it bug or feature. But in this case I don't understand how to create models with nested models that can have another nested models. In 1.3 there was not this problem.

Update I've found solution for creating user without error. I need do this:

async def create_user_async(async_db: AsyncSession) -> User:
    user = User(name='test', permissions=None)
    async_db.add(user)
    await async_db.flush()
    permissions = Permissions(user_id=user.id)
    async_db.add(permissions)
    await async_db.commit()
    user.user = permissions
    return user

In this case creating of user is going without error. Now I want to get my user from db, and I'm getting same traceback error in my user.permissions instead of permission model. Here is what I do:

    async def get_multi(async_db: AsyncSession) -> List[User]:

        stm =select(User).options(selectinload(User.permissions))
        result = await async_db.execute(stm)
        return result.scalars().all()

In debbuger I can see in User.permissions field this traceback string:

greenlet_spawn has not been called; can't call await_() here. Was IO attempted in an unexpected place? (Background on this error at: http://sqlalche.me/e/14/xd2s)

Example of select statement I get from this example

Now I realy don't understand how to get user from db with related model of permissions

1 Answers

My problem was in how I define relationship field. By default it get lazy loading, but when I try to user AsyncSession it raise an error. If you want to use AsyncSession in sqlalchemy 1.4 you must define relationship like this permissions = relationship('Permissions', lazy='joined', uselist=False, cascade="all, delete-orphan") or use options in select statements like this stmt = select(A).options(joinedload(A.bs))

Related