NoSuchColumnError (mapper, offset, limit, relations)

Ok, I have extracted the minimal code sample wich reproduces the error. The problem is that code worked ok in 0.3.6 fails in 0.3.8

The code:

#!/usr/bin/env python
from sqlalchemy import *
import sys

#init db
global_connect('mysql://test:test@localhost/test')
engine = create_engine('mysql://test:test@localhost/test')

project_t = Table('prj', 
                  Column('id',            Integer,      primary_key=True),
                  Column('created',       DateTime ,    nullable=False, default=func.current_timestamp()),
                  Column('title',         Unicode(100), nullable=False),
                  mysql_engine='InnoDB')


task_t = Table('task', 
                  Column('id',            Integer,      primary_key=True),
                  Column('status_id',     Integer,      ForeignKey('task_status.id'), nullable=False),
                  Column('title',         Unicode(100), nullable=False),
                  Column('task_type_id',  Integer ,     ForeignKey('task_type.id'), nullable=False),
                  Column('prj_id',        Integer ,     ForeignKey('prj.id'), nullable=False),
                  mysql_engine='InnoDB')

task_status_t = Table('task_status', 
                        Column('id',                Integer,      primary_key=True),
                        mysql_engine='InnoDB')

task_type_t = Table('task_type', 
                    Column('id',   Integer,    primary_key=True),
                    mysql_engine='InnoDB')

message_t  = Table('msg', 
                    Column('id', Integer,  primary_key=True),
                    Column('posted',    DateTime, nullable=False, index=True, default=func.current_timestamp()),
                    Column('type_id',   Integer, ForeignKey('msg_type.id'), nullable=False, index=True),
                    Column('from_uid',  Integer, nullable=False,  index=True),
                    Column('to_uid',    Integer, nullable=False,  index=True),
                    Column('task_id',   Integer, ForeignKey('task.id'), nullable=True,  index=True),
                    Column('time_est_days', Integer, nullable=True),
                    Column('subject',   Unicode(60), nullable=True),
                    Column('body',      Unicode,     nullable=True),
                    Column('new',       Boolean,     nullable=False, default=True),
                    Column('removed_by_sender',      Boolean,  nullable=False, default=False),
                    Column('removed_by_recipient',   Boolean,  nullable=False, default=False),
                    mysql_engine='InnoDB')

message_type_t = Table('msg_type', 
                        Column('id',                Integer,      primary_key=True),
                        Column('name',              Unicode(20),  nullable=False, unique=True),
                        Column('display_name',      Unicode(20),  nullable=False, unique=True),
                        mysql_engine='InnoDB')

class Task(object):pass

class Task_Type(object):pass

class Message(object):pass

class Message_Type(object):pass

tsk_cnt_join = outerjoin(project_t, task_t, task_t.c.prj_id==project_t.c.id)

ss = select([func.count(task_t.c.id).label('tasks_number')](project_t.c.id.label('prj_id'),), 
            from_obj=[tsk_cnt_join](tsk_cnt_join), group_by=[project_t.c.id](project_t.c.id)).alias('prj_tsk_cnt_s')
j = join(project_t, ss, project_t.c.id == ss.c.prj_id)

Task_Type.mapper = mapper(Task_Type, task_type_t)


Task.mapper = mapper( Task, task_t,
                      properties=dict(type=relation(Task_Type, lazy=False),
                                     ))

Message_Type.mapper = mapper(Message_Type, message_type_t)

Message.mapper = mapper(Message, message_t, 
                 properties=dict(type=relation(Message_Type, lazy=False, uselist=False),
                                 ))

tsk_cnt_join = outerjoin(project_t, task_t, task_t.c.prj_id==project_t.c.id)
ss = select([func.count(task_t.c.id).label('tasks_number')](project_t.c.id.label('prj_id'),), 
            from_obj=[tsk_cnt_join](tsk_cnt_join), group_by=[project_t.c.id](project_t.c.id)).alias('prj_tsk_cnt_s')
j = join(project_t, ss, project_t.c.id == ss.c.prj_id)

j  = outerjoin( task_t, message_t, task_t.c.id==message_t.c.task_id)
jj = select([ task_t.c.id.label('task_id'),
              func.count(message_t.c.id).label('props_cnt')],
              from_obj=[j](j), group_by=[task_t.c.id](task_t.c.id)).alias('prop_c_s')
jjj = join(task_t, jj, task_t.c.id == jj.c.task_id)

class cls(object):pass

props =dict(type=relation(Task_Type, lazy=False))
cls.mapper = mapper( cls, jjj, properties=props)


default_metadata.engine.echo = True
default_metadata.drop_all()
default_metadata.create_all()

