標籤: MSSQL

在docker安裝pyodbc以連線到MSSQL的步驟

在docker安裝pyodbc以連線到MSSQL的步驟

在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