PYODBC truncates the response of a SQL Server FOR JSON query

Question

I'm having issues trying to get a SQL Server JSON result set to play nicely with PYODBC. My Python/Flask skills aren't the best, so I'm not entirely sure if this is something stupid I'm doing, or that the driver doesn't work with JSON.

The failing process is calling a Stored Procedure that returns the results of a SELECT statement using FOR JSON PATH. The procedure itself is fine and the result set looks correct in SSMS.

However, within the following block of code, the line that assigns a value to search_results is throwing an error.

with DB() as cnxn:
    results = cnxn.query('dbo.getLocationByTrain', params)
    search_results = json.loads(results.fetchone()[0]);

The error is:

Traceback (most recent call last):
  File "C:\Users\Mark\source\repos\CommuteTo\CommuteTo\env\lib\site-packages\flask\app.py", line 1982, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\Users\Mark\source\repos\CommuteTo\CommuteTo\env\lib\site-packages\flask\app.py", line 1614, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "C:\Users\Mark\source\repos\CommuteTo\CommuteTo\env\lib\site-packages\flask\app.py", line 1517, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "C:\Users\Mark\source\repos\CommuteTo\CommuteTo\env\lib\site-packages\flask\_compat.py", line 33, in reraise
    raise value
  File "C:\Users\Mark\source\repos\CommuteTo\CommuteTo\env\lib\site-packages\flask\app.py", line 1612, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:\Users\Mark\source\repos\CommuteTo\CommuteTo\env\lib\site-packages\flask\app.py", line 1598, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "C:\Users\Mark\source\repos\CommuteTo\CommuteTo\CommuteTo\views.py", line 81, in search
    search_results = json.loads(results.fetchone()[0]);
  File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python36_64\lib\json\__init__.py", line 354, in loads
    return _default_decoder.decode(s)
  File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python36_64\lib\json\decoder.py", line 339, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python36_64\lib\json\decoder.py", line 355, in raw_decode
    obj, end = self.scan_once(s, idx)
json.decoder.JSONDecodeError: Unterminated string starting at: line 1 column 2028 (char 2027)

If I remove the json.loads and instead jsonify the result, the error obviously goes away, but I can see the result set returned to Flask is incomplete:

with DB() as cnxn:
    results = cnxn.query('dbo.getLocationByTrain', params)
    search_results = results.fetchone()[0];
return jsonify(search_results)

Result in Flask:

"[{\"Name\":\"ABBEY WOOD\",\"Latitude\":\"51.490772\",\"Longitude\":\"0.120326\",\"LatestDepartureTime\":\"08:18:00\",\"MinJourneyTime\":14,\"MaxJourneyTime\":17,\"schools\":[{\"Name\":\"Abbey Wood Nursery School\",\"Address1\":\"Dahlia Road\",\"PostCode\":\"SE2 0SX\",\"OverallEffectiveNess\":2},{\"Name\":\"Bannockburn Primary School\",\"Address1\":\"Plumstead High Street\",\"PostCode\":\"SE18 1HE\",\"OverallEffectiveNess\":1},{\"Name\":\"Gallions Mount Primary School\",\"Address1\":\"Purrett Road\",\"PostCode\":\"SE18 1JR\",\"OverallEffectiveNess\":2},{\"Name\":\"Boxgrove Primary School\",\"Address1\":\"Boxgrove Road\",\"PostCode\":\"SE2 9JP\",\"OverallEffectiveNess\":2},{\"Name\":\"De Lucy Primary School\",\"Address1\":\"Cookhill Road\",\"PostCode\":\"SE2 9PD\",\"OverallEffectiveNess\":2},{\"Name\":\"Heronsgate Primary School\",\"Address1\":\"Whinchat Road\",\"PostCode\":\"SE28 0EA\",\"OverallEffectiveNess\":1},{\"Name\":\"Linton Mead Primary School\",\"Address1\":\"Central Way\",\"PostCode\":\"SE28 8DT\",\"OverallEffectiveNess\":2},{\"Name\":\"Greenslade Primary School\",\"Address1\":\"Erindale\",\"PostCode\":\"SE18 2QQ\",\"OverallEffectiveNess\":2},{\"Name\":\"St Patrick's Catholic Primary School\",\"Address1\":\"Griffin Road\",\"PostCode\":\"SE18 7QG\",\"OverallEffectiveNess\":2},{\"Name\":\"St Thomas A Becket Roman Catholic Primary School\",\"Address1\":\"Mottisfont Road\",\"PostCode\":\"SE2 9LY\",\"OverallEffectiveNess\":2},{\"Name\":\"St Margaret Clitherow Catholic Primary School\",\"Address1\":\"Cole Close\",\"PostCode\":\"SE28 8GB\",\"OverallEffectiveNess\":2},{\"Name\":\"Bishop John Robinson Church of England Primary School\",\"Address1\":\"Hoveton Road\",\"PostCode\":\"SE28 8LW\",\"OverallEffectiveNess\":2},{\"Name\":\"Plumstead Manor School\",\"Address1\":\"Old Mill Road\",\"PostCode\":\"SE18 1QF\",\"OverallEffectiveNess\":3},{\"Name\":\"Hawksmoor School\",\"Address1\":\"Bentham Road\",\"PostCode\":\"SE28 8AS\",\"OverallEffectiveNess\":1},{\"Name\":\"Belmont Primary School\",\"Address1\":\"Belmont Road\",\"PostCode\":\"DA8 1LE\",\"OverallEffectiveNess\":2},{\"Name\":\"Parkway Primary School\",\"Address1\":\"Alsike Road\",\"PostCode\":\"DA18 4DP\",\"OverallEffectiveNess\":2},{\"Name\":\"Belvedere Infant School\",\"Address1\":\"Mitchell"

