Rows returned by pyodbc are not JSON serializable

Question

I am trying to retrieve the rows of a table using pyobc library in Python.

I was able to retrieve the tables and fields of a table successfully. Now I have a table named "apx_roomtypes" with the data as follows,

However, when I append the pyodbc rows to a list and then try dumping the list to JSON I get the error

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

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 are iterating over rows, each row is a Row instance and not a list. You can convert it to a list (which is JSON serializable) 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 a look at this answer.