Using SQLAlchemy 0.7.2 with pyodbc 2.1.9, FreeTDS 0.91, unixODBC 2.3.0
and SQL Server 2008 I find that the supports_unicode_bind may be
incorrectly set to False in the PyODBCConnector.initialize. As a
result a unicode parameter gets encoded as str and to make matters
worse the value gets silently overridden with empty Unicode string
(u'').
Consider a simple table (IDENTITY, NTEXT, VARCHAR(255)) with one
record:
ID, col1, col2
1, 'Łódź', 'abc'.
We will update existing value in col1 to 'Łódź!'.
>>> from sqlalchemy import Column, Sequence, create_engine
>>> from sqlalchemy.types import UnicodeText, Integer, VARCHAR
>>> from sqlalchemy.orm import sessionmaker
>>> from sqlalchemy.ext.declarative import declarative_base
>>>
>>> Base = declarative_base()
>>> metadata = Base.metadata
>>>
>>> class A(Base):
... __tablename__ = 'A'
... id = Column(u'ID', Integer, Sequence('A_PK'),
primary_key=True)
... col1 = Column(u'col1', UnicodeText())
... col2 = Column(u'col2', VARCHAR(255))
...
>>> e =
>>> create_engine('mssql://user:pwd@sqlserverhost:2431/MYDB?driver=FreeTDS&TDS_Version=8.0',
>>> echo=True)
>>> Session=sessionmaker()
>>> s = Session(bind=e)
>>> lodz = u'\u0141\xf3d\u017a'
>>> oa = s.query(A).one()
2011-09-07 17:22:25,260 INFO sqlalchemy.engine.base.Engine SELECT
user_name() as user_name;
2011-09-07 17:22:25,261 INFO sqlalchemy.engine.base.Engine ()
2011-09-07 17:22:25,270 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
2011-09-07 17:22:25,271 INFO sqlalchemy.engine.base.Engine
(u'SPEED_IT',)
2011-09-07 17:22:25,291 INFO sqlalchemy.engine.base.Engine BEGIN
(implicit)
2011-09-07 17:22:25,292 INFO sqlalchemy.engine.base.Engine SELECT [A].
[ID]
AS [A_ID], [A].col1 AS [A_col1], [A].col2 AS [A_col2]
FROM [A]
2011-09-07 17:22:25,292 INFO sqlalchemy.engine.base.Engine ()
>>> oa.col1
u'\u0141\xf3d\u017a'
>>> oa.col2
'abc'
>>> oa.col1 = u'\u0141\xf3d\u017a!'
>>> s.commit()
2011-09-07 17:23:17,016 INFO sqlalchemy.engine.base.Engine UPDATE [A]
SET
col1=? WHERE [A].[ID] = ?
2011-09-07 17:23:17,016 INFO sqlalchemy.engine.base.Engine
('\xc5\x81\xc3\xb3d\xc5\xba!', 1)
2011-09-07 17:23:17,061 INFO sqlalchemy.engine.base.Engine COMMIT
>>> oa.col1
2011-09-07 17:23:24,226 INFO sqlalchemy.engine.base.Engine BEGIN
(implicit)
2011-09-07 17:23:24,227 INFO sqlalchemy.engine.base.Engine SELECT [A].
[ID]
AS [A_ID], [A].col1 AS [A_col1], [A].col2 AS [A_col2]
FROM [A]
WHERE [A].[ID] = ?
2011-09-07 17:23:24,227 INFO sqlalchemy.engine.base.Engine (1,)
u''
Using a patched initialize method with the supports_unicode_binds line
#110 removed the parameter gets passed as Unicode and the database
updates correctly as does the in memory object. Different version
combinations of pyodbc, FreeTDS and SQL may likely yield a different
result so unless a deterministic factor is found I would like to
propose adding parameter bind_unicode to dialect class and connection
url.
Regards and respect,
Victor Olex
http://linkedin.com/in/victorolex
http://twitter.com/agilevic
--
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.
