安装pymysql,在pycharm中安装最好,中间用命令安装到script一直报错!!!
下面是造数据的详细代码
数据库中已 创建好数据库
create table if NOT EXISTS data_stu1(
id int auto_increment primary key,
name VARCHAR(10),
sex char(3),
school VARCHAR(25),
hobby VARCHAR(25),
home VARCHAR(25),
enter_data VARCHAR(25),
score DOUBLE,
birthday VARCHAR(25)
)
import time
from pymysql import *
# 装饰器,计算插入50000条数据需要的时间
def timer(func):
def decor(*args):
start_time = time.time()
func(*args)
end_time = time.time()
d_time = end_time - start_time
print("the running time is : ", d_time)
return decor
@timer
def add_test_users():
conn = connect(host='10.75.20.xx', port=xx, user='root', password='xxx', database='xxx',
charset='utf8')
cs = conn.cursor() # 获取光标
# 注意这里使用的是executemany而不是execute,写代码字符串拼接花12s左右的时间,写入SQL语句花了8s左右,而executemany 字符串拼接仅花了2s左右。遇到大批量数据写入时尽量使用executemany 方法。这次测试用的execute。
for i in range(10000):
cs.execute('INSERT into data_stu1(name,sex,school,hobby,home,enter_data,score,birthday) VALUES("小米", "女", "重庆大学", "跑步", "重庆","2020-11-12", 98, "1998-19-12")')
conn.commit()
cs.close()
conn.close()
print('OK')
if __name__ == "__main__":
add_test_users()
插入语句中传入变量的用法,并批量插入千万级数据
import pymysql
'''连接数据库,包括数据库ip、端口、用户名、密码、数据库'''
db = pymysql.connect(host='10.xx.XX.11', user='XX.', password='XX~L81M', port=3306,
db='dwxx')
'''创建游标'''
cursor = db.cursor()
i = 1
while i <= 10000000:
b = 3
a="INSERT INTO dw_dev.jc_test1 (id,s_suppkey,s_name,s_address,s_nationkey,s_phone,s_acctbal,s_comment) VALUES({},18,'Supplier#xx','xxx',16,'26-729-551-xx',704.82,'accountsriously bold ')"
c=a.format(b)
cursor.execute(c)
'''提交数据,必须提交,不然数据不会保存'''
db.commit()
b = b + 1
i = i + 1
print("已经插入完成")
cursor.close()
db.close()