Chow's Notes

关于linux 安装 python pymssql模块

发表于 | 分类于 |

需要先安装freetds

是一个开源的C程序库，它可以实现在Linux系统下访问操作微软的SQL数据库。可以用在Sybase的db-lib或者ct-lib库，在里面也包含了一个ODBC的库。允许许多应用软件连接到Sybase或者微软的SQL服务器。

配置文件 默认安装在 /usr/local/etc/freetds.conf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
[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

测试代码

1
2
3
4
5
6
7
8
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 时读取数据库中文字符数据时报如下错（不知到是环境的原因还是版本的原因） )

1
2
3
4
5
6
7
8
9
10
11
Traceback (most recent call last):
File "", line 1, in
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

1
2
3
4
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