1

Is there any way I can get the column names from my SQL select result? Here is my little test code and all I want is to generate a sample JSON from the result:

import MySQLdb
...
....

cursor.execute('SELECT * from table')
rows = cursor.fetchall()
column = [t[0] for t in cursor.description]

for row in rows:
    myjson = {"id": row[column['id']], "name": row[column['name']], "age": row[column['age']]}
    myresult = json.dumps(myjson, indent=4)
    return myresult

But I'm getting TypeError: tuple indices must be integers, not str I don't want to use indices like so row[2] in case I add or remove a column from my table, it does not affect the result. I want to be able to refer to the column names.

Any help would be appreciated.

Helen Neely
  • 4,666
  • 8
  • 40
  • 64

1 Answers1

1

You can access the column names through cursor.description, which is a tuple of tuples. By accessing the first element of each, you get the column names.

column_names = [t[0] for t in cursor.description]

This means you can create a dict with the column names -> index mapping which would at least let you write something like

for row in rows:
    myjson = {"id": row[columns['id']], "name": row[columns['name']], "age": row[columns['age']]}
    myresult = json.dumps(myjson, indent=4)
    return myresult
Johanna Larsson
  • 10,531
  • 6
  • 39
  • 50