[sqlalchemy] Re: Query generation in 0.3.8 is broken?
Hi,
the queries generated in 0.3.6 and 0.3.8 are identical except for an
anonymous alias name. they are also both incorrect, and its only
because 0.3.6 is less accurate about targeting Column objects in
result sets that it works.
Are they incorrect because of how they constructed, or it's because
of SA's internal behaviour?
There is also another point here - in fact, this mapper it's just a
view,
and seems that primary key is not very useful here, but I did not find
the
way to specify it.
I also do not want to mix mappers and simple selects.
So I probably stay on 0.3.6 until 0.4 will be released.
the task_id column which youve
labeled inside of jj does not appear due to a column targeting bug
that is present in all versions, resolved in changeset 2708. 0.3.6
just grabs the same column twice which is wrong as well.
the mapper in your example considers the primary key of cls to be
the composite of task.id and the task_id label youve defined inside
of jj:
illustration of the primary key:
cls.mapper = mapper( cls, jjj, properties=props)
cls.mapper.compile()
print cls.mapper.primary_key
OrderedSet([Column('id',Integer(),primary_key=True,nullable=False),
Column('task_id',Integer(),primary_key=True,nullable=False)])
which you probably should define as:
cls.mapper = mapper( cls, jjj, properties=props, primary_key=
[jjj.c.task_id])
the bug also prevented the primary_key setting above from compiling
properly in the mapper.
also, heres an alternate mapping to avoid the ambiguity presented by
mapping to a join:
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], group_by=[task_t.c.id])
jjj = join(task_t, jj, task_t.c.id == jj.c.joined_task_id).select
().alias('hi')
mapper(cls, jjj, primary_key=[jjj.c.task_id])
On Jun 6, 2007, at 3:55 PM, Dmitry Zuikov wrote:
once all that is done, then ill have a better idea of what youre
actually trying to do (if it doesnt actually fix the problem).
Okay, I have posted the cleaned code above (or here http://
dzuikov2.firstvds.ru/qqq.py).
There are some comments about it.
What I am trying to do? The simple thing: I have some related tables
with mappers and I need the query wich shows some statistics (group
functions involved). So I created a new mapper against the query.
That's all. The query and the mapper work ok in 0.3.6 - you may run
this code to check it out. In 0.3.8 it works with properties and
relations but without offset/limit, or with offset/limit, but without
properties. The query seems correct - it runs without mapper.
I do not use clear_mapper or even clear_mappers in real code.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Query generation in 0.3.8 is broken?
On Jun 7, 2007, at 2:25 AM, Dmitry Zuikov wrote: Hi, the queries generated in 0.3.6 and 0.3.8 are identical except for an anonymous alias name. they are also both incorrect, and its only because 0.3.6 is less accurate about targeting Column objects in result sets that it works. Are they incorrect because of how they constructed, or it's because of SA's internal behaviour? as i said, a bug, fixed in 2708. There is also another point here - in fact, this mapper it's just a view, and seems that primary key is not very useful here, but I did not find the way to specify it. primary_key=[columns] all mappers require a primary key. views and other relations certainly can have candidate keys just like tables. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Query generation in 0.3.8 is broken?
can you send me full Table/Mapper/class constructs, running against
sqlite://, so i can run this example, thanks.
On Jun 6, 2007, at 8:30 AM, [EMAIL PROTECTED] wrote:
To be more precise,
the code:
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], group_by=[task_t.c.id]).alias('prop_c_s')
jjj = join(task_t, jj, task_t.c.id == jj.c.task_id)
#jjj = outerjoin(task_effort_t, jjj, task_effort_t.c.task_id
== jjj.c.task_id)
class cls(object):pass
props =dict(type=relation(Task_Type, lazy=False))
cls.mapper = mapper( cls, jjj, order_by=[desc(task_t.c.updated)],
properties=props)
#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], from_obj=[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, order_by=[desc(task_t.c.updated)],
properties=props)
# not works
for t in session.query(cls.mapper).limit(10).offset(0).list():
print t.id, t.title, t.props_cnt
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Query generation in 0.3.8 is broken?
I have just submitted the ticket #523, there is a minimalistic code
snippet, wich reproduces the error.
I am sorry that I did not sent the working example right in ticket
#592, but I could not reproduce it.
But not I did (see below, or ticket #523):
The problem appears when mapper, relations and limit/offset come
together
#!/usr/bin/env python
from sqlalchemy import *
import sys, datetime
#init db
#global_connect('mysql://test:[EMAIL PROTECTED]/test')
#engine = create_engine('mysql://test:[EMAIL PROTECTED]/test')
global_connect('sqlite:///tutorial.db')
engine = create_engine('sqlite:///tutorial.db')
project_t = Table('prj',
Column('id',Integer,
primary_key=True),
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([project_t.c.id.label('prj_id'),
func.count(task_t.c.id).label('tasks_number')],
from_obj=[tsk_cnt_join],
group_by=[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([project_t.c.id.label('prj_id'),
func.count(task_t.c.id).label('tasks_number')],
from_obj=[tsk_cnt_join],
group_by=[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], group_by=[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
[sqlalchemy] Re: Query generation in 0.3.8 is broken?
do me several huge favors:
- do not redefine j three times, ss and tsk_count_join two
times, etc. Particuilarly i have no idea which j join you actually
would like to use. define the individual clauses youd like to use
once, then use that same instance. to SQLAlchemy, two identical
clauses are *not* interchangeable since it uses identity in many
cases with selectables.
- do *not* use orm.clear_mapper(). this method is gone in the next
version. use clear_mappers(), and redefine *all* mappers for each
test you are creating. mappers have lots of dependencies on each
other which are compiled when any of the mappers are first used.
there is no capability to surgically remove a mapper from that
compiled structure, you have to start over again.
- do *not* say, mapper.properties = {}. once a mapper is compiled,
the properties argument is not even looked at.
once all that is done, then ill have a better idea of what youre
actually trying to do (if it doesnt actually fix the problem).
On Jun 6, 2007, at 12:58 PM, [EMAIL PROTECTED] wrote:
#!/usr/bin/env python
from sqlalchemy import *
import sys, datetime
#init db
#global_connect('mysql://test:[EMAIL PROTECTED]/test')
#engine = create_engine('mysql://test:[EMAIL PROTECTED]/test')
global_connect('sqlite:///tutorial.db')
engine = create_engine('sqlite:///tutorial.db')
project_t = Table('prj',
Column('id',Integer,
primary_key=True),
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([project_t.c.id.label('prj_id'),
func.count(task_t.c.id).label('tasks_number')],
from_obj=[tsk_cnt_join],
group_by=[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([project_t.c.id.label('prj_id'),
func.count(task_t.c.id).label('tasks_number')],
from_obj=[tsk_cnt_join],
[sqlalchemy] Re: Query generation in 0.3.8 is broken?
Well,
Sorry, I just copy-pasted it from real code, trying to get minimal
example. I was in rush.
I do not really use clear_mapper, just put it there to demostrate how
it fails.
Here the more clean code (or you may download it from
http://dzuikov2.firstvds.ru/qqq.py)
#!/usr/bin/env python
from sqlalchemy import *
import sys
#init db
#global_connect('mysql://test:[EMAIL PROTECTED]/test')
#engine = create_engine('mysql://test:[EMAIL PROTECTED]/test')
global_connect('sqlite:///tutorial.db')
engine = create_engine('sqlite:///tutorial.db')
project_t = Table('prj',
Column('id',Integer,
primary_key=True),
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
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),
))
# join with messages
j = outerjoin( task_t, message_t, task_t.c.id==message_t.c.task_id)
# get count
jj = select([ task_t.c.id.label('task_id'),
func.count(message_t.c.id).label('props_cnt')],
from_obj=[j], group_by=[task_t.c.id]).alias('prop_c_s')
# etc
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)
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], from_obj=[jjj],
limit=10, offset=0).execute(bind_to=session.bind_to):
print t.id, t.title, t.props_cnt
#now fail
orm.clear_mappers()
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 =
[sqlalchemy] Re: Query generation in 0.3.8 is broken?
the queries generated in 0.3.6 and 0.3.8 are identical except for an
anonymous alias name. they are also both incorrect, and its only
because 0.3.6 is less accurate about targeting Column objects in
result sets that it works. the task_id column which youve
labeled inside of jj does not appear due to a column targeting bug
that is present in all versions, resolved in changeset 2708. 0.3.6
just grabs the same column twice which is wrong as well.
the mapper in your example considers the primary key of cls to be
the composite of task.id and the task_id label youve defined inside
of jj:
illustration of the primary key:
cls.mapper = mapper( cls, jjj, properties=props)
cls.mapper.compile()
print cls.mapper.primary_key
OrderedSet([Column('id',Integer(),primary_key=True,nullable=False),
Column('task_id',Integer(),primary_key=True,nullable=False)])
which you probably should define as:
cls.mapper = mapper( cls, jjj, properties=props, primary_key=
[jjj.c.task_id])
the bug also prevented the primary_key setting above from compiling
properly in the mapper.
also, heres an alternate mapping to avoid the ambiguity presented by
mapping to a join:
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], group_by=[task_t.c.id])
jjj = join(task_t, jj, task_t.c.id == jj.c.joined_task_id).select
().alias('hi')
mapper(cls, jjj, primary_key=[jjj.c.task_id])
On Jun 6, 2007, at 3:55 PM, Dmitry Zuikov wrote:
once all that is done, then ill have a better idea of what youre
actually trying to do (if it doesnt actually fix the problem).
Okay, I have posted the cleaned code above (or here http://
dzuikov2.firstvds.ru/qqq.py).
There are some comments about it.
What I am trying to do? The simple thing: I have some related tables
with mappers and I need the query wich shows some statistics (group
functions involved). So I created a new mapper against the query.
That's all. The query and the mapper work ok in 0.3.6 - you may run
this code to check it out. In 0.3.8 it works with properties and
relations but without offset/limit, or with offset/limit, but without
properties. The query seems correct - it runs without mapper.
I do not use clear_mapper or even clear_mappers in real code.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
