On Jan 17, 2012, at 7:46 AM, Pierre Bossé wrote:
> Thank you Michael for your answers, but
>
> This does not work even with the UFT-8 encoding.
>
> 2012-01-17 07:10:03,405 INFO sqlalchemy.engine.base.Engine
> CREATE TABLE "TEST_ENUM" (
> id INTEGER NOT NULL,
> "MY_ENUM" NVARCHAR2(100),
> PRIMARY KEY (id),
> CHECK ("MY_ENUM" IN ('avec é', 'avec è', 'avec à'))
> )
OK but very significantly, the behavior has changed. SQLAlchemy is now
sending in the correct DDL to Oracle. What remains is whether or not it gets
to cx_oracle in the best way possible as well as if cx_oracle does the right
thing with it. We've had a very hard time getting cx_oracle/Oracle to
understand fully unicode DDL expressions and it doesn't work completely.
At the very least you need to be on a recent cx_Oracle, 5.1 or later, and you
need to ensure your NLS_LANG environment variable is set, such as
NLS_LANG=AMERICAN.AMERICA.UTF8 though I guess you might need to adjust that for
your locale.
Try that and I'll see if we can maybe make some more adjustments to the dialect
for the most recent cx_Oracle versions, I notice that we're still encoding the
DDL before we pass to cx_Oracle so maybe we can improve on that.
>
>
> 2012-01-17 07:10:03,405 INFO sqlalchemy.engine.base.Engine {}
> 2012-01-17 07:10:03,453 INFO sqlalchemy.engine.base.Engine COMMIT
> 2012-01-17 07:10:03,453 INFO sqlalchemy.engine.base.Engine BEGIN
> (implicit)
> 2012-01-17 07:10:03,453 INFO sqlalchemy.engine.base.Engine INSERT INTO
> "TEST_ENUM" (id, "MY_ENUM") VALUES (:id, :MY_ENUM)
> 2012-01-17 07:10:03,453 INFO sqlalchemy.engine.base.Engine {'MY_ENUM':
> u'avec \xe9', 'id': 1}
> 2012-01-17 07:10:03,453 INFO sqlalchemy.engine.base.Engine ROLLBACK
> Traceback (most recent call last):
> File "E:\Data\!RefVec\dev\workspace\BdrsMD_Metadata\bdrs\models
> \TestEnum.py", line 38, in <module>
> session.commit()
> File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
> py2.7.egg\sqlalchemy\orm\scoping.py", line 113, in do
> return getattr(self.registry(), name)(*args, **kwargs)
> File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
> py2.7.egg\sqlalchemy\orm\session.py", line 645, in commit
> self.transaction.commit()
> File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
> py2.7.egg\sqlalchemy\orm\session.py", line 313, in commit
> self._prepare_impl()
> File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
> py2.7.egg\sqlalchemy\orm\session.py", line 297, in _prepare_impl
> self.session.flush()
> File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
> py2.7.egg\sqlalchemy\orm\session.py", line 1547, in flush
> self._flush(objects)
> File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
> py2.7.egg\sqlalchemy\orm\session.py", line 1616, in _flush
> flush_context.execute()
> File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
> py2.7.egg\sqlalchemy\orm\unitofwork.py", line 328, in execute
> rec.execute(self)
> File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
> py2.7.egg\sqlalchemy\orm\unitofwork.py", line 472, in execute
> uow
> File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
> py2.7.egg\sqlalchemy\orm\mapper.py", line 2193, in _save_obj
> execute(statement, multiparams)
> File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
> py2.7.egg\sqlalchemy\engine\base.py", line 1399, in execute
> params)
> File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
> py2.7.egg\sqlalchemy\engine\base.py", line 1532, in
> _execute_clauseelement
> compiled_sql, distilled_params
> File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
> py2.7.egg\sqlalchemy\engine\base.py", line 1640, in _execute_context
> context)
> File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
> py2.7.egg\sqlalchemy\engine\base.py", line 1633, in _execute_context
> context)
> File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
> py2.7.egg\sqlalchemy\engine\default.py", line 330, in do_execute
> cursor.execute(statement, parameters)
> sqlalchemy.exc.IntegrityError: (IntegrityError) ORA-02290: check
> constraint (PBOSSE.SYS_C0017639) violated
> 'INSERT INTO "TEST_ENUM" (id, "MY_ENUM") VALUES
> (:id, :MY_ENUM)' {'MY_ENUM': u'avec \xe9', 'id': 1}
>
> The contraint in the Oracle DB is recorded as follows:
> "MY_ENUM" IN ('avec é', 'avec è', 'avec à ')
> It is normal that it does not work.
> =====================================================
>
> If I do not add value domain constraint (Enum), adding to the database
> and is non-ACII are preserved.
>
> The program without Enum :
> =====================================================
> # -*- coding: utf-8 -*-
>
> from elixir import *
> from sqlalchemy import create_engine
>
> class TestEnum(Entity):
> using_options(tablename='TEST_ENUM')
> # myEnum = Field(Unicode(100),\
> # Enum('avec é',
> # u'avec è',
> # u'avec à'),\
> # colname='MY_ENUM')
> myEnum = Field(Unicode(100), colname='MY_ENUM')
>
> if __name__ == '__main__':
> metadata.bind = create_engine('oracle://..:..@..',
> encoding='utf-8', echo=True)
>
> x = u'avec é'
> print '=' * 20
> print x.encode('utf-8')
> print '=' * 20
>
> setup_all()
> drop_all()
> create_all()
>
> test1 = TestEnum()
> test1.id = 1
>
> test1.myEnum = u'avec é'
> session.commit()
>
> test2 = TestEnum.query.filter(TestEnum.myEnum == u'avec é').all()
>
> print '=' * 20
> print test2[0].myEnum
> print '=' * 20
>
> The execution log :
> =====================================================
> ====================
> avec é
> ====================
> 2012-01-17 07:37:54,536 INFO sqlalchemy.engine.base.Engine SELECT USER
> FROM DUAL
> 2012-01-17 07:37:54,536 INFO sqlalchemy.engine.base.Engine {}
> 2012-01-17 07:37:54,536 INFO sqlalchemy.engine.base.Engine SELECT
> table_name FROM all_tables WHERE table_name = :name AND owner
> = :schema_name
> 2012-01-17 07:37:54,536 INFO sqlalchemy.engine.base.Engine {'name':
> u'TEST_ENUM', 'schema_name': u'PBOSSE'}
> 2012-01-17 07:37:54,552 INFO sqlalchemy.engine.base.Engine
> DROP TABLE "TEST_ENUM"
> 2012-01-17 07:37:54,552 INFO sqlalchemy.engine.base.Engine {}
> 2012-01-17 07:37:54,568 INFO sqlalchemy.engine.base.Engine COMMIT
> 2012-01-17 07:37:54,568 INFO sqlalchemy.engine.base.Engine SELECT
> table_name FROM all_tables WHERE table_name = :name AND owner
> = :schema_name
> 2012-01-17 07:37:54,568 INFO sqlalchemy.engine.base.Engine {'name':
> u'TEST_ENUM', 'schema_name': u'PBOSSE'}
> 2012-01-17 07:37:54,568 INFO sqlalchemy.engine.base.Engine
> CREATE TABLE "TEST_ENUM" (
> id INTEGER NOT NULL,
> "MY_ENUM" NVARCHAR2(100),
> PRIMARY KEY (id)
> )
>
>
> 2012-01-17 07:37:54,568 INFO sqlalchemy.engine.base.Engine {}
> 2012-01-17 07:37:54,818 INFO sqlalchemy.engine.base.Engine COMMIT
> 2012-01-17 07:37:54,832 INFO sqlalchemy.engine.base.Engine BEGIN
> (implicit)
> 2012-01-17 07:37:54,832 INFO sqlalchemy.engine.base.Engine INSERT INTO
> "TEST_ENUM" (id, "MY_ENUM") VALUES (:id, :MY_ENUM)
> 2012-01-17 07:37:54,832 INFO sqlalchemy.engine.base.Engine {'MY_ENUM':
> u'avec \xe9', 'id': 1}
> 2012-01-17 07:37:54,832 INFO sqlalchemy.engine.base.Engine COMMIT
> 2012-01-17 07:37:54,832 INFO sqlalchemy.engine.base.Engine BEGIN
> (implicit)
> 2012-01-17 07:37:54,832 INFO sqlalchemy.engine.base.Engine SELECT
> "TEST_ENUM".id AS "TEST_ENUM_id", "TEST_ENUM"."MY_ENUM" AS
> "TEST_ENUM_MY_ENUM"
> FROM "TEST_ENUM"
> WHERE "TEST_ENUM"."MY_ENUM" = :MY_ENUM_1
> 2012-01-17 07:37:54,832 INFO sqlalchemy.engine.base.Engine
> {'MY_ENUM_1': u'avec \xe9'}
> ====================
> avec é
> ====================
> =====================================================
>
>
> What happens with Enum??
>
> --
> 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.
