Re: [sqlalchemy] Could not locate column in row for column
Hello, Mike
thank you for your answer
for now it works fine with the patch create_engine(
description_encoding = 'utf8')
2014-06-20 22:36 GMT+04:00 Mike Bayer :
>
> On 6/20/14, 12:14 PM, Mike Bayer wrote:
> > On 6/20/14, 7:32 AM, Mike Bayer wrote:
> >> NoSuchColumnError: "Could not locate column in row for column
> >>
> '\\u0417\\u0430\\u043a\\u0430\\u0437\\u044b.\\u041d\\u043e\\u043c\\u0435\\u0440
> >> \\u0437\\u0430\\u043a\\u0430\\u0437\\u0430'"
> >>
> >> The problem is i can't really debug this error because it isn't clear
> >> what should be in pk_cols
> >>
> >> Previously in orm/loading.py there is a string
> >> pk_cols = mapper.primary_key # line number 250
> >>
> >> So, pk_cols is a list of Column() objects.
> >> row is a list of values from query
> >> Surely, [row[column] for column in pk_cols] raises error, because
> >> column is a Column() object and can't be index for row. But i can't
> >> imagine how this code may work. Is this a bug?
> >> the ResultProxy contains translation logic that receives Column objects
> >> and locates the data by name. This is documented at
> >> http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html#selecting and
> >> is the primary method by which the ORM relates mapped columns to result
> >> sets.
> >>
> >> In this case the issue is most likely yet another pyodbc + unicode
> >> encoding issue, of which there are many, and often there's no way to
> >> work around. Need to know 1. OS platform 2. FreeTDS version 3. UnixODBC
> >> or iODBC (and what version) 4. pyodbc version 5. SQL Server version. I
> >> can try to test but often these unicode issues aren't easy to resolve
> >> (have you tried pymssql? ).thanks.
> > good news, I can reproduce this, and on my end at least it needs the
> > so-called "description_encoding" workaround. We may have to revisit
> > the defaults on this parameter for modern versions of Pyodbc. The test
> > below produces your error without the param, resolves with it. Please
> > try this out on your create_engine(), thanks.
> >
> > #! coding: utf-8
> >
> > from sqlalchemy import *
> >
> > engine = create_engine("mssql+pyodbc://scott:tiger@ms_2008", echo=True,
> > description_encoding='utf8')
> >
> > colname = u'Заказ.Номер заказа'
> >
> > m = MetaData()
> > t = Table(u"Заказы", m, Column(colname, String(30), key='somecol'))
> >
> > m.drop_all(engine)
> > m.create_all(engine)
> >
> > engine.execute(t.insert().values(somecol='some value'))
> > result = engine.execute(t.select().where(t.c.somecol == 'some value'))
> > row = result.fetchone()
> > print row[t.c.somecol]
> yeah this is the workaround for now, but totally this is a bug back to
> 0.8 and further, should be backported for 0.8, 0.9 and 1.0 in
>
> https://bitbucket.org/zzzeek/sqlalchemy/issue/3091/update-description_encoding-for-pyodbc
> .
> Two different issues located.
>
> --
> 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/svIe6UWMWyU/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.
Re: [sqlalchemy] SQLAlchemy and pymssql and cyrillic names of tables/columns
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 :
> 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 = 5000
>
> 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 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
> ValueErrorTraceback (most recent call last)
> in ()
> > 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, dis
Re: [sqlalchemy] SQLAlchemy and pymssql and cyrillic names of tables/columns
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
ValueErrorTraceback (most recent call last)
in ()
> 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
-> 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)()
: (,
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 :
> yes, make sure you use Python unicode objects in Py2K (e.g. u'thename').
>
>
> On Apr 18, 2014, at 8:06 AM, Belegnar Dragon 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 i
