On executing statement in asynchronous it would just hang?

[timelessnesses]

Description

So I tried making a function that write new records to the sqlite and when the function ran it just stuck

print("inserting")
            await self.db.execute(
                """
                    INSERT INTO flights
                    (flight_id, flight_number, flight_host, flight_backup_host, flight_date, flight_vip_unlock, flight_boarding_time, flight_route, flight_aircraft, flight_game_link, flight_event_link, flight_image_link, flight_airplane_image_link)
                    VALUES
                    (?, ?, ?, ? ,? , ? ,? ,? ,? ,? ,?, ?, ?)
                    """,
                (
                    flight_id,
                    flight_number,
                    flight_host.id,
                    flight_backup_host.id,
                    flight_date,
                    flight_vip_unlock,
                    flight_boarding_time,
                    flight_route,
                    flight_aircraft,
                    flight_game_link,
                    flight_event_link,
                    flight_image_link,
                    flight_airplane_image_link,
                ),
            )
            print("queried")
            await self.db.commit()
            print("committed")

Note that only inserting printed out

Details

• OS: Windows 10 x64
• Python version: 3.10.9
• aiosqlite version: 0.19.0
• Can you repro on 'main' branch?: yes
• Can you repro in a clean virtualenv?: yes

[kumarvimal]

I am experiencing the same issue on Mac additionally, all my 16 core of CPU is utilized 100%.

OS: Mac OS 13.2.1 (22D68) / Intel 2,6 GHz 6-Core Intel Core i7 / 32 GB 2667 MHz DDR4
Python Version: 3.9.16
aiosqlite: 0.19.0

[NucleaPeon]

I am also experiencing this behavior. I'm running a tornado web server and during my authentication phase when I query the existing aiosqlite database to get the password hash, I can log before the connection but once the connection is attempted, the page hangs and no further logs are printed out. Web query is 'pending' for minutes. I'll try to get an example that shows this behavior.

Gentoo Linux, Python 3.11.6, aiosqlite 019.0

EDIT: One issue I'm seeing is that when I have a function, the first connection and execute() statement works, but any additional connection and execute() statements seem to not (nothing else is called)

        # Create the test database
        if not os.path.exists(DBNAME):
            async with aiosqlite.connect(DBNAME) as db:
                print("CREATING DATABASE")
                cursor = await db.execute("CREATE TABLE test (str TEXT);")
                await db.commit()
            print("ATTEMPTING CONNECTION")
            async with aiosqlite.connect(DBNAME) as db:
                # THIS IS NOT PRINTED OUT!!!
                print("INSERTING DATA")
                cursor = await db.execute("INSERT INTO test ('hello world!');")
                await db.commit()

This also doesn't work:

        if not os.path.exists(DBNAME):
            async with aiosqlite.connect(DBNAME) as db:
                print("CREATING DATABASE")
                cursor = await db.execute("CREATE TABLE test (str TEXT);")
                await db.commit()
                # THIS ALSO DOESN'T PRINT OUT
                print("INSERTING DATA")
                cursor = await db.execute("INSERT INTO test ('hello world!');")
                await db.commit()

Now I don't know if this is also a problem, this is also running in a daemon process.
Here is an example with the daemon code.
aiosqlitetest.zip

Another edit: This is all in a direnv environment, so it's in a completely clean virtual environment with just aiosqlite and tornado libraries installed.

[amyreese]

Not sure if this is related to tornado, or another coroutine/task stalling the event loop, but I can't reproduce this issue.

Test code: https://gist.github.com/amyreese/de20d95f5d6697e427a5e8b7a035b298

Env:

(venv) amethyst@mordin-2 ~/scratch/aiosqlite-test » python -VV
Python 3.11.4 (main, Jul 19 2023, 23:41:57) [Clang 14.0.3 (clang-1403.0.22.14.1)]

(venv) amethyst@mordin-2 ~/scratch/aiosqlite-test » pip list
Package    Version
---------- -------
aiosqlite  0.19.0
pip        23.1.2
setuptools 65.5.0

Output:

(venv) amethyst@mordin-2 ~/scratch/aiosqlite-test » python foo.py
create
insert
select
rows = [('hello world',)]
done

(venv) amethyst@mordin-2 ~/scratch/aiosqlite-test » python foo.py
create
insert
select
rows = [('hello world',)]
done

[NucleaPeon]

I migrated my aiosqlite code to sqlite3 and it works fine, no hanging. I'll take a look at your example and see if I can create a reproducable example.

[NucleaPeon]

Not sure if this is related to tornado, or another coroutine/task stalling the event loop, but I can't reproduce this issue.

Test code: https://gist.github.com/amyreese/de20d95f5d6697e427a5e8b7a035b298

Using your code, I wasn't able to reproduce the issue.

Have you tried using my example to reproduce? If aiosqlite cannot run multiple aiosqlite commands in a daemon, that seems like a potentially useful use case that isn't being met.

[NucleaPeon]

I think I know what went wrong, it's nothing to do with aiosqlite. I'll try re-utilizing it within my project.
The problem was due to the daemon chroot bringing me back to / as the current directory (and not setting it to the running directory) and when creating the database file, it would silently fail.

I found out by creating the database outside of the running daemon process and it would work and I could make multiple inserts before a selection in a web server route.

Attaching a working version of my code.
I don't think my issue is linked to the above posters.

aiosqlitetest.tar.gz

@kumarvimal @timelessnesses Do you have a minimal example that hangs?