SQLAlchemy Truncating Strings On Import From MS SQL [duplicate]

Question

First off this is my setup:

Windows 7
MS SQL Server 2008
Python 3.6 Anaconda Distribution

I am working in a Jupyter notebook and trying to import a column of data from a MS SQL Server database using SQLAlchemy. The column in question contains cells which store long strings of text (datatype is nvarchar(max)). This is my code:

engine = create_engine('mssql+pyodbc://user:password@server:port/db_name?driver=SQL+Server+Native+Client+11.0'
stmt = 'SELECT componenttext FROM TranscriptComponent WHERE transcriptId=1265293'

connection = engine.connect() results = connection.execute(stmt).fetchall()

This executes fine, and imports a list of strings. However when I examine the strings they are truncated, and in the middle of the strings the following message seems to have been inserted:

... (8326 characters truncated) ...

With the number of characters varying from string to string. I did a check on how long the strings that got imported are, and the ones that have been truncated are all limited at either 339 or 340 characters.

Is this a limitation in SQLAlchemy, Python or something else entirely?

Any help appreciated!

guest_56 · Accepted Answer · 2018-01-31 10:14:03Z

1

Same problem here!

Set up :

Windows Server 2012 MS SQL Server 2016/PostgreSQL 10.1 Python 3.6 Anaconda Distribution I've tested everything I could, but can't overpass this 33x limitation in field length. Either varchar/text seems to be affected and the DBMS/driver doesn't seem to have any influence.

EDIT:

Found the source of the "problem": https://bitbucket.org/zzzeek/sqlalchemy/issues/2837

Seems like fetchall() is affected by this feature.

The only workaround i found was :

empty_list=[]
connection = engine.connect()
results = connection.execute(stmt)
for row in results:
    empty_list.append(row['componenttext'])

This way i haven't found any truncation in my long string field(>3000 ch).

answered Jan 31, 2018 at 10:14

guest_56

112 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Ledger Over a year ago

Thanks, the workaround works well! It does appear that the behavior of fetchall() is indeed truncate large scalar values and insert the "N characters truncated" notation, and this is by design according to the discussion on the page you provided.

johnDanger · Accepted Answer · 2024-01-18 04:01:37Z

0

This was irreparably occurring for me when I used sqlalchemy to execute:

SELECT OBJECT_DEFINITION(OBJECT_ID('my_view')) AS ViewDefinition

But getting the view definition a different way instead worked:

EXEC sp_helptext 'my_view'

As this seems to return the text by line instead of one string.

answered Jan 18, 2024 at 4:01

johnDanger

2,37920 silver badges25 bronze badges

Collectives™ on Stack Overflow

SQLAlchemy Truncating Strings On Import From MS SQL [duplicate]

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related