Mac Connect to SQL Server
前陣子需要用 mac 連 sql server, 記錄一下
這裡用DSN連線
Step1 安裝driver
使用 brew
brew update
brew install unixodbc freetds
Step2 修改 odbc 設定
in /usr/local/etc/odbcinst.ini
[FreeTDS]
Description=FreeTDS Driver for Mac & MSSQL
Driver=/usr/local/lib/libtdsodbc.so # 這兩個 .so 在下 brew install unixodbc 的時候會自己裝到這
Setup=/usr/local/lib/libtdsodbc.so
UsageCount=1
in /usr/local/etc/odbc.ini
[TEST_DB] # DSN名稱, 要跟 freetds.conf 的 name mapping
Description = test_db # 隨便
Driver = FreeTDS # 要跟 odbcinst.ini 的 name mapping
Servername = TEST_DB # 隨便
in /usr/local/etc/freetds.conf
[TEST_DB]
host = sqlserver host
port = sqlserver port
tds version = 7.3 # version 說明參考 https://www.freetds.org/userguide/choosingtdsprotocol.htm
Step3 測試連線
設定完成後用command測試能否連上
tsql -S TEST_DB -U username -P password
下完有看到類似下面的畫面表示成功
locale is "zh_TW.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>
python 連線範例
安裝必要套件
pip install sqlalchemy pyodbc
範例如下
from sqlalchemy import create_engine
namespace = YOUR_NAME_SPACE
account = YOUR_ACCOUNT
password = YOUR_PASSWORD
DSN = 'TEST_DB'
engine = create_engine(
f'mssql+pyodbc://{namespace}\\{account}:{password}@{DSN}')
con = engine.connect()
# more sample @see https://kite.com/python/docs/sqlalchemy.dialects.mssql.pyodbc
Reference
發佈時間
2019-9-5