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.
2014-04-18 23:13 GMT+04:00 Michael Bayer <mike...@zzzcomputing.com>:
> 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:tiger@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',)
>
>
>
>
>
>
>
>
> On Apr 18, 2014, at 2:48 PM, Timur Ozheghin <tozheg...@gmail.com> wrote:
>
> 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
>
>
>
>
> 2014-04-18 18:47 GMT+04:00 Michael Bayer <mike...@zzzcomputing.com>:
>
>> yes, make sure you use Python unicode objects in Py2K (e.g. u'thename').
>>
>>
>> On Apr 18, 2014, at 8:06 AM, Belegnar Dragon <tozheg...@gmail.com> wrote:
>>
>> Hello!
>>
>> Is it possible to handle with SQLAlchemy mssql database with cyrillic
>> table and column names?
>>
>> --
>> WBR,
>> TO
>>
>>
>> --
>> 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+unsubscr...@googlegroups.com.
>>
>> To post to this group, send email to sqlalchemy@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 to
>> sqlalchemy+unsubscr...@googlegroups.com.
>> To post to this group, send email to sqlalchemy@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 to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@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 to
> sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@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
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
