如何使用Python pypyodbc获取整个VARCHAR(MAX)列 [英] How to get entire VARCHAR(MAX) column with Python pypyodbc
问题描述
我有一个使用ODBC连接连接到MSSQL数据库的Python程序.我正在使用的Python库是pypyodbc.
I have a Python program that connects to an MSSQL database using an ODBC connection. The Python library I'm using is pypyodbc.
这是我的设置:
- Windows 8.1 x64
- SQL Server 2014 x64
- Python 2.7.9150
- PyPyODBC 1.3.3
- ODBC驱动程序:SQL Server本机客户端11.0
我遇到的问题是，当我查询带有varchar(max)列的表时，内容被截断了.
The problem I'm having is that when I query a table with a varchar(max) column, the content is being truncated.
我是pypyodbc的新手，我一直在疯狂搜索，无法找到任何方法来防止这种情况在pypyodbc甚至pyodbc中发生.至少与我一直使用的搜索词不同，我不知道可以尝试其他什么短语.
I'm new to pypyodbc and I've been searching around like crazy and can't find anything on how to prevent this from happening in pypyodbc or even pyodbc. At least not with the search terms I've been using and I don't know what other phrases to try.
我什至尝试将SET TEXTSIZE 2147483647;添加到我的SQL查询中，但是数据仍被截断.
I even tried adding SET TEXTSIZE 2147483647; to my SQL query, but the data is still being truncated.
如何防止这种情况发生?还是可以给我指出正确的方向?
How do I prevent this from happening? Or can you point me in the right direction, please?
更新:
因此，我尝试在SQL查询中执行强制类型转换.当我执行CAST(my_column as VARCHAR(MAX))时，它会在同一位置截断.但是，如果我执行CAST(my_column as VARCHAR(8000))，它将为我提供更大的文本集，但仍会截断某些内容.如果我尝试做的事情比8000大，我会收到一条错误消息，说8000是我可以使用的最大的东西.有人知道这里会发生什么吗?使用MAX无法正常工作似乎很奇怪.
So, I tried performing a cast in my SQL query. When I do CAST(my_column as VARCHAR(MAX)) it truncates at the same position. However, if I do CAST(my_column as VARCHAR(8000)) it gives me a larger set of the text, but it's still truncating some of the contents. If I try to do anything larger than 8000 I get an error saying that 8000 is the largest I can use. Anyone know what might be going on here? It seem strange that using MAX won't work.
推荐答案
好，我最终解决了问题.我发现此链接与一个类似的问题有关，只是不在python中，并且他们发现该问题与SQL Server本机客户端驱动程序有关.他们建议改为使用SQL Server标准驱动程序.
Well, I ended up getting the problem resolved. I found this link regarding a similar problem, just not in python, and they found that the problem was with the SQL Server native client driver. They recommended using the SQL Server standard driver instead.
因此，我将ODBC连接字符串中的驱动程序从SQL Server Native Client 11.0更改为SQL Server，它运行良好！我正在MSSQL数据表中获取VARCHAR(MAX)列的全部内容.
So I changed my driver in my ODBC connection string from SQL Server Native Client 11.0 to SQL Server and it's working perfectly! I'm getting the entire contents of the VARCHAR(MAX) column in my MSSQL data table.
我真的希望这对遇到此问题的任何人都有用！祝你好运！
I really hope this proves to be useful for anyone else who encounters this issue! Good luck!
这里是链接: http://www.sqlservercentral.com/Forums/Topic1534163-391-1.aspx
这篇关于如何使用Python pypyodbc获取整个VARCHAR(MAX)列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
