在windows上,python要連線到mssql,只需要透過pyodbc,幾乎不用什麼設定,就可以輕鬆連線上mssql
但是在linux上,遇到的坑與血淚,相信前人遇到的已經太多了!
以下記錄一下步驟與眉角:
首先我們先假設已經有一個存在的docker container在運作了,裡面有基本python 3.6的環境(或其他版本,這邊以3.x為主,自行上docker hub找吧…)
連進去container後,有3大工程要施作…
1.安裝freetds
wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
tar zxvf freetds-stable.tgz
cd freetds-0.91/
./configure --with-tdsver=7.1 --prefix=/usr/local/freetds0.91 --build=x86_64-pc-linux-gnu --host=x86_64-pc-linux-gnu --mandir=/usr/share/man --infodir=/usr/share/info --datadir=/usr/share --sysconfdir=/etc --localstatedir=/var/lib --libdir=/usr/lib64 --without-ldap --without-tcl --enable-pkinit --enable-thread-support --without-hesiod --enable-shared --with-system-et --with-system-ss --enable-dns-for-realm --enable-kdc-lookaside-cache --with-system-verto --disable-rpath --with-pkinit-crypto-impl=openssl --with-openssl
make
make install
cat >> /usr/local/freetds0.91/etc/freetds.conf
加入
[TestDB]
host = mesotest.database.windows.net
port = 1433
tds version = 7.0
註:freetds.conf 的dump file = /tmp/freetds.log反註解,global的tds版本也要改成7.0一致的版本,有dump log的話,後續連線失敗的話,可以看的到錯誤原因,事半功倍
例: severity:9, dberr:20002[Adaptive Server connection failed], oserr:0[Success] –>tds版本問題,要調整,若8.0不行,就7.2->7.1->7.0往回裝
2.測試freetds連線
/usr/local/freetds0.91/bin/tsql -S TestDB -U app_user@mesotest.database.windows.net -P {password} -D test1
若freetds可以連線,也可以查詢的話,應該會像這樣:
可以下sql指令,也回傳的了資料集
2.設定ODBCInit
apt-get install unixodbc-dev
apt-get install python-pip
pip install pyodbc
#yum install gcc-c++
#關鍵中的關鍵
find /usr -name "*\.so" |egrep "libtdsodbc|libtdsS"
#/usr/lib/libtdsS.so
#/usr/local/freetds0.91/lib/libtdsodbc.so
# cp /etc/odbcinst.ini /etc/odbcinst.ini.20160102
# cat >> /etc/odbcinst.ini
[SQL Server]
Description = FreeTDS ODBC driver for MSSQL
Driver = /usr/local/freetds0.91/lib/libtdsodbc.so
Setup = /usr/lib/libtdsS.so
FileUsage = 1
# 檢查一下驅動
# odbcinst -d -q
[SQL Server]
cat >> /etc/odbc.ini
[TESTDSN]
Driver = SQL Server
Server = xxx.xxx.xxx.xxx
User = xxxx
TDS_Version = 7.0
Port = 1433
3.執行簡單的python連mssql程式
import pyodbc
conn = pyodbc.connect("driver={SQL Server};server=mesotest.database.windows.net;PORT=1433 database=test1;UID=app_user@mesotest.database.windows.net;PWD=%s;TDS_Version=7.0;" % "{yourpassword}" )
cursor = conn.cursor()
query = "select getdate()"
print(query)
cursor.execute(query)
row = cursor.fetchone()
while row:
print(str(row[0]))
row = cursor.fetchone()
執行成功,我要哭了…凌晨3點了!!
根據網友們的分享,這裡還有一個很大的坑就是連線字串要包含TDS_Version的資訊,版本要跟freetds內配置的版本一樣…
否則就會陷入無限的…08001輪迴,而不知其所以然…
Traceback (most recent call last):
File “<stdin>”, line 1, in <module>
pyodbc.Error: (‘08001’, ‘[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)’)
關鍵2篇REF
https://blog.csdn.net/samed/article/details/50449808
http://www.voidcn.com/article/p-vaxmczdi-dc.html