DB-API: how can I find the column names in a cursor?
A.M
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
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
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" <fre...@pythonware.com> wrote in message
news:mailman.6403.1149166...@python.org...
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
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.
--
\S -- si...@chiark.greenend.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
sk...@pobox.com
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