Detailed explanation of the precautions for using executemany in python

This article mainly explains in detailpythonNotes on using executemany, it is very good and has reference value. Friends who need it can refer to it

Using executemany If data is inserted in batches, please pay attention to the following matters:

#coding:utf8
conn = MySQLdb.connect(host = “localhost”, user = “root”, passwd = “123456”, db = “myDB”)
cursor = conn.cursor()
sql = “insert into myTable (created_day,name,count) values(%s,%s,%s) ON DUPLICATE KEY UPDATE count=count+values(count)”
args=[("2012-08-27","name1",100),("2012-08-27","name1",200),("2012-08-27","name2",300)]
try:
  cursor.executemany(sql, args)
except Exception as e:
  print0(“执行MySQL: %s 时出错：%s” % (sql, e))
finally:
　　cursor.close()
　　conn.commit()
　　conn.close()

Here args is an array containing multiple tuples, each tuple corresponds to A piece of data in mysql. Note that the %s corresponding to created_day here does not have quotation marks. It is speculated that executemany first performs a regular match on the sql statement %s and then embeds the string on this basis. If %s is quoted here, "0000-00" will appear when inserted into mysql. -00″ type wrong date.

If you want to insert many pieces of data at one time, it is highly recommended to use executemany. From my own experience, it takes 2-3 hours to insert data one by one. Using executemany only takes 2- 3 seconds! ! !

When executemany and ON DUPLICATE KEY UPDATE are used together, if you follow the sql regular mode, that is: sql="insert into myTable (created_day,name,count) values(%s,%s,%s) ON DUPLICATE KEY UPDATE count=count+%s" will report a bug: not all arguments converted during string formatting

The above is The editor introduces to you the use and precautions of python executemany. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank you all for your support of the PHP Chinese website!

The above is the detailed content of Detailed explanation of the precautions for using executemany in python. For more information, please follow other related articles on the PHP Chinese website!