django-pyodbc 及 freetds 查询 mssql 中文视图及表时报错问题求教,系统是 centos7

2018-01-23 22:27:28 +08:00
 qile1

如题,环境是 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)")
2189 次点击
所在节点    问与答
3 条回复
PureWhite
2018-01-24 00:37:38 +08:00
大哥,这个已经很明显了好吧。。。中文出错基本上就是字符编码问题,你看下面的 column name,都是乱码,一看就知道是编码问题啊。。。
具体如何解决就 Google 吧。
/etc/odbcinst.ini 和 /etc/freetds.conf 这两个应该都要同步改吧?
还有,在 sqlserver 里面查询一下表的编码是什么,应该能解。
具体怎么查询 Google。
qile1
2018-01-24 14:39:33 +08:00
@PureWhite 非常感谢回复,是编码问题,但是在哪里改才可以支持?我谷歌一圈没有找到,数据库是中午编码,兰亭字符集 lant ……,具体编码我一会查下,问题应该在 freetds 什么的编码,用 GB2312 及 UTF8 都乱码,已经找原因一个星期,查出数据中文显示正确,mssql 数据库服务器是 win10,数据库服务器安装 pymssql,可以查询,centos7 连接就不能!是否无解?
PureWhite
2018-01-24 15:31:56 +08:00
@qile1 不是无解,字符编码虽然是一个比较麻烦的问题,但是也是很容易解决的。
你自己都说了,数据库是中文编码,字符集是什么 兰亭(没听说过这个,我猜应该是 latin 吧。。。)?
你要么就修改数据库的编码为 utf-8,要么就把配置里面的编码全部改成和数据库一样。
包括记得 Django 的编码也得改,你在 python 里面调用的时候记得要 decode 或者 encode,根据你解决方案的不同。

其实要做的事非常简单,就是把数据库,client 和你的 Django 的应用编码设置成一致就可以了。

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/425381

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX