DB-API: how can I find the column names in a cursor?

[A.M]

Hi



I use a code similar to this to retrieve data from Oracle database:



import cx_Oracle



con = cx_Oracle.connect("me/secret@tns")

cur = con.cursor()

outcur = con.cursor()

cur.execute("""

BEGIN

MyPkg.MyProccur);

END;""", cur=outcur)



for row in out_cur:

print row





The problem is I don't know how to find out what are the column name and
type that comes out of query (each row in cursor).



Is there any possibility that my Python code can find out the column name
and type in each row in cursor?



The other problem is accessing data in each row by column name. In Ruby I
can say:



Print row["ColName"]



In Python; however, I must access to row contents by integer index, like
PRINT ROW[0], which reduces my program's readability.



Can I access to row's contents by column name?



Any help would be appreciated,

Alan

 

[Fredrik Lundh]

The problem is I don't know how to find out what are the column name and type that comes out of
query (each row in cursor).

Is there any possibility that my Python code can find out the column name and type in each row in
cursor?

From "cursor objects" in the DB-API documentation:

.description

"This read-only attribute is a sequence of 7-item
sequences. Each of these sequences contains information
describing one result column: (name, type_code,
display_size, internal_size, precision, scale,
null_ok). The first two items (name and type_code) are
mandatory, the other five are optional and must be set to
None if meaningfull values are not provided."

The full spec is available here: http://www.python.org/dev/peps/pep-0249/

</F>

 

[A.M]

Thank you Fredrik for help.

Would you be able to help with the second part of question:

The other problem is accessing data in each row by column name. In Ruby I
can say:

Print row["ColName"]

In Python; however, I must access to row contents by integer index, like
PRINT ROW[0], which reduces my program's readability.

Can I access to row's contents by column name?

Thanks again,
Alan

 

[Daniel Dittmar]

for row in out_cur:

print row
[...]


The other problem is accessing data in each row by column name.

One useful technique is
for col1, col2, col3 in out_cur:
sum = sum + col3

Access is still by index, but your code uses ordinary Python variables.
It works better with SELECTs as you can choose the output columns in the
SQL. With stored procedures, there's always the possibility of someone
changing the structure of the result set.

There exists a general library for your solution:
http://opensource.theopalgroup.com/

Daniel

 

[=?iso-8859-1?q?Luis_M._Gonz=E1lez?=]

I don't know if it works this way with Oracle, but the python dbpai has
the cursor.description method that can help. For example:

cur.execute( "your query here" )
columns = [i[0] for i in cur.description]

cur.description gives a lot of data about your recordset, and the first
field is the column name.

Hope this helps...
Luis

 

[Sion Arrowsmith]

The other problem is accessing data in each row by column name. In Ruby I
can say:

Print row["ColName"]

In Python; however, I must access to row contents by integer index, like
PRINT ROW[0], which reduces my program's readability.

Can I access to row's contents by column name?

columns = dict((name, col) for col, name in enumerate(cursor.description))
print row[columns["ColName"]]

And please don't top-post.

 

[skip]

Alan> The other problem is accessing data in each row by column name. In
Alan> Ruby I can say:

Alan> Print row["ColName"]

Alan> In Python; however, I must access to row contents by integer
Alan> index, like PRINT ROW[0], which reduces my program's readability.

Alan> Can I access to row's contents by column name?

There are a couple things you can try. First, see if the adaptor for your
database has a way to specify that query results should be returned as a
list of dicts instead of a list of tuples. MySQLdb allows you to select the
style of cursor class to instantiate when you create the connection. I
think Psycopg provides a dictcursor() method on the connection (though I may
be misremembering - it's been awhile). Other adaptors may provide similar
functionality.

Failing that, you can whip something up yourself using the description
attribute to which Fredrik referred:

for row in curs.fetchall():
row = dict(zip([d[0] for d in curs.description], row))
...

Skip

 

[Dennis Lee Bieber]

In Python; however, I must access to row contents by integer index, like
PRINT ROW[0], which reduces my program's readability.

Can I access to row's contents by column name?

You'd have to check the features of the Oracle DB-API adapter.

MySQLdb implements an alternate dictionary cursor that returns a
dictionary rather than a tuple for each row.

Dictionary cursors are not a requirement of the DB-API spec.

-=-=-=-=-=-=-=-
import MySQLdb

cn = MySQLdb.connect(host="localhost", db="bestiaria",
user="bestiaria", passwd="web-asst")

cr = cn.cursor(MySQLdb.cursors.DictCursor)

print cr.execute("select * from conventions")
print cr.fetchone()
-=-=-=-=-=-=-=-
18
{'dates': 'October 27 - 29, 2006', 'name': "OklaCon 2006<br><i>Ruffin'
It</i>", 'URL': 'http://www.oklacon.com/', 'notes': 'Roman Nose State
Park', 'site': 'Watonga, OK', 'height': None, 'sortdate':
datetime.date(2006, 10, 29), 'width': None, 'banner': None, 'ID': 28L}

If the Oracle adapter doesn't offer a dictionary alternate, you
could probably create a wrapper that uses the description data to
convert...

def toDict(desc, data):
...
...
return dictionary

dictData = toDict(crsr.description(), crsr.fetchall())
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[Dennis Lee Bieber]

You'd have to check the features of the Oracle DB-API adapter.

<talking to myself>

I perused the docs for cx_oracle (or whatever it was that I'd seen
above)... No dictionary return type.

Firebird/kinterbasdb has .fetchXXXmap() calls that return
dictionaries. pysqlite has some sort of "row factory" attribute causes
it to return (case insensitive) name AND index lookup rows. And everyone
has mentioned MySQLdb dictionary cursor type...

Strangely the "big databases" (going by a sampling of two:
cx_oracle, and the MaxDB/SAPDB module) do not offer a dictionary
retrieval method (and MaxDB isn't even db-api compatible).
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/