Postgres:TEXT and Oracle:CLOB

[jo]

Hi all,

I have this definition of a table.


    session = Table('session', database.metadata,
         Column('id', Unicode(40), primary_key=True, nullable=False),
         Column('data', Text),
         Column('expiration_time', TIMESTAMP(timezone=False)),
         )

In the PostgreSQL DB, it creates a table like this:

      name       |            type             | default | not_null
-----------------+-----------------------------+---------+----------
 id              | character varying(40)       |         | t
 data            | text                        |         | f
 expiration_time | timestamp without time zone |         | f

In the Oracle DB, like this:

name            | data_type    | nullable | data_default | data_length
--------------- + ------------ + -------- + ------------ + -----------
ID              | NVARCHAR2    | N        | NULL         | 80
DATA            | CLOB         | Y        | NULL         | 4000
EXPIRATION_TIME | TIMESTAMP(6) | Y        | NULL         | 11

When I use it with PostgreSQL all is OK but
when I try to use it with Oracle, pickle raises this error:

...data *=* pickle*.*loads*(*pickled_data*)*|
*TypeError: ('loads() argument 1 must be string, not cx_Oracle.LOB', <bound method Root.index of <sicer.BASE.controller.Root object at 0x8231f10>>)*


What can I do to avoid this error?
thank you,

j

[Michael Bayer]

SQLAlchemy ResultProxy is set up by the cx_oracle dialect to add the sqlalchemy.dialect.oracle.CLOB type into any result set with a CLOB which intercepts cx_oracle's LOB and converts to a stream.   If you are using a SQLAlchemy engine and not the cx_oracle cursor directly you should not be getting the LOB back.   

from sqlalchemy import *

e = create_engine('oracle://scott:tiger@localhost/xe', echo=True)

m = MetaData()

t = Table('x', m, Column('id', Integer, primary_key=True), Column('data', Text))

m.drop_all(e)

m.create_all(e)

e.execute(t.insert().values(id=1, data='adjfnadkjfdanfkjdanjkdn'))

for row in e.execute(t.select()):

    print row['data']

# works with plain SQL too, SQLA uses cursor.description for this particular type of conversion:

for row in e.execute("SELECT data FROM x"):

    print row['data']

CREATE TABLE x (

id INTEGER NOT NULL, 

data CLOB, 

PRIMARY KEY (id)

)

INSERT INTO x (id, data) VALUES (:id, :data)

{'data': 'adjfnadkjfdanfkjdanjkdn', 'id': 1}

SELECT x.id, x.data 

FROM x

adjfnadkjfdanfkjdanjkdn

SELECT data FROM x

{}

adjfnadkjfdanfkjdanjkdn

--
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.