Largest possible size for executemany() in PEP-249 (Database API)

[Roy Smith]

I'm trying to batch up inserts to a database using MySQLdb. When I
get to something over 10,000 records per call, I get an exception:

_mysql_exceptions.OperationalError: (1153, "Got a packet bigger than
'max_allowed_packet' bytes")

Is there any way (other than trial and error) to know how many records
I can pass in one call before I blow up? As a practical matter, if I
do batches of 1000 per call, I've probably gotten as much performance
enhancement as I need, but it would be nice to know if there's a
useful way to determine exactly what the maximum is.

 

[Dennis Lee Bieber]

I'm trying to batch up inserts to a database using MySQLdb. When I
get to something over 10,000 records per call, I get an exception:

_mysql_exceptions.OperationalError: (1153, "Got a packet bigger than
'max_allowed_packet' bytes")

Is there any way (other than trial and error) to know how many records
I can pass in one call before I blow up? As a practical matter, if I
do batches of 1000 per call, I've probably gotten as much performance
enhancement as I need, but it would be nice to know if there's a
useful way to determine exactly what the maximum is.

Sure... Check the MySQL server settings...

According to the (former) MySQL AB/MySQL Press "MySQL
Administrator's Guide" (page 480-481) the default for max_allowed_packet
is 16MB -- though that doesn't match the apparent default in my
installation which is showing it as 1MB...

-=-=-=-=-.... if varb.startswith("max"):
.... print varb, val
....
max_allowed_packet 1048576
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connect_errors 10
max_connections 50
max_delayed_threads 20
max_error_count 64
max_heap_table_size 16777216
max_insert_delayed_threads 20
max_join_size 4294967295
max_length_for_sort_data 1024
max_prepared_stmt_count 16382
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_sp_recursion_depth 0
max_tmp_tables 32
max_user_connections 0
max_write_lock_count 4294967295-=-=-=-=-=-