Re: [sqlalchemy] UnicodeDecode error on sqlalchemy select query

Mike Bayer Wed, 05 Aug 2015 07:12:07 -0700


On 8/5/15 9:24 AM, Abhishek Sharma wrote:

Hi Team,
I have created customized data type using TypeDecorator approach.

*from sqlalchemy import TypeDecorator, CLOB*
*class ForceUnicodeClob(TypeDecorator):
     impl = CLOB*
*     def process_bind_param(self, value, dialect):
         if isinstance(value, str):
            value = value.decode('utf-8', 'ignore')
         return value*
*     def process_result_value(self, value, dialect):
         if value is not None:
             value = "PREFIX" + value.decode('utf-8', 'ignore')
         return value*

After this in my table definition I declared the type of one of columnas *ForceUnicodeClob.*

*dlr_dclmr = Table('dlr_dclmr', metadata,
Column('dclmr_ds', ForceUnicodeClob(), primary_key=False))*

After this I am executing the query on this table withsession.query(Model) , but when I am accessing result.*dclmr_ds *i amnot getting response prepended with "PREFIX".

this can happen in the ORM if you retrieve a Model object that isalready present in the Session, which you created as a pending object:



model = Model(dclmr_ds='somestring')
session.add(model)
session.flush()

m2 = session.query(Model).filter(...).first()

assert model is m2

that is, you get the same object back. The values of the attributes arenot updated unless you first expire that object, which normally happenswhen you say session.commit().


am i missing some  steps?

SQLAlchemy Version: 0.9.9
We are using classic mapping for attributes mapping with DB columns.

On Tue, Aug 4, 2015 at 3:49 PM, Mike Bayer <[email protected]<mailto:[email protected]>> wrote:




    On 8/4/15 1:41 PM, Abhishek Sharma wrote:

    is this followings two instructions compulsory while defining new
    type?
    m.drop_all(e)
    m.create_all(e)


    no that is just part of the demonstration script.


    this instructions are not feasible , because DB team already
    defined schema and normal user can not drop and create table.

    yup, that is a given



    On Tue, Aug 4, 2015 at 8:59 PM, Mike Bayer
    <[email protected] <mailto:[email protected]>> wrote:



        On 8/4/15 7:41 AM, Abhishek Sharma wrote:

        in case lot of overhead will be there so it is better to use
        that column label only

        well it doesn't work anyway because data from a CLOB is not
        in cx_oracle's world a "String", it's a LOB.   The CLOB /
        NCLOB types for cx_oracle are organized in their own way
        where only NCLOB actually has unicode handling capability,
        regardless of the coerce_to_unicode or convert_unicode flags;
        CLOB does not.   So either use NCLOB, or build out your own
        convert unicode, here is a demo:

        from sqlalchemy import create_engine, CLOB, Table, MetaData,
        Column, select, TypeDecorator
        from sqlalchemy.dialects.oracle import NCLOB

        e = create_engine("oracle+cx_oracle://scott:tiger@xe",
        echo='debug')

        class ForceUnicodeClob(TypeDecorator):
            impl = CLOB
            def process_result_value(self, value, dialect):
                if value is not None:
                    value = value.decode('utf-8')
                return value

        m = MetaData()
        t = Table('test', m, Column('data1', NCLOB()),
        Column('data2', ForceUnicodeClob()))

        m.drop_all(e)
        m.create_all(e)

        e.execute(t.insert(), data1=u'unicode', data2=u'unicode')

        result = e.execute(select([t.c.data1, t.c.data2]))
        value1, value2 = result.fetchone()
        print repr(value1), repr(value2)



        On Tue, Aug 4, 2015 at 6:15 AM, Mike Bayer
        <[email protected] <mailto:[email protected]>>
        wrote:



            On 8/4/15 5:47 AM, Abhishek Sharma wrote:

            applying convert_unicode to CLOB type does not have any
            effect. Still I am getting str type object from
            sqlalchemy for CLOB type column


            have you tried the coerce_to_unicode flag on
            create_engine() ?


            On Mon, Aug 3, 2015 at 1:27 PM, Mike Bayer
            <[email protected]
            <mailto:[email protected]>> wrote:



                On 8/3/15 1:04 PM, Abhishek Sharma wrote:

                what about CLOB type?  Unicode only handles String
                type. Do i need to use convert_unicode there?


                if your CLOB expects non-ascii characters then yes.

                though on Oracle I thought you really need to be
                using NCLOB for a col that stores unicode.


                On Mon, Aug 3, 2015 at 6:56 PM, Mike Bayer
                <[email protected]
                <mailto:[email protected]>> wrote:



                    On 8/1/15 12:12 PM, Abhishek Sharma wrote:

                    Thanks for help. But still i have confusion
                    over encoding and decoding procedure which
                    will take place before retrieving and storing
                    the results in DB.

                    In case if i am not using convert_unicode
                    option and data type is String so python
                    process will give str object to sqlalchemy at
                    the time of insert record in DB using ORM. So
                    will alchemy store that object in encoded
                    form?. So at the time of retrieving ORM will
                    give str object for String type column to
                    python and python decode that object with
                    default encoding?

                    Can i simply use Unicode Data type for
                    columns where there might be chance of using
                    non ascii data?


                    if you know that your unicode data is on
                    specific columns then yes, the Unicode type
                    plugs in an encoder/decoder for those backends
                    that require it.


                    On Thu, Jul 30, 2015 at 2:55 AM, Mike Bayer
                    <[email protected]
                    <mailto:[email protected]>> wrote:



                        On 7/29/15 2:23 PM, Abhishek Sharma wrote:

                        We are using sqlalchemy version 0.7,
                        python 2.7 and oracle Database.
                        We have ASCII as default python encoding
                        and DB have ISO-8052 encoding. Our DB
                        tables contains some of characters which
                        are out of ASCII range. So when we are
                        running query on those tables we are
                        getting Unicode Decode error saying
                        "ASCII" codec can not decode. This error
                        we are getting without accessing model
                        attributes.

                        How i can handle these errors without
                        changing python default encoding.


                        Oracle's client encoding is controlled by
                        the NLS_LANG environment variable. That
                        has to be set correctly first off (see
                        
http://www.oracle.com/technetwork/products/globalization/nls-lang-099431.html).
                        If you have non-ASCII strings encoded in
                        datatypes that are explicitly not of type
                        NVARCHAR or NCLOB , or you're relying on
                        a lot of raw SQL, and you are still
                        getting errors, I would set the
                        "coerce_to_unicode=True" flag on
                        create_engine(), which allows cx_Oracle's
                        unicode facilities to take place fully
                        for all string data being returned, at
                        the expense of some performance. See
                        
http://docs.sqlalchemy.org/en/rel_1_0/dialects/oracle.html#unicode
                        for background.

--You received this message because you

                        are subscribed to the Google Groups
                        "sqlalchemy" group.
                        To unsubscribe from this group and stop
                        receiving emails from it, send an email
                        to
                        [email protected]
                        <mailto:[email protected]>.
                        To post to this group, send email to
                        [email protected]
                        <mailto:[email protected]>.
                        Visit this group at
                        http://groups.google.com/group/sqlalchemy.
                        For more options, visit
                        https://groups.google.com/d/optout.