pandas写入MySQL

安装好pandas、mysql

pip install pandas
pip install pymysql

导入pandas、mysql

import pymysql as mysql
import pandas as pd

建立连接

con=mysql.connect(host='10.10.0.221',user='root',password='root',database='pandas',port=3306,charset='utf8')

创建游标

cur=con.cursor()

读取本地Excel

df=pd.read_excel(r'c:/users/administrator/desktop/2023-10-20缺货汇总.xlsx')

提交

table = 'quehuo'
columns='仓库代码,仓库名称,物料代码,单据数量,库存可用数量,缺货数量'
for row in df.itertuples(index=False):
    value=(row.仓库代码,row.仓库名称,row.物料代码,row.单据数量,row.库存可用数量,row.缺货数量)
    sql = "Insert Into {0} ({1}) values {2}".format(table,columns,value)
    cur.execute(sql)
    con.commit()

数据及表结构