"UPDATE" has no effect

[darkforest42]

await aiomysql.create_pool(maxsize=db['pool'], host=connection['host'], port=connection['port'], user=connection['user'], password=connection['password'], db=connection['database'], autocommit=True, loop=loop, charset='utf8') But, Run "UPDATE" has no effect.

[jettify]

what do you mean by "UPDATE" has no effect ? Can you show query? and table definition?

[olexandr-klymenko]

I'm experiencing the same issue with update:

async with self._engine.acquire() as conn:
                await conn.execute(
                    user_accounts.update().where(
                        user_accounts.c.user_id == user_id
                    ).values(amount=new_amount)
                )
                account = await conn.execute(
                    user_accounts.select().where(
                        user_accounts.c.user_id == user_id
                    )
                )
                res = await account.fetchone()
                obj = dict(res)
                print(obj)

{'id': 1, 'user_id': '1', 'amount': 111}

then select * from ...

+----+---------+--------+
| id | user_id | amount |
+----+---------+--------+
|  1 | 1       |      0 |
+----+---------+--------+

[olexandr-klymenko]

user_accounts = sa.Table(
    'user_accounts', metadata,
    sa.Column('id', sa.Integer, primary_key=True, autoincrement=True),
    sa.Column('user_id', sa.String(100), index=True, nullable=False),
    sa.Column('amount', sa.Integer, index=True, default=0),
)

[olexandr-klymenko]

there is a workaround for the issue: just put conn.execute('commit') why autocommit is set to False by default?!

[asvetlov]

Because PyMySql disables it by default too. It is pretty standard behavior. Maybe aiomysql should emphasize it though.

[asvetlov]

@jettify ?

[anselal]

I am having the same issue. With autocommit=True, and still the update statement won't update the table. Select works fine

@Nothing4You is this a known issue or are we doing something wrong ?

This is my code

        async with pool.acquire() as connection:
            async with connection.cursor() as cursor:
                await cursor.execute(
                    f"update logs set counter=counter+1 where command='{cmd}'"
                )

[Nothing4You]

Hi @anselal, sorry for the late response.

The following snippet works just fine for me:

import asyncio
import ssl

import aiomysql

async def main():
    pool = await aiomysql.create_pool(
        host=...,
        port=...,
        user=...,
        password=...,
        db=...,
        charset="utf8mb4",
        autocommit=True,
        ssl=ssl.create_default_context(),
    )

    cmd = "foo"

    async with pool.acquire() as connection:
        async with connection.cursor() as cursor:
            await cursor.execute(
                f"update logs set counter=counter+1 where command=%s",
                (cmd, )
            )

if __name__ == "__main__":
    asyncio.run(main())

This is on aiomysql 0.1.1 and MariaDB 10.4.

As long as autocommit=True is set or you explicitly commit you should not lose anything.

[anselal]

thnx for the reply. I rewrote the query and it worked. Must have had a typo somewhere... 🤷‍♀️