MySQL中批量插入数据

例1：

方法一：SQL语句操作

delimiter $$ //以delimiter来标记用$表示存储过程结束
create procedure pre() //创建pre()存储方法
begin
declare i int; //定义i变量
set i=2;
while i<53 do
insert into lineinfo SET ID=i, lineName=concat('北京地铁',i,'号线');
set i=i+1;
end while;
end
$$
call pre();
DROP procedure pre;

select * from lineinfo //查询结果

方法二：python语句操作

import pymysql
# 链接数据库
db = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='mydata', charset='utf8')
# 创建游标对象cursor
cursor = db.cursor()

# 查询数据库版本
cursor.execute("select version()")
data = cursor.fetchone()
print(" Database Version:%s" % data)

# 删除数据
sql = "delete from lineinfo where ID>=2"
cursor.execute(sql)
db.commit()
# 查看删除后的结果
sql = "select * from lineinfo"
cursor.execute(sql)
data = cursor.fetchone()
print("删除后lineinfo表：" + "\n", data)

# 插入数据
sql = "insert into lineinfo (ID, lineName) values (%s, %s)"
for i in range(2, 10, 1):
    lineName = "test北京地铁"+str(i)+"号线"
    cursor.execute(sql, (i, lineName))        # 传值
    db.commit()                               # 提交事务

# 查看插入后的结果
sql2 = "select * from lineinfo"
cursor.execute(sql2)
data2 = cursor.fetchone()
print("插入后lineinfo表：" + "\n", data2)

# 关闭数据库连接
db.close()

例2：

delimiter $$
create procedure pre()
begin
declare i int; //列车数
declare j int; //车厢数
declare k int; //数据总数
set i=1;
set j=1;
set k=1;
WHILE i<=20 DO
WHILE j<=3 DO
INSERT into deviceinfo set ID=k, trainNum=concat(i,'00'),carName=concat(trainNum,j),deviceName=concat('第',k,'个设备'),deviceIP=concat('192.168.100.',k),deviceRemarks=k;
set j=j+1;
set k=k+1;
END WHILE;
set i=i+1;
set j=1;
END WHILE;
END
$$
call pre();
DROP procedure pre;