Re: [sqlalchemy] UnicodeDecode error on sqlalchemy select query

Mike Bayer Tue, 11 Aug 2015 11:23:06 -0700


On 8/11/15 1:58 PM, Abhishek Sharma wrote:

Hi Team,
With asynchronous request my model object Unicode typecolumn not returning Unicode object but if I do same action usingsynchronous I am getting Unicode object

SQLAlchemy has no asynchrnous API itself so this has to do with theasynchronous framework you're using and how you are integrating it withSQLAlchemy.

On 05-Aug-2015 11:43 PM, "Abhishek Sharma" <[email protected]<mailto:[email protected]>> wrote:


    thanks for your help.
    its seems to be working. i will troubleshoot in my development
    environment.

    On Wed, Aug 5, 2015 at 10:37 PM, Mike Bayer
    <[email protected] <mailto:[email protected]>> wrote:

        Attached is a test script illustrating the code you have below
        along with a round trip verification using a simple Model
        class mapped to the table.  Please confirm this script works
        as expected, as it does here.  Assuming that works, determine
        what's different about your real-world environment versus this
        test case.




        On 8/5/15 11:45 AM, Abhishek Sharma wrote:

        that object already got saved in DB after session.commit().
        After that i am retrieving object which already stored in db.
        still same issue.

        On Wed, Aug 5, 2015 at 7:41 PM, Mike Bayer
        <[email protected] <mailto:[email protected]>>
        wrote:



            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 column as *ForceUnicodeClob.*
            *dlr_dclmr = Table('dlr_dclmr', metadata,
            Column('dclmr_ds', ForceUnicodeClob(), primary_key=False))*

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

            this can happen in the ORM if you retrieve a Model object
            that is already 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 are not updated unless you first expire that
            object, which normally happens when 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 compulsorywhile 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.

--You received this message

                                    because you are subscribed
                                    to a topic in the Google
                                    Groups "sqlalchemy" group.
                                    To unsubscribe from this
                                    topic, visit
                                    
https://groups.google.com/d/topic/sqlalchemy/eThubIMnL4o/unsubscribe.
                                    To unsubscribe from this
                                    group and all its topics,
                                    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.

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

--You received this message

                                because you are subscribed to a
                                topic in the Google Groups
                                "sqlalchemy" group.
                                To unsubscribe from this topic,
                                visit
                                
https://groups.google.com/d/topic/sqlalchemy/eThubIMnL4o/unsubscribe.
                                To unsubscribe from this group
                                and all its topics, 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.

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

--You received this message because you

                            are subscribed to a topic in the
                            Google Groups "sqlalchemy" group.
                            To unsubscribe from this topic, visit
                            
https://groups.google.com/d/topic/sqlalchemy/eThubIMnL4o/unsubscribe.
                            To unsubscribe from this group and
                            all its topics, 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.