SQLAlchemy and pymssql and cyrillic names of tables/columns

[Belegnar Dragon]

Hello!

Is it possible to handle with SQLAlchemy mssql database with cyrillic table and column names?

--
WBR,
 TO

[Michael Bayer]

yes, make sure you use Python unicode objects in Py2K (e.g. u’thename’).

--
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 sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Timur Ozheghin]

may you provide an example of this? i've broke my mind for past three days, but can't get this code to work
i'm using latest sqlalchemy and pymssql from pip

engine = create_engine("mssql+pymssql://%s:%s@RTBD/rt?charset=utf8" % (settings.RT_USER, settings.RT_PWD), echo = True, encoding = 'utf8')
metadata = MetaData()
metadata.reflect(engine, only = [u"Заказы",])
orders = metadata.tables[u'Заказы']
engine.execute(orders.select(orders.c[u'Номер заказа'] == u'14-01-0001'))

the exception is
ValueError                                Traceback (most recent call last)
<ipython-input-3-659410bb024c> in <module>()
----> 1 engine.execute(orders.select(orders.c[u'Номер заказа'] == u'14-01-0001'))

python2.7/site-packages/sqlalchemy/engine/base.pyc in execute(self, statement, *multiparams, **params)
   1680
   1681         connection = self.contextual_connect(close_with_result=True)
-> 1682         return connection.execute(statement, *multiparams, **params)
   1683
   1684     def scalar(self, statement, *multiparams, **params):

python2.7/site-packages/sqlalchemy/engine/base.pyc in execute(self, object, *multiparams, **params)
    718                                 type(object))
    719         else:
--> 720             return meth(self, multiparams, params)
    721
    722     def _execute_function(self, func, multiparams, params):

python2.7/site-packages/sqlalchemy/sql/elements.pyc in _execute_on_connection(self, connection, multiparams, params)
    315
    316     def _execute_on_connection(self, connection, multiparams, params):
--> 317         return connection._execute_clauseelement(self, multiparams, params)
    318
    319     def unique_params(self, *optionaldict, **kwargs):

python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_clauseelement(self, elem, multiparams, params)
    815             compiled_sql,
    816             distilled_params,
--> 817             compiled_sql, distilled_params
    818         )
    819         if self._has_events or self.engine._has_events:

python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
    945                                 parameters,
    946                                 cursor,
--> 947                                 context)
    948
    949         if self._has_events or self.engine._has_events:

python2.7/site-packages/sqlalchemy/engine/base.pyc in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1109                                 )
   1110
-> 1111             util.reraise(*exc_info)
   1112
   1113         finally:

python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
    938                                      statement,
    939                                      parameters,
--> 940                                      context)
    941         except Exception as e:
    942             self._handle_dbapi_exception(

python2.7/site-packages/sqlalchemy/engine/default.pyc in do_execute(self, cursor, statement, parameters, context)
    433
    434     def do_execute(self, cursor, statement, parameters, context=None):
--> 435         cursor.execute(statement, parameters)
    436
    437     def do_execute_no_params(self, cursor, statement, context=None):

python2.7/site-packages/pymssql.so in pymssql.Cursor.execute (pymssql.c:6057)()

python2.7/site-packages/_mssql.so in _mssql.MSSQLConnection.execute_query (_mssql.c:9858)()

python2.7/site-packages/_mssql.so in _mssql.MSSQLConnection.execute_query (_mssql.c:9734)()

python2.7/site-packages/_mssql.so in _mssql.MSSQLConnection.format_and_run_query (_mssql.c:10814)()

python2.7/site-packages/_mssql.so in _mssql.MSSQLConnection.format_sql_command (_mssql.c:11042)()

python2.7/site-packages/_mssql.so in _mssql._substitute_params (_mssql.c:18359)()

<type 'str'>: (<type 'exceptions.UnicodeEncodeError'>, UnicodeEncodeError('ascii', u'params dictionary did not contain value for placeholder: \u041d\u043e\u043c\u0435\u0440 \u0437\u0430\u043a\u0430\u0437\u0430_1', 57, 62, 'ordinal not in range(128)'))

SQLAlchemy log is
2014-04-18 22:43:46,413 INFO sqlalchemy.engine.base.Engine SELECT [Заказы].[Номер заказа], <... a lot of fields here ...>
FROM [Заказы]
WHERE [Заказы].[Номер заказа] = %(Номер заказа_1)s
2014-04-18 22:43:46,414 INFO sqlalchemy.engine.base.Engine {'\xd0\x9d\xd0\xbe\xd0\xbc\xd0\xb5\xd1\x80 \xd0\xb7\xd0\xb0\xd0\xba\xd0\xb0\xd0\xb7\xd0\xb0_1': u'14-01-0001'}
2014-04-18 22:43:46,415 INFO sqlalchemy.engine.base.Engine ROLLBACK

--
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/Cv8DzGRzA5M/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
Счастливой радости!
 Тимур Ожегин

[Michael Bayer]

its not clear pymssql supports this fully yet.   Additionally you need to make sure your freetds.conf file is set up correctly.    Overall, using non-ASCII identifiers is a tough road to travel for sure and if it can be avoided, you should.   However it can be done.

I’m not able to get the most recent pymssql to work, but pyodbc does.  Versions are very important here:

1. UnixODBC 2.3.0

2. FreeTDS 0.91

3. Pyodbc 3.0.7

4. Linux, not OSX, OSX has tons of problems with tds / pyodbc, I’m running on a Fedora 14 machine here

Freetds setting:

[sqlserver_2008_vmware]

        host = 172.16.248.142

        port = 1213

        tds version = 7.2

        client charset = UTF8

        text size = 50000000

Test script:

# coding: utf-8

from sqlalchemy import create_engine, MetaData, Table, Column, String

e = create_engine("mssql+pyodbc://scott:tiger@ms_2008", echo=True)

#e = create_engine("mssql+pymssql://scott:ti...@172.16.248.142:1213", echo=True)

m = MetaData()

t = Table(u'Заказы', m, Column(u'Номер заказа', String(50)))

m.drop_all(e)

m.create_all(e)

orders = m.tables[u'Заказы']

e.execute(orders.select(orders.c[u'Номер заказа'] == u'14-01-0001'))

part of the output:

CREATE TABLE [Заказы] (

[Номер заказа] VARCHAR(50) NULL

)

2014-03-31 20:57:16,266 INFO sqlalchemy.engine.base.Engine ()

2014-03-31 20:57:16,268 INFO sqlalchemy.engine.base.Engine COMMIT

2014-03-31 20:57:16,270 INFO sqlalchemy.engine.base.Engine SELECT [Заказы].[Номер заказа] 

FROM [Заказы] 

WHERE [Заказы].[Номер заказа] = ?

2014-03-31 20:57:16,270 INFO sqlalchemy.engine.base.Engine (u'14-01-0001',)

[Timur Ozheghin]

thank you for your answer, Michael!

definitely, pyodbc does not work either. but in different way =(

i'm working on ubuntu 12. i've install unixodbc from sources and now have version 2.3

other versions are correct

but it does not help

here description is

testing connection
$ isql -v DB user pwd
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select [Заказы].[Номер заказа] from [Заказы] where [Заказы].[Номер заказа] = '14-01-0001'
+------------------------+
| Номер заказа|
+------------------------+
| 14-01-0001             |
+------------------------+
SQLRowCount returns 1
1 rows fetched

next i'm executing the code
engine = create_engine("mssql+pyodbc://%s:%s@DB" % (settings.RT_USER, settings.RT_PWD), echo = True, encoding = 'utf8')
metadata = MetaData()
metadata.reflect(engine, only = [u"Заказы",]) # metadata is successfuly reflected

orders = metadata.tables[u'Заказы']

res = engine.execute(orders.select(orders.c[u'Номер заказа'] == u'14-01-0001')) # error generator

sqlalchemy select looks ok
INFO sqlalchemy.engine.base.Engine SELECT [Заказы].[Номер заказа], <... a lot of fields ...>
FROM [Заказы]
WHERE [Заказы].[Номер заказа] = ?
2014-04-19 01:43:14,825 INFO sqlalchemy.engine.base.Engine (u'14-01-0001',)
2014-04-19 01:43:14,828 INFO sqlalchemy.engine.base.Engine ROLLBACK

but exception follows
    res = engine.execute(orders.select(orders.c[u'Номер заказа'] == u'14-01-0001'))
  File "python2.7/site-packages/sqlalchemy/engine/base.py", line 1682, in execute
    return connection.execute(statement, *multiparams, **params)
  File "python2.7/site-packages/sqlalchemy/engine/base.py", line 720, in execute
    return meth(self, multiparams, params)
  File "python2.7/site-packages/sqlalchemy/sql/elements.py", line 317, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "python2.7/site-packages/sqlalchemy/engine/base.py", line 817, in _execute_clauseelement
    compiled_sql, distilled_params
  File "python2.7/site-packages/sqlalchemy/engine/base.py", line 947, in _execute_context
    context)
  File "python2.7/site-packages/sqlalchemy/engine/base.py", line 1108, in _handle_dbapi_exception
    exc_info
  File "python2.7/site-packages/sqlalchemy/util/compat.py", line 185, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "python2.7/site-packages/sqlalchemy/engine/base.py", line 940, in _execute_context
    context)
  File "python2.7/site-packages/sqlalchemy/engine/default.py", line 435, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42S02', "[42S02] [FreeTDS][SQL Server]Invalid object name '\xd0\x97\xd0\xb0\xd0\xba\xd0\xb0\xd0\xb7\xd1\x8b'. (208) (SQLExecDirectW)") 'SELECT [\xd0\x97\xd0\xb0\xd0\xba\xd0\xb0\xd0\xb7\xd1\x8b].[\xd0\x9d\xd0\xbe\xd0\xbc\xd0\xb5\xd1\x80 \xd0\xb7\xd0\xb0\xd0\xba\xd0\xb0\xd0\xb7\xd0\xb0], [\xd0\x97\xd0\xb0\xd0\xba\xd0\x

<... a lot of same garbage here ...>

The same error is in the odbc log file
[ODBC][11715][1397857605.657008][SQLPrepare.c][192]
        Entry:           
            Statement = 0x9ffc320           
            SQL = [SELECT [Заказы].[Номер заказа], [Заказы].[Дата приема], [Заказы].[Срок сдачи], [...][length = 2906 (SQL_NTS)]
[ODBC][11715][1397857605.657059][SQLPrepare.c][367]
        Exit:[SQL_SUCCESS]
[ODBC][11715][1397857605.657087][SQLNumParams.c][140]
        Entry:           
            Statement = 0x9ffc320           
            Param Count = 0xbfab7c6c
[ODBC][11715][1397857605.657116][SQLNumParams.c][224]
        Exit:[SQL_SUCCESS]               
            Count = 0xbfab7c6c -> 1
[ODBC][11715][1397857605.657150][SQLBindParameter.c][213]
        Entry:           
            Statement = 0x9ffc320           
            Param Number = 1           
            Param Type = 1           
            C Type = -8 SQL_C_WCHAR           
            SQL Type = -9 SQL_WVARCHAR           
            Col Def = 10           
            Scale = 0           
            Rgb Value = 0x9f18640           
            Value Max = 0           
            StrLen Or Ind = 0x9b6ca8c
[ODBC][11715][1397857605.657182][SQLBindParameter.c][393]
        Exit:[SQL_SUCCESS]
[ODBC][11715][1397857605.657210][SQLExecute.c][183]
        Entry:           
            Statement = 0x9ffc320
[ODBC][11715][1397857605.659126][SQLExecute.c][344]
        Exit:[SQL_ERROR]
        DIAG [42S02] [FreeTDS][SQL Server]Invalid object name 'Заказы'.

        DIAG [42000] [FreeTDS][SQL Server]Statement(s) could not be prepared.

[ODBC][11715][1397857605.659246][SQLGetDiagRec.c][739]
        Entry:               
            Statement = 0x9ffc320               
            Rec Number = 1               
            SQLState = 0xbfab7c46               
            Native = 0xbfab7838               
            Message Text = 0xbfab7840               
            Buffer Length = 1023               
            Text Len Ptr = 0xbfab783e
[ODBC][11715][1397857605.659285][SQLGetDiagRec.c][776]
        Exit:[SQL_SUCCESS]                   
            SQLState = 42S02                   
            Native = 0xbfab7838 -> 208                   
            Message Text = [[FreeTDS][SQL Server]Invalid object name 'Заказы'.]

It looks like odbc looses active database and switched somewhere else. Because as you can see earlier isql works fine with this datasource and similar "select" query.

[Michael Bayer]

what happens if you run the SQL as a string through engine.execute()?

engine.execute(u“select [Заказы].[Номер заказа] from [Заказы] where [Заказы].[Номер заказа] = '14-01-0001’”)

?

failing that, what about raw pyodbc?

conn = pyodbc.connect(…)

cursor = conn.cursor()

cursor.execute(u“select [Заказы].[Номер заказа] from [Заказы] where [Заказы].[Номер заказа] = '14-01-0001’”)

(make sure to always use a u”” string)

[Timur Ozheghin]

sqla fails with mostly the same error
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42S02', "[42S02] [FreeTDS][SQL Server]Invalid object name '\xd0\x97\xd0\xb0\xd0\xba\xd0\xb0\xd0\xb7\xd1\x8b'. (208) (SQLExecDirectW)") "select [\xd0\x97\xd0\xb0\xd0\xba\xd0\xb0\xd0\xb7\xd1\x8b].[\xd0\x9d\xd0\xbe\xd0\xbc\xd0\xb5\xd1\x80 \xd0\xb7\xd0\xb0\xd0\xba\xd0\xb0\xd0\xb7\xd0\xb0] from [\xd0\x97\xd0\xb0\xd0\xba\xd0\xb0\xd0\xb7\xd1\x8b] where [\xd0\x97\xd0\xb0\xd0\xba\xd0\xb0\xd0\xb7\xd1\x8b].[\xd0\x9d\xd0\xbe\xd0\xbc\xd0\xb5\xd1\x80 \xd0\xb7\xd0\xb0\xd0\xba\xd0\xb0\xd0\xb7\xd0\xb0] = '14-01-0001'" ()

pyodbc fails either, but exception is very strange

cursor.execute(u"select [Заказы].[Номер заказа] from [Заказы] where [Заказы].[Номер заказа] = '14-01-0001'")

ProgrammingError: ('42S02', "[42S02] [FreeTDS][SQL Server]Invalid object name '\x170:07K'. (208) (SQLExecDirectW)")

i can handle this with django.utils.encoding.smart_str https://github.com/django/django/blob/master/django/utils/encoding.py#L107
cursor.execute(smart_str(u"select [Заказы].[Номер заказа] from [Заказы] where [Заказы].[Номер заказа] = '14-01-0001'"))

then the error is the same

cursor.execute(smart_str(u"select [Заказы].[Номер заказа] from [Заказы] where [Заказы].[Номер заказа] = '14-01-0001'"))
ProgrammingError: ('42S02', "[42S02] [FreeTDS][SQL Server]Invalid object name '\xd0\x97\xd0\xb0\xd0\xba\xd0\xb0\xd0\xb7\xd1\x8b'. (208) (SQLExecDirectW)")

odbc log
[ODBC][17877][1397892797.194928][SQLExecDirect.c][236]
        Entry:           
            Statement = 0x8d6e690           
            SQL = [select [Заказы].[Номер заказа] from [Заказы] where [Заказы].[Номер заказа] = '14-01-0001...][length = 129 (SQL_NTS)]
[ODBC][17877][1397892797.196146][SQLExecDirect.c][499]

        Exit:[SQL_ERROR]
        DIAG [42S02] [FreeTDS][SQL Server]Invalid object name 'Заказы'.

[ODBC][17877][1397892797.196303][SQLGetDiagRec.c][739]
        Entry:               
            Statement = 0x8d6e690               
            Rec Number = 1               
            SQLState = 0xbfb263d6               
            Native = 0xbfb25fc8               
            Message Text = 0xbfb25fd0               
            Buffer Length = 1023               
            Text Len Ptr = 0xbfb25fce
[ODBC][17877][1397892797.196374][SQLGetDiagRec.c][776]

        Exit:[SQL_SUCCESS]                   
            SQLState = 42S02                   

            Native = 0xbfb25fc8 -> 208                   

            Message Text = [[FreeTDS][SQL Server]Invalid object name 'Заказы'.]

[Timur Ozheghin]

definitly, this code
conn = pyodbc.connect("DSN=RTBD;UID=%s;PWD=%s" % (settings.RT_USER, settings.RT_PWD))
cursor = conn.cursor()
res = cursor.execute(smart_str(u"use rt; select [Заказы].[Номер заказа] from [Заказы] where [Заказы].[Номер заказа] = '14-01-0001'"))

works without errors for sqla engine.execute() and for pure pyodbc, but returns nothing: res.rowcount == -1

but there is a row in the table
$ tsql -S RTBD -U user
Password:
locale is "ru_RU.UTF-8"
locale charset is "UTF-8"
using default charset "UTF8"
1> select [Заказы].[Номер заказа] from [Заказы] where [Заказы].[Номер заказа] = '14-01-0001'
2> go
Msg 208 (severity 16, state 1) from RT-BD Line 1:

    "Invalid object name 'Заказы'."

1> use rt; select [Заказы].[Номер заказа] from [Заказы] where [Заказы].[Номер заказа] = '14-01-0001'
2> go
Номер заказа
14-01-0001
(1 row affected)

[Timur Ozheghin]

meanwhile, i suppose the problem is localized in freetds

because of this script

$ tsql -S RTBD -U

Password:
locale is "ru_RU.UTF-8"
locale charset is "UTF-8"
using default charset "UTF8"
1> select [Заказы].[Номер заказа] from [Заказы] where [Заказы].[Номер заказа] = '14-01-0001'
2> go
Msg 208 (severity 16, state 1) from RT-BD Line 1:
    "Invalid object name 'Заказы'."
1> use rt; select [Заказы].[Номер заказа] from [Заказы] where [Заказы].[Номер заказа] = '14-01-0001'
2> go
Номер заказа
14-01-0001
(1 row affected)

but database is surely mentioned in odbc.ini
$ cat /etc/odbc.ini
[RTBD]
driver          = FreeTDS
server          = 192.168.1.9
port            = 1433
database        = rt
tds_version     = 7.2
client_charset  = UTF8

[Michael Bayer]

yeah, so you’re hitting the same wall with FreeTDS that I’ve banged my head against for many, many years.     I’ve never been able to fully overcome all the unicode issues with freetds, pyodbc, different ODBC implementations, etc.     If you can’t get the query through FreeTDS then you can’t go much further.  *Unless* you want to perhaps try Microsoft’s ODBC driver for Linux - nobody is using that yet to my knowledge, but it would be great if it actually worked.

i can handle this with django.utils.encoding.smart_strhttps://github.com/django/django/blob/master/django/utils/encoding.py#L107

To unsubscribe from this topic, visithttps://groups.google.com/d/topic/sqlalchemy/Cv8DzGRzA5M/unsubscribe.

To unsubscribe from this group and all its topics, send an email tosqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
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 tosqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
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, visithttps://groups.google.com/d/topic/sqlalchemy/Cv8DzGRzA5M/unsubscribe.

To unsubscribe from this group and all its topics, send an email tosqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
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 tosqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
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/Cv8DzGRzA5M/unsubscribe.
To unsubscribe from this group and all its topics, send an email tosqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

-- 
Счастливой радости!
 Тимур Ожегин

-- 
Счастливой радости!
 Тимур Ожегин

-- 
Счастливой радости!
 Тимур Ожегин

[Timur Ozheghin]

thx for the participation, anyway

[Marc Abramowitz]

We have a unit test in pymssql that uses Cyrillic with a stored proc and it fails still I think. I think we were able to get regular queries to work if we used a FreeTDS from the gitorious master branch. I think they have a bunch of unreleased Unicode fixes - have you tried that?

Might want to post on the FreeTDS list if you haven't already.

http://www.freetds.org/userguide/mailinglist.htm

Marc

...

[Timur Ozheghin]

yes, i've posted the bug to the freetds mail list this morning, but didn't get the answer yet

to be clear, i didn't try master branch of freetds either. hope, i'll have a time at the weekend to build and try it

will keep you informed about my progress, thx

To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/Cv8DzGRzA5M/unsubscribe.

To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Marc Abramowitz]

Cool, let us know what happens.

It's possible using the FreeTDS master branch might fix this -- that would be interesting for folks to know.

[Timur Ozheghin]

Hello everybody.

To be clear, i've done with pyodbc connection error. The point is in the config files, but for now i can't clearly define what is that change wich makes positive result.

Working configs are:
$ cat .odbc.ini
[RTBD]
driver          = FreeTDS
servername      = RTTDS
port            = 1433
database        = db
tds_version     = 7.0
client_charset  = UTF8

$ cat .freetds.conf
[global]
    port = 1433
    tds version = 7.0
    dump file = /tmp/freetds.log
    debug flags = 0xffff

[RTTDS]
        host = dns.hostname
        port = 1433
        tds version = 7.0

        client charset = UTF8
        text size = 50000000

I'll continue in next post because topic error is still actual.

[Timur Ozheghin]

The following code
engine = create_engine("mssql+pyodbc://%s:%s@RTBD" % (settings.RT_USER, settings.RT_PWD), echo = True)

metadata = MetaData()
metadata.reflect(engine, only = [u"Заказы",])
orders = metadata.tables[u'Заказы']

class Order(object):
    pass

mapper(Order, orders)

raises following error
  File "python2.7/site-packages/sqlalchemy/orm/instrumentation.py", line 246, in install_descriptor
    setattr(self.class_, key, inst)
UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-4: ordinal not in range(128)

because key is unicode string u'\u041d\u043e\u043c\u0435\u0440 \u0437\u0430\u043a\u0430\u0437\u0430' witch can't be used in setattr.

Help me, please, how can i handle this?

[Michael Bayer]

On Apr 23, 2014, at 9:20 AM, Timur Ozheghin <tozh...@gmail.com> wrote:

The following code
engine = create_engine("mssql+pyodbc://%s:%s@RTBD" % (settings.RT_USER, settings.RT_PWD), echo = True)
metadata = MetaData()
metadata.reflect(engine, only = [u"Заказы",])
orders = metadata.tables[u'Заказы']
class Order(object):
    pass

mapper(Order, orders)

raises following error
  File "python2.7/site-packages/sqlalchemy/orm/instrumentation.py", line 246, in install_descriptor
    setattr(self.class_, key, inst)
UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-4: ordinal not in range(128)

because key is unicode string u'\u041d\u043e\u043c\u0435\u0440 \u0437\u0430\u043a\u0430\u0437\u0430' witch can't be used in setattr.

Help me, please, how can i handle this?

If you intend to map these tables to a Python class, each of those Column objects needs to be associated with a non-unicode key.    This can be established either by mapping the Column objects explicitly with a different attribute name, or by setting the .key attribute on each Column.  To intercept the columns as they are reflected you use the column_reflect event:  http://docs.sqlalchemy.org/en/latest/core/events.html?highlight=column_reflect#sqlalchemy.events.DDLEvents.column_reflect

Within the event, set the “key” entry within the column_info dictionary to a name that is acceptable for a Python attribute name.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

[Timur Ozheghin]

Thank you for advice. Unfortunately, it does not work for me.

Reflected table does have one column renamed ('Номер заказа' -> 'number'), but query to the table looks strange in WHERE clause
2014-05-19 17:33:04,747 INFO sqlalchemy.engine.base.Engine SELECT < ... list of columns w/ original names, a lot of them ... >
FROM [Заказы]
WHERE 0

The error is:
ProgrammingError: (ProgrammingError) ('42000', "[42000] [FreeTDS][SQL Server]Line 3: Incorrect syntax near '0'. (170) (SQLExecDirectW)") < ... query text goes here, the same as above ... >

The code is:
engine = create_engine("mssql+pyodbc://%s:%s@BD" % (settings.RT_USER, settings.RT_PWD), echo = True)
metadata = MetaData()
def order_column_name(inspector, table, column_info):
    if column_info['name'] == u'Номер заказа':
        column_info['key'] = u'number'

lorders = Table(u'Заказы', metadata, autoload = True, autoload_with = engine, listeners = [('column_reflect', order_column_name)])
sess = Session(engine)
q = sess.query(Order)
q.filter(u'number' == u'14-01-0001').all()

[Michael Bayer]

It can be very challenging to get FreeTDS to work with non-ASCII characters in conjunction with Pyodbc, particularly when involving the information schema tables.    Below is a full test script which succeeds as long as the drivers are up to date and configured correctly.  I cannot get this script to complete in OSX, for example, as I am only using FreeTDS 0.82 on OSX and I can’t get these kinds of features to work with 0.91 and OSX in any case - there are long-standing issues that remain unfixed in Pyodbc.    However, if I run it against FreeTDS 0.91 on a Linux host, then the script completes.    Versions are important here:

SQLAlchemy 0.9.4

FreeTDS 0.91

pyodbc 3.0.7

python 2.7

tds version (string you put in freetds.conf) = 7.2

the relevant section of FreeTDS conf includes:

[sqlserver_2008_vmware]

        host = 172.16.248.142

        port = 1213

        # for freetds 0.91 use 7.2

        tds version = 7.2

        client charset = UTF8

        text size = 50000000

the output is attached.

#coding: utf-8

from sqlalchemy import create_engine, MetaData, Table

engine = create_engine("mssql+pyodbc://scott:tiger@ms_2008", echo=True)

conn = engine.connect()

trans = conn.begin()

try:

    conn.execute(u"""drop table [Заказы]""")

except:

    pass

conn.execute(u"""

    create table [Заказы] ([Номер заказа] varchar(30))

""")

metadata = MetaData()

def order_column_name(inspector, table, column_info):

    if column_info['name'] == u'Номер заказа':

        column_info['key'] = u'number'

lorders = Table(u'Заказы', metadata, autoload=True, autoload_with=conn,

            listeners = [('column_reflect', order_column_name)])

conn.execute(lorders.select().where(lorders.c.number == 'hi')).fetchall()

trans.rollback()

[Timur Ozheghin]

Yes, it works in this way
conn.execute(lorders.select().where(lorders.c.number == 'hi')).fetchall()

session.Query(lorders).all() works fine also, i suppose - there is no errors at start, but i haven't wait a long enough for result

But doesn't work with session queries with filter like

session.Query(lorders).filter(u'number' == '14-01-0001').all()

sqla inserts 'where 0' instead of query params

On May 19, 2014, at 9:51 AM, Timur Ozheghin <tozh...@gmail.com> wrote:

Thank you for advice. Unfortunately, it does not work for me.

Reflected table does have one column renamed ('Номер заказа' -> 'number'), but query to the table looks strange in WHERE clause
2014-05-19 17:33:04,747 INFO sqlalchemy.engine.base.Engine SELECT < ... list of columns w/ original names, a lot of them ... >
FROM [Заказы] 
WHERE 0

The error is:
ProgrammingError: (ProgrammingError) ('42000', "[42000] [FreeTDS][SQL Server]Line 3: Incorrect syntax near '0'. (170) (SQLExecDirectW)") < ... query text goes here, the same as above ... >

The code is:
engine = create_engine("mssql+pyodbc://%s:%s@BD" % (settings.RT_USER, settings.RT_PWD), echo = True)

metadata = MetaData()
def order_column_name(inspector, table, column_info):
    if column_info['name'] == u'Номер заказа':
        column_info['key'] = u'number'

        client charset = UTF8
        text size = 50000000

[sqlserver_2008_vmware]

        host = 172.16.248.142

        port = 1213

        tds version = 7.2

        client charset = UTF8

        text size = 50000000

Test script:

# coding: utf-8

from sqlalchemy import create_engine, MetaData, Table, Column, String

e = create_engine("mssql+pyodbc://scott:tiger@ms_2008", echo=True)

...

[Michael Bayer]

Sent from my iPhone

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

[Timur Ozheghin]

Sorry, there is no text in your message..

[Belegnar Dragon]

Everything works fine with freetds compiled at git master

пятница, 18 апреля 2014 г., 16:06:30 UTC+4 пользователь Belegnar Dragon написал: