Python + MySQL（7）批量插入数据到MySQL数据库

方式一： 单笔数据插入

import MySQLdb
import MySQLdb.cursors

db = MySQLdb.connect("localhost", "app", "123456", "Big_Promotion_Flow")   # 打开数据库连接
sql= "insert into Big_Promotion_Flow.report_flow_data(service, num, ip, path, rps, p50RT, p99RT, maxRT, CPU, point_time, query_time_range) VALUES ('ad-biz-custom-server', 3, '172.19.67.188|172.19.67.50|172.19.67.51',	'/capi/ad/public/adGroup/mget',	'1831.09', '8.09', '43.94', '143', '31.00%',2022-11-11 01:04:00,'2022-11-11 00:00:00 - 2022-11-11 08:00:00')"
cursor = db.cursor()  # 使用cursor()方法获取操作游标
cursor.execute(sql)   # 使用execute方法执行SQL语句
db.commit()  # 更新数据库
db.close()  # 关闭数据库连接

也可以用把插入数据库的操作，进行模块化

import MySQLdb
import MySQLdb.cursors

# 连接到数据库
def connect(db_name):
    con = MySQLdb.connect(host="localhost", user="app", passwd="123456", db=db_name,,charset='utf-8')
    return con

# 向表中插入一条记录
def insert(sql, db_name):
    con = connect(db_name)
    cursor = con.cursor()  #创建一个游标对象
    result = cursor.execute(sql)  #执行SQL语句，插入数据
    con.commit()
    con.close()
    if result == 1:
        print("执行成功!")
    return
    
if __name__ == "__main__":
	sql = "insert into Big_Promotion_Flow.report_flow_data(service, num, ip, path, rps, p50RT, p99RT, maxRT, CPU, point_time, query_time_range) VALUES ('ad-biz-custom-server', 3, '172.19.67.188|172.19.67.50|172.19.67.51',	'/capi/ad/public/adGroup/mget',	'1831.09', '8.09', '43.94', '143', '31.00%',2022-11-11 01:04:00,'2022-11-11 00:00:00 - 2022-11-11 08:00:00')"
    insert(sql, "Big_Promotion_Flow")

方式二：实时批量插入

背景：循环请求多次，每次接口返回的数据都会写入进mysql表中

import MySQLdb
import MySQLdb.cursors

def get_data():
	url = "http://xxx"
    header = {
    	"Content-Type": "application/json; charset=UTF-8",
        "xxx": "xxx"}
    body = {
    	"xxx": "xxx", 
    	"xxx": "xxx"}
	res = requests.post(url, headers=header, json=body, verify=False)
	val1 = res.json()["data"]["xxx"]
	val2 = res.json()["data"]["xxx"]
	val3 = res.json()["data"]["xxx"]
	return val1,val2,val3
	    
def insert_into_Mysql():
	db = MySQLdb.connect("localhost", "app", "123456", "Big_Promotion_Flow")
	sql = "INSERT INTO sku_info(spuId, skuId, price) VALUES (%s,%s,%s)"
	cursor = db.cursor()  #获取游标
	d1, d2, d3 = get_data()
	try:
		cursor.execute(sql,(d1, d2, d3))
	except Exception as e:
		db.rollback()  #如果插入失败，回滚
	else：
		db.commit()  #插入成功，提交记录
	finally：
		db.close()  #关闭游标
	
if __name__ == "__main__":
	for i in range 10:
		insert_into_Mysql()

方式三：csv文件批量插入

import pymsql
import pandas as pd
from sqlalchemy import create_engine

def insert_into_mysql(table_name):
	engine = create_engine("mysql+pymysql://app:123456@localhost:3306/Big_Promotion_Flow")
    path = r'D:\Files\Documents\python\sku.csv'
    data = pd.read_csv(path,encoding='utf-8')
	print(data)
	data.to_sql(table_name,engine,chunksize=100000,index=None)
	print('导入成功...')
	return
	
if __name__ == "__main__":
	insert_into_mysql("sku_info")

参考：https://www.jb51.net/article/253176.htm