The perils of shared database connections and cursors

1_shared_cursor.py

import asyncio

import asqlite


async def shared_cursor():
    """
    Expected result:
    Each `fetchone` should return a row with a `user_id` matching the passed value.

    Actual result:
    The first `SELECT` result is discarded when the second `SELECT` is ran,
    the first `fetchone` receives the result of the second `SELECT`, giving incorrect data.

    Output:
    passed user_id = 1
    row = (2, 'banana')
    passed user_id = 2
    row = None

    Solution:
    Get a new cursor inside the function.

    Fixed output:
    passed user_id = 1
    row = (1, 'apple')
    passed user_id = 2
    row = (2, 'banana')
    """
    conn = await asqlite.connect(":memory:")
    conn._conn.row_factory = None  # hack just for example output, don't do this

    await conn.execute("CREATE TABLE test (user_id INT, fruit TEXT)")
    await conn.executemany(
        "INSERT INTO test VALUES (?, ?)", ((1, "apple"), (2, "banana"))
    )

    cur = await conn.cursor()

    async def command(user_id):
        await cur.execute("SELECT * FROM test WHERE user_id = ?", user_id)
        row = await cur.fetchone()
        print(f"passed {user_id = }\n{row = }")

    await asyncio.wait({asyncio.create_task(command(1)),
                        asyncio.create_task(command(2))})


asyncio.run(shared_cursor())

2_shared_connection.py

import asyncio

import asqlite


async def shared_connection():
    """
    Expected result:
    Guild and relevant members both exist or neither exist.

    Actual result:
    Guild is deleted, but members still exist for some time.

    Output:
    Deleting guild and members...
    guilds = []
    members = [(1, 5), (1, 6), (1, 7)]
    Done deleting guild and members.
    guilds = []
    members = []

    Solution:
    Connect in each function where you need a connection.

    Fixed output:
    Deleting guild and members...
    guilds = [(1,)]
    members = [(1, 5), (1, 6), (1, 7)]
    Done deleting guild and members.
    guilds = []
    members = []
    """
    conn = await asqlite.connect(":memory:")
    conn._conn.row_factory = None  # hack just for example output, don't do this

    # a real DB should use foreign keys, this is just for example
    await conn.execute("CREATE TABLE guilds (guild_id INT)")
    await conn.execute("CREATE TABLE members (guild_id INT, user_id INT)")

    await conn.execute("INSERT INTO guilds VALUES (?)", (1,))
    await conn.executemany(
        "INSERT INTO members VALUES (?, ?)", ((1, 5), (1, 6), (1, 7))
    )

    async def command_one():
        print("Deleting guild and members...")
        await conn.execute("DELETE FROM guilds WHERE guild_id = ?", (1,))
        # this sleep is to reliably reproduce the example
        # normal bot things outside your control would cause the same issue
        await asyncio.sleep(0.5)
        await conn.execute("DELETE FROM members WHERE guild_id = ?", (1,))
        await conn.commit()
        print("Done deleting guild and members.")

    async def command_two():
        ...  # do something unrelated that you need to commit
        await conn.commit()
        cur = await conn.execute("SELECT * FROM guilds")
        guilds = await cur.fetchall()
        cur = await conn.execute("SELECT * FROM members")
        members = await cur.fetchall()
        print(f"{guilds = }\n{members = }")

    await asyncio.wait({asyncio.create_task(command_one()),
                        asyncio.create_task(command_two())})
    await command_two()


asyncio.run(shared_connection())