cx-oracle-users

[cx-oracle-users] Modifying fetched data python types for zope adaptor

[Andy H. <and...@gm...>]

Hi all.

I've just written a Zope Database Adaptor based on cx_Oracle, unfortunately
the DCOracle adaptor or DBAPI doesn't appear to be maintained any longer.

The last part of the code I need to do is have the data returned by the zope
adaptor return Zopes DataTime objects instaed of datetime.datetime objects.

Ideally I'd be able to get cx_Oracle to create objects with Zopes type
DateTime.DateTime when it's building the fetch list of data. Is there anyway
of doing this? Can I replace cx_Oracle.Date safely and have it use that to
build the returned object?

Is there another way of doing type conversions?

Thanks for any pointers or help you can give.

Andy Hird

Re: [cx-oracle-users] Modifying fetched data python types for zope adaptor

[Anthony T. <ant...@gm...>]

There is no way of hooking into the returning of date objects. You'll
have to hook the entire result set. You can do that quite easily by
subclassing cx_Oracle.Cursor and overriding fetchone(), fetchmany()
and fetchall() to transform any datetime.datetime entities into Zopes
DateTime objects. Clearly there is a performance penalty in doing
this. If you can suggest an alternative that would eliminate the
performance penalty I'd be happy to entertain it. Perhaps something
along the lines of settype(cx_Oracle.DATETIME, <YourTypeOrMethod>)
which would then call <YourTypeOrMethod> with a particular set of
parameters? Any thoughts on this?

On 11/7/06, Andy Hird <and...@gm...> wrote:
> Hi all.
>
> I've just written a Zope Database Adaptor based on cx_Oracle, unfortunately
> the DCOracle adaptor or DBAPI doesn't appear to be maintained any longer.
>
> The last part of the code I need to do is have the data returned by the zope
> adaptor return Zopes DataTime objects instaed of datetime.datetime objects.
>
> Ideally I'd be able to get cx_Oracle to create objects with Zopes type
> DateTime.DateTime when it's building the fetch list of data. Is there anyway
> of doing this? Can I replace cx_Oracle.Date safely and have it use that to
> build the returned object?
>
> Is there another way of doing type conversions?
>
> Thanks for any pointers or help you can give.
>
> Andy Hird
>
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job
> easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
>

Re: [cx-oracle-users] Modifying fetched data python types for zope adaptor

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

Hello Anthony,

Anthony Tuininga wrote:
> There is no way of hooking into the returning of date objects. You'll
> have to hook the entire result set. You can do that quite easily by
> subclassing cx_Oracle.Cursor and overriding fetchone(), fetchmany()
> and fetchall() to transform any datetime.datetime entities into Zopes
> DateTime objects. Clearly there is a performance penalty in doing
> this. If you can suggest an alternative that would eliminate the
> performance penalty I'd be happy to entertain it. Perhaps something
> along the lines of settype(cx_Oracle.DATETIME, <YourTypeOrMethod>)
> which would then call <YourTypeOrMethod> with a particular set of
> parameters? Any thoughts on this?

Some wild thoughts, without any consideration of the diffulty to implement:

cx_Oracle types should be subclassable: the Cursor class and the Variable types.
When subclassing, users should be able to provide:

- OracleCursor.variableFactory(self, oracleType, <dataLength_and
others?>): the base implementation ends up by calling the current C
function "Variable_New" and returns its value.
Of course, when subclassing, users will want to substitute their own
udt_Variable class.

- OracleCursor.createRow(self): the base implementation is "Cursor_CreateTuple".
As an example of subclassing, I could copy it into a subclass of
tuple, with a custom __getattribute__ handling the column names...

- xxxVariable.getvalue and setvalue. Here can take place the
conversions to/from Zope datatypes.


Now, for the implementation:
The setValueProc and getValueProc members move from udt_VariableType
to udt_Variable.
Variable_New then checks for derived methods: if so, it replaces
setValueProc and getValueProc with appropriate wrappers like:
PyObject *derivedGetValue(udt_Variable *var, unsigned pos)
{
    return PyObject_CallMethod(var, "getvalue", "i", pos);
}
The advantage of this method is that advanced users can write their
own C code, derive DateTimeVar in C, and directly access the OCIDate*
buffer, with no performance penalty.

Thank you for having read so far.
Now my brain is too hot, I'd better go to bed...

-- 
Amaury Forgeot d'Arc

Re: [cx-oracle-users] Modifying fetched data python types for zope adaptor

[Andy H. <and...@gm...>]

Hi Anthony, thanks for the fast response.

I'd thought about doing something similar to your suggestion for subclassing
cx_Oracle.Cursor and converting the types on the fly, but like you said
there's a performance hit. I'll probably implement this anyway so old
versions of cx_Oracle can be used.

Your second suggestion is definitely something that'd be much appreciated by
me. Both the psycopg2 and MySQLdb modules implement something similar.
Here's a quick summary of what they do:

With MySQLdb you can define a dict, keyed on the modules database types
(much like cx_Oracle.NUMBER) with the values being callables which return
the desired objects type. The callable is passed a string representation of
the data returned, and it returns a python object of the desired type. The
conversion dict is passed as an argument to MySQLdb connect method. See:
http://sourceforge.net/docman/display_doc.php?docid=32071&group_id=22307#functions-and-attributesfor
more details (search for conv)

psycopg2 defines a bunch of methods in its extensions module which do pretty
much the same thing. You define a casting method by tieing a function to a
Postgresql type via the types OID using the method
psycopg2.extensions.new_type and then psycopyg2.extensions.regster_type. As
with MySQLdb a string representing the returned data is passed to the
conversion function, and the conversion function returns a python object.
More info at
http://initd.org/tracker/psycopg/browser/psycopg2/trunk/doc/extensions.rstand
search for "Type casting of SQL types into Python values".

Which really is pretty much the same as your settype(cx_Oracle.DATETIME,
<YourTypeOrMethod>). I guess the one thing they do in common is pass a
string to the conversion method, although it'd probably be as simple to pass
cx_Oracles native value for the given type, i.e. a datetime (or
cx_Oracle.Date) for cx_Oracle.DATETIME and so on. After all if I'm defining
the conversion methods then I should know what I'd be getting.

Thanks again in advance. Sorry for going on so long there.

Andy


On 11/9/06, Anthony Tuininga <ant...@gm...> wrote:
>
> There is no way of hooking into the returning of date objects. You'll
> have to hook the entire result set. You can do that quite easily by
> subclassing cx_Oracle.Cursor and overriding fetchone(), fetchmany()
> and fetchall() to transform any datetime.datetime entities into Zopes
> DateTime objects. Clearly there is a performance penalty in doing
> this. If you can suggest an alternative that would eliminate the
> performance penalty I'd be happy to entertain it. Perhaps something
> along the lines of settype(cx_Oracle.DATETIME, <YourTypeOrMethod>)
> which would then call <YourTypeOrMethod> with a particular set of
> parameters? Any thoughts on this?

Re: [cx-oracle-users] Modifying fetched data python types for zope adaptor

[Andy H. <and...@gm...>]

One thought I had after getting some sleep is that passing in the types
cx_Oracle normally returns means you end up constructing the returned object
twice anyway and you lose some of the performance benefits of doing this.
I'm not sure whether building a string to pass into settype is much less
work or not.

Andy


