Re: [sqlalchemy] UnicodeDecode error on sqlalchemy select query

Mike Bayer Wed, 05 Aug 2015 10:07:48 -0700

Attached is a test script illustrating the code you have below alongwith a round trip verification using a simple Model class mapped to thetable. Please confirm this script works as expected, as it does here.Assuming that works, determine what's different about your real-worldenvironment 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 thati 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 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.

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