python将Excel文件内容导入Mysql数据

╰+攻爆jí腚メ 2023-06-04 02:41 23阅读 0赞

为了方便起见，将所有字段类型设置为str,理解mysql的语法这个玩意贼简单
    
    # _*_ coding:utf-8 _*_
    import pandas as pd  #先装个pandas ,pip install pandas
    import pymysql
    
    #读入数据库
    filename='F:\pycharm\sales.xls'   #本地需要导入数据库的文件
    data=pd.read_excel(filename)
    #建立数据库连接
    db=pymysql.connect('10.1.9.176','root','123456','python_excel')
    cursor=db.cursor()
    #判断数据表是否存在
    try:
        cursor.execute('create table sales( ORGCODE varchar(20),ORGNAME varchar(100),SHIPPINGTOTAL varchar(100),BOXTOTAL varchar(100),YHTOTAL varchar(100),YSTOTAL varchar(100),PAYTOTAL varchar(100),PLATFORMSERVICETOTAL varchar(100),PLATFORMYHTOTAL  varchar(100),MERCHANTYHTOTAL  varchar(100),PLUTOTAL  varchar(100), MINDELIVERYAMOUNT varchar(100),SSTOTAL varchar(100),ORDERDATE varchar(100),FINISHDATE varchar(100), ORDERNO varchar(100), PLATFORM varchar(100),STATUS varchar(100))')
    except:
            print('已存在的表')
    query = 'insert into sales(ORGCODE,ORGNAME,SHIPPINGTOTAL ,BOXTOTAL ,YHTOTAL ,YSTOTAL ,PAYTOTAL ,PLATFORMSERVICETOTAL ,PLATFORMYHTOTAL  ,MERCHANTYHTOTAL  ,PLUTOTAL  , MINDELIVERYAMOUNT,SSTOTAL,ORDERDATE ,FINISHDATE , ORDERNO , PLATFORM ,STATUS) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
    for i in range(0,len(data)):
        ORGCODE=data.iloc[i,0]#不清楚iloc可以百度下iloc的用法
        ORGNAME=data.iloc[i,1]
        SHIPPINGTOTAL=data.iloc[i,2]
        BOXTOTAL=data.iloc[i,3]
        YHTOTAL=data.iloc[i,4]
        YSTOTAL=data.iloc[i,5]
        PAYTOTAL=data.iloc[i,6]
        PLATFORMSERVICETOTAL=data.iloc[i,7]
        PLATFORMYHTOTAL=data.iloc[i,8]
        MERCHANTYHTOTAL=data.iloc[i,9]
        PLUTOTAL=data.iloc[i,10]
        MINDELIVERYAMOUNT=data.iloc[i,11]
        SSTOTAL=data.iloc[i,12]
        ORDERDATE=data.iloc[i,13]
        FINISHDATE=data.iloc[i,14]
        ORDERNO=data.iloc[i,15]
        PLATFORM=data.iloc[i,16]
        STATUS=data.iloc[i,17]
        values = (str(ORGCODE),str(ORGNAME),str(SHIPPINGTOTAL),str(BOXTOTAL),str(YHTOTAL) ,str(YSTOTAL) ,str(PAYTOTAL) ,str(PLATFORMSERVICETOTAL) ,str(PLATFORMYHTOTAL) ,str(MERCHANTYHTOTAL) ,str(PLUTOTAL) ,str(MINDELIVERYAMOUNT),str(SSTOTAL),str(ORDERDATE) ,str(FINISHDATE) ,str(ORDERNO) , str(PLATFORM) ,str(STATUS))
        cursor.execute(query,values)
    cursor.close()
    db.commit()
    print("数据导入成功")
    db.close()
    
    如图提示数据导入成功

![1180843-20190917164555785-826951425.png][]

查询数据（截图不全）

![1180843-20190917165057186-2135174410.png][]

用Navicat会有乱码，不知道什么原因，以后在慢慢研究吧

![1180843-20190917165206096-2020893468.png][]

搞完以后发现贼鸡儿简单啊

转载于:https://www.cnblogs.com/python2687806834/p/11535372.html

[1180843-20190917164555785-826951425.png]: /images/20230601/24b83199ba2b42e3a98dbd93999ec1b0.png
[1180843-20190917165057186-2135174410.png]: /images/20230601/3a58dd3e36bb42709c0aa62a5edd4ec9.png
[1180843-20190917165206096-2020893468.png]: /images/20230601/2e016da6b75d41f0aeeb7caadeb291c7.png