> Which really is pretty much the same as your settype(cx_Oracle.DATETIME,
> <YourTypeOrMethod>). I guess the one thing they do in common is pass a
> string to the conversion method, although it'd probably be as simple to pass
> cx_Oracles native value for the given type, i.e. a datetime (or
> cx_Oracle.Date) for cx_Oracle.DATETIME and so on. After all if I'm defining
> the conversion methods then I should know what I'd be getting.
>
> Thanks again in advance. Sorry for going on so long there.
>
> Andy
>
>
> On 11/9/06, Anthony Tuininga <ant...@gm...> wrote:
> >
> > There is no way of hooking into the returning of date objects. You'll
> > have to hook the entire result set. You can do that quite easily by
> > subclassing cx_Oracle.Cursor and overriding fetchone(), fetchmany()
> > and fetchall() to transform any datetime.datetime entities into Zopes
> > DateTime objects. Clearly there is a performance penalty in doing
> > this. If you can suggest an alternative that would eliminate the
> > performance penalty I'd be happy to entertain it. Perhaps something
> > along the lines of settype(cx_Oracle.DATETIME, <YourTypeOrMethod>)
> > which would then call <YourTypeOrMethod> with a particular set of
> > parameters? Any thoughts on this?
>
>
>

Re: [cx-oracle-users] Modifying fetched data python types for zope adaptor

[Anthony T. <ant...@gm...>]

If you pass in the datetime variable then yes, you construct the
returned object twice. Passing a string would imply parsing would be
necessary which is less than ideal. Passing in a series of integers
(year, month, day, hour, minute, second, fsecond) would imply the
construction of six integers -- which may or may not be faster than
the construction of a datetime variable.

Regardless of all that, however, it would perform better than
subclassing a cursor and massaging all of the data that is
returned.... Anyone else care to comment?

On 11/9/06, Andy Hird <and...@gm...> wrote:
> One thought I had after getting some sleep is that passing in the types
> cx_Oracle normally returns means you end up constructing the returned object
> twice anyway and you lose some of the performance benefits of doing this.
> I'm not sure whether building a string to pass into settype is much less
> work or not.
>
> Andy
>
>
>
> >
> > Which really is pretty much the same as your settype(cx_Oracle.DATETIME,
> <YourTypeOrMethod>). I guess the one thing they do in common is pass a
> string to the conversion method, although it'd probably be as simple to pass
> cx_Oracles native value for the given type, i.e. a datetime (or
> cx_Oracle.Date) for cx_Oracle.DATETIME and so on. After all if I'm defining
> the conversion methods then I should know what I'd be getting.
> >
> > Thanks again in advance. Sorry for going on so long there.
> >
> > Andy
> >
> >
> >
> >
> > On 11/9/06, Anthony Tuininga < ant...@gm...> wrote:
> > > There is no way of hooking into the returning of date objects. You'll
> > > have to hook the entire result set. You can do that quite easily by
> > > subclassing cx_Oracle.Cursor and overriding fetchone(), fetchmany()
> > > and fetchall() to transform any datetime.datetime entities into Zopes
> > > DateTime objects. Clearly there is a performance penalty in doing
> > > this. If you can suggest an alternative that would eliminate the
> > > performance penalty I'd be happy to entertain it. Perhaps something
> > > along the lines of settype(cx_Oracle.DATETIME, <YourTypeOrMethod>)
> > > which would then call <YourTypeOrMethod> with a particular set of
> > > parameters? Any thoughts on this?
> >
> >
>
>
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job
> easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
>

Re: [cx-oracle-users] Modifying fetched data python types for zope adaptor

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

Hello,

Andy Hird wrote:
> One thought I had after getting some sleep is that passing in the types
> cx_Oracle normally returns means you end up constructing the returned object
> twice anyway and you lose some of the performance benefits of doing this.
> I'm not sure whether building a string to pass into settype is much less
> work or not.

Yes, this is inherent to any method derivation : you have one more
function to call, one more list of arguments to pass, new values to
build... In python IIRC, the first two are the most significant for
performance.
At least you don't have to keep the two lists of rows in memory.

For advanced users, we should provide way to derive their own types in
C code. The Variable object would have access to the raw OCI buffer,
to be as fast as the original type.

-- 
Amaury Forgeot d'Arc