CODEWITHSUNDEEP
pythoncx-oracle
mikebmassey
mikebmassey

Reputation: 8594

Get column value by name rather than position in cx_Oracle

Using cx_Oracle, I am selecting data from an Oracle database. 

curs = cxn.cursor()
result = curs.execute(SELECT FirstName, LastName FROM Person)

Is there a way to return only the firstname without numerical index? For example:

for row in result:
    result[0] #will return the first column

What I am hoping to do is call the value like result['FirstName']

Upvotes: 3

Views: 11562

Answers (3)

Guillermo Castaño
Guillermo Castaño

Reputation: 11

nmcol=[row[0] for row in c.description]
# print(nmcol)
for row in cursor:
  print(row[nmcol.index('FirstName')])

Upvotes: 1

Anthony Tuininga
Anthony Tuininga

Reputation: 7096

You can use a row factory to return a row that responds to names in addition to indices. One simple way to do that is using collections.namedtuple, as in the following code (which uses the cx_Oracle test suite setup):

import cx_Oracle
import collections

connection = cx_Oracle.Connection("cx_Oracle/dev@localhost/orcl")
cursor = connection.cursor()
cursor.execute("select * from TestStrings")
names = [c[0] for c in cursor.description]
cursor.rowfactory = collections.namedtuple("TestStrings", names)
for row in cursor:
    print(row)
    print(row[0])
    print(row.STRINGCOL)

You can also look at the sample provided in the cx_Oracle repository, as well, for further inspiration.

https://github.com/oracle/python-cx_Oracle/blob/main/samples/rows_as_instance.py

You can use an output type handler as well to make this all seamless, too. I will add that sample to the cx_Oracle repository later on.

Upvotes: 7

ivan_pozdeev
ivan_pozdeev

Reputation: 36046

DB API 2.0 does not provide such a capability. Anything various DB client modules provide like this is nonstandard.

The API does, however, provide the cursor.description read-only attribute with this information:

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 are set to None if no meaningful values can be provided.

<...>

So, you can do like this:

for row in result:
    result[(i for i,cd in enumerate(result.description) if cd[0]=='column_name').next()]

Rather wordy and should be only called once, so it's reasonable to move this to a subroutine:

def columns(cursor):
    return {cd[0]:i for i,cd in enumerate(cursor.description)}
<...>
result=c.execute(...)
ccls=columns(result)
for row in result:
    row[ccls['name']]

Upvotes: 1

Related Questions