开发者

SQLAlchemy and max_allowed_packet problem

Due to the nature of my application, I need to support fast inserts of large volumes of data into the database. Using executemany() increases performance, but there's a caveat. For example, MySQL has a configuration parameter called max_allowed_packet, and if the total size of my insert queries exce开发者_开发技巧eds its value, MySQL throws an error.

Question #1: Is there a way to tell SQLAlchemy to split the packet into several smaller ones?

Question #2: If other RDBS have similar constraints, how should I work around them as well?

P.S. I had posted this question earlier but deleted it when I wrongly assumed that likely I will not encounter this problem after all. Sadly, that's not the case.


I had a similar problem recently and used the - not very elegant - work-around:

  • First I parsed my.cnf for a value for max_allow_packets, if I can't find it, the maximum is set to a default value.
  • All data items are stored in a list.
  • Next, for each data item I count the approximate byte length (with strings, it's the length of the string in bytes, for other data types I take the maximum bytes used to be safe.)
  • I add them up, committing after I have reached approx. 75% of max_allow_packets (as SQL queries will take up space as well, just to be on the safe side).

This approach is not really beautiful, but it worked flawlessly for me.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