Hi,

PyMySQL doesn't support MariaDB's protocol extension for bulk operations, this is afaik only supported by MariaDB Connector/Python when using cursors executemany() method.

If you have only INSERT statements PyMySQL will transform executemany to multi value statement which also reduces roundtrips:

cursor.executemany("INSERT INTO t1 VALUES (%s)", [("foo",),("bar,),("john",),("doe",)]) will be sent as

INSERT INTO t1 VALUES ("foo"),("bar"),("john"),("doe")

This approach is a little bit slower than using the binary protocol implementation but it is limited to SELECT statements and doesn't work with UPDATE, REPLACE or DELETE.

/Georg

On Sun, Jan 26, 2025 at 2:33 PM E.S. Rosenberg via developers <developers@lists.mariadb.org> wrote:

Hey Sergei et al.,
I have been trying to find information on how to do pipelining, I guess I am not looking in the right place or using the right terms, the thing closest to it that I found was compressed packets that can contain multiple packets.
Any hints would be appreciated.
Thanks!
Eli

Op do 23 jan 2025 om 20:42 schreef E.S. Rosenberg <es.rosenberg+lists.mariadb.org@gmail.com>:
Sergei thanks for the information!

You also immediately hit the nail on the head as to the issue of the extra round trip, I will have a look at pipelining prepared statements, specifically in PyMySQL.

Thanks,
Eli

Op do 23 jan 2025 om 19:08 schreef Sergei Golubchik <serg@mariadb.org>:
Hi, Eli,

It's incompatibility.

MariaDB only supports parameters in COM_STMT_PREPARE/COM_STMT_EXECUTE.
You can pipeline them and avoid an extra round-trip, but it still be
binary protocol, not text.

On Jan 23, E.S. Rosenberg via developers wrote:
> Hi,
> Recently I found out about parameterized queries in MySQL, it seems to have
> been supported at least since 8.0.x but it is not listed as an
> incompatibility between mariadb and mysql yet the package spec for
> COM_QUERY on mariadb does not mention any support for parameterized queries.
>
> So is this an incompatibility, some out of date docs or am I just looking
> at the wrong com_query page?
>
> Refs:
> https://dev.mysql.com/doc/dev/mysql-server/8.0.40/page_protocol_com_query.html
> https://mariadb.com/kb/en/com_query/
>
> Thanks!
> Eli

Regards,
Sergei
Chief Architect, MariaDB Server
and security@mariadb.org

_______________________________________________
developers mailing list -- developers@lists.mariadb.org
To unsubscribe send an email to developers-leave@lists.mariadb.org