有时候在insert之后需要获取该表的自增id
# coding=utf-8
import traceback
import pymysql
sql = "INSERT INTO table_name(`name`, `age`) values(%s, %s)"
value_list = [
("rose", 18),
("lila", 19),
("john", 20),
]
def insert_get_id():
conn=pymysql.connect(
host='127.0.0.1',
port=3306,
user="hhh",
password="pwd",
database="test"
)
mysql_cursor = conn.cursor()
"""
插入数据并获取其自增 ID：支持批量插入多条
:return: {"1": 1, "2": 2, "3": 3}
"""
info = {}
for index, value in enumerate(value_list, 1):
try:
mysql_cursor.execute(sql, value)
# 方法一
insert_id = conn.insert_id()
# 将 insert_id 放在 info 中
info[str(index)] = insert_id
# # 方法二
# cursor.execute("select last_insert_id();")
# insert_id_info = mysql_cursor.fetchall()
# 获取 ID 必须在 commit 之前，否则获取为 0
db.commit()
finally:
mysql_cursor.close()
conn.close()
return info
if __name__ == '__main__':
insert_and_get_id()