python操作数据库

!/usr/bin/env python

-- coding:utf-8 --

import pymysql

数据库连接配置

db_config = {
"host": "127.0.0.1",
"port": 3306,
"user": "root",
"password": "123",
"database": "t1",
"charset": "utf8mb4" # 指定字符集，避免中文乱码
}

要插入的数据

data_to_insert = [
("1.1.1.11", 1),
("1.1.1.11", 2)
]

try:
# 使用上下文管理器自动关闭连接和游标
with pymysql.connect(**db_config) as conn:
with conn.cursor() as cursor:
# 批量插入数据
sql = "INSERT INTO hosts (host, color_id) VALUES (%s, %s)"
cursor.executemany(sql, data_to_insert)

        # 获取最新自增ID
        new_id = cursor.lastrowid
        
        # 提交事务
        conn.commit()
        
        # 打印结果
        print(f"成功插入 {cursor.rowcount} 条数据，最新ID是：{new_id}")

except pymysql.MySQLError as e:
print(f"数据库操作失败：{e}")