Could not locate column in row for column

[Belegnar Dragon]

Hello.

SQLAlchemy 0.9.4

In this code

def unidecode_column_name(inspector, table, column_info):
    column_info['key'] = unidecode(column_info['name']).replace(u' ', u'_')

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

sess = Session(engine)
q = sess.query(Order)
q.filter_by(Kod_zakazchika = u'F07301m').all()

I've got the following error

NoSuchColumnError                         Traceback (most recent call last)
<ipython-input-2-83aa85e1bb5a> in <module>()
----> 1 q.filter_by(Kod_zakazchika = u'F07301m').all()

local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in all(self)
   2290
   2291         """
-> 2292         return list(self)
   2293
   2294     @_generative(_no_clauseelement_condition)

local/lib/python2.7/site-packages/sqlalchemy/orm/loading.py in instances(query, cursor, context)
     70                 process[0](row, rows)
     71         elif single_entity:
---> 72             rows = [process[0](row, None) for row in fetch]
     73         else:
     74             rows = [util.KeyedTuple([proc(row, None) for proc in process],

local/lib/python2.7/site-packages/sqlalchemy/orm/loading.py in _instance(row, result)
    359             identitykey = (
    360                             identity_class,
--> 361                             tuple([row[column] for column in pk_cols])
    362                         )
    363

local/lib/python2.7/site-packages/sqlalchemy/engine/result.pyc in _key_fallback(self, key, raiseerr)
    330                 raise exc.NoSuchColumnError(
    331                     "Could not locate column in row for column '%s'" %
--> 332                         expression._string_or_unprintable(key))
    333             else:
    334                 return None

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?

--
WBR,
 Belegnar

[Mike Bayer]

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.

[Mike Bayer]

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]

[Mike Bayer]

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.

[Timur Ozheghin]

Hello, Mike

thank you for your answer

for now it works fine with the patch create_engine(.... description_encoding = 'utf8')

--
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+...@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.

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