Why Python MySQL Insert into the table not working?

Question

I am using aiomysql and MariaDB. I can create a table or select data, but I can't insert data into the table. If you SELECT data with fetchall(), then it will show what you just inserted, but immediately delete from the database.

async def test_example(loop):
    pool = await aiomysql.create_pool(host='127.0.0.1', port=3306,
                                      user='root', password='',
                                      db='test', loop=loop)
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute("INSERT INTO `tbl`(`id`, `val`) VALUES (37, 'z');")
            print(cur.fetchall())
    pool.close()
    await pool.wait_closed()

loop = asyncio.get_event_loop()
loop.run_until_complete(test_example(loop))

Why?

Welcome to StackOverflow. Please provide a detailed description of the error — mattyx17
– mattyx17, Commented Aug 10, 2020 at 13:41

Georg Richter · Accepted Answer · 2020-08-10 15:13:34Z

1

From the PEP-249 specification:

.fetchall()

Fetch all (remaining) rows of a query result, returning them as a sequence of sequences (e.g. a list of tuples).

Since sql INSERT statement doesn't produce a result set you should try a SELECT statement before trying to obtain information from the database server.

answered Aug 10, 2020 at 15:13

Georg Richter

7,6742 gold badges19 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dmitriy Over a year ago

I know this, the select works, I am interested in the question with insertion into the table

Rick James Over a year ago

@Dmitriy - There are no results from an INSERT, hence nothing to display. Sure, the table has some results; but you cannot see them without using a SELECT.

Jortega · Accepted Answer · 2020-08-10 20:58:55Z

-1

Remove the quotes from the table and column names.

import aiomysql
import asyncio


async def select(loop, sql, pool):
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute(sql)
            r = await cur.fetchone()
            print(r)


async def insert(loop, sql, pool):
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute(sql)
            await conn.commit()


async def main(loop):
    pool = await aiomysql.create_pool(host='127.0.0.1', port=3306,
                                      user='root', password='',
                                      db='test', loop=loop)
    c1 = select(loop=loop, sql='select * from tbl', pool=pool)
    c2 = insert(loop=loop, sql="INSERT INTO tbl(id, val) VALUES (37, 'z');", pool=pool)

    tasks = [asyncio.ensure_future(c1), asyncio.ensure_future(c2)]
    return await asyncio.gather(*tasks)


if __name__ == '__main__':
    cur_loop = asyncio.get_event_loop()
    cur_loop.run_until_complete(main(cur_loop))

edited Aug 10, 2020 at 20:58

answered Aug 10, 2020 at 12:18

Jortega

3,8081 gold badge22 silver badges23 bronze badges

9 Comments

Dmitriy Over a year ago

It did not help

Dmitriy Over a year ago

I can get data, but I cannot insert it for some reason

Jortega Over a year ago

@Dmitriy see the updated answer you are missing cur.commit()

Dmitriy Over a year ago

and I tried this too, now I have checked all the cursor attributes again, it doesn't have such an attribute. Most likely this is due to the fact that everywhere in StackOverflow answers there is synchronous code.

Jortega Over a year ago

@Dmitriy use .cursor(prepared=True) to define cur.

|

Collectives™ on Stack Overflow

Why Python MySQL Insert into the table not working?

2 Answers 2

2 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related