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