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

Answer 1

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:

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

2. 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?

Answer 2

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.