SQL Server Result:

[{"Name":"ABBEY WOOD","Latitude":"51.490772","Longitude":"0.120326","LatestDepartureTime":"08:18:00","MinJourneyTime":14,"MaxJourneyTime":17,"schools":[{"Name":"Abbey Wood Nursery School","Address1":"Dahlia Road","PostCode":"SE2 0SX","OverallEffectiveNess":2},{"Name":"Bannockburn Primary School","Address1":"Plumstead High Street","PostCode":"SE18 1HE","OverallEffectiveNess":1},{"Name":"Gallions Mount Primary School","Address1":"Purrett Road","PostCode":"SE18 1JR","OverallEffectiveNess":2},{"Name":"Boxgrove Primary School","Address1":"Boxgrove Road","PostCode":"SE2 9JP","OverallEffectiveNess":2},{"Name":"De Lucy Primary School","Address1":"Cookhill Road","PostCode":"SE2 9PD","OverallEffectiveNess":2},{"Name":"Heronsgate Primary School","Address1":"Whinchat Road","PostCode":"SE28 0EA","OverallEffectiveNess":1},{"Name":"Linton Mead Primary School","Address1":"Central Way","PostCode":"SE28 8DT","OverallEffectiveNess":2},{"Name":"Greenslade Primary School","Address1":"Erindale","PostCode":"SE18 2QQ","OverallEffectiveNess":2},{"Name":"St Patrick's Catholic Primary School","Address1":"Griffin Road","PostCode":"SE18 7QG","OverallEffectiveNess":2},{"Name":"St Thomas A Becket Roman Catholic Primary School","Address1":"Mottisfont Road","PostCode":"SE2 9LY","OverallEffectiveNess":2},{"Name":"St Margaret Clitherow Catholic Primary School","Address1":"Cole Close","PostCode":"SE28 8GB","OverallEffectiveNess":2},{"Name":"Bishop John Robinson Church of England Primary School","Address1":"Hoveton Road","PostCode":"SE28 8LW","OverallEffectiveNess":2},{"Name":"Plumstead Manor School","Address1":"Old Mill Road","PostCode":"SE18 1QF","OverallEffectiveNess":3},{"Name":"Hawksmoor School","Address1":"Bentham Road","PostCode":"SE28 8AS","OverallEffectiveNess":1},{"Name":"Belmont Primary School","Address1":"Belmont Road","PostCode":"DA8 1LE","OverallEffectiveNess":2},{"Name":"Parkway Primary School","Address1":"Alsike Road","PostCode":"DA18 4DP","OverallEffectiveNess":2},{"Name":"Belvedere Infant School","Address1":"Mitchell Close","PostCode":"DA17 6AA","OverallEffectiveNess":2},{"Name":"Castilion Primary School","Address1":"Copperfield Road","PostCode":"SE28 8QA","OverallEffectiveNess":1},{"Name":"St Thomas More Catholic Primary School","Address1":"Sheldon Road","PostCode":"DA7 4PH","OverallEffectiveNess":1},{"Name":"St John Fisher Catholic Primary School","Address1":"Kale Road","PostCode":"DA18 4BA","OverallEffectiveNess":2},{"Name":"St Paul's Academy","Address1":"Finchale Road","PostCode":"SE2 9PX","OverallEffectiveNess":2},{"Name":"Discovery Primary School","Address1":"Battery Road","PostCode":"SE28 0JN","OverallEffectiveNess":2},{"Name":"Alexander McLeod Primary School","Address1":"Fuchsia Street","PostCode":"SE2 0QS","OverallEffectiveNess":2},{"Name":"Conway Primary School","Address1":"Gallosson Road","PostCode":"SE18 1QY","OverallEffectiveNess":2},{"Name":"Waterside School","Address1":"Robert Street","PostCode":"SE18 7NB","OverallEffectiveNess":2},{"Name":"Trinity Church of England School, Belvedere","Address1":"Erith Road","PostCode":"DA17 6HT","OverallEffectiveNess":2},{"Name":"East Wickham Primary Academy","Address1":"Wickham Street","PostCode":"DA16 3BP","OverallEffectiveNess":2},{"Name":"Welling School","Address1":"Elsa Road","PostCode":"DA16 1LB","OverallEffectiveNess":2},{"Name":"Willow Bank Primary School","Address1":"Seacourt Road","PostCode":"SE2 9XB","OverallEffectiveNess":2},{"Name":"Belvedere Junior School","Address1":"Mitchell Close","PostCode":"DA17 6AA","OverallEffectiveNess":2},{"Name":"St Augustine of Canterbury CofE Primary School","Address1":"St Augustine's Road","PostCode":"DA17 5HP","OverallEffectiveNess":2},{"Name":"Brampton Primary Academy","Address1":"Brampton Road","PostCode":"DA7 4SL","OverallEffectiveNess":4},{"Name":"Woolwich Polytechnic School","Address1":"Hutchins Road","PostCode":"SE28 8AT","OverallEffectiveNess":1},{"Name":"Hillsgrove Primary School","Address1":"Sidmouth Road","PostCode":"DA16 1DR","OverallEffectiveNess":3},{"Name":"Windrush Primary School","Address1":"2 Bentham Road","PostCode":"SE28 8AR","OverallEffectiveNess":1},{"Name":"Timbercroft Primary School","Address1":"Timbercroft Lane","PostCode":"SE18 2SG","OverallEffectiveNess":2},{"Name":"Rockliffe Manor Primary School","Address1":"Bassant Road","PostCode":"SE18 2NP","OverallEffectiveNess":2},{"Name":"Willow Dene School","Address1":"Swingate Lane","PostCode":"SE18 2JD","OverallEffectiveNess":2},{"Name":"South Rise Primary School","Address1":"Brewery Road","PostCode":"SE18 7PX","OverallEffectiveNess":2},{"Name":"Bedonwell Infant and Nursery School","Address1":"Bedonwell Road","PostCode":"DA17 5PF","OverallEffectiveNess":1},{"Name":"Bedonwell Junior School","Address1":"Bedonwell Road","PostCode":"DA17 5PF","OverallEffectiveNess":2},{"Name":"Pathways Short Stay School","Address1":"Pathways Short Stay School","PostCode":"SE2 9TA","OverallEffectiveNess":2},{"Name":"Northwood Primary School","Address1":"Northwood Place","PostCode":"DA18 4HN","OverallEffectiveNess":2},{"Name":"St Michael's East Wickham Church of England Voluntary Aided Primary School","Address1":"Wrotham Road","PostCode":"DA16 1LS","OverallEffectiveNess":1},{"Name":"Jubilee Primary School","Address1":"Crowden Way","PostCode":"SE28 8JB","OverallEffectiveNess":3}]}]

Somewhere it would appear as though PYODBC is truncating to around 2050 characters?

I would guess I'm not the first person to try to use FOR JSON and PYODBC, so what am I doing wrong?

My connection string (if it's important):

cnxn = r'Driver={SQL Server};Server=MARKSLAPTOP\MSSQLSERVER17;Database=MyDBName;Trusted_Connection=yes;'

And cnxn.query just calls cursor.execute(sql, params).

Edit The issue sort of sounds similar to this query, but I’m already using the standard SQL Server driver...

Hmmm, that's awfully close to terminating at 2048 bytes - I count 2035 characters. Can you think of anywhere in the stack that might restrict text-like fields to be 2k? — FlipperPA, Mar 24 '18 at 21:38
Is that a limitation somewhere within pyodbc? I can’t seem to find anything documenting this. There’s nothing fancy I’m doing, so it sounds driver related — Mark Sinkinson, Mar 24 '18 at 22:43
@FlipperPA added an edit with a possibly related issue. Although doesn’t quite make sense. — Mark Sinkinson, Mar 24 '18 at 22:46
Yeah, 2k seems like a really small number. I’ve never used SQL Server’s JSON functions. Could you modify the stored procedure to return the JSON as text instead, by CASTing the result to VARCHAR(8000) as a test? — FlipperPA, Mar 24 '18 at 23:02
I do not know anything about Python and pyodbc, but I assume your stored procedure returns the JSON as an output parameter (well, it has to). So what do you parameter declaration look like in your Python code (I see there is a parameter called `params` in your code)? — Niels Berglund, Mar 25 '18 at 03:26
@NielsBerglund the JSON is the result set from a SELECT statement, so technically doesn’t have to be assigned to an output parameter as pyodbc can interpret the result set (or its supposed to!). I’ll give that a go though! — Mark Sinkinson, Mar 25 '18 at 09:00
@MarkSinkinson - The [issue you cited in your edit](https://stackoverflow.com/questions/33878291/how-to-get-entire-varcharmax-column-with-python-pypyodbc) was a problem with pypyodbc, not pyodbc. `DRIVER=SQL Server` is very old and only supports SQL Server 2000 features (TDS protocol version 7.1), so you actually might have better luck using a more recent version of the SQL Server ODBC driver for Windows. — Gord Thompson, Mar 25 '18 at 12:08
@GordThompson @FlipperPA I upgraded the driver, but also required the `CAST`ing of the result to a `VARCHAR(MAX)`, otherwise I was only getting a truncated JSON object. — Mark Sinkinson, Mar 25 '18 at 13:01

Mark Sinkinson · Accepted Answer · 2018-03-26T11:17:23.503

10

Thanks to a combination of the comments above, I have managed to get this working.

In case anyone else comes across my issue, I'll summarise:

As per @GordThompson's comment, I changed the driver from the old SQL Server to ODBC Driver 13 for SQL Server. There were a couple of tweaks needed in my parameter passing once I had upgraded the driver version, but on the whole this seemed like a wise move
Perhaps more importantly, @FlipperPA's comment suggested I CAST the JSON Object in the SQL Server query to a VARCHAR(MAX).

SELECT CAST( (SELECT ..... FOR JSON PATH) AS VARCHAR(MAX))

This returned the full JSON object that was being selected and everything is now working correctly.

I can only assume there is some issue with pyodbc supporting JSON in SQL Server 2017/Azure?

edited Mar 26 '18 at 11:17

answered Mar 25 '18 at 13:00

Mark Sinkinson

970
6
18

Hi Mark, Glad you got it to work. Quick comment - JSON is not a data type in SQL Server. JSON is basically nvarchar with some specific functionality. – Niels Berglund Mar 26 '18 at 11:11
I am using pymssql for my flask-sqlalchemy connection and was facing the same issue. `Select cast` saved my day. – AKJ Dec 14 '18 at 02:00

bendodge · Answer 2 · 2019-06-06T18:07:46.757

8

I ran into this same bizarre issue today. The JSON result, which ought to be a single row and column, is cut off at 2033 characters.

However, the result set is all there, just spread across many rows! You can reconstruct it:

rows = cursor.fetchall()
json_result = ''
json_result = json_result.join([row[0] for row in rows])

Or alternately:

rows = cursor.fetchall()
search_results = json.loads(''.join([row[0] for row in rows]))

Switching between ODBC Driver 13 for SQL Server and SQL Server drivers didn't help. I'm on Windows 10, Python 3.7, pyodbc 4.0.26, and SQL Server 2017.

This seems like a bug.

edited Jun 06 '19 at 18:07

answered Jun 06 '19 at 17:52

bendodge

470
5
12

This solved it for me without having to cast the whole json to NVARCHAR on the server. Did not have to switch OBDC driver. Calling fetchone() repeatedly and concatenating the string did not work, it would just do one fetch and the second fetchone() would return None and exit out of the loop. – TomEberhard Jan 27 '23 at 21:57

PYODBC truncates the response of a SQL Server FOR JSON query

2 Answers2