The strings returned by pyodbc are not JSON serializable

Question

I am trying to get table rows using pyobc library in Python.

I managed to get the tables and table fields successfully. Now I have a table called "apx_roomtypes" with data as follows:

However, when I add pyobbc lines to the list and then try to dump the list in JSON, I get an error

TypeError: (1, 'Standard', 'For 5 members', 123) is not serializable JSON

Here is the python code :

class execute_query: def GET(self,r): web.header('Access-Control-Allow-Origin', '*') web.header('Access-Control-Allow-Credentials', 'true') cnxn = pyodbc.connect(connection_string) data = [] cursor = cnxn.cursor() query = web.input().query cursor.execute(query) rows = cursor.fetchall() for row in rows: data.append(row) return json.dumps(data) 

How can I fix this error?

Answer 1

When you iterate over rows , each row is an instance of row , not list . You can convert it to a list (which is serializable JSON) as follows:

 rows = cursor.fetchall() for row in rows: data.append([x for x in row]) # or simply data.append(list(row)) 

If you want it to return a dictionary of key / value pairs instead of a list of values, then take this answer .