cx-oracle-users

[cx-oracle-users] looking for a quicker / more elegant way to form objects with attributes out of query result rows

[Massa, H. A. <ch...@gh...>]

Hello,

I am using cx_Oracle to provide Oracle-Acces for a PostgreSQL database
via pl/python.

To be able to return an cx_Oracle result set as a "set of records"
from a function (which is similiar to the "table returning functions"
within Oracle), the columns of the record need to be attributes of an
object. Actually I do:
-------------------
  import cx_Oracle
  cn=cx_Oracle.connect("user","password","tnsname")
  cs=cn.cursor()
  cs.execute("""select id, firstname, secondname, street, zipcode from
adresses""")
  daten=cs.fetchall()
  cn.commit()
  class xrow(object):
     pass
  ergebnis=[]
  fieldnames = [a[0].lower() for  a in cs.description]

  for row in daten:
     myrow=xrow()
     for index, fn in enumerate(fieldnames):
         setattr(myrow,fn,row[index])
     ergebnis.append(myrow)

  return ergebnis

which gives me a nice table as a result of that function call, which
can be perfectly used.

Now... that "loop through the result and attach every column to a stub
object" does not feel very elegant; and it needs python to loop
through every field.

Is there a more elegant / faster / more compact solution?

Best wishes,

Harald

-- 
GHUM GmbH
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607

Amtsgericht Stuttgart, HRB 734971
-
persuadere.
et programmare

Re: [cx-oracle-users] looking for a quicker / more elegant way to form objects with attributes out of query result rows

[Amaury F. d'A. <ama...@gm...>]

Hi,

2011/3/3 Massa, Harald Armin <ch...@gh...>:
> To be able to return an cx_Oracle result set as a "set of records"
> from a function (which is similiar to the "table returning functions"
> within Oracle), the columns of the record need to be attributes of an
> object. Actually I do:

I'd do it with a "row factory", here are two examples, one which
builds a dictionary, the other one builds a "named tuple" and is
very similar to sqlite3.Row.

def makeDictFactory(cursor):
    columnNames = [d[0] for d in cursor.description]
    def createRow(*args):
        return dict(zip(columnNames, args))
    return createRow

def makeNamedTupleFactory(cursor):
    columnNames = [d[0].lower() for d in cursor.description]
    import collections
    Row = collections.namedtuple('Row', columnNames)
    return Row

Then, just after the cs.execute() call, you can add:

cs.rowfactory = makeDictFactory(cs)
or
cs.rowfactory = makeNamedTupleFactory(cs)

-- 
Amaury Forgeot d'Arc

Re: [cx-oracle-users] looking for a quicker / more elegant way to form objects with attributes out of query result rows

[Massa, H. A. <ch...@gh...>]

Amaury,

>> To be able to return an cx_Oracle result set as a "set of records"
>> from a function (which is similiar to the "table returning functions"
>> within Oracle), the columns of the record need to be attributes of an
>> object. Actually I do:
>
> I'd do it with a "row factory", here are two examples, one which
> builds a dictionary, the other one builds a "named tuple" and is
> very similar to sqlite3.Row.

Thank you very much for those row-factory examples! With those I will
be able to reduce the "data-moving-around-in-memory" part of my usage.

So I am still missing out a more elegant way to have those columns
transformed to attributes of an object...

Thanks so far,

Harald



-- 
GHUM GmbH
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607

Amtsgericht Stuttgart, HRB 734971
-
persuadere.
et programmare

Re: [cx-oracle-users] looking for a quicker / more elegant way to form objects with attributes out of query result rows

[Amaury F. d'A. <ama...@gm...>]

2011/3/3 Massa, Harald Armin <ch...@gh...>:
> So I am still missing out a more elegant way to have those columns
> transformed to attributes of an object...

Isn't the namedtuple solution enough?
it allows both attribute access and number-based index.

Your use case is even mentioned in the namedtuple documentation:
http://docs.python.org/dev/library/collections.html#collections.namedtuple
(look for the "sqlite" example)

-- 
Amaury Forgeot d'Arc

Re: [cx-oracle-users] looking for a quicker / more elegant way to form objects with attributes out of query result rows

[Massa, H. A. <ch...@gh...>]

Amaury,

> Isn't the namedtuple solution enough?
> it allows both attribute access and number-based index.
aaah, thanks for taking the time to remind me again!

I was so focussed on the PostgreSQL PL/Python documentation which
talks about "must be attributes to an object" that I missed the "if it
behaves like an object, it will be fine" ... it works perfectly with
named tuple (which also should
give a memory benefit)

so for the casual googler:
pl/python setof <type> returning functions can use named tuples for
the data rows in PostgreSQL

Thanks again,

Harald


>
> Your use case is even mentioned in the namedtuple documentation:
> http://docs.python.org/dev/library/collections.html#collections.namedtuple
> (look for the "sqlite" example)
>
> --
> Amaury Forgeot d'Arc
>



-- 
GHUM GmbH
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607

Amtsgericht Stuttgart, HRB 734971
-
persuadere.
et programmare