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")
网友评论