利用 Python 直接匯出資料庫 (MSSQL) 的資料 (Pandas ReadSQL)

這篇來介紹如何不透過資料庫介面 (SSMS)，直接透過 Python 來抓取資料庫中的資料，並將資料另存成 Excel 檔。
適合給不熟悉 資料庫 或沒有 資料庫權限 的 User 自行下載資料用

用的資料庫是 MS SQL，套件則是 Pyodbc（連接資料庫用） 及 Pandas ReadSQL（將資料庫的資料轉為 dataframe，方便我們轉存成 excel 檔）

要達成這個目的，只需要下面二個步驟：

1. 連接資料庫，設定要抓取資料的條件
2. 將抓到的資料轉為 dataframe，並輸出成 Excel 檔

連接 MSSQL 資料庫

設定好資料庫 IP 跟名字後，透過 pyodbc.connect 就可以連接到資料庫了

TIP

連接資料庫的方法有二種：

1. 用 Windows 帳號直接登入時，pyodbc.connect 不須設定 uid & password，Trusted_Connection 設定為 yes (若不填時，預設值也是 yes)
2. 用 SQL Server 帳號登入，pyodbc.connect 須設定 uid & password，Trusted_Connection 設定為 no

連接到資料庫後，我們就可以直接開始寫 script，看是要抓取哪個 table 的資料
而若我們想讓 User 可自行設定篩選條件的話，則可以用一些 input 來抓取 User 輸入的資料，並帶入到 where 的條件裡面

這裡假設我們要抓取的 table 名稱是 TableName，而裡面有 MESG_CREATE_DATE 及 SEND_SWIFT_ADDR 這二個欄位，User 可以自行輸入 時間區間 以及 發送者地址 來篩選資料

import pyodbc
import os
import pandas as pd

server = 'XX.XX.XX.XX'
db = 'DbName'
uid = 'XXXXX'
pwd = 'XXXXXXX'

# 用 Windows 帳號登入 (Windows authentication) 
#conn = pyodbc.connect('DRIVER={SQL Server}; SERVER=%s; DATABASE=%s; Trusted_Connection=yes;' %(server, db))

# 用 SQL 帳號登入 (SQL Server authentication)
conn = pyodbc.connect('DRIVER={SQL Server}; SERVER=%s; DATABASE=%s; UID=%s; PWD=%s; Trusted_Connection=no;' %(server, db, uid, pwd))

fromDate = input("Type the start (YYYYMMDD) of the period (eg. 20210316)：")
endDate = input("Type the end (YYYYMMDD) of the period (eg. 20210318)：")
sender = input("Type the sender (eg. CBCTTWTP) or press enter to download all：")


script = """
SELECT * FROM [dbo].[TableName] 
WHERE (MESG_CREATE_DATE BETWEEN {dateFrom} AND {dateEnd}) AND SEND_SWIFT_ADDR LIKE '%{sender}%' 
ORDER BY MESG_CREATE_DATE""".format(dateFrom = fromDate, dateEnd = endDate, sender = sender)

用 Pandas 轉存資料為 Dataframe 並輸出成 Excel

上個步驟，我們已經連接了資料庫，以及設定好資料的搜尋條件
這裡我們可以直接用 pd.read_sql 帶入 script 跟資料庫的連線資訊，將抓到的資料轉成 dataframe

最後用 pd.ExcelWriter 來將 dataframe 輸出成 xlsx 檔即可
這邊的 desktopPath 只是單純取得 User 的位址，並將檔案存到這個位置

df = pd.read_sql(script, conn)
desktopPath = os.path.abspath(os.path.dirname(os.getcwd()))

with pd.ExcelWriter(desktopPath  + r'\%d_%d.xlsx' %(fromDate, endDate)) as writer:
	df.to_excel(writer, sheet_name = "Incoming", index=False)

print("File has been Downloaded at %s" %desktopPath)

程式寫完後，就可以打包成 exe 檔給 User 使用囉
若對打包不熟的，可以參考這篇：
利用 Pyinstaller 將 python 程式打包成一個可直接執行的 exe 檔 (opens new window)

Source Code

import pyodbc
import os
import pandas as pd

server = 'XX.XX.XX.XX'
db = 'DbName'
uid = 'XXXXX'
pwd = 'XXXXXXX'

try:
	conn = pyodbc.connect('DRIVER={SQL Server}; SERVER=%s; DATABASE=%s; UID=%s; PWD=%s; Trusted_Connection=no;' %(server, db, uid, pwd))
	fromDate = input("Type the start (YYYYMMDD) of the period (eg. 20210316) : ")
	endDate = input("Type the end (YYYYMMDD) of the period (eg. 20210318) : ")
	sender = input("Type the sender (eg. CBCTTWTP) or press enter to download all : ")

	script = """
	SELECT * FROM [dbo].TableName
	WHERE (MESG_CREATE_DATE BETWEEN {dateFrom} AND {dateEnd}) AND SEND_SWIFT_ADDR LIKE '%{sender}%' 
	ORDER BY MESG_CREATE_DATE""".format(dateFrom = fromDate, dateEnd = endDate, sender = sender)


	df = pd.read_sql(script, conn)

	desktopPath = os.path.abspath(os.path.dirname(os.getcwd()))

	with pd.ExcelWriter(desktopPath  + r'\%s_%s.xlsx' %(fromDate, endDate)) as writer:
		df.to_excel(writer, sheet_name = 'Incoming', index=False)


	print("")
	print("File has been Downloaded at %s" %desktopPath)
	
except Exception as e:
	print("Connect Failed")
	print("Error Message : ", e)

conn.close()
print("Connect Closed")
input("Press any key to leave")