[sqlalchemy] Could not locate column in row for column
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
--
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] hybrid_properties and literals
On Fri, Jun 20, 2014 at 5:02 AM, AM ams@gmail.com wrote:
On 06/19/2014 10:24 AM, Mike Bayer wrote:
On 6/19/14, 1:05 PM, AM wrote:
What I am storing is things like string versions of lists, tuples and
dicts, for e.g.:
str([1, 2, 3])
str({'a':1}
and so on. ast.literal_eval will only parse those and return those, it
does not evaluate expressions and statements so no real code at all.
I got around this issue by creating a PythonASTLiteralColumn based on
the example in the docs and that worked perfectly.
Having said that I would still like to understand how to approach a
situation where I want a hybrid property that is a normal python data
type, if that's possible at all?
SQLAlchemy is a bridge between Python and SQL but there is no magic in
creating the thing on each side of that bridge. If you want a SQL
query that interrogates a column of data in a way that is meaningful
regarding some kind of in-Python behavior, you need to decide how that
behavior will be expressed in your SQL query. The hybrid will work
fine at the instance level but if you want it to behave meaningfully in
a SQL query you'd first need to know what the SELECT statement you want
will actually look like in terms of raw SQL.
Ah ok, I see what you mean. In my particular case I really don't want
anything special. Basically if I have a table like I mentioned before:
class SystemModel(BaseModel):
__tablename__ = 'system'
class_number = DB.Column(DB.Integer, DB.ForeignKey(
ClassModel.get_fk('number')), primary_key=True)
name = DB.Column(DB.String, nullable=False)
_ports = DB.Column('ports', DB.String)
The only queries I am going to be running at the SQL level are of the form:
select _ports from system where _ports is not null;
select _ports from system where class_number = 1
update system set _ports=[(1, 2, 3), ...] where class_number = 1
inserts and deletes.
What I wanted at the python end was that if I ran this query
select _ports from system where class_number = 1
I would get
result.ports = [(1, 2, 3), ...] # python list
instead of
result.ports = [(1, 2, 3), ...] # python string
As I mentioned I can do that via the custom column type, but wanted to find
out if there was an easier way that I was missing.
Thanks for all your help.
AM
I'm not sure this is really the intended use for hybrid properties.
Hybrid properties exist mostly to allow you to do clever things at
query time (with the class-level SystemModel.ports attribute),
whereas all you seem to want is a way of transparently converting a
python data structure to and from a string. For that, I think you want
a TypeDecorator, such as the JSON example in the docs:
http://docs.sqlalchemy.org/en/rel_0_9/core/types.html#marshal-json-strings
Hope that helps,
Simon
--
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] Could not locate column in row for column
On 6/20/14, 3:59 AM, Belegnar Dragon wrote:
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?
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.
--
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] correlating related deletes
thanks! -- 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] Could not locate column in row for column
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] -- 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] Could not locate column in row for column
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 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.
[sqlalchemy] A question regarding hybrid properties, relationships and schema changes
So in the ongoing improvement of one of our internal databases, we created
a new table named 'environments' whose SQLA code looks something like
this:
class Environment(Base):
__tablename__ = 'environments'
id = Column(u'environmentID', INTEGER(), primary_key=True)
environment = Column(String(length=15), nullable=False, unique=True)
env = Column(String(length=12), nullable=False, unique=True)
domain = Column(String(length=32), nullable=False, unique=True)
prefix = Column(String(length=1), nullable=False)
Two of our tables recently needed conversion to stop using their own local
'environment' column to using this table. The first part's been put in
place
(a new foreign key for 'environment_id'), but to prevent large swaths of
code
from needing changes, a thought of using a hybrid property might allow the
change to be hidden (until the code using it had been rewritten at least).
My naive attempt was the following (just the relevant snippet):
environment_obj = relationship('Environment')
@hybrid_property
def environment(self):
return self.environment_obj.environment
Unfortunately (and in hindsight for obvious reasons), this code doesn't
work,
but a very brief conversation with someone on the #sqlalchemy channel on
Freenode indicated there was no way to do this and all the relevant code
must be reworked. While it's only a few dozen places this occurs, I can see
this coming up again in the future as further schema refactorings occur, so
I turn to those with more expertise to find out if there is a way to
accomplish
what I desire, or if there's really no hope. :) Any insight would be
greatly
appreciated.
--
- Ken Lareau
--
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.
[sqlalchemy] Re: A question regarding hybrid properties, relationships and schema changes
I think this might work... 1. remove the 'environment' column 2. set up an 'Association Proxy' to map `environment_obj.environment` column as the table's `environment` property -- 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] A question regarding hybrid properties, relationships and schema changes
On 6/20/14, 3:38 PM, Ken Lareau wrote:
So in the ongoing improvement of one of our internal databases, we created
a new table named 'environments' whose SQLA code looks something like
this:
class Environment(Base):
__tablename__ = 'environments'
id = Column(u'environmentID', INTEGER(), primary_key=True)
environment = Column(String(length=15), nullable=False, unique=True)
env = Column(String(length=12), nullable=False, unique=True)
domain = Column(String(length=32), nullable=False, unique=True)
prefix = Column(String(length=1), nullable=False)
Two of our tables recently needed conversion to stop using their own local
'environment' column to using this table. The first part's been put
in place
(a new foreign key for 'environment_id'), but to prevent large swaths
of code
from needing changes, a thought of using a hybrid property might allow the
change to be hidden (until the code using it had been rewritten at least).
My naive attempt was the following (just the relevant snippet):
environment_obj = relationship('Environment')
@hybrid_property
def environment(self):
return self.environment_obj.environment
Unfortunately (and in hindsight for obvious reasons), this code
doesn't work,
what does doesn't work mean here? This will work at the instance
level. At the query level, not so much, that's true, if you truly want
no code to change you'd need to implement an @expression here that's a
little inefficient, as it needs to do a correlated subq:
class HasEnv(Base):
__tablename__ = 'has_env'
id = Column(INTEGER, primary_key=True)
environment_id = Column(ForeignKey('environments.environmentID'))
environment_obj = relationship('Environment')
@hybrid_property
def environment(self):
return self.environment_obj.environment
@environment.expression
def environment(cls):
return select([Environment.environment]).\
where(Environment.id == cls.environment_id).correlate(cls).\
as_scalar()
s = Session()
print s.query(HasEnv).filter(HasEnv.environment == 'some env')
output:
SELECT has_env.id AS has_env_id, has_env.environment_id AS
has_env_environment_id
FROM has_env
WHERE (SELECT environments.environment
FROM environments
WHERE environments.environmentID = has_env.environment_id) = :param_1
wont perform well from a SQL perspective but will do the job...
but a very brief conversation with someone on the #sqlalchemy channel on
Freenode indicated there was no way to do this and all the relevant code
must be reworked. While it's only a few dozen places this occurs, I
can see
this coming up again in the future as further schema refactorings
occur, so
I turn to those with more expertise to find out if there is a way to
accomplish
what I desire, or if there's really no hope. :) Any insight would be
greatly
appreciated.
I don't know how to fix this issue with IRC and stackoverflow that
people constantly are getting bad information.
--
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] A question regarding hybrid properties, relationships and schema changes
On Fri, Jun 20, 2014 at 1:23 PM, Mike Bayer mike...@zzzcomputing.com
wrote:
On 6/20/14, 3:38 PM, Ken Lareau wrote:
So in the ongoing improvement of one of our internal databases, we
created
a new table named 'environments' whose SQLA code looks something like
this:
class Environment(Base):
__tablename__ = 'environments'
id = Column(u'environmentID', INTEGER(), primary_key=True)
environment = Column(String(length=15), nullable=False, unique=True)
env = Column(String(length=12), nullable=False, unique=True)
domain = Column(String(length=32), nullable=False, unique=True)
prefix = Column(String(length=1), nullable=False)
Two of our tables recently needed conversion to stop using their own
local
'environment' column to using this table. The first part's been put in
place
(a new foreign key for 'environment_id'), but to prevent large swaths of
code
from needing changes, a thought of using a hybrid property might allow
the
change to be hidden (until the code using it had been rewritten at
least).
My naive attempt was the following (just the relevant snippet):
environment_obj = relationship('Environment')
@hybrid_property
def environment(self):
return self.environment_obj.environment
Unfortunately (and in hindsight for obvious reasons), this code doesn't
work,
what does doesn't work mean here? This will work at the instance
level. At the query level, not so much, that's true, if you truly want no
code to change you'd need to implement an @expression here that's a little
inefficient, as it needs to do a correlated subq:
Yes, the doesn't work was specifically related to an attempt to use it in
a query, which of course failed miserably. :)
class HasEnv(Base):
__tablename__ = 'has_env'
id = Column(INTEGER, primary_key=True)
environment_id = Column(ForeignKey('environments.environmentID'))
environment_obj = relationship('Environment')
@hybrid_property
def environment(self):
return self.environment_obj.environment
@environment.expression
def environment(cls):
return select([Environment.environment]).\
where(Environment.id ==
cls.environment_id).correlate(cls).\
as_scalar()
s = Session()
print s.query(HasEnv).filter(HasEnv.environment == 'some env')
output:
SELECT has_env.id AS has_env_id, has_env.environment_id AS
has_env_environment_id
FROM has_env
WHERE (SELECT environments.environment
FROM environments
WHERE environments.environmentID = has_env.environment_id) = :param_1
wont perform well from a SQL perspective but will do the job...
This worked perfectly, thank you! This is honestly a stop-gap measure
to allow much of the code to be rewritten (after which it can be removed),
and for what it's being used for at the moment, it won't be too bad
regarding
performance.
but a very brief conversation with someone on the #sqlalchemy channel
on
Freenode indicated there was no way to do this and all the relevant code
must be reworked. While it's only a few dozen places this occurs, I can
see
this coming up again in the future as further schema refactorings occur, so
I turn to those with more expertise to find out if there is a way to
accomplish
what I desire, or if there's really no hope. :) Any insight would be
greatly
appreciated.
I don't know how to fix this issue with IRC and stackoverflow that people
constantly are getting bad information.
Heh, Sometimes I think I should know better about asking for help
on IRC, but sometimes I get lucky. In this case, I decided I might
have better luck on the mailing list after the answer I got on IRC
was very unsatisfactory. :)
Thanks for the help!
--
- Ken Lareau
--
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.
