Belatedly... Upgrade successfully completed, thanks to your recipe! Francesca
On 16 Mag, 15:36, Michael Bayer <[email protected]> wrote: > On May 15, 2012, at 5:52 AM, FrancescaL wrote: > > > > > Another possibility to get desired result could be use of > > "EMPTY_CLOB()" into insert statements. > > > For example: > > stmt = u"INSERT INTO t_tmp_sqla_upgrade (id, text) VALUES (:text_id, > > EMPTY_CLOB())" > > > Is there any way, using SqlAlchemy, to define a custom data type (?) > > that behaves exactly like sqlalchemy.types.Text except for the insert/ > > update of an empty text (that should be "adjusted" with "EMPTY_CLOB")? > > > (Mad question, maybe) > > this is a great question, which is that of combining typing information with > SQL rules to affect the compilation of the statement. SQLAlchemy doesn't > support exactly that operation just yet, however - an old ticket 1534 is in > place for when this feature gets implemented directly. > > Here's a recipe that should be able to do it for now: > > from sqlalchemy import Text > from sqlalchemy.sql.expression import _BindParamClause > from sqlalchemy.ext.compiler import compiles > > class EmptyClobParam(_BindParamClause): > def __init__(self, value): > self.value = value > super(EmptyClobParam, self).__init__( > key=None, value=value, type_=Text) > > @compiles(EmptyClobParam) > def _empt_clob(element, compiler, **kw): > if element.value == '': > return "EMPTY_CLOB()" > else: > return compiler.visit_bindparam(element) > > You need now to use EmptyClobParam explicitly to set this value: > > from sqlalchemy import * > m = MetaData() > t1 = Table('t_tmp', m, Column('id', Integer), Column('text', Text)) > > print t1.insert().values(id=1, text=EmptyClobParam("some value")) > print t1.insert().values(id=1, text=EmptyClobParam("")) > > if using the ORM, you can assign EmptyClobParam directly. One easy way to > get it automatic is to use @validates, such as below: > > from sqlalchemy.ext.declarative import declarative_base > from sqlalchemy.orm import Session, validates > > Base = declarative_base() > > class Foo(Base): > __tablename__ = 'foo' > > id = Column(Integer, primary_key=True) > text = Column(Text) > > @validates("text") > def _set_empty(self, key, value): > if value == "": > return EmptyClobParam(value) > else: > return value > > > > > > > > > > > Thank you, > > Francesca > > > On 14 Mag, 19:26, "Michael Bayer" <[email protected]> wrote: > >> Michael Bayer wrote: > >>> FrancescaL wrote: > >>>> Hi group, > > >>>> I have a question about Oracle CLOBs. > > >>>> I expect to be able to manage all 3 different LOB column states: > >>>> (a) Populated > >>>> (b) Empty > >>>> (c) NULL > >>>> First of all: is it my expectation correct? > > >>> possibly not. my experience with oracle CLOB is that a "blank" ends up > >>> being a single blank string. > > >> correction, I meant a "single space", that is, a string of length one > >> consisting of " ". > > >>> SQLAlchemy doesn't do anything here, data is passed directly from > >>> cx_oracle and back, my advise would be to create a cx_oracle-only test > >>> case (see the docs on cx_oracle's site, I can't copy and paste a URL on > >>> this terminal unfortunately) and email the cx_oracle list if you believe > >>> the DBAPI's direct behavior is erroneous, however this is sort of how it > >>> goes with Oracle and there might not be a workaround. > > >>>> I'm in process of upgrading to SqlAlchemy 0.6.9 and this seems (to me) > >>>> no more possible. > > >>>> Here it is how to build 3 examples (one for each column state). > > >>>> # ------------------------------ > >>>> # SQL for manual table creation: > >>>> DROP TABLE t_tmp_sqla_upgrade; > >>>> CREATE TABLE t_tmp_sqla_upgrade ( > >>>> id NUMBER (20) not null, > >>>> text CLOB > >>>> ); > > >>>> # ------------------------------ > >>>> # Python code: > >>>> from sqlalchemy import MetaData, Table, Column, Integer, Text, > >>>> create_engine > >>>> from sqlalchemy.orm import mapper, sessionmaker > > >>>> metadata = MetaData() > >>>> table = Table('t_tmp_sqla_upgrade', metadata, > >>>> Column('id', Integer, primary_key=True), > >>>> Column('text', Text()) > >>>> ) > > >>>> class TmpTableEntity(object): > >>>> pass > > >>>> mapper(TmpTableEntity, table) > > >>>> kwargs = {} > >>>> kwargs['encoding'] = 'utf-8' > >>>> engine = create_engine(u"oracle+cx_oracle://user:pwd@db", **kwargs) > >>>> Session = sessionmaker(bind=engine, autoflush=False) > > >>>> def createItem(session, text): > >>>> e = TmpTableEntity() > >>>> e.id = 1 > >>>> e.text = text > >>>> session.add(e) > >>>> session.commit() > >>>> session.expunge_all() > >>>> return e > > >>>> def readItem(session, id): > >>>> return > >>>> session.query(TmpTableEntity).filter(TmpTableEntity.id==id).one() > > >>>> session = Session() > > >>>> # 3 cases (setup and teardown is needed for each case) > > >>>> # Case (a): some text (OK) > >>>> text = u'some plain ascii text' > >>>> item = createItem(session, text) > >>>> rItem = readItem(session, 1) > >>>> assert rItem.text == text > > >>>> # Case (b): empty text (KO) > >>>> text = u'' > >>>> item = createItem(session, text) > >>>> rItem = readItem(session, 1) > >>>> assert rItem.text == None > >>>> # But I expect: assert rItem.text == u'' > >>>> # since this is a CLOB field and not a VARCHAR2 one. > > >>>> # Case (c): null text (OK) > >>>> text = None > >>>> item = createItem(session, text) > >>>> rItem = readItem(session, 1) > >>>> assert rItem.text == None > > >>>> Thank you for your time, > >>>> Francesca > > >>>> -- > >>>> You received this message because you are subscribed to the Google > >>>> Groups > >>>> "sqlalchemy" group. > >>>> To post to this group, send email to [email protected]. > >>>> To unsubscribe from this group, send email to > >>>> [email protected]. > >>>> For more options, visit this group at > >>>>http://groups.google.com/group/sqlalchemy?hl=en. > > >>> -- > >>> You received this message because you are subscribed to the Google Groups > >>> "sqlalchemy" group. > >>> To post to this group, send email to [email protected]. > >>> To unsubscribe from this group, send email to > >>> [email protected]. > >>> For more options, visit this group at > >>>http://groups.google.com/group/sqlalchemy?hl=en. > > > -- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]. > > For more options, visit this group > > athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
