All geek questions in one place

SQLAlchemy Query does not return an entire large text field

First, a little about my environment:

  • Using MSSMS as a database management tool.
  • Using Django 1.3
  • Using SQLAlchemy

I have a text field in a database with no maximum length. It has text that is 890591 characters long.

When I retrieve this field using SQLAlchemy , it is truncated to 64512 characters. I also tried it with a few other big lines, and it always truncated to 64512.

SELECT @@TEXTSIZE returns some kind of absurd value, for example, 1.6 million, so this is not a problem. If I do SELECT DATALENGTH(field) , it returns the correct 890591. So it doesn’t look like a database, it seems to be SQLAlchemy . Or maybe it could be some Python limit.

Any ideas? I seem to be on my way.

EDIT: More info was requested here:

OS: Debian 5.0.9

SQLAlchemy: 0.7.3

SQL: MS Sql Server 2008

Database connection: mssql + pymssql: // name: password @ server / dbname

pymssql version: 1.0.2

The model in question:

 class RACReport(Base): __tablename__ = 'RACReport' id = Column(properUUID(), primary_key=True, nullable=False, default=genuuid4, server_default=text('NEWID()')) client_id = Column(properUUID(), ForeignKey(Client.id), nullable=False) rawdata = Column(Text(), nullable=True) rawtime = Column(DateTime(), nullable=True, default=datetime.datetime.now()) processeddata = Column(Text(), nullable=True) processedtime = Column(DateTime(), nullable=True) reportstartdate = Column(DateTime(), nullable=False) reportenddata = Column(DateTime(), nullable=False) numberofdocs = Column(Integer(), nullable=True) RACReport.__table__.schema='rac' class properUUID(st.TypeDecorator): impl = mssql.MSUniqueIdentifier def process_result_value(self, value, dialect): if value: return str(uuid.UUID(bytes_le=value)) def genuuid4(): return str(uuid.uuid4())

rawdata and processdata are the two fields that it encounters.

Here's a test request and an echo:

rac.session.query(rac.RACReport).filter(rac.RACReport.id=='8fb76cb7-d752-45af-a20a-3b85d5e7b8a6').all()

2011-11-17 09:39:46,890 INFO sqlalchemy.engine.base.Engine SELECT [RACReport_1].id AS [rac_RACReport_id], [RACReport_1].client_id AS [rac_RACReport_client_id], [RACReport_1].rawdata AS [rac_RACReport_rawdata], [RACReport_1].rawtime AS [rac_RACReport_rawtime], [RACReport_1].processeddata AS [rac_RACReport_processeddata], [RACReport_1].processedtime AS [rac_RACReport_processedtime], [RACReport_1].reportstartdate AS [rac_RACReport_reportstartdate], [RACReport_1].reportenddate AS [rac_RACReport_reportenddate] FROM rac.[RACReport] AS [RACReport_1] WHERE [RACReport_1].id = %(id_1)s 2011-11-17 09:39:46,890 INFO sqlalchemy.engine.base.Engine {'id_1': '8fb76cb7-d752-45af-a20a-3b85d5e7b8a6'}

+4
source share
1 answer

I know little about * nix connecting to SQL Server, but a simple Google search suggests that the problem is with the FreeTDS configuration:

My text data is truncated or causes my client to break.

The text data type is different from the char and varchar types. the maximum length of the text column data is determined by the connection text. Microsoft claims in its documentation to use a default text of 4000 characters, but in reality their implementation is inconsistent. Sometimes text columns are returned with a size of 4 GB!

The best solution is to make sure that you set the textize parameter to a reasonable value when establishing a connection. For instance:

 1> set textsize 10000 2> go

See also text size option in freetds.conf.

And just a note: you seem to be using a rather outdated version of pymssql .

+4
source

Source: https://habr.com/ru/post/1381596/

More articles:


All Articles