說明此程式碼的應用情境:
用工作排程器
將經過pyinstaller打包後的此程式
設定為每月月底16:30時執行
即可自動將選定幣別的每月最後營業日的匯率
上傳至指定的SQL與SAP環境

#!/usr/bin/env python
#coding: utf-8
#RATE_TYPE = E
#執行日為每月最後一個營業日16:30
#生效日為執行日的隔日00:00

import os
import sys
import ssl
import json
import pyrfc
import pyodbc
import pandas as pd
from decimal import Decimal
from datetime import datetime, timedelta

ssl._create_default_https_context = ssl._create_unverified_context

def generate_url(date, retry):
    base_url = "https://rate.bot.com.tw/xrt/all/"
    return f"{base_url}day" if retry == 0 else f"{base_url}{date.strftime('%Y-%m-%d')}"

def filter_and_transform_data(currency, adjusted_date):
    cur_table = []
    for _, row in currency.iterrows():
        if row['幣別'] in ['CNY', 'EUR', 'HKD', 'JPY', 'SGD', 'USD', 'GBP', 'PHP', 'KRW', 'VND']:
            if row['即期匯率-本行買入'] == '-':
                av_amt = (float(row['現金匯率-本行買入']) + float(row['現金匯率-本行賣出'])) / 2
            else:
                av_amt = (float(row['即期匯率-本行買入']) + float(row['即期匯率-本行賣出'])) / 2
            row['銀行中價'] = av_amt
            cur_table.append(row)

    cur_df = pd.DataFrame(cur_table)
    cur_df = cur_df.rename(columns={'幣別': 'FROM_CURR', '銀行中價': 'EXCH_RATE'})
    cur_df['EXCH_RATE'] = cur_df['EXCH_RATE'].round(4)
    cur_df.insert(2, 'TO_CURR', 'TWD')
    cur_df.insert(3, 'RATE_TYPE', 'E')
    cur_df['VALID_FROM'] = adjusted_date.strftime('%Y%m%d')
    cur_df.insert(5, 'FROM_FACTOR', 1)
    cur_df.insert(6, 'TO_FACTOR', 1)
    return cur_df[['FROM_CURR', 'EXCH_RATE', 'TO_CURR', 'RATE_TYPE', 'VALID_FROM', 'FROM_FACTOR', 'TO_FACTOR']]

def fetch_cur_data():
    today = datetime.today()
    retry_days = 0

    while retry_days < 3:
        retry_date = today - timedelta(days=retry_days)
        url = generate_url(retry_date, retry_days)

        dfs = pd.read_html(url)
        currency = dfs[0].iloc[:, 0:5]
        currency.columns = [u"幣別", u"現金匯率-本行買入", u"現金匯率-本行賣出", u"即期匯率-本行買入", u"即期匯率-本行賣出"]
        currency[u"幣別"] = currency[u"幣別"].str.extract('\((\w+)\)')

        if currency is not None:
            cur_data = filter_and_transform_data(currency, retry_date)
            return cur_data
        elif retry_days == 2:
            print("三次嘗試均未成功獲取匯率資料，程序終止。")
            return None
        else:
            retry_days += 1

def read_sql_configs(file_path):
    with open(file_path, 'r') as file:
        lines = file.read().split('\n\n')

    configs = []

    for config_str in lines:
        config = {}
        for line in config_str.split('\n'):
            if line:
                key, value = line.split("=")
                config[key] = value
        configs.append(config)

    return configs

def connect_to_database(config):
    return pyodbc.connect(
        DRIVER=config['DRIVER'],
        SERVER=config['SERVER'],
        DATABASE=config['DATABASE'],
        UID=config['UID'],
        PWD=config['PWD'],
        Encrypt=config['Encrypt'],
        TrustServerCertificate=config['TrustServerCertificate'])

def upload_to_sql_server(data, configs):
    json_str = data.to_json(orient='records')
    json_data = json.loads(json_str)

    for idx, config in enumerate(configs):
        conn = connect_to_database(config)
        cursor = conn.cursor()

        try:
            for record in json_data:
                sql = (f"INSERT INTO [ZFIT801] (FROM_CURR, EXCH_RATE, TO_CURR, RATE_TYPE, VALID_FROM, FROM_FACTOR, TO_FACTOR) "
                       f"VALUES ('{record['FROM_CURR']}', {record['EXCH_RATE']}, '{record['TO_CURR']}', '{record['RATE_TYPE']}', "
                       f"'{record['VALID_FROM']}', {record['FROM_FACTOR']}, {record['TO_FACTOR']})")
                cursor.execute(sql)
            print(f"成功上傳資料至連接 {idx + 1} SQL Server")
            conn.commit()

        except Exception as e:
            print(f"連接 {idx + 1} SQL 上傳失敗: {str(e)}")

        finally:
            conn.close()

def read_sap_configs(file_path):
    with open(file_path, 'r') as file:
        sap_configs = file.read().split('\n\n')
    return [dict(line.strip().split('=') for line in sap_config.split('\n')) for sap_config in sap_configs]

def connect_to_sap(params):
    return pyrfc.Connection(
        user=params['user'],
        passwd=params['passwd'],
        ashost=params['ashost'],
        sysnr=params['sysnr'],
        client=params['client'])

def upload_to_sap_server(data, sap_params_list):
    for sap_params in sap_params_list:
        conn = connect_to_sap(sap_params)

        for record in data.to_dict(orient='records'):
            ex_rate = {
                'FROM_CURR': record['FROM_CURR'],
                'EXCH_RATE': Decimal(record['EXCH_RATE']),
                'TO_CURRNCY': record['TO_CURR'],
                'RATE_TYPE': record['RATE_TYPE'],
                'VALID_FROM': record['VALID_FROM'],
                'FROM_FACTOR': Decimal(record['FROM_FACTOR']),
                'TO_FACTOR': Decimal(record['TO_FACTOR'])}
            result = conn.call('BAPI_EXCHANGERATE_CREATE', EXCH_RATE=ex_rate)

        result = conn.call('BAPI_TRANSACTION_COMMIT')

        if result['RETURN'] and result['RETURN']['TYPE'] == 'E':
            print(f'Error: {result["RETURN"][0]["MESSAGE"]}')
        else:
            print(f'成功上傳資料至 SAP Server {sap_params["ashost"]} {sap_params["sysnr"]} {sap_params["client"]}')

def main():
    cur_data = fetch_cur_data()
    print(cur_data)

    base_path = os.path.dirname(sys.executable) if getattr(sys, 'frozen', False) else os.path.dirname(__file__)

    file_path = 'SQLCONFIG.txt'
    sql_params_list = read_sql_configs(os.path.join(base_path, file_path))
    upload_to_sql_server(cur_data, sql_params_list)

    file_path = 'SAPCONFIG.txt'
    sap_params_list = read_sap_configs(os.path.join(base_path, file_path))
    upload_to_sap_server(cur_data, sap_params_list)

if __name__ == "__main__":
    main()

程式需要配合以下兩個文檔使用
文檔需要放在源碼or打包後程式的相對路徑

SAPCONFIG.txt
內容:
user=帳號
passwd=密碼
ashost=伺服器IP
sysnr=系統ID
client=用戶端

SQLCONFIG.txt
內容:
DRIVER={ODBC Driver ODBC驅動器版本 for SQL Server}
SERVER=伺服器IP
DATABASE=資料庫名稱
UID=帳號
PWD=密碼
Encrypt=yes
TrustServerCertificate=yes

可以使用pyinstaller打包為exe使用
亦可以於編譯器直接執行