第一步需要连接数据库
# 连接数据库
db = pymysql.connect(host='127.0.0.1', user='root', password='root', db='caught_data', port=3306, charset='utf8')
第二步把数据添加到数据库中,在这之前还需要获取光标
conn = db.cursor() # 获取光标
conn.execute('insert into concept_plate(plate_name,latest_price,change_amount,price_limit,total_value,'
'turnover_rate,ADVANCE,DECLINE,Led_by_stock,applies) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',
result)
conn.execute()是插入单条数据到mysql的方法,源代码如下:
完整代码:
import pymysql
@timer
def add_test_users():
# 初始化一个数组
usersvalues = []
# 循环执行五次
for num in range(1, 5):
usersvalues.append(('ww', '3.1', '555', '20', '88888888', '2.1', '10', '1', 'ddd', '4.1')) # 注意要用两个括号扩起来
# 连接数据库
db = pymysql.connect(host='127.0.0.1', user='root', password='root', db='caught_data', port=3306, charset='utf8')
print('链接成功')
print(usersvalues)
result = ['ww', '3.1', '555', '20', '88888888', '2.1', '10', '1', 'ddd', '4.1']
conn = db.cursor() # 获取光标
# 注意这里使用的是executemany而不是execute,executemany可以一次插入多条数据,这里插入五条
# conn.executemany('insert into concept_plate(plate_name,latest_price,change_amount,price_limit,total_value,'
# 'turnover_rate,ADVANCE,DECLINE,Led_by_stock,applies) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',
# usersvalues)
conn.execute('insert into concept_plate(plate_name,latest_price,change_amount,price_limit,total_value,'
'turnover_rate,ADVANCE,DECLINE,Led_by_stock,applies) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',
result)
db.commit()
conn.close()
db.close()
print('OK')
add_test_users()
执行前数据库表所示
执行后:
接下来是插入多条数据到数据库中,与插入单条道理差不多,不过得把conn.execute()方法换成conn.executemany()方法:
插入多条数据完整代码:
import pymysql
import time
# 装饰器,计算插入5条数据需要的时间
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():
# 初始化一个数组
usersvalues = []
# 循环执行五次
for num in range(1, 5):
usersvalues.append(('ww', '3.1', '555', '20', '88888888', '2.1', '10', '1', 'ddd', '4.1')) # 注意要用两个括号扩起来
# 连接数据库
db = pymysql.connect(host='127.0.0.1', user='root', password='root', db='caught_data', port=3306, charset='utf8')
print('链接成功')
print(usersvalues)
# result = ['ww', '3.1', '555', '20', '88888888', '2.1', '10', '1', 'ddd', '4.1']
conn = db.cursor() # 获取光标
# 注意这里使用的是executemany而不是execute,executemany可以一次插入多条数据,这里插入五条
conn.executemany('insert into concept_plate(plate_name,latest_price,change_amount,price_limit,total_value,'
'turnover_rate,ADVANCE,DECLINE,Led_by_stock,applies) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',
usersvalues)
# conn.execute('insert into concept_plate(plate_name,latest_price,change_amount,price_limit,total_value,'
# 'turnover_rate,ADVANCE,DECLINE,Led_by_stock,applies) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',
# result)
db.commit()
conn.close()
db.close()
print('OK')
add_test_users()
执行效果: