<p>通过提前(在循环中)准备参数并在循环完成后调用<code>cursor.executemany</code>,可以减少对<code>cursor.execute</code>的调用次数:</p>
<pre><code>cursor = conn.cursor()
user_args = []
perm_args = []
perms = '(read)(write)(view)(delete)(resume)(share)(slideshow)(rename)(makedir)(deletedir)'
with open(sys.argv[1], 'rt') as f:
for id, row in enumerate(csv.reader(f), start = 24):
username, password, path = row
user_args.append((id, username, password, 'MainUsers'))
perm_args.append((id, path, perms))
insert_users = '''
INSERT IGNORE INTO `USERS`
(`userid`, `username`, `password`, `server_group`)
VALUES (%s, %s, %s, %s)
'''
insert_vfs_permissions = '''
INSERT IGNORE INTO `VFS_PERMISSIONS`
(`userid`, `path`, `privs`)
VALUES (%s, %s, %s)
'''
cursor.executemany(insert_users,user_args)
cursor.executemany(insert_vfs_permissions,perm_args)
</code></pre>
<hr/>
<p><code>INSERT IGNORE</code>告诉MySQL尝试在MySQL表中插入行,但是如果存在冲突,则忽略该命令。例如,如果userid是主键,并且已经有一行具有相同的userid,<code>INSERT IGNORE</code>SQL将忽略插入新行的命令,因为这将创建两个具有相同主键的行。在</p>
<p>如果没有<code>IGNORE</code>,<code>cursor.executemany</code>命令将引发异常并无法插入任何行。在</p>
<p>我使用了<code>INSERT IGNORE</code>,因此您可以多次运行代码,而不会<code>cursor.executemany</code>引发异常。在</p>
<p>还有一个<code>INSERT ... ON DUPLICATE KEY UPDATE</code>命令,它告诉MySQL尝试插入一个行,但是如果有冲突,就更新它,但是除非您想了解更多关于<code>ON DUPLICATE KEY</code>的信息,否则我就不谈了。在</p>