0、背景

在数据分析的时候，需要将excel/csv/txt保存的格式化数据，通过python程序写入mysql中，以便于进行数据查询分析。处理过程是，读取excel/csv/txt -> pandas数据清洗 -> 导入Mysql库，一般是做一个表append 或者是 if exist -> replace的操作。

1、批量方式写入

用pandas.to_sql()方法, 以块的方式批量导入数据，优点是效率高，当数据表已存在, 可在表中追加数据，若表不存在, 导入时可直接创建表，缺点是如果表存在, 且设置了主键约束, 写入会报错。
代码实例：

import pandas as pd
from sqlalchemy import create_engine


#设置文件目录
filePath = "d:/pythonP/人员信息.xls"

#读取excel文件"明细"页签数据
table = pd.read_excel(filePath, sheet_name="明细")

#创建MySql数据库连接串, //后的参数为: 用户名, 密码, 主机, 数据库名
engine = create_engine("mysql+pymysql://root:123456@192.168.1.86:3306/companyINF")

#MySQL连接测试，验证能否连通
try:
    pd.read_sql("show tables;", con=engine); print("connect successfully!")
except Exception as error:
    print("connect fail! because of :", error)

#用to_sql()方法插入数据,if_exists参数值："replace"表示如果表存在, 则删掉重建该表, 重新创建；"append"表示如果表存在, 则会追加数据。
try:
    table.to_sql("person", con=engine, index=False, if_exists="replace");
    print("insert successfully!")
except Exception as error: 
    print("insert fail! because of:", error)
print("data write complete!")

2、逐条方式写入

用Python的pymysql驱动，将每条数据拼接SQL语句insert into执行，适用于有主键约束的的表，直接将新数据逐条插入, 如遇到ID重复，则更新对应记录。
代码实例：

import pandas as pd
import pymysql

#数据库连接测试，执行sql, 提交, fetchall查询
def connectTEST(cursor):
    try:
        cursor.execute("show tables;")
        con.commit(
        print(cursor.fetchall())
        cursor.close()
        con.close()
        print("连接测试成功!")
    except Exception as e:
        print("连接测试失败!", e)

#数据处理，sheet=None返回所有sheet数据，可指定序号sheet=[0,1,2]，默认第一个为0
def dataDEAL(path, sheet=None):
    table = pd.read_excel(path, sheet)
    data = table.where(pd.notnull(table), None)     # 将缺失值用 None 填充
    return table

#数据导入，获取字段个数，指定数据插入方法replace，遇到主键重复直接替换
def dataIMP(cursor, table, to_table, method="replace"):
    len_cols = table.columns.size  #获取table字段个数
    insert_sql = "%s into %s values (%s)" % (method,to_table, "%s,"*(len_cols-1) + "%s")
    #将每行数据, 组成一条记录
    args =  (tuple(row) for _, row in table.iterrows())
    try:
        _ = cursor.executemany(insert_sql, args)
        con.commit()
    except Exception as e:
        print("fail",e)
    finally:
        cursor.close()
        con.close()
        print("数据导入完成!")


if __name__ == '__main__':
    #MySQL服务器配置
    host = '192.168.1.86'
    user = 'root'
    passwd = '123456'
    dataBase = 'companyINF'
    to_table = 'person'
    path = 'd:/pythonP/人员信息.xls'

    #创建连接对象con, 游标对象cursor
    con = pymysql.connect(host, user, passwd, dataBase)
    cursor = con.cursor()
    connectTEST(cursor)
    dataDEAL(path, sheet=None)
    dataIMP(cursor, table, to_table, method="replace")