aiomysql executemany is very slow to insert

[RuoCJ]

Discussed in #983

^{Originally posted by RuoCJ April 27, 2024}
I used aiomysql executemany to insert over 800 pieces of data, which took over thirty seconds, but pymysql only took over two seconds。

[yarodevuci]

same here, it's slow insert also one row as well..

[RuoCJ]

same here, it's slow insert also one row as well..这里也是一样，插入一行也很慢。

One idea I recently tried was to combine several insert statements into one, executed using execute instead of executemany

[yarodevuci]

same here, it's slow insert also one row as well..这里也是一样，插入一行也很慢。

One idea I recently tried was to combine several insert statements into one, executed using execute instead of executemany

Can you post an example how you call multiple statements?

[RuoCJ]

same here, it's slow insert also one row as well..这里也是一样，插入一行也很慢。

One idea I recently tried was to combine several insert statements into one, executed using execute instead of executemany

Can you post an example how you call multiple statements?

This operation is only for insert statements

sql = "INSERT INTO table_a (field1, field2, ..) VALUES {}"

values_str = create_insert_values(tlis_schp)
conn = db_pool.connection()
cursor = conn.cursor()
cursor.execute(sql, values_str or ())

def create_insert_values(data):
    values_list = []
    for record in data:
        values = []
        for item in record:
            if isinstance(item, str):
                escaped_item = item.replace("'", "''")
                values.append(f"'{escaped_item}'")
            elif item is None or (isinstance(item, float) and math.isnan(item)):
                values.append("NULL")
            elif isinstance(item, datetime.datetime):
                values.append(f"'{item}'")
            elif isinstance(item, decimal.Decimal):
                values.append(f"'{item}'")
            elif isinstance(item, decimal.Decimal):
                values.append(f"'{item}'")
            else:
                values.append(str(item))
        values_str = f"({', '.join(values)})"
        values_list.append(values_str)
    return ',  '.join(values_list)

This is a bit more cumbersome, but much faster than using executemany directly