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?

score 38 · Accepted Answer · edited May 23 '17 at 12:33

38

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.

edited May 23 '17 at 12:33

Community

1
1

answered Jan 11 '16 at 06:52

Martin Konecny

57,827
19
139
159

1

Thanks for the link bro, thats what i needed – Sajeetharan Jan 11 '16 at 07:04
5

`list(row)` should do the trick without the generator expression. – kindall Jan 11 '16 at 07:05
Thx @kindall - applied. – Martin Konecny Jan 11 '16 at 07:12
If data includes DateTime data type, then this solution needs to be modified to also convert these fields to sting. – YNR Jun 07 '21 at 09:26

Rows returned by pyodbc are not JSON serializable

1 Answers1

Linked