CODEWITHSUNDEEP
sql-serverpython-3.xsqlalchemyanaconda
David Ledger
David Ledger

Reputation: 13

SQLAlchemy Truncating Strings On Import From MS SQL

First off this is my setup:

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!

Upvotes: 1

Views: 2043

Answers (2)

johnDanger
johnDanger

Reputation: 2333

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.

Upvotes: 0

guest_56
guest_56

Reputation: 11

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).

Upvotes: 1

Related Questions