具有MS SQL的Python-截断的输出 [英] Python with MS SQL - truncated output
问题描述
我尝试从Linux框中( Python 2.7 , Ubuntu 11.04 )用python连接到MSSQL DB.我收到的输出被截断为500个字符.请参阅下面的脚本和配置.如何解决?我想在ODBC驱动程序中或附近的问题.
I try to connect to MSSQL DB with python from Linux box (Python 2.7, Ubuntu 11.04). Output that I receive is truncated to 500 characters. Please, see script and configs below. How it could be resolved? The problem I suppose in ODBC driver or near it.
代码(pyodbc,pymssql):
The code (pyodbc, pymssql):
conn = pymssql.connect(host='my_remote_host', user='ro_user',
password='password', database='current', as_dict=True)
cur = conn.cursor()
cur.execute(sql)
for i in cur:
print i
conn.close()
cnxn = pyodbc.connect(driver='FreeTDS', server='my_remote_host', database='current', uid='ro_user', pwd='password')
cursor = cnxn.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
...
cnxn.close()
我没有对MS SQL DB的写访问权,它实际上是不属于我们系统的远程服务器.
I have no write access to MS SQL DB, it's actually remote server that doesn't belong to our system.
sql = '''
SELECT Req.ID,
ShReq.Summary AS [Short Name],
ShReq.ALM_SharedText AS [Text],
Req.ContainedBy,
Req.DocumentID
FROM CurMKS..ALM_Requirement Req
JOIN CurMKS..ALM_SharedRequirement ShReq ON Req.[References] = ShReq.ID
WHERE DocumentID = 1111111'''
问题出在ShReq.ALM_SharedText
字段上.它被截断为255个字符,但是使用类似convert(text,ShReq.ALM_SharedText) AS TEXT
和CAST(ShReq.ALM_SharedText AS TEXT)
的转换,我将截断增加为500个字符.但是,有些字段的文本长度超过500个字符,并且会被截断.
The problem is with ShReq.ALM_SharedText
field. It's truncated to 255 chars, but using conversions like convert(text,ShReq.ALM_SharedText) AS TEXT
and CAST(ShReq.ALM_SharedText AS TEXT)
I increase truncating to 500 chars. However there are fields with longer text than 500 chars and they are truncated.
/etc/odbc.ini
:
[MKS]
#Driver=FreeTDS
Driver=/usr/lib/odbc/libtdsodbc.so
Description=Database
Trace=No
Server=my_remote_host
Port=1433
Database=current
UID=ro_user
PWD=password
TDS Version=8.0
/etc/odbcinst.ini
:
[FreeTDS]
Description=FreeTDS
Driver=/usr/lib/odbc/libtdsodbc.so
UsageCount=1
/etc/freetds/freetds.conf
:
[global]
tds version = 8.0
; dump file = /tmp/freetds.log
; debug flags = 0xffff
; timeout = 10
; connect timeout = 10
; text size = 2097152
[mksserver]
host = my_remote_host
port = 1433
tds version = 8.0
client charset = UTF-8
有人在想如何解决?
推荐答案
将freetds.conf
的global
部分中的text size
更改为最大(4294967295字节):
Change the text size
in global
section of freetds.conf
to the maximum (4294967295 bytes):
[global]
tds version = 8.0
text size = 4294967295
还必须将SQL中的TEXTSIZE
设置为最大(2147483647字节):
Also have to set TEXTSIZE
in SQL to maximum (2147483647 bytes):
sql = """
SET TEXTSIZE 2147483647;
SELECT Req.ID,
ShReq.Summary AS [Short Name],
ShReq.ALM_SharedText AS [Text],
Req.ContainedBy,
Req.DocumentID
FROM CurMKS..ALM_Requirement Req
JOIN CurMKS..ALM_SharedRequirement ShReq ON Req.[References] = ShReq.ID
WHERE DocumentID = 111111;
"""
这篇关于具有MS SQL的Python-截断的输出的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!