使用Pandas批量读取CSV文件并写入MySQL

lkme

人不远行，怎知世界之大。

最近在尝试分析骑行数据，发现电脑在执行统计分析一些维度的数据时比较慢，很不方便实时的验证想法，寻思最终都以Matplotlib画图呈现，就考虑把数据写入MySQL，然后通过查询结果作图，也方便后续分析其它问题，所以就有了这篇分享。

使用Python读取CSV文件并写入MySQL的方法比较多，比如使用Pandas的to_sql方法，或者读取CSV之后批量写入MySQL，而本文分享的是逐条读取&写入。

数据源

分析的数据是2015年Citi-Bike的数据，来自 CitiBike官网。

打开CSV文件，看到的内容如下图。

如下是步骤

向数据库kai_test创建数据表，脚本如下。

# -*- coding: utf-8 -*-
import pymysql
# 1.链接数据库
db = pymysql.connect(
      host='127.0.0.1',
      port=3306,
      user='***',
      passwd='***',
      db='kai_test',
      charset='utf8')
# 建立链接游标
cursor = db.cursor()
print ('>> 已连接数据表，处理中...')
# 2.添加数据库表头（创建的字段，不要使用空格）
sql = '''CREATE TABLE IF NOT EXISTS kai_test (
        `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `tripduration` CHAR(10),
        `starttime` CHAR(30),
        `stoptime` CHAR(30),
        `start_station_id` CHAR(10),
        `start_station_name` CHAR(60),
        `start_station_latitude` CHAR(20),
        `start_station_longitude` CHAR(20),
        `end_station_id` CHAR(10),
        `end_station_name` CHAR(60),
        `end_station_latitude` CHAR(20),
        `end_station_longitude` CHAR(20),
        `bikeid` CHAR(10),
        `usertype` CHAR(15),
        `birth_year` CHAR(10),
        `gender` CHAR(2)        
cursor.execute(sql)
# 3.提交并关闭链接
cursor.close()
db.close()
print ('>> Done.')

2. 先读取指定目录的所有CSV文件，然后逐个读取并逐条写入MySQL，脚本如下。

# -*- coding: utf-8 -*-
import pymysql,time
import glob,os
import pandas as pd
# 1.准备，指定目录
time_start = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) # 记录当前时间
print('>> 当前时间：',time_start)
print('>> 开始处理……')
filelocation = r"D:/Year2015-citibike-tripdata/Data_all/"
# 2.链接数据库
print('>> 连接MySQL...')
db = pymysql.connect(
      host='127.0.0.1',
      port=3306,
      user='***',
      passwd='***',
      db='kai_test',
      charset='utf8')
# 建立链接游标
cursor = db.cursor()
print ('>> 已连接数据表。')
# 3.查看本地新文件名
filenames=[]
os.chdir(filelocation) #指定目录
for i in glob.glob("*.csv"): # 获取指定目标下所有的CSV文件名
    filenames.append(i[:-4]) # 文件名不包含“.csv”
count = len(filenames)
print('>> 本地文件：',count,'个') # 如下是以“Num.**”为序号打印出每个文件名
for i in range(0,count): # 把0-9的数字用0补齐2位，也可以用zfill函数或者format格式化实现
    if i<9:
        ii = i+1
        ij = '0'+str(ii)
    else:
        ij = i+1
    print(' - Num.', end='')
    print(ij, filenames[i])
# 4.把新文件的数据提交mysql
print('>> 读取中...')
# MySQL语句
insert_sql = 'insert into kai_test (tripduration,starttime,stoptime,start_station_id,start_station_name,start_station_latitude,start_station_longitude,end_station_id,end_station_name,end_station_latitude,end_station_longitude,bikeid,usertype,birth_year,gender) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
# 开始逐个文件处理
for file_name in filenames:
    print(" + 正在处理：", file_name,'（第',filenames.index(file_name)+1,'个）')
    time_now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())  # 记录处理每个文件的时间
    print(' - 当前时间：', time_now)
    data_csv = pd.read_csv(open(filelocation + file_name+'.csv')) # 使用Pandas读取数据文件
    # print(data_csv.head(3)) # 查看前3条数据
    # print(data_csv.info()) # 查看数据表信息
    # print(len(data_csv.index)) # 查看数据量
    # print(data_csv.loc[2].values) # 查看指定某一行的数据
    ii = 0 # 用于统计每个文件的数据量
    for i in range(0,data_csv.shape[0]): # 逐行读取
        row = data_csv.loc[i].values # 获取第i行数据
        # print(i,'>>:',data_csv.loc[i].values) # 打印第i行数据
        cursor.execute(insert_sql, (str(row[0]), str(row[1]), str(row[2]), str(row[3]), str(row[4]), str(row[5]), str(row[6]), str(row[7]),
        str(row[8]), str(row[9]), str(row[10]), str(row[11]), str(row[12]), str(row[13]), str(row[14])))
        ii = i + 1
    print(' - 提交数量：',ii,'条')
# 5.结束
db.commit() # 提交记录