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

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.MyProc(:cur);

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

A.M

"A.M" wrote:

> 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>

Fredrik Lundh

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



"Fredrik Lundh" <> wrote in message
news:mailman.6403.1149166487.27775.python-...
> "A.M" wrote:
>
>> 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

A.M wrote:
> 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

Daniel Dittmar

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


A.M wrote:
> 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.MyProc(:cur);
>
> 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

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

A.M <> wrote:
>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.

--
\S -- -- http://www.chaos.org.uk/~sion/
___ | "Frankly I have no feelings towards penguins one way or the other"
\X/ | -- Arthur C. Clarke
her nu becomeþ se bera eadward ofdun hlæddre heafdes bæce bump bump bump

Sion Arrowsmith

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

skip@pobox.com

On Thu, 1 Jun 2006 09:20:09 -0400, "A.M" <>
declaimed the following in comp.lang.python:

> 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

HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: web-)
HTTP://www.bestiaria.com/

Dennis Lee Bieber

On Thu, 01 Jun 2006 17:23:29 GMT, Dennis Lee Bieber
<> declaimed the following in comp.lang.python:

> 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

HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: web-)
HTTP://www.bestiaria.com/

Dennis Lee Bieber