session = create_session()

engine.execute("INSERT INTO prj (title) values('project 1');")
engine.execute("INSERT INTO task_status (id) values(1);")
engine.execute("INSERT INTO task_type(id) values(1);")
engine.execute("INSERT INTO task (title, task_type_id, status_id, prj_id) values('task 1',1,1,1);")

 #works
cls.mapper.properties = {}
for t in session.query(cls.mapper).limit(10).offset(0).list():
    print t.id, t.title, t.props_cnt

 #works
for t in select([jjj](jjj), from_obj=[jjj](jjj),
               limit=10, offset=0).execute(bind_to=session.bind_to):
    print t.id, t.title, t.props_cnt

orm.clear_mapper(cls.mapper)
cls.mapper = mapper( cls, jjj, properties=props)

 #not works
for t in session.query(cls.mapper).limit(10).offset(0).list():
    print t.id, t.title, t.props_cnt

The log:

2007-06-06 20:42:29,515 INFO sqlalchemy.engine.base.Engine.0x..8c SELECT task.title AS task_title, task.prj_id AS task_prj_id, task.status_id AS task_status_id, prop_c_s.props_cnt AS prop_c_s_props_cnt, task.task_type_id AS task_task_type_id, task.id AS task_id 
FROM task JOIN (SELECT task.id AS task_id, count(msg.id) AS props_cnt 
FROM task LEFT OUTER JOIN msg ON task.id = msg.task_id GROUP BY task.id) AS prop_c_s ON task.id = prop_c_s.task_id ORDER BY task.id 
 LIMIT 10 OFFSET 0
2007-06-06 20:42:29,515 INFO sqlalchemy.engine.base.Engine.0x..8c [task 1 0
2007-06-06 20:42:29,525 INFO sqlalchemy.engine.base.Engine.0x..8c SELECT task.id, task.status_id, task.title, task.task_type_id, task.prj_id, prop_c_s.task_id, prop_c_s.props_cnt 
FROM task JOIN (SELECT task.id AS task_id, count(msg.id) AS props_cnt 
FROM task LEFT OUTER JOIN msg ON task.id = msg.task_id GROUP BY task.id) AS prop_c_s ON task.id = prop_c_s.task_id 
 LIMIT 10 OFFSET 0
2007-06-06 20:42:29,525 INFO sqlalchemy.engine.base.Engine.0x..8c [](]
1)
1 task 1 0
2007-06-06 20:42:29,558 INFO sqlalchemy.engine.base.Engine.0x..8c SELECT task.title AS task_title, task.prj_id AS task_prj_id, task.status_id AS task_status_id, prop_c_s.props_cnt AS prop_c_s_props_cnt, task.task_type_id AS task_task_type_id, anon_fe68.id AS anon_fe68_id, task.id AS task_id 
FROM (SELECT task.id AS task_id, prop_c_s.task_id AS prop_c_s_task_id, task.id AS task_oid 
FROM task JOIN (SELECT task.id AS task_id, count(msg.id) AS props_cnt 
FROM task LEFT OUTER JOIN msg ON task.id = msg.task_id GROUP BY task.id) AS prop_c_s ON task.id = prop_c_s.task_id ORDER BY task.id 
 LIMIT 10 OFFSET 0) AS tbl_row_count, task JOIN (SELECT task.id AS task_id, count(msg.id) AS props_cnt 
FROM task LEFT OUTER JOIN msg ON task.id = msg.task_id GROUP BY task.id) AS prop_c_s ON task.id = prop_c_s.task_id LEFT OUTER JOIN task_type AS anon_fe68 ON anon_fe68.id = task.task_type_id 
WHERE task.id = tbl_row_count.task_id AND task.id = tbl_row_count.prop_c_s_task_id AND prop_c_s.task_id = tbl_row_count.task_id AND prop_c_s.task_id = tbl_row_count.prop_c_s_task_id ORDER BY tbl_row_count.task_id, anon_fe68.id
2007-06-06 20:42:29,558 INFO sqlalchemy.engine.base.Engine.0x..8c []
Traceback (most recent call last):
  File "./qqq.py", line 123, in <module>
    for t in session.query(cls.mapper).limit(10).offset(0).list():
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 829, in list
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 842, in __iter__
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 326, in select_whereclause
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 955, in _select_statement
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 857, in execute
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 916, in instances
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py", line 1414, in _instance
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py", line 924, in identity_key_from_row
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 1173, in __getitem__
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 994, in _get_col
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 931, in _convert_key
sqlalchemy.exceptions.NoSuchColumnError: "Could not locate column in row for column 'Column('task_id',Integer(),primary_key=True,nullable=False)'"

Comments (1)