Async Nested DB Session Not Working

[slyduda]

I'm getting an error when attempting to use nested async functions wrapped with the @db_session decorator. Here is my simplified code:

database.py

@db_session
async def check_username(username):
    return User.exists(username=username)

@db_session
async def check_email(email):
    return User.exists(email=email)

@db_session
async def check_user(username, email):
    is_username = await check_username(username)
    is_email = await check_email(email)
    return is_username or is_email

@db_session
async def add_user(email, username, hash):
    user = User(
        email=email, username=username, password=hash,
    )
    return user

@db_session
async def add_lead(email, user=None):
    if user:
        user.lead = Lead(email=user.email)
    else:
        Lead(email=email)

@db_session
async def create_account_db(email, username, hash, is_lead: bool):
    user = await add_user(email, username, hash)
    if is_lead:
        lead = await add_lead(None, user)

account.py

exists = await check_user(username, email)

if exists:
    pass

await create_account_db(email, username, hash, is_subscribe)

The error message that I get is as follow:

...
...
  File "C:\Users\xxxxx\account.py", line 40, in create_account
    exists = await check_user(username, email)
  File "C:\Users\xxxxx\lib\site-packages\pony\orm\core.py", line 610, in new_gen_func
    output = wrapped_interact(iterator)
  File "C:\Users\xxxxx\lib\site-packages\pony\orm\core.py", line 596, in wrapped_interact
    rollback_and_reraise(sys.exc_info())
  File "C:\Users\xxxxx\lib\site-packages\pony\orm\core.py", line 370, in rollback_and_reraise
    reraise(*exc_info)
  File "C:\Users\xxxxx\lib\site-packages\pony\utils\utils.py", line 95, in reraise
    try: raise exc.with_traceback(tb)
  File "C:\Users\xxxxx\lib\site-packages\pony\orm\core.py", line 585, in wrapped_interact
    output = interact(iterator, input, exc_info)
  File "C:\Users\xxxxx\lib\site-packages\pony\orm\core.py", line 558, in interact
    return next(iterator) if input is None else iterator.send(input)
  File "C:\Users\xxxxx\database.py", line 218, in check_user
    is_username = await check_username(username)
  File "C:\Users\xxxxx\lib\site-packages\pony\orm\core.py", line 610, in new_gen_func
    output = wrapped_interact(iterator)
  File "C:\Users\xxxxx\lib\site-packages\pony\orm\core.py", line 575, in wrapped_interact
    '@db_session-wrapped generator cannot be used inside another db_session')
  File "C:\Users\xxxxx\lib\site-packages\pony\utils\utils.py", line 106, in throw
    raise exc

pony.orm.core.TransactionError: @db_session-wrapped generator cannot be used inside another db_session

Edit: When I make everything ssynchronous everything works perfectly.

[slyduda]

After doing further testing I am noticing that I am not getting conflicts when nesting 2 db sessions within one session. The following code works for the second db_session function.

@db_session
async def create_account_db(email, username, hash, is_lead: bool):
    user = User(
        email=email, username=username, password=hash,
    )
    if is_lead:
        user.lead = Lead(email=user.email)

The correct information is getting updated in the database. I found this a bit dissapointing as I was hoping to remove large chunks of redundant code by nesting smaller db_session functions like in my first example.

PROBLEM 2

Although async works if I don't nest, my first check_user function is not working and returns None while its synchronous counterpart returns the correct values.

@db_session
async def check_user(username, email):
    is_username = User.exists(username=username)
    is_email = User.exists(email=email)
    return is_username or is_email

exists = await check_user(username, email)
print(exists) # PRINTS OUT NONE HERE IF ASYNC AND THE CORRECT VALUE IF SYNC. 

Is this an issue with the cache bein wiped before returning the correct variable?

[slyduda]

After some further testing, I have found that when the db_session wrapper is used with a async functions, the call property is not working correctly causing no value to be returned. I am still investigating.

[BobTheBuidler]

I wouldn't expect this to work, best practice for using pony with async code can be found here:

https://docs.ponyorm.org/integration_with_fastapi.html#async-and-db-session