使用python將csv文件快速轉存到mysql
- 2019 年 10 月 6 日
- 筆記
因為一些工作需要，我們經常會做一些數據持久化的事情，例如將臨時數據存到文件里，又或者是存到資料庫里。
對於一個規範的表文件（例如csv），我們如何才能快速將數據存到mysql裡面呢？
這個時候，我們可以使用python來快速編寫腳本。
正文
對於一個正式的csv文件，我們將它打開，看到的數據是這樣的：
這個數據很簡單，只有三個列，現在我們要使用python將它快速轉存到mysql。
既然使用python連接mysql，我們就少不了使用pymysql這個模組。
使用pip進行快速安裝：
pip install pymysql
安裝結束，我們使用pymysql連接資料庫：
import pymysql con = pymysql.connect(user="root", passwd="root", db="test", host="47.95.xxx.xxx", local_infile=1)
user是連接資料庫的用戶名，passwd是連接數據的密碼，db是你想要連接資料庫的名字，host是你要連接資料庫的主機，如果就是自己的電腦，就填127.0.0.1。我們這邊是將csv批量寫到資料庫，需要設置local_infile參數，如果不添加會報錯。
連接完資料庫我們便可以使用游標來執行sql語句了：
cur = con.cursor()
定義好了游標我們就可以使用execute方法來執行sql語句了。
cur.execute("set names utf8") cur.execute("SET character_set_connection=utf8;")
下面我們來打開我們的csv文件，讀取裡面的內容，我們需要提取第一行列名的資訊，然後創建表：
with open(file_path, 'r', encoding='utf8') as f: reader = f.readline() print(reader) devide = reader.split(',') # 做成列表 devide[-1] = devide[-1].rstrip('n') # 去除最後的換行符 print(devide)
默認讀出來的數據就是一行字元串，現在我們通過「，」提取我們的列名，並且去除我們最後一個列名的換行符，這樣我們就能得到所有的列名了。
下面我們需要創建表，在創建表之前我們需要將每個列指定一下格式：
column = '' for dd in devide: column = column + dd + ' varchar(255),'
拼接好後我們需要將最後一個列的逗號去掉
col = column.rstrip(',')
這樣我們就可以寫創建表的sql語句了：
table_name = "TBexport"
create_table_sql = 'create table if not exists {} ({}) DEFAULT CHARSET=utf8' .format(table_name, col)
cur.execute(create_table_sql)
下面我們可以向表中插數據了：
首先要介紹一下，mysql支援csv數據的導入，以下是sql的語法：
LOAD DATA INFILE '文件名'
REPLACE INTO TABLE 表名
CHARACTER SET UTF8
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY 'n'
那這邊我們根據上面這個語句去拼寫我們需要插入數據的語句：
file_path = "export.csv"
data = 'LOAD DATA LOCAL INFILE '' + file_path + ''REPLACE INTO TABLE ' + table_name + 'CHARACTER SET UTF8 FIELDS TERMINATED BY ',' '' ENCLOSED BY '"' ' 'LINES TERMINATED BY 'n' IGNORE 1 LINES;'
cur.execute(data.encode('utf8'))
最後一步，提交事務。
（事務保證他們的連貫性，只要一步錯就會進行回滾）
con.commit()
記得關閉游標和資料庫連接。
cur.close() con.close()
結果：
完整程式碼：
import pymysql # file_path = "exam.csv" # table_name = 'update_time_table' file_path = "export.csv" table_name = "TBexport" try: con = pymysql.connect(user="root", passwd="root", db="test", host="47.95.20x.xxx", local_infile=1) con.set_charset('utf8') cur = con.cursor() cur.execute("set names utf8") cur.execute("SET character_set_connection=utf8;") with open(file_path, 'r', encoding='utf8') as f: reader = f.readline() print(reader) devide = reader.split(',') # 做成列表 devide[-1] = devide[-1].rstrip('n') # 去除最後的換行符 print(devide) column = '' for dd in devide: #如果標題過長，只能存成text格式 if dd == "標題": column = column + dd + ' TEXT,' else: column = column + dd + ' varchar(255),' col = column.rstrip(',') # 去除最後一個多餘的， # print(column[:-1]) create_table_sql = 'create table if not exists {} ({}) DEFAULT CHARSET=utf8'.format(table_name, col) print(create_table_sql) data = 'LOAD DATA LOCAL INFILE '' + file_path + ''REPLACE INTO TABLE ' + table_name + ' CHARACTER SET UTF8 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n' IGNORE 1 LINES;' cur.execute(create_table_sql) cur.execute(data.encode('utf8')) print(cur.rowcount) con.commit() except: print("發生錯誤") con.rollback() finally: cur.close() con.close()
程式碼也可以去github：
https://github.com/johnturingwu/csv_to_mysql
點擊閱讀原文可直達
