Re: [sqlalchemy] Re: Elixir: Enum and accented characters (non-ASCII character)

[Michael Bayer]

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')

Re: [sqlalchemy] Re: Elixir: Enum and accented characters (non-ASCII character)

[Michael Bayer]

On Jan 17, 2012, at 12:29 PM, Michael Bayer wrote:

 
 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.

It's all working for me:

NLS_LANG=AMERICAN_AMERICA.UTF8
cx_Oracle 5.1.1

script, tests creating the constraint, inserting, selecting, matching on the 
column:

# -*- coding: utf-8 -*-

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base= declarative_base()

class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)

value = Column(Unicode(100),
Enum(u'avec é',
 u'avec è',
 u'avec à')
)

e = create_engine(oracle://scott:tiger@/xe, echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)

s = Session(e)

s.add(Foo(id=1, value=u'avec è'))
s.commit()
s.close()

f = s.query(Foo).filter(Foo.value==u'avec è').first()
assert f.value == u'avec è'

output:

2012-01-17 17:55:49,916 INFO sqlalchemy.engine.base.Engine SELECT USER FROM DUAL
2012-01-17 17:55:49,917 INFO sqlalchemy.engine.base.Engine {}
2012-01-17 17:55:49,922 INFO sqlalchemy.engine.base.Engine SELECT table_name 
FROM all_tables WHERE table_name = :name AND owner = :schema_name
2012-01-17 17:55:49,922 INFO sqlalchemy.engine.base.Engine {'name': u'FOO', 
'schema_name': u'SCOTT'}
2012-01-17 17:55:49,928 INFO sqlalchemy.engine.base.Engine 
DROP TABLE foo
2012-01-17 17:55:49,928 INFO sqlalchemy.engine.base.Engine {}
2012-01-17 17:55:49,963 INFO sqlalchemy.engine.base.Engine COMMIT
2012-01-17 17:55:49,965 INFO sqlalchemy.engine.base.Engine SELECT table_name 
FROM all_tables WHERE table_name = :name AND owner = :schema_name
2012-01-17 17:55:49,965 INFO sqlalchemy.engine.base.Engine {'name': u'FOO', 
'schema_name': u'SCOTT'}
2012-01-17 17:55:49,970 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE foo (
id INTEGER NOT NULL, 
value NVARCHAR2(100), 
PRIMARY KEY (id), 
CHECK (value IN ('avec é', 'avec è', 'avec à'))
)


2012-01-17 17:55:49,970 INFO sqlalchemy.engine.base.Engine {}
2012-01-17 17:55:50,131 INFO sqlalchemy.engine.base.Engine COMMIT
2012-01-17 17:55:50,133 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2012-01-17 17:55:50,134 INFO sqlalchemy.engine.base.Engine INSERT INTO foo (id, 
value) VALUES (:id, :value)
2012-01-17 17:55:50,134 INFO sqlalchemy.engine.base.Engine {'id': 1, 'value': 
u'avec \xe8'}
2012-01-17 17:55:50,137 INFO sqlalchemy.engine.base.Engine COMMIT
2012-01-17 17:55:50,139 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2012-01-17 17:55:50,141 INFO sqlalchemy.engine.base.Engine SELECT foo_id, 
foo_value 
FROM (SELECT foo.id AS foo_id, foo.value AS foo_value 
FROM foo 
WHERE foo.value = :value_1) 
WHERE ROWNUM = :ROWNUM_1
2012-01-17 17:55:50,141 INFO sqlalchemy.engine.base.Engine {'ROWNUM_1': 1, 
'value_1': u'avec \xe8'}



-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.