只是浅浅接触过数据库,今天突然有批量插入数据的需求。可以使用 Java 编写代码驱动远程的 MySQL 数据库,但是 Java 好像又要搞一堆 .xml 文件,模型类等等,我对这些都不熟。发现 Python 可以一个脚本搞定,运行也极为简单,在 cmd 里 python ./insert.py 即可。
import pymysql # 先pip install pymysql
import random
import time
user_counter = 0
class DatabaseAcess:
# 初始化属性(包括host、port、user、password和database)
def __init__(self):
self.__db_host = 'xxx.xxx.xxx.xxx'
self.__db_port = 3306
self.__db_user = 'xxx'
self.__db_password = 'xxx'
self.__db_database = 'xxx'
# 连接数据库
def isConnection(self):
self.__db=pymysql.connect(
host=self.__db_host,
port=self.__db_port,
user=self.__db_user,
password=self.__db_password,
database=self.__db_database,
charset='utf8'
)
# 插入数据进 user 表
def user_insert(self, user_values):
try:
# 连接数据库
self.isConnection()
# 创建游标
global cursor
cursor=self.__db.cursor()
# 执行
cursor.executemany(
'INSERT INTO `user` (name, pwd, realName, studentID, major, phone, role, img, del) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)',
user_values)
except Exception as e:
print(e)
finally:
cursor.close()
self.__db.commit()
self.__db.close()
# 生成 user 表所需字段,并调用 sql
def user_data(self,data_number):
user_values=list()
for i in range(1, data_number):
global user_counter
user_counter += 1
user_values.append(
('test' + str(user_counter), '123456', 'test' + str(user_counter), str(user_counter), 'CS',
'123456', 1, '../images/0da8974fa4304499907d488759812159.png', 0))
self.user_insert(user_values)
if __name__ == '__main__':
db = DatabaseAcess()
data_number = 10000
begin_time = time.time()
for i in range(1, 20):
db.user_data(data_number)
end_time = time.time()
print('插入数据耗时:', end_time - begin_time)
print ("{0}条数据插入完成".format(data_number-1))