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.conne ct("me/secret@tns")

cur = con.cursor()

outcur = con.cursor()

cur.execute("""

BEGIN

MyPkg.MyProc(:c ur);

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]

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

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" <fr*****@python ware.com> wrote in message
news:ma******** *************** *************** *@python.org...

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

[Luis M. González]

I don't know if it works this way with Oracle, but the python dbpai has
the cursor.descript ion 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.conne ct("me/secret@tns")

cur = con.cursor()

outcur = con.cursor()

cur.execute("""

BEGIN

MyPkg.MyProc(:c ur);

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

[Daniel Dittmar]

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

[Sion Arrowsmith]

A.M <al******@newsg roup.nospam> 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(curso r.description))
print row[columns["ColName"]]

And please don't top-post.

--
\S -- si***@chiark.gr eenend.org.uk -- 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

[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.descriptio n], row))
...

Skip