Python processing sql records

Question

This is how I process the records from a db in Python

code.py

conn = cx_Oracle.connect('pass the connection string')
cursor = conn.cursor()
sql = "select * from emp table"
cursor.execute(sql)
result =cursor.fetchall()

for row in result:
     name = row[0]
     age  = row[1]

Question: Instead of hardcoding as row[0],row[1], Is there a way to get the column names directly ?

abarnert · Accepted Answer · 2012-09-27 22:01:03Z

1

The cursor has a .description attribute; it's a sequence of tuples. Each tuple describes a column in the result and it's first value is the name.

You can use it to construct a dict of each row:

for x in result:
    row = { d[0].lower(): col for (d, col) in zip(cursor.description, x) }
    name = row['name']
    age = row['age']

The .description attribute is described in more detail in the Python DB API 2.0.

edited Sep 27, 2012 at 22:01

abarnert

368k54 gold badges626 silver badges691 bronze badges

answered Sep 27, 2012 at 21:57

Martijn Pieters

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

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

2 Comments

abarnert Over a year ago

+1. You had two minor typos, but the basic idea is exactly right.

user1050619 Over a year ago

dict= {}.....dict1 = { d[0].lower():col for(d, col) in zip(self.cursor.description, row) }

Collectives™ on Stack Overflow

Python processing sql records

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related