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)
On Apr 18, 2014, at 5:52 PM, Timur Ozheghin <tozheg...@gmail.com> wrote:
> 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.
--
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.
