How to use column names when creating JSON object, Python

Question

In order to improve readability in my code I want to use plain words instead index numbers, when creating JSON object:

this is my database table school_subjects:

mysql> DESCRIBE school_subjects;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | int(11)      | NO   | PRI | NULL    | auto_increment |
| name             | varchar(500) | NO   |     | NULL    |                |
| user_id          | int(11)      | NO   | MUL | NULL    |                |
| created_by       | varchar(64)  | NO   |     | NULL    |                |
| created_time     | datetime     | NO   |     | NULL    |                |
| num_of_followers | int(11)      | NO   |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql>

my python code:

serg@serg-PORTEGE-Z835:~$ python
Python 2.7.2+ (default, Oct  4 2011, 20:03:08) 
[GCC 4.6.1] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import simplejson as json
>>> import MySQLdb
>>> import collections
>>> 
>>> mydb = MySQLdb.connect(host='localhost', user='root', passwd='', db='schooldb')
>>> cursor = mydb.cursor()
>>> cursor.execute("""
...                     SELECT id, name
...                     FROM school_subjects
...             """)
6L
>>> rows = cursor.fetchall()
>>> result = []
>>> for row in rows:
...     d = dict()
...     d['id'] = row.id        #I want something similar to this
...     d['name'] = row.name    #but it doesn't work
...     result.append(d)
... 
Traceback (most recent call last):
  File "<stdin>", line 2, in <module>
AttributeError: 'tuple' object has no attribute 'id'

as you can see I'm getting this error:

Traceback (most recent call last):
  File "<stdin>", line 2, in <module>
AttributeError: 'tuple' object has no attribute 'id'

however this code works fine:

>>> result = []
>>> for row in rows:
...     d = dict()
...     d['id'] = row[0]
...     d['name'] = row[1]
...     result.append(d)
... 
>>> subjects = json.dumps(result, indent=4)
>>> print subjects
[
    {
        "id": 1,
        "name": "Math 140"
    },
    {
        "id": 2,
        "name": "English 102"
    },
    {
        "id": 3,
        "name": "CS 240"
    },
    {
        "id": 4,
        "name": "CS 210"
    },
    {
        "id": 5,
        "name": "Math 140"
    },
    {
        "id": 6,
        "name": "English 102"
    }
]
>>>

Any reason why you shouldn't be using DictCursor? Just change your cursor call to cursor = mydb.cursor(MySQLdb.cursors.DictCursor). — Scott Lowrey
– Scott Lowrey, Commented Feb 4, 2015 at 20:11

Tidoni · Accepted Answer · 2025-05-11 00:08:23Z

8

The cursor object has a .description attribute that tells you the names of each column, use that to turn a row into a dict:

cursor.execute("""
                    SELECT id, name
                    FROM school_subjects
            """)
columns = [desc[0] for desc in cursor.description]
result = []
rows = cursor.fetchall()
for row in rows:
    row = dict(zip(columns, row))
    result.append(row)

For details on the .description attribute, see the Python DB API 2.0 specification.

edited May 11 at 0:08

Tidoni

3711 gold badge5 silver badges15 bronze badges

answered Sep 4, 2012 at 20:14

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

chm · Accepted Answer · 2019-03-09 06:58:55Z

0

    from django.db import connection

    cursor = connection.cursor()
    cursor.execute("SELECT * FROM blog_contact")
    rows = cursor.fetchall()
    columns = [desc[0] for desc in cursor.description]
    result = []
    for row in rows:
        row = dict(zip(columns, row))
        result.append(row)
    row = {"columns": columns,"rows":rows,"results":result}
    return render(request, 'blog/contact.html', row)

answered Mar 9, 2019 at 6:58

chm

14 bronze badges

Collectives™ on Stack Overflow

How to use column names when creating JSON object, Python

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related