如何用 Python 將 CSV 檔案匯入資料庫（MSSQL）

YC JHUO 3/6/2021 PythonMSSQLDatabase

這篇來介紹要怎麼透過 Python 將 CSV 檔案匯入到微軟資料庫（MSSQL），若檔案是每日都會產生的話，也可以搭配排程（Server 中的 Task Schedule）來達到自動化的效果

# 設定資料庫

首先 import pymssql 這個套件來連接資料庫
host 填上資料庫的 IP，user 填入帳號，password 填入密碼，database 則是資料庫名稱

import pymssql
import csv

conn = pymssql.connect(host='X.X.X.X',port='1433',user='XXX',password='XXXX',database='XX',charset='utf8')
cursor = conn.cursor()

1
2
3
4
5

# 讀取 CSV 檔案

指定好要讀取的 CSV 檔案路徑後，就能透過 csv.reader 得到 CSV 裡面的值了
接著再用 for in 來取得 CSV 中每一行的值
index 可以得出這是第幾行的資料，row 則是得到一整列資料（類型是 list）

x.strip(' ')則是可以幫我們將 CSV 中每個值的頭尾空格都去除

filePath = r'C:\USERS\009641\Desktop\FFCDB_999.CSV'

with open(filePath) as f:
    f_csv = csv.reader(f)
    for index, row in enumerate(f_csv):
        # trim leading and trailing empty spaces
        row = [x.strip(' ') for x in row]

1
2
3
4
5
6
7

# 匯入資料庫

匯入資料庫的語法是：
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...)

在這個步驟，我們就來組出這段 script

首先將匯入的 Script 分成二段：

script 是要匯入資料庫的 table 名稱及各個欄位名稱
row 負責產生要匯入資料庫的值

TIP

因為我們上一段已經透過 for in 來取得 CSV 中每一列的資料了
因此可以透過 .format 來將每個 row 的值帶進 value 中）

最後再將 script 跟 row 加起來就可以得到匯入 DB 的語法了
將語法透過 cursor.execute執行後，再 commit 就可成功將資料匯入了

TIP

如果 CSV 檔案中資料很多的話，也可以印出 index 來看看匯入的筆數是否正確
這裡之所以將要 index + 1 是因為 index 是從 0 開始算的

script = "INSERT INTO table_name([column1, column2, column3, ...)"

row = "Values ('value1', 'value2', 'value3', ...)".format(column1 = row[0], column2 = row[1], column3 = row[2], ...)

finalScript = script + row

try:
    cursor.execute(finalScript)
    conn.commit()
    print("Inserted Count: ", index + 1)
except:
    conn.rollback()
    print("Script failed")

print("Conn closed")
conn.close()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 消除單引號

若是 CSV 檔案中的資料有包含單引號（'）的話，在匯入時會因為單引號是資料庫中分隔資料的符號而造成匯入失敗
解決的方法則是再加上一個單引號

例如：'20210306', 'B', 'INT'L COMM'L BANK' 這列資料的第三個欄位含有單引號（INT'L COMM'L BANK）
這時，我們只要將該欄位的資料改為 INT''L COMM''L BANK，再匯入資料庫就沒問題了

解決的方式很簡單，只要建立一個 function，將每一列的資料傳進去，然後用 replace 將單引號更新成二個單引號就可以了
因 replace 無法用在 list 中，因此需要再透過一次 for in 來單獨取得每一列中的每一個值

def updateValue(row):
    newRow = []
    for value in row:
        newValue = value.replace("'", "''")
        newRow.append(newValue)
    return newRow

1
2
3
4
5
6

# Source Code

附上完整 source code

import pymssql
import csv


filePath = r'C:\USERS\009641\Desktop\FFCDB_999.CSV'

conn = pymssql.connect(host='X.X.X.X',port='1433',user='XXX',password='XXXX',database='XX',charset='utf8')
cursor = conn.cursor()

script = "INSERT INTO table_name([column1, column2, column3, ...)"

def updateValue(row):
    newRow = []
    for value in row:
        newValue = value.replace("'", "''")
        newRow.append(newValue)
    return newRow


with open(filePath) as f:
    f_csv = csv.reader(f)
    # row = 一整列 data (type: list)
    for index, row in enumerate(f_csv):
        # trim leading and trailing empty spaces
        row = [x.strip(' ') for x in row]
        # replace ' to ''
        row = updateValue(row)
        
        row = "Values ('value1', 'value2', 'value3', ...)".format(column1 = row[0], column2 = row[1], column3 = row[2], ...)

        # get insert script
        finalScript = script + row

        # Insert script
        try:
            cursor.execute(finalScript)
            conn.commit()
            print("Inserted Count: ", index + 1)
        except:
            conn.rollback()
            print("Script failed")


print("Conn closed")
conn.close()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

點擊觀看更多：

YC's Weekly Journal

Choose mode