Oracle's XMLTyoe
millsks
python field when using declarative. I have been able to do it with
cx_Oracle by executing a query using XMLType.getClobVal and would
probably work with sqlalchemy's manual query system too, but would
love it if I could use it as part of an ORM object as an actual column/
field or as a relationship.
If this is possible where should I look to learn how to integrate it
into our sqlalchemy code?
Michael Bayer
first you'd have to tell me what XMLType.getClobVal is, as cx_oracle's documentation does not mention it in their index:
http://cx-oracle.sourceforge.net/html/genindex.html
next, you would make sure you can make it work within a SQLAlchemy Table object, probably using a plain sqlalchemy.types.CLOB object if that's the kind of type cx_oracle treats it as - when used with cx_oracle, the ultimate type would call upon _LOBMixin in order to provide the read() method that cx_oracle asks of those types.
Failing that, you'd subclass UserDefinedType and make a type that does what cx_oracle wants to read/write a Python value.
The ORM part then comes for free as it rides on top of Table metadata.
Kevin Mills
Kevin
43rd Law of Computing:
Anything that can go wr
fortune: Segmentation violation -- Core dumped
--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Michael Bayer
On Sep 12, 2010, at 12:28 PM, Kevin Mills wrote:
> Sorry about that. It was 4am this morning on my android so i wasnt thinking clearly yet.
>
> XMLType is an Oracle object type and library for storing and interacting with XML documents. getClobVal is a method of XMLType that returns an XML chunk of data as a CLOB instead of as an object. I have found that when I was using cx_Oracle directly it did not like when I tried to process an Object so I used getClobVal when I was trying to store an XML document retrieved from a query.
>
> For example when I was using cx_Oracle I would open a cursor and execute the following query to get my XML document or portion of it.
>
> cursor = cx_Oracle.Cursor(connection)
> cursor.execute("select object_value from xmlrepos")
> result_obj = cursor.fetchone()
>
> cursor.execute("select XMLType.getClobVal(object_value) from xmlrepos")
> result_clob = cursor.fetchone()
>
> result_obj gives me a cx_Oracle.OBJECT that I have yet to figure out what I could do with by itself and the result_clob gives me a cx_Oracle.LOB object that I take the str() value of to get my value.
>
> I was just curious if there was any ways to accomplish this with ORM. I saw that oracle has a CLOB colum in the dialects definition for Oracle, but was not sure how I could create a declarative object using a CLOB column that would allow me to create a custom query so that I can use XMLType.getClobVal to access it.
>
> Sorry if I am still not making any sense. I will attempt to give a better example of what I am looking to do when I get back.
>
> I will also try to create a custom UserDefinedType and see if I can go from there. I just have yet to have any luck to find anything that directly interacts with an XML object in the database without having to convert it to anohter data type.
If you're getting cx_oracle.LOB, it sounds like you would declare a Column using CLOB as the type, and it would work.
As far as how to declare Columns using CLOBs, that's all basic SQLAlchemy stuff, like here: http://www.sqlalchemy.org/docs/orm/tutorial.html#creating-table-class-and-mapper-all-at-once-declaratively where you see Column(String) you'd instead be saying Column(CLOB).
If you wanted to emit CREATE TABLE statements too, then we'd have to subclass CLOB a bit but it sounds like you're not at that point yet.
