django-pyodbc 及 freetds 查询 mssql 中文视图及表时报错问题求教，系统是 centos7
qile1 · 2018-01-23 22:27:28 +08:00 · 2181 次点击这是一个创建于 2501 天前的主题，其中的信息可能已经有所发展或是发生改变。
如题，环境是 python 3.6 + django 1.11 + sqlserver 2008 安装服务器 centos7 +nginx 查询英文表，英文 sql 语句正常，列是中文也可以正常显示，查询中文视图报错如执行 select [名字] from [报告单视图]会报错
python3 manage.py shell
from django.db import connections
conn = connections['mssqldb']
cur = conn.cursor()
cur.execute('SELECT TOP 1 name FROM vw_report')
<pyodbc.Cursor object at 0xac1b528>
cur.fetchall()
cur.execute("SELECT top 1 [姓名] FROM [接口视图_报告结果] where [姓名]='郎秀兰'") #报错
cur.fetchall()
/etc/odbc.ini 修改内容为这样
[ODBC Data Sources]
ODBCNAME = Microsoft SQL Server
[MSSQL-PYTHON]
Driver = /usr/lib64/libtdsodbc.so
Description = Hi! This is a description of the MSSQL-PYTHON driver
Trace = No
#Database = cxonline
Server = 192.168.123.93
Port = 1433
Setup = /usr/lib64/libtdsS.so
FileUsage = 1
client charset = UTF-8
/etc/odbcinst.ini 修改内容为这样
[FreeTDS]
Description = FreeTDS
Driver = /usr/lib64/libtdsodbc.so
[ODBC Data Sources]
ODBCNAME = Microsoft SQL Server
[MSSQL-PYTHON]
Driver = /usr/lib64/libtdsodbc.so
Description = Hi! This is a description of the MSSQL-PYTHON driver
Trace = No
#Database = cxonline
Server = 192.168.123.93
Port = 1433
Setup = /usr/lib64/libtdsS.so
FileUsage = 1
client charset = UTF-8
/etc/freetds.conf 此文件修改编码 client charset = GB2312 无效果，改 utf8 也试过不对！
[192.168.1.108]
host = 192.168.123.93
port = 1433
tds version = 8.0
client charset = GB2312
[ODBC Data Sources]
ODBCNAME = Microsoft SQL Server
[MSSQL-PYTHON]
Driver = /usr/lib64/libtdsodbc.so
Description = Hi! This is a description of the MSSQL-PYTHON driver
Trace = No
#Database = cxonline
Server = 192.168.123.93
Port = 1433
django settings.py 配置如下
'mssqldb': {
'ENGINE': 'sqlserver',
'NAME': 'data1',
'HOST': '127.0.0.1',
'PORT': '1433',
'USER': 'sa',
'PASSWORD': '*********',
'OPTIONS': {
'DRIVER': 'SQL Server Native Client 10.0',
},
其他配置
LANGUAGE_CODE = 'zh-Hans'
TIME_ZONE = 'Asia/Shanghai'
报如下错误
conn = connections['mssqldb'] cur = conn.cursor() cur.execute('SELECT TOP 1 reportid FROM vw_report') <pyodbc.Cursor object="" at="" 0x7fdbfb2c25d0=""> cur.fetchall() [(3.0, )] cur.execute("SELECT top 1 [姓名] FROM [接口视图_1] where [姓名]='郎秀兰'") Traceback (most recent call last): File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 63, in execute return self.cursor.execute(sql) File "/usr/local/lib/python3.6/site-packages/sql_server/pyodbc/base.py", line 545, in execute return self.cursor.execute(sql, params) pyodbc.ProgrammingError: ('42000', "[42000] [FreeTDS][SQL Server]Incorrect syntax near '='. (102) (SQLExecDirectW)")
The above exception was the direct cause of the following exception:
Traceback (most recent call last): File "<console>", line 1, in <module> File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 80, in execute return super(CursorDebugWrapper, self).execute(sql, params) File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 65, in execute return self.cursor.execute(sql, params) File "/usr/local/lib/python3.6/site-packages/django/db/utils.py", line 94, in exit six.reraise(dj_exc_type, dj_exc_value, traceback) File "/usr/local/lib/python3.6/site-packages/django/utils/six.py", line 685, in reraise raise value.with_traceback(tb) File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 63, in execute return self.cursor.execute(sql) File "/usr/local/lib/python3.6/site-packages/sql_server/pyodbc/base.py", line 545, in execute return self.cursor.execute(sql, params) django.db.utils.ProgrammingError: ('42000', "[42000] [FreeTDS][SQL Server]Incorrect syntax near '='. (102) (SQLExecDirectW)")
django 访问引用数据报如下错误
ProgrammingError at /Vresult/
('42S22', "[42S22] [FreeTDS][SQL Server]Invalid column name '鎶ュ憡鍗曞彿'. (207) (SQLExecDirectW)")
 |
1
PureWhite 2018-01-24 00:37:38 +08:00
大哥，这个已经很明显了好吧。。。中文出错基本上就是字符编码问题，你看下面的 column name，都是乱码，一看就知道是编码问题啊。。。
具体如何解决就 Google 吧。 /etc/odbcinst.ini 和 /etc/freetds.conf 这两个应该都要同步改吧？ 还有，在 sqlserver 里面查询一下表的编码是什么，应该能解。 具体怎么查询 Google。 |
Select Language