23

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,

enter image description here

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?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Sajeetharan
  • 216,225
  • 63
  • 350
  • 396

1 Answers1

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.

Community
  • 1
  • 1
Martin Konecny
  • 57,827
  • 19
  • 139
  • 159