關於linux 安裝 python pymssql模組

標籤：

 

需要先安裝freetds
是一個開源的C程式庫，它可以實現在Linux系統下訪問操作微軟的SQL資料庫。可以用在Sybase的db-lib或者ct-lib庫，在裡面也包含了一個ODBC的庫。允許許多應用軟體串連到Sybase或者微軟的SQL伺服器。

設定檔 預設安裝在 /usr/local/etc/freetds.conf

[global]
# TDS protocol version
tds version = 7.0
client charset = UTF-8
# Whether to write a TDSDUMP file for diagnostic purposes
# (setting this to /tmp is insecure on a multi-user system)
; dump file = /tmp/freetds.log
; debug flags = 0xffff

# Command and connection timeouts
; timeout = 10
; connect timeout = 10

# If you get out-of-memory errors, it may mean that your client
# is trying to allocate a huge buffer for a TEXT field.
# Try setting ‘text size‘ to a more reasonable limit
text size = 64512

# A typical Sybase server
[Server2012]
host = 10.20.17.102
port = 1433
tds version = 7.0
client charset = UTF-8
# A typical Microsoft server
[egServer70]
host = ntmachine.domain.com
port = 1433
tds version = 7.0

測試命令 tsql -S Server2012 -U sa (注意只能用設定檔配置的主機，不能直接寫地址)
如果出現編碼不一致的錯誤 嘗試更改client charset

安裝pymssql
測試代碼
import pymssql
conn=pymssql.connect(host=‘10.20.17.102‘,database=‘webdata‘,user=‘sa‘,password=‘***‘,charset="cp936")
cur=conn.cursor()
cur.execute("SELECT TOP 10 * FROM sys_Codes")
for i in cur.fetchall():
print i

conn.close()

(pymssql 2.1.0 時讀取資料庫中文字元資料時報如下錯（不知到是環境的原因還是版本的原因） )

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "pymssql.pyx", line 515, in pymssql.Cursor.fetchall (pymssql.c:8022)
File "pymssql.pyx", line 467, in pymssql.Cursor.getrow (pymssql.c:7035)
File "_mssql.pyx", line 418, in _mssql.MSSQLRowIterator.__next__ (_mssql.c:4317)
File "_mssql.pyx", line 1069, in _mssql.MSSQLConnection.fetch_next_row (_mssql.c:10650)
File "_mssql.pyx", line 1235, in _mssql.MSSQLConnection.get_row (_mssql.c:12131)
File "_mssql.pyx", line 758, in _mssql.MSSQLConnection.convert_db_value (_mssql.c:7762)
File "/usr/local/lib/python2.7/encodings/utf_8.py", line 16, in decode
return codecs.utf_8_decode(input, errors, True)
UnicodeDecodeError: ‘utf8‘ codec can‘t decode byte 0xc2 in position 0: invalid continuation byte
可以嘗試將 charset參數設定為 cp936
如果程式使用sqlalchemy 讀取資料 要在 create_engine時 的charset參數設定為 cp936,不是連接字串的charset，後面傳給pymssql
create_engine(self.conn_str,
connect_args={‘charset‘:‘cp936‘, ‘appname‘: ‘test‘, ‘timeout‘: 10},
echo=False, convert_unicode=True,
pool_recycle=-1, pool_size=100, max_overflow=50)

SQLAlchemy 0.9.7 與SQLAlchemy0.8.x不相容 （undefe()最新版中需要求傳參數）

----------------------

pymssql 2.1.0 不相容sql語句中文注釋

pymssql-2.0.0b1-dev-20111019 

 

關於linux 安裝 python pymssql模組