如何使用 freeTDS 和 unixODBC 配置 pyodbc 以正确接受来自 SQL Server 的字符串?
我无法将有效的字符串从 MSSQL 服务器获取到 Python 中.我相信某处存在编码不匹配.我相信它在 ODBC 层和 python 之间,因为我能够在 tsql 和 isql 中获得可读的结果.
I can not get a valid string from an MSSQL server into python. I believe there is an encoding mismatch somewhere. I believe it is between the ODBC layer and python because I am able to get readable results in tsql and isql.
pyodbc 期望什么字符编码?我需要在链中进行哪些更改才能使其正常工作?
What character encoding does pyodbc expect? What do I need to change in the chain to get this to work?
具体例子
这里以一个简化的python脚本为例:
Here is a simplified python script as an example:
#!/usr/bin/env python
import pyodbc
dsn = 'yourdb'
user = 'import'
password = 'get0lddata'
database = 'YourDb'
def get_cursor():
con_string = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (dsn, user, password, database)
conn = pyodbc.connect(con_string)
return conn.cursor()
if __name__ == '__main__':
c = get_cursor()
c.execute("select id, name from recipe where id = 4140567")
row = c.fetchone()
if row:
print row
这个脚本的输出是:
(Decimal('4140567'), u'\U0072006f\U006e0061\U00650067')
或者,如果脚本的最后一行改为:
Alternatively, if the last line of the script is changed to:
print "{0}, '{1}'".format(row.id, row.name)
那么结果是:
Traceback (most recent call last):
File "/home/mdenson/projects/test.py", line 20, in <module>
print "{0}, '{1}'".format(row.id, row.name)
UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-2: ordinal not in range(128)
使用 tsql 执行相同查询的脚本:
A transcript using tsql to execute the same query:
root@luke:~# tsql -S cmw -U import -P get0lddata
locale is "C"
locale charset is "ANSI_X3.4-1968"
using default charset "UTF-8"
1> select id, name from recipe where id = 4140567
2> go
id name
4140567 orange2
(1 row affected)
以及isql:
root@luke:~# isql -v yourdb import get0lddata
SQL> select id, name from recipe where id = 4140567
+----------------------+--------------------------+
| id | name |
+----------------------+--------------------------+
| 4140567 | orange2 |
+----------------------+--------------------------+
SQLRowCount returns 1
1 rows fetched
所以我早上一直在研究这个,看起来高高低低,还没有弄清楚哪里出了问题.
So I have worked at this for the morning and looked high and low and haven't figured out what is amiss.
详情
以下是版本详情:
- 客户端是 Ubuntu 12.04
- freetds v0.91
- unixodbc 2.2.14
- python 2.7.3
pyodbc 2.1.7-1(来自 ubuntu 包)&3.0.7-beta06(从源代码编译)
- Client is Ubuntu 12.04
- freetds v0.91
- unixodbc 2.2.14
- python 2.7.3
pyodbc 2.1.7-1 (from ubuntu package) & 3.0.7-beta06 (compiled from source)
服务器是 XP 和 SQL Server Express 2008 R2
Server is XP with SQL Server Express 2008 R2
这里是客户端的几个配置文件的内容.
Here are the contents of a few configuration files on the client.
/etc/freetds/freetds.conf
/etc/freetds/freetds.conf
[global]
tds version = 8.0
text size = 64512
[cmw]
host = 192.168.90.104
port = 1433
tds version = 8.0
client charset = UTF-8
/etc/odbcinst.ini
/etc/odbcinst.ini
[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1
/etc/odbc.ini
/etc/odbc.ini
[yourdb]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = cmw
Database = YourDB
Charset = UTF-8
所以在继续工作之后,我现在正在将 unicode 字符导入 python.不幸的是,我偶然发现的解决方案就像亲吻你的堂兄一样令人满意.
So after continued work I am now getting unicode characters into python. Unfortunately the solution I've stumbled upon is about as satisfying as kissing your cousin.
我通过安装python3和python3-dev包,然后用python3重建pyodbc解决了这个问题.
I solved the problem by installing the python3 and python3-dev packages and then rebuilding pyodbc with python3.
现在我已经完成了我的脚本,即使我仍然使用 python 2.7 运行它们.
Now that I've done this my scripts now work even though I am still running them with python 2.7.
所以我不知道这样做修复了什么问题,但它现在有效,我可以继续我开始的项目.
So I don't know what was fixed by doing this, but it now works and I can move on to the project I started with.