@使用Python将csv文件批量导入MySQL
使用Python将csv文件批量导入MySQL
小菜鸟在使用Python将csv文件批量导入mysql的时候踩了好多坑,磕磕绊绊也终于还是试成功了哈哈哈哈
总结几个坑点:
1,将csv批量导入mysql有的大佬建议用load data语句的方法参见(https://zhuanlan.zhihu.com/p/45533287),我试了很久,检查了很多遍,总是报1064的错误,后面也没查找到原因。。。于是我换成了先存入pandas,再转存MySQL
2,存入mysql的步骤一般自己设计好数据结构,保存好一套建表的sql,然后建好表再导入。我设计的数据结构过于简单粗暴了
3,csv文件的取名不要有空格啥的,不然也会报错
4,写SQL语句的时候,可以先print出来,放在Navicat里面试试,不然你很难知道自己摔哪个坑了[哭]
5,多写多练,遇到代码出错多百度,不懂就多问,奥利给!
附上代码:
#-- coding: utf-8 --
###换成读入pandas再读进SQL的方法
import pandas as pd
import numpy as np
import csv
from pandas import Series,DataFrame
##将CSV文件内容存入数据库
#首先导入PyMySQL库
import pymysql
# 参数设置 DictCursor使输出为字典模式 连接到本地
config = dict(host='localhost', user='root', password='******',
cursorclass=pymysql.cursors.DictCursor
)
# 建立连接
conn = pymysql.Connect(**config)
# 自动确认commit True
conn.autocommit(1)
# 设置光标
cursor = conn.cursor()
# 一个根据pandas自动识别type来设定table的type
def make_table_sql(df):
columns = df.columns.tolist()
types = df.ftypes
# 添加id 制动递增主键模式
make_table = []
for item in columns:
if 'int' in types[item]:
char = '`'+item +'`' + ' INT'
elif 'float' in types[item]:
char ='`'+item +'`' + ' FLOAT'
elif 'object' in types[item]:
char ='`'+item +'`'+ ' mediumtext'
elif 'datetime' in types[item]:
char ='`'+item +'`'+ ' DATETIME'
make_table.append(char)
return ','.join(make_table)
# csv 格式输入 mysql 中
def csv2mysql(db_name, table_name, df):
# 创建database
cursor.execute('CREATE DATABASE IF NOT EXISTS {}'.format(db_name))
# 选择连接database
conn.select_db(db_name)
# 创建table
cursor.execute('DROP TABLE IF EXISTS {}'.format(table_name))
data_sql = 'CREATE TABLE %s (%s)'%(table_name,make_table_sql(df))
cursor.execute(data_sql)
# 提取数据转list 这里有与pandas时间模式无法写入因此换成str 此时mysql上格式已经设置完成
# df['日期'] = df['日期'].astype('str')
values = df.values.tolist()
# 根据columns个数
s = ','.join(['%s' for _ in range(len(df.columns))])
# executemany批量操作 插入数据 批量操作比逐个操作速度快很多
cursor.executemany('INSERT INTO {} VALUES ({})'.format(table_name,s), values)
##测试单个csv文件导入是否成功
# path = r'路径
# df2 = pd.read_csv(path)
# df1 = df2.astype(object).where(pd.notnull(df2), None)
# csv2mysql(db_name='数据库名', table_name='表名', df=df1)
##得到了所有的文件名,并且存入了file_path集合
file_dir='文件保存的目录路径'
for root, dirs, files in os.walk(file_dir):
#print(root) #当前目录路径
#print(dirs) #当前路径下所有子目录
FileN =files
#print(type(FileN)) #当前路径下所有非目录子文件
file_path = []
for a in FileN:##file_path为所有期刊路径的list
file_path.append(root +'\\'+a)
#print(file_path)
# for a in FileN:##这段代码不成功的原因是将filepath存成了二维list
# file_path_str = root + '\\'+ a+','
# file_path = file_path_str.split(',')
# file_path.pop(-1) ##file_path为所有期刊路径的list
# print(file_path)
for b in file_path:
# path = "r\'"+ b +"\'"
# print(path)
df2 = pd.read_csv(b,encoding= 'utf-8-sig')
#print(df2)
df1 = df2.astype(object).where(pd.notnull(df2), None)## 将nan值转换为none
path_split = b.split('\\')
table_name1 = path_split[-1]
table_name2 = table_name1.split('.')
table_name =table_name2[0] ##得到table name
# print(csv_file_path)
print(table_name)
csv2mysql(db_name='数据库名', table_name=table_name, df=df1)