Detailed explanation of how to use executemany

This article mainly introduces the use and precautions of python executemany. It is very good and has reference value. Friends in need can refer to it

If you use executemany to batch insert data, you should pay attention to some things :

#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

【Related recommendations】

1. Special recommendation : "php Programmer Toolbox" V0.1 version download

2. Python free video tutorial

3. Python object-oriented video tutorial

The above is the detailed content of Detailed explanation of how to use executemany. For more information, please follow other related articles on the PHP Chinese website!