How to Use a Single INSERT INTO Statement for Multiple Rows in Python?

Question

I’m currently working on a Discord Python bot where I loop through a list of ForumTags and generate an INSERT INTO SQL statement for each object to insert data into a MySQL database.

However, I want to optimize my code by combining all these individual INSERT INTO statements into a single query like this as an example:

INSERT INTO guild_support_tags (guild_id, tag_id, tag_name, category) VALUES 
(123, 1, "test", "test_category"), (456, 2, "another tag", "test_category2)

That is my current code:

start = time.time()
for tag in forum.available_tags:
    await write_query("INSERT INTO guild_support_tags (guild_id, tag_id, tag_name, category) VALUES "
                      "(:guild_id, :tag_id, :tag_name, :category)", 
                      {"guild_id": interaction.guild_id, "tag_id": tag.id, "tag_name": tag.name, 
                       "category": category})

    print(f"loop done after {time.time() - start}")


# from other file - created by myself to execute MySQL statements
# I would like a solution where this part is untouched. But if it can be improved, its okay.
async def write_query(query: str, params: dict) -> None:
    async with async_session() as session:
        async with session.begin():
            await session.execute(text(query), params)

Maybe it's good to know: I currently use SQLAlchemy with aiomysql and Python3.12 on a MySQL database.

I think you're looking for the cursor.executemany() function. — John Gordon
– John Gordon, Commented Jul 25, 2024 at 20:30

Razzer · Accepted Answer · 2024-07-26 23:40:50Z

I found out that I still can use the execute() function and by changing the argument params type from dict to dict | list, it works.

SQLAlchemy will detect that and change its behavior to insert all needed rows. This approach allows a efficient insertion without looping:

# Prepare the data for the query
query_values: list[dict] = [{"guild_id": interaction.guild_id, "tag_id": tag.id, 
                             "tag_name": tag.name, "category": category} 
                            for tag in forum.available_tags]

# Modify the function to allow "list" and "dict" as type for "params"
async def write_query(query: str, params: dict | list) -> None:
    async with async_session() as session:
        async with session.begin():
            await session.execute(text(query), params)

# execute it
await write_query("INSERT INTO guild_support_tags (guild_id, tag_id, tag_name, category) 
                   VALUES (:guild_id, :tag_id, :tag_name, :category)", query_values)

Collectives™ on Stack Overflow

How to Use a Single INSERT INTO Statement for Multiple Rows in Python?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related