需求：一次插入多条，获取每条记录的自增 ID
# coding=utf-8
import traceback
import pymysql
db = pymysql.connect(
host='localhost',
port=3306,
user="root",
password="pwd",
database="test"
)
cursor = db.cursor()
sql = "INSERT INTO table_name(`name`, `age`) values(%s, %s)"
value_list = [
("rose", 18),
("lila", 19),
("john", 20),
]
# # 批量插入，只会获取最后一条数据的 ID
# cursor.executemany(sql, value_list)
def insert_and_get_id():
"""
插入并获取其自增 ID：支持批量插入多条
:return: {"1": 1, "2": 2, "3": 3}
"""
info = {}
for index, value in enumerate(value_list, 1):
try:
cursor.execute(sql, value)
# 方法一
insert_id = db.insert_id()
# 将 insert_id 放在 info 中
info[str(index)] = insert_id
# # 方法二
# cursor.execute("select last_insert_id();")
# insert_id_info = cursor.fetchall()
# 获取 ID 必须在 commit 之前，否则获取为 0
db.commit()
except Exception as e:
print(traceback.format_exc())
cursor.close()
db.close()
return info
if __name__ == '__main__':
insert_and_get_id()
注意：获取 ID 必须在
commit之前，否则获取为 0
