Re: [sqlalchemy] strange behavior in count with custom mapper option
yeah, that's a little confusing for most people (including me), but a
simple session.query(func.count(Entity.id_)).filter(...).scalar() solved
the issue, since it produces a statement close to what i really want.
Mike, thanks for your time! :)
best regards,
richard.
On 07/15/2015 07:50 PM, Mike Bayer wrote:
there's your two rows:
Row (None, 24769797950537732L, datetime.datetime(2015, 7, 15, 17, 49,
57, 410290, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-180,
name=None)), datetime.datetime(2015, 7, 15, 17, 49, 57, 410305,
tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-180, name=None)), 0L,
24769797950537768L, 24769797950537768L, 24769797950537729L,
u'Workstation Super Legal do Richard', '10.0.0.2', 2, 1, 50, 0)
Row (None, 24769797950537732L, datetime.datetime(2015, 7, 15, 17, 49,
57, 410290, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-180,
name=None)), datetime.datetime(2015, 7, 15, 17, 49, 57, 410305,
tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-180, name=None)), 0L,
24769797950537768L, 24769797950537768L, 24769797950537729L,
u'Workstation Super Legal do Richard', '10.0.0.2', 2, 1, 50, 0)
and you can see they both are the same. therefore count of 2, .all()
gives you one object.
On 7/15/15 3:58 PM, Richard Gerd Kuesters wrote:
right! sorry, now here we go (again):
(Pdb) import logging
(Pdb) logging.basicConfig()
(Pdb) logging.getLogger('sqlalchemy.engine').setLevel(logging.DEBUG)
(Pdb) session.query(MachineUnit).filter(MachineUnit.id_ ==
24769797950537768).count()
2015-07-15 16:56:44,565 INFO sqlalchemy.engine.base.Engine BEGIN
(implicit)
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
2015-07-15 16:56:44,566 INFO sqlalchemy.engine.base.Engine SELECT
count(*) AS count_1
FROM system_unit, (SELECT system_unit.fk_updated_by AS
system_unit_fk_updated_by, system_unit.fk_created_by AS
system_unit_fk_created_by, system_unit.dt_created_on AS
system_unit_dt_created_on, system_unit.dt_updated_on AS
system_unit_updated_on, system_unit.bi_rm_timestamp AS
system_unit_bi_rm_timestamp, machine_unit.pk_fk_system_unit_id AS
machine_unit_pk_fk_system_unit_id, system_unit.pk_system_unit_id
AS system_unit_pk_system_unit_id, system_unit.fk_organization_id
AS system_unit_fk_organization_id, system_unit.u_system_unit_name
AS system_unit_u_system_unit_name, system_unit.in_ipv4 AS
system_unit_in_ipv4, system_unit.p_system_unit_type AS
system_unit_system_unit_type, system_unit.i_version AS
system_unit_i_version, machine_unit.e_machine_type AS
machine_unit_e_machine_type, machine_unit.e_printer_type AS
machine_unit_e_printer_type
FROM system_unit JOIN machine_unit ON
system_unit.pk_system_unit_id = machine_unit.pk_fk_system_unit_id
WHERE machine_unit.pk_fk_system_unit_id = %(system_unit_id_1)s
AND system_unit.bi_rm_timestamp = %(rm_timestamp_1)s) AS anon_1
WHERE system_unit.bi_rm_timestamp = %(rm_timestamp_2)s
INFO:sqlalchemy.engine.base.Engine:SELECT count(*) AS count_1
FROM system_unit, (SELECT system_unit.fk_updated_by AS
system_unit_fk_updated_by, system_unit.fk_created_by AS
system_unit_fk_created_by, system_unit.dt_created_on AS
system_unit_dt_created_on, system_unit.dt_updated_on AS
system_unit_updated_on, system_unit.bi_rm_timestamp AS
system_unit_bi_rm_timestamp, machine_unit.pk_fk_system_unit_id AS
machine_unit_pk_fk_system_unit_id, system_unit.pk_system_unit_id
AS system_unit_pk_system_unit_id, system_unit.fk_organization_id
AS system_unit_fk_organization_id, system_unit.u_system_unit_name
AS system_unit_u_system_unit_name, system_unit.in_ipv4 AS
system_unit_in_ipv4, system_unit.p_system_unit_type AS
system_unit_system_unit_type, system_unit.i_version AS
system_unit_i_version, machine_unit.e_machine_type AS
machine_unit_e_machine_type, machine_unit.e_printer_type AS
machine_unit_e_printer_type
FROM system_unit JOIN machine_unit ON
system_unit.pk_system_unit_id = machine_unit.pk_fk_system_unit_id
WHERE machine_unit.pk_fk_system_unit_id = %(system_unit_id_1)s
AND system_unit.bi_rm_timestamp = %(rm_timestamp_1)s) AS anon_1
WHERE system_unit.bi_rm_timestamp = %(rm_timestamp_2)s
2015-07-15 16:56:44,566 INFO sqlalchemy.engine.base.Engine
{'rm_timestamp_1': 0, 'system_unit_id_1': 24769797950537768,
'rm_timestamp_2': 0}
INFO:sqlalchemy.engine.base.Engine:{'rm_timestamp_1': 0,
'system_unit_id_1': 24769797950537768, 'rm_timestamp_2': 0}
2015-07-15 16:56:44,567 DEBUG sqlalchemy.engine.base.Engine Col
('count_1',)
DEBUG:sqlalchemy.engine.base.Engine:Col ('count_1',)
2015-07-15 16:56:44,568 DEBUG sqlalchemy.engine.base.Engine Row (2L,)
DEBUG:sqlalchemy.engine.base.Engine:Row (2L,)
2L
(Pdb) len(session.query(MachineUnit).filter(MachineUnit.id_ ==
24769797950537768).all())
2015-07-15 16:57:06,070 INFO sqlalchemy.engine.base.Engine
Re: [sqlalchemy] strange behavior in count with custom mapper option
oh, forgot to mention: * this occurs even with a filter that's supposed to bring one register only (at the database level it works); * in the database level, a count *without* the where clause brings the result i mentioned earlier. thanks, richard. On 07/15/2015 03:11 PM, Richard Gerd Kuesters wrote: hello! i'm encountering a weird behaviur with session.count() when using a custom mapper that implements a where condition to every session. first, what is happening: len(session.query(Entity).all()) == 1 session.query(Entity).count() == 2 Entity is a base polymorphic entity, inherited by other classes. my custom mapper: class DeletedOption(MapperOption): propagate_to_loaders = True def process_query(self, query): mzo = query._mapper_zero() if hasattr(mzo, 'class_'): parent_cls = mzo.class_ filter_crit = parent_cls.rm_timestamp == 0 if query._criterion is None: query._criterion = filter_crit else: query._criterion = query._criterion filter_crit i remember getting this custom mapper option in a recipe ... i'm running sqla 1.0.6. if there's any information i can provide to enlighten this out, please let me know. best regards, richard. -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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. attachment: richard.vcf
Re: [sqlalchemy] strange behavior in count with custom mapper option
does this happen even with a filter for a PK? the problem is: session.query(Entity).filter(Entity.id_ == 24769797950537768).count() == 2 len(session.query(Entity).filter(Entity.id_ == 24769797950537768).all()) == 1 i don't see where i have 2 pks with the same value ... in psql: mydb=# select count(*) from entity where id_ = 24769797950537768; count --- 1 (1 row) is this really right? On 07/15/2015 04:02 PM, Mike Bayer wrote: On 7/15/15 2:11 PM, Richard Gerd Kuesters wrote: hello! i'm encountering a weird behaviur with session.count() when using a custom mapper that implements a where condition to every session. first, what is happening: len(session.query(Entity).all()) == 1 session.query(Entity).count() == 2 your all() query is returning two rows that have the same primary key for Entity.The Query object de-duplicates redundant rows based on entity identity. Turn on echo='debug' and you should see this in the rows coming back. we really need an FAQ entry for this one. Entity is a base polymorphic entity, inherited by other classes. my custom mapper: class DeletedOption(MapperOption): propagate_to_loaders = True def process_query(self, query): mzo = query._mapper_zero() if hasattr(mzo, 'class_'): parent_cls = mzo.class_ filter_crit = parent_cls.rm_timestamp == 0 if query._criterion is None: query._criterion = filter_crit else: query._criterion = query._criterion filter_crit i remember getting this custom mapper option in a recipe ... i'm running sqla 1.0.6. if there's any information i can provide to enlighten this out, please let me know. best regards, richard. -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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. attachment: richard.vcf
Re: [sqlalchemy] strange behavior in count with custom mapper option
thanks Mike!
here we go:
(Pdb) session.query(MachineUnit).filter(MachineUnit.id_ ==
24769797950537768).count()
2015-07-15 16:43:53,114 INFO sqlalchemy.engine.base.Engine SELECT
count(*) AS count_1
FROM system_unit, (SELECT system_unit.fk_updated_by AS
system_unit_fk_updated_by, system_unit.fk_created_by AS
system_unit_fk_created_by, system_unit.dt_created_on AS
system_unit_dt_created_on, system_unit.dt_updated_on AS
system_unit_updated_on, system_unit.bi_rm_timestamp AS
system_unit_bi_rm_timestamp, machine_unit.pk_fk_system_unit_id AS
machine_unit_pk_fk_system_unit_id, system_unit.pk_system_unit_id AS
system_unit_pk_system_unit_id, system_unit.fk_organization_id AS
system_unit_fk_organization_id, system_unit.u_system_unit_name AS
system_unit_u_system_unit_name, system_unit.in_ipv4 AS
system_unit_in_ipv4, system_unit.p_system_unit_type AS
system_unit_system_unit_type, system_unit.i_version AS
system_unit_i_version, machine_unit.e_machine_type AS
machine_unit_e_machine_type, machine_unit.e_printer_type AS
machine_unit_e_printer_type
FROM system_unit JOIN machine_unit ON system_unit.pk_system_unit_id
= machine_unit.pk_fk_system_unit_id
WHERE machine_unit.pk_fk_system_unit_id = %(system_unit_id_1)s AND
system_unit.bi_rm_timestamp = %(rm_timestamp_1)s) AS anon_1
WHERE system_unit.bi_rm_timestamp = %(rm_timestamp_2)s
2015-07-15 16:43:53,114 INFO sqlalchemy.engine.base.Engine
{'system_unit_id_1': 24769797950537768, 'rm_timestamp_1': 0,
'rm_timestamp_2': 0}
2L
(Pdb) len(session.query(MachineUnit).filter(MachineUnit.id_ ==
24769797950537768).all())
2015-07-15 16:44:32,891 INFO sqlalchemy.engine.base.Engine SELECT
system_unit.fk_updated_by AS system_unit_fk_updated_by,
system_unit.fk_created_by AS system_unit_fk_created_by,
system_unit.dt_created_on AS system_unit_dt_created_on,
system_unit.dt_updated_on AS system_unit_updated_on,
system_unit.bi_rm_timestamp AS system_unit_bi_rm_timestamp,
machine_unit.pk_fk_system_unit_id AS
machine_unit_pk_fk_system_unit_id, system_unit.pk_system_unit_id AS
system_unit_pk_system_unit_id, system_unit.fk_organization_id AS
system_unit_fk_organization_id, system_unit.u_system_unit_name AS
system_unit_u_system_unit_name, system_unit.in_ipv4 AS
system_unit_in_ipv4, system_unit.p_system_unit_type AS
system_unit_system_unit_type, system_unit.i_version AS
system_unit_i_version, machine_unit.e_machine_type AS
machine_unit_e_machine_type, machine_unit.e_printer_type AS
machine_unit_e_printer_type
FROM system_unit JOIN machine_unit ON system_unit.pk_system_unit_id
= machine_unit.pk_fk_system_unit_id
WHERE machine_unit.pk_fk_system_unit_id = %(system_unit_id_1)s AND
system_unit.bi_rm_timestamp = %(rm_timestamp_1)s
2015-07-15 16:44:32,891 INFO sqlalchemy.engine.base.Engine
{'system_unit_id_1': 24769797950537768, 'rm_timestamp_1': 0}
1
# MachineUnit = Entity
it fits to the faq entry you mentioned earlier? :)
cheers,
richard.
On 07/15/2015 04:22 PM, Mike Bayer wrote:
On 7/15/15 3:13 PM, Richard Gerd Kuesters wrote:
does this happen even with a filter for a PK?
the problem is:
session.query(Entity).filter(Entity.id_ ==
24769797950537768).count() == 2
len(session.query(Entity).filter(Entity.id_ ==
24769797950537768).all()) == 1
i don't see where i have 2 pks with the same value ... in psql:
mydb=# select count(*) from entity where id_ = 24769797950537768;
count
---
1
(1 row)
is this really right?
what does echo='debug' say ? both for the first query and the
second.Also, that integer ID looks kind of like it itself could be
hitting some overflow limit at some point. I assume no issue for a
simple integer in the thousands ?
On 07/15/2015 04:02 PM, Mike Bayer wrote:
On 7/15/15 2:11 PM, Richard Gerd Kuesters wrote:
hello!
i'm encountering a weird behaviur with session.count() when using a
custom mapper that implements a where condition to every session.
first, what is happening:
len(session.query(Entity).all()) == 1
session.query(Entity).count() == 2
your all() query is returning two rows that have the same primary
key for Entity.The Query object de-duplicates redundant rows
based on entity identity. Turn on echo='debug' and you should see
this in the rows coming back.
we really need an FAQ entry for this one.
Entity is a base polymorphic entity, inherited by other classes.
my custom mapper:
class DeletedOption(MapperOption):
propagate_to_loaders = True
def process_query(self, query):
mzo = query._mapper_zero()
if hasattr(mzo, 'class_'):
parent_cls = mzo.class_
filter_crit = parent_cls.rm_timestamp == 0
if query._criterion is None:
query._criterion = filter_crit
else:
query._criterion = query._criterion filter_crit
i remember getting this custom mapper option in a recipe
Re: [sqlalchemy] strange behavior in count with custom mapper option
oh, the pk 24769797950537768 is a postgres biginteger.
On 07/15/2015 04:46 PM, Richard Gerd Kuesters wrote:
thanks Mike!
here we go:
(Pdb) session.query(MachineUnit).filter(MachineUnit.id_ ==
24769797950537768).count()
2015-07-15 16:43:53,114 INFO sqlalchemy.engine.base.Engine SELECT
count(*) AS count_1
FROM system_unit, (SELECT system_unit.fk_updated_by AS
system_unit_fk_updated_by, system_unit.fk_created_by AS
system_unit_fk_created_by, system_unit.dt_created_on AS
system_unit_dt_created_on, system_unit.dt_updated_on AS
system_unit_updated_on, system_unit.bi_rm_timestamp AS
system_unit_bi_rm_timestamp, machine_unit.pk_fk_system_unit_id AS
machine_unit_pk_fk_system_unit_id, system_unit.pk_system_unit_id
AS system_unit_pk_system_unit_id, system_unit.fk_organization_id
AS system_unit_fk_organization_id, system_unit.u_system_unit_name
AS system_unit_u_system_unit_name, system_unit.in_ipv4 AS
system_unit_in_ipv4, system_unit.p_system_unit_type AS
system_unit_system_unit_type, system_unit.i_version AS
system_unit_i_version, machine_unit.e_machine_type AS
machine_unit_e_machine_type, machine_unit.e_printer_type AS
machine_unit_e_printer_type
FROM system_unit JOIN machine_unit ON
system_unit.pk_system_unit_id = machine_unit.pk_fk_system_unit_id
WHERE machine_unit.pk_fk_system_unit_id = %(system_unit_id_1)s AND
system_unit.bi_rm_timestamp = %(rm_timestamp_1)s) AS anon_1
WHERE system_unit.bi_rm_timestamp = %(rm_timestamp_2)s
2015-07-15 16:43:53,114 INFO sqlalchemy.engine.base.Engine
{'system_unit_id_1': 24769797950537768, 'rm_timestamp_1': 0,
'rm_timestamp_2': 0}
2L
(Pdb) len(session.query(MachineUnit).filter(MachineUnit.id_ ==
24769797950537768).all())
2015-07-15 16:44:32,891 INFO sqlalchemy.engine.base.Engine SELECT
system_unit.fk_updated_by AS system_unit_fk_updated_by,
system_unit.fk_created_by AS system_unit_fk_created_by,
system_unit.dt_created_on AS system_unit_dt_created_on,
system_unit.dt_updated_on AS system_unit_updated_on,
system_unit.bi_rm_timestamp AS system_unit_bi_rm_timestamp,
machine_unit.pk_fk_system_unit_id AS
machine_unit_pk_fk_system_unit_id, system_unit.pk_system_unit_id
AS system_unit_pk_system_unit_id, system_unit.fk_organization_id
AS system_unit_fk_organization_id, system_unit.u_system_unit_name
AS system_unit_u_system_unit_name, system_unit.in_ipv4 AS
system_unit_in_ipv4, system_unit.p_system_unit_type AS
system_unit_system_unit_type, system_unit.i_version AS
system_unit_i_version, machine_unit.e_machine_type AS
machine_unit_e_machine_type, machine_unit.e_printer_type AS
machine_unit_e_printer_type
FROM system_unit JOIN machine_unit ON
system_unit.pk_system_unit_id = machine_unit.pk_fk_system_unit_id
WHERE machine_unit.pk_fk_system_unit_id = %(system_unit_id_1)s AND
system_unit.bi_rm_timestamp = %(rm_timestamp_1)s
2015-07-15 16:44:32,891 INFO sqlalchemy.engine.base.Engine
{'system_unit_id_1': 24769797950537768, 'rm_timestamp_1': 0}
1
# MachineUnit = Entity
it fits to the faq entry you mentioned earlier? :)
cheers,
richard.
On 07/15/2015 04:22 PM, Mike Bayer wrote:
On 7/15/15 3:13 PM, Richard Gerd Kuesters wrote:
does this happen even with a filter for a PK?
the problem is:
session.query(Entity).filter(Entity.id_ ==
24769797950537768).count() == 2
len(session.query(Entity).filter(Entity.id_ ==
24769797950537768).all()) == 1
i don't see where i have 2 pks with the same value ... in psql:
mydb=# select count(*) from entity where id_ = 24769797950537768;
count
---
1
(1 row)
is this really right?
what does echo='debug' say ? both for the first query and the
second.Also, that integer ID looks kind of like it itself could
be hitting some overflow limit at some point. I assume no issue for
a simple integer in the thousands ?
On 07/15/2015 04:02 PM, Mike Bayer wrote:
On 7/15/15 2:11 PM, Richard Gerd Kuesters wrote:
hello!
i'm encountering a weird behaviur with session.count() when using
a custom mapper that implements a where condition to every session.
first, what is happening:
len(session.query(Entity).all()) == 1
session.query(Entity).count() == 2
your all() query is returning two rows that have the same primary
key for Entity.The Query object de-duplicates redundant rows
based on entity identity. Turn on echo='debug' and you should see
this in the rows coming back.
we really need an FAQ entry for this one.
Entity is a base polymorphic entity, inherited by other classes.
my custom mapper:
class DeletedOption(MapperOption):
propagate_to_loaders = True
def process_query(self, query):
mzo = query._mapper_zero()
if hasattr(mzo, 'class_'):
parent_cls = mzo.class_
filter_crit = parent_cls.rm_timestamp == 0
if query._criterion is None:
Re: [sqlalchemy] strange behavior in count with custom mapper option
On 7/15/15 3:46 PM, Richard Gerd Kuesters wrote:
thanks Mike!
here we go:
(Pdb) session.query(MachineUnit).filter(MachineUnit.id_ ==
24769797950537768).count()
2015-07-15 16:43:53,114 INFO sqlalchemy.engine.base.Engine SELECT
count(*) AS count_1
FROM system_unit, (SELECT system_unit.fk_updated_by AS
system_unit_fk_updated_by, system_unit.fk_created_by AS
system_unit_fk_created_by, system_unit.dt_created_on AS
system_unit_dt_created_on, system_unit.dt_updated_on AS
system_unit_updated_on, system_unit.bi_rm_timestamp AS
system_unit_bi_rm_timestamp, machine_unit.pk_fk_system_unit_id AS
machine_unit_pk_fk_system_unit_id, system_unit.pk_system_unit_id
AS system_unit_pk_system_unit_id, system_unit.fk_organization_id
AS system_unit_fk_organization_id, system_unit.u_system_unit_name
AS system_unit_u_system_unit_name, system_unit.in_ipv4 AS
system_unit_in_ipv4, system_unit.p_system_unit_type AS
system_unit_system_unit_type, system_unit.i_version AS
system_unit_i_version, machine_unit.e_machine_type AS
machine_unit_e_machine_type, machine_unit.e_printer_type AS
machine_unit_e_printer_type
FROM system_unit JOIN machine_unit ON
system_unit.pk_system_unit_id = machine_unit.pk_fk_system_unit_id
WHERE machine_unit.pk_fk_system_unit_id = %(system_unit_id_1)s AND
system_unit.bi_rm_timestamp = %(rm_timestamp_1)s) AS anon_1
WHERE system_unit.bi_rm_timestamp = %(rm_timestamp_2)s
2015-07-15 16:43:53,114 INFO sqlalchemy.engine.base.Engine
{'system_unit_id_1': 24769797950537768, 'rm_timestamp_1': 0,
'rm_timestamp_2': 0}
2L
(Pdb) len(session.query(MachineUnit).filter(MachineUnit.id_ ==
24769797950537768).all())
2015-07-15 16:44:32,891 INFO sqlalchemy.engine.base.Engine SELECT
system_unit.fk_updated_by AS system_unit_fk_updated_by,
system_unit.fk_created_by AS system_unit_fk_created_by,
system_unit.dt_created_on AS system_unit_dt_created_on,
system_unit.dt_updated_on AS system_unit_updated_on,
system_unit.bi_rm_timestamp AS system_unit_bi_rm_timestamp,
machine_unit.pk_fk_system_unit_id AS
machine_unit_pk_fk_system_unit_id, system_unit.pk_system_unit_id
AS system_unit_pk_system_unit_id, system_unit.fk_organization_id
AS system_unit_fk_organization_id, system_unit.u_system_unit_name
AS system_unit_u_system_unit_name, system_unit.in_ipv4 AS
system_unit_in_ipv4, system_unit.p_system_unit_type AS
system_unit_system_unit_type, system_unit.i_version AS
system_unit_i_version, machine_unit.e_machine_type AS
machine_unit_e_machine_type, machine_unit.e_printer_type AS
machine_unit_e_printer_type
FROM system_unit JOIN machine_unit ON
system_unit.pk_system_unit_id = machine_unit.pk_fk_system_unit_id
WHERE machine_unit.pk_fk_system_unit_id = %(system_unit_id_1)s AND
system_unit.bi_rm_timestamp = %(rm_timestamp_1)s
2015-07-15 16:44:32,891 INFO sqlalchemy.engine.base.Engine
{'system_unit_id_1': 24769797950537768, 'rm_timestamp_1': 0}
1
# MachineUnit = Entity
it fits to the faq entry you mentioned earlier? :)
probably, but this is still only INFO output, not DEBUG. echo='debug'
(not echo=True) will show you the result rows coming back as DEBUG entries.
the JOIN you're seeing seems like it will return two rows for the single
PK identity.
cheers,
richard.
On 07/15/2015 04:22 PM, Mike Bayer wrote:
On 7/15/15 3:13 PM, Richard Gerd Kuesters wrote:
does this happen even with a filter for a PK?
the problem is:
session.query(Entity).filter(Entity.id_ ==
24769797950537768).count() == 2
len(session.query(Entity).filter(Entity.id_ ==
24769797950537768).all()) == 1
i don't see where i have 2 pks with the same value ... in psql:
mydb=# select count(*) from entity where id_ = 24769797950537768;
count
---
1
(1 row)
is this really right?
what does echo='debug' say ? both for the first query and the
second.Also, that integer ID looks kind of like it itself could
be hitting some overflow limit at some point. I assume no issue for
a simple integer in the thousands ?
On 07/15/2015 04:02 PM, Mike Bayer wrote:
On 7/15/15 2:11 PM, Richard Gerd Kuesters wrote:
hello!
i'm encountering a weird behaviur with session.count() when using
a custom mapper that implements a where condition to every session.
first, what is happening:
len(session.query(Entity).all()) == 1
session.query(Entity).count() == 2
your all() query is returning two rows that have the same primary
key for Entity.The Query object de-duplicates redundant rows
based on entity identity. Turn on echo='debug' and you should see
this in the rows coming back.
we really need an FAQ entry for this one.
Entity is a base polymorphic entity, inherited by other classes.
my custom mapper:
class DeletedOption(MapperOption):
propagate_to_loaders = True
def process_query(self, query):
mzo =
Re: [sqlalchemy] strange behavior in count with custom mapper option
On 7/15/15 2:11 PM, Richard Gerd Kuesters wrote: hello! i'm encountering a weird behaviur with session.count() when using a custom mapper that implements a where condition to every session. first, what is happening: len(session.query(Entity).all()) == 1 session.query(Entity).count() == 2 your all() query is returning two rows that have the same primary key for Entity.The Query object de-duplicates redundant rows based on entity identity. Turn on echo='debug' and you should see this in the rows coming back. we really need an FAQ entry for this one. Entity is a base polymorphic entity, inherited by other classes. my custom mapper: class DeletedOption(MapperOption): propagate_to_loaders = True def process_query(self, query): mzo = query._mapper_zero() if hasattr(mzo, 'class_'): parent_cls = mzo.class_ filter_crit = parent_cls.rm_timestamp == 0 if query._criterion is None: query._criterion = filter_crit else: query._criterion = query._criterion filter_crit i remember getting this custom mapper option in a recipe ... i'm running sqla 1.0.6. if there's any information i can provide to enlighten this out, please let me know. best regards, richard. -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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] strange behavior in count with custom mapper option
On 7/15/15 3:13 PM, Richard Gerd Kuesters wrote: does this happen even with a filter for a PK? the problem is: session.query(Entity).filter(Entity.id_ == 24769797950537768).count() == 2 len(session.query(Entity).filter(Entity.id_ == 24769797950537768).all()) == 1 i don't see where i have 2 pks with the same value ... in psql: mydb=# select count(*) from entity where id_ = 24769797950537768; count --- 1 (1 row) is this really right? what does echo='debug' say ? both for the first query and the second.Also, that integer ID looks kind of like it itself could be hitting some overflow limit at some point. I assume no issue for a simple integer in the thousands ? On 07/15/2015 04:02 PM, Mike Bayer wrote: On 7/15/15 2:11 PM, Richard Gerd Kuesters wrote: hello! i'm encountering a weird behaviur with session.count() when using a custom mapper that implements a where condition to every session. first, what is happening: len(session.query(Entity).all()) == 1 session.query(Entity).count() == 2 your all() query is returning two rows that have the same primary key for Entity.The Query object de-duplicates redundant rows based on entity identity. Turn on echo='debug' and you should see this in the rows coming back. we really need an FAQ entry for this one. Entity is a base polymorphic entity, inherited by other classes. my custom mapper: class DeletedOption(MapperOption): propagate_to_loaders = True def process_query(self, query): mzo = query._mapper_zero() if hasattr(mzo, 'class_'): parent_cls = mzo.class_ filter_crit = parent_cls.rm_timestamp == 0 if query._criterion is None: query._criterion = filter_crit else: query._criterion = query._criterion filter_crit i remember getting this custom mapper option in a recipe ... i'm running sqla 1.0.6. if there's any information i can provide to enlighten this out, please let me know. best regards, richard. -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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] strange behavior in count with custom mapper option
right! sorry, now here we go (again):
(Pdb) import logging
(Pdb) logging.basicConfig()
(Pdb) logging.getLogger('sqlalchemy.engine').setLevel(logging.DEBUG)
(Pdb) session.query(MachineUnit).filter(MachineUnit.id_ ==
24769797950537768).count()
2015-07-15 16:56:44,565 INFO sqlalchemy.engine.base.Engine BEGIN
(implicit)
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
2015-07-15 16:56:44,566 INFO sqlalchemy.engine.base.Engine SELECT
count(*) AS count_1
FROM system_unit, (SELECT system_unit.fk_updated_by AS
system_unit_fk_updated_by, system_unit.fk_created_by AS
system_unit_fk_created_by, system_unit.dt_created_on AS
system_unit_dt_created_on, system_unit.dt_updated_on AS
system_unit_updated_on, system_unit.bi_rm_timestamp AS
system_unit_bi_rm_timestamp, machine_unit.pk_fk_system_unit_id AS
machine_unit_pk_fk_system_unit_id, system_unit.pk_system_unit_id AS
system_unit_pk_system_unit_id, system_unit.fk_organization_id AS
system_unit_fk_organization_id, system_unit.u_system_unit_name AS
system_unit_u_system_unit_name, system_unit.in_ipv4 AS
system_unit_in_ipv4, system_unit.p_system_unit_type AS
system_unit_system_unit_type, system_unit.i_version AS
system_unit_i_version, machine_unit.e_machine_type AS
machine_unit_e_machine_type, machine_unit.e_printer_type AS
machine_unit_e_printer_type
FROM system_unit JOIN machine_unit ON system_unit.pk_system_unit_id
= machine_unit.pk_fk_system_unit_id
WHERE machine_unit.pk_fk_system_unit_id = %(system_unit_id_1)s AND
system_unit.bi_rm_timestamp = %(rm_timestamp_1)s) AS anon_1
WHERE system_unit.bi_rm_timestamp = %(rm_timestamp_2)s
INFO:sqlalchemy.engine.base.Engine:SELECT count(*) AS count_1
FROM system_unit, (SELECT system_unit.fk_updated_by AS
system_unit_fk_updated_by, system_unit.fk_created_by AS
system_unit_fk_created_by, system_unit.dt_created_on AS
system_unit_dt_created_on, system_unit.dt_updated_on AS
system_unit_updated_on, system_unit.bi_rm_timestamp AS
system_unit_bi_rm_timestamp, machine_unit.pk_fk_system_unit_id AS
machine_unit_pk_fk_system_unit_id, system_unit.pk_system_unit_id AS
system_unit_pk_system_unit_id, system_unit.fk_organization_id AS
system_unit_fk_organization_id, system_unit.u_system_unit_name AS
system_unit_u_system_unit_name, system_unit.in_ipv4 AS
system_unit_in_ipv4, system_unit.p_system_unit_type AS
system_unit_system_unit_type, system_unit.i_version AS
system_unit_i_version, machine_unit.e_machine_type AS
machine_unit_e_machine_type, machine_unit.e_printer_type AS
machine_unit_e_printer_type
FROM system_unit JOIN machine_unit ON system_unit.pk_system_unit_id
= machine_unit.pk_fk_system_unit_id
WHERE machine_unit.pk_fk_system_unit_id = %(system_unit_id_1)s AND
system_unit.bi_rm_timestamp = %(rm_timestamp_1)s) AS anon_1
WHERE system_unit.bi_rm_timestamp = %(rm_timestamp_2)s
2015-07-15 16:56:44,566 INFO sqlalchemy.engine.base.Engine
{'rm_timestamp_1': 0, 'system_unit_id_1': 24769797950537768,
'rm_timestamp_2': 0}
INFO:sqlalchemy.engine.base.Engine:{'rm_timestamp_1': 0,
'system_unit_id_1': 24769797950537768, 'rm_timestamp_2': 0}
2015-07-15 16:56:44,567 DEBUG sqlalchemy.engine.base.Engine Col
('count_1',)
DEBUG:sqlalchemy.engine.base.Engine:Col ('count_1',)
2015-07-15 16:56:44,568 DEBUG sqlalchemy.engine.base.Engine Row (2L,)
DEBUG:sqlalchemy.engine.base.Engine:Row (2L,)
2L
(Pdb) len(session.query(MachineUnit).filter(MachineUnit.id_ ==
24769797950537768).all())
2015-07-15 16:57:06,070 INFO sqlalchemy.engine.base.Engine SELECT
system_unit.fk_updated_by AS system_unit_fk_updated_by,
system_unit.fk_created_by AS system_unit_fk_created_by,
system_unit.dt_created_on AS system_unit_dt_created_on,
system_unit.dt_updated_on AS system_unit_updated_on,
system_unit.bi_rm_timestamp AS system_unit_bi_rm_timestamp,
machine_unit.pk_fk_system_unit_id AS
machine_unit_pk_fk_system_unit_id, system_unit.pk_system_unit_id AS
system_unit_pk_system_unit_id, system_unit.fk_organization_id AS
system_unit_fk_organization_id, system_unit.u_system_unit_name AS
system_unit_u_system_unit_name, system_unit.in_ipv4 AS
system_unit_in_ipv4, system_unit.p_system_unit_type AS
system_unit_system_unit_type, system_unit.i_version AS
system_unit_i_version, machine_unit.e_machine_type AS
machine_unit_e_machine_type, machine_unit.e_printer_type AS
machine_unit_e_printer_type
FROM system_unit JOIN machine_unit ON system_unit.pk_system_unit_id
= machine_unit.pk_fk_system_unit_id
WHERE machine_unit.pk_fk_system_unit_id = %(system_unit_id_1)s AND
system_unit.bi_rm_timestamp = %(rm_timestamp_1)s
INFO:sqlalchemy.engine.base.Engine:SELECT system_unit.fk_updated_by
AS system_unit_fk_updated_by, system_unit.fk_created_by AS
system_unit_fk_created_by, system_unit.dt_created_on AS
system_unit_dt_created_on, system_unit.dt_updated_on
Re: [sqlalchemy] strange behavior in count with custom mapper option
the interesting is, if i get the count() produced sql and put it into psql ... SELECT count(*) AS count_1 FROM system_unit, ( SELECT system_unit.fk_updated_by AS system_unit_fk_updated_by, system_unit.fk_created_by AS system_unit_fk_created_by, system_unit.dt_created_on AS system_unit_dt_created_on, system_unit.dt_updated_on AS system_unit_updated_on, system_unit.bi_rm_timestamp AS system_unit_bi_rm_timestamp, machine_unit.pk_fk_system_unit_id AS machine_unit_pk_fk_system_unit_id, system_unit.pk_system_unit_id AS system_unit_pk_system_unit_id, system_unit.fk_organization_id AS system_unit_fk_organization_id, system_unit.u_system_unit_name AS system_unit_u_system_unit_name, system_unit.in_ipv4 AS system_unit_in_ipv4, system_unit.p_system_unit_type AS system_unit_system_unit_type, system_unit.i_version AS system_unit_i_version, machine_unit.e_machine_type AS machine_unit_e_machine_type, machine_unit.e_printer_type AS machine_unit_e_printer_type FROM system_unit JOIN machine_unit ON system_unit.pk_system_unit_id = machine_unit.pk_fk_system_unit_id WHERE machine_unit.pk_fk_system_unit_id = 24769797950537768 AND system_unit.bi_rm_timestamp = 0) AS anon_1 WHERE system_unit.bi_rm_timestamp = 0 first, the inner sql: pollux=# SELECT system_unit.fk_updated_by AS system_unit_fk_updated_by, system_unit.fk_created_by AS system_unit_fk_created_by, system_unit.dt_created_on AS system_unit_dt_created_on, system_unit.dt_updated_on AS system_unit_updated_on, pollux-# system_unit.bi_rm_timestamp AS system_unit_bi_rm_timestamp, machine_unit.pk_fk_system_unit_id AS machine_unit_pk_fk_system_unit_id, system_unit.pk_system_unit_id AS system_unit_pk_system_unit_id, system_unit.fk_organization_id AS pollux-# system_unit_fk_organization_id, system_unit.u_system_unit_name AS system_unit_u_system_unit_name, system_unit.in_ipv4 AS system_unit_in_ipv4, system_unit.p_system_unit_type AS system_unit_system_unit_type, system_unit.i_version AS pollux-# system_unit_i_version, machine_unit.e_machine_type AS machine_unit_e_machine_type, machine_unit.e_printer_type AS machine_unit_e_printer_type pollux-# FROM system_unit JOIN machine_unit ON system_unit.pk_system_unit_id = machine_unit.pk_fk_system_unit_id pollux-# WHERE machine_unit.pk_fk_system_unit_id = 24769797950537768 AND system_unit.bi_rm_timestamp = 0; system_unit_fk_updated_by | system_unit_fk_created_by | system_unit_dt_created_on |syst em_unit_updated_on | system_unit_bi_rm_timestamp | machine_unit_pk_fk_system_unit_id | syst em_unit_pk_system_unit_id | system_unit_fk_organization_id | system_unit_u_system_unit_name | system_unit_in_ipv4 | system_unit_system_unit_type | system_unit_i_version | machine_unit_e_ machine_type | machine_unit_e_printer_type ---+---+--+ ---+-+---+- --++--- -+-+--+---+ -+- | 24769797950537732 | 2015-07-15 17:49:57.41029-03 | 2015-07 -15 17:49:57.410305-03 | 0 | 24769797950537768 | 24769797950537768 | 24769797950537729 | Workstation Super Legal do Richard | 10.0.0.2|2 | 1 | 50 | 0 (1 row) 1 row. ok, so far it's good. now, the whole query ... pollux=# SELECT count(*) AS count_1 pollux-# FROM system_unit, ( pollux(# SELECT system_unit.fk_updated_by AS system_unit_fk_updated_by, system_unit.fk_created_by AS system_unit_fk_created_by, system_unit.dt_created_on AS system_unit_dt_created_on, system_unit.dt_updated_on AS system_unit_updated_on, pollux(# system_unit.bi_rm_timestamp AS system_unit_bi_rm_timestamp, machine_unit.pk_fk_system_unit_id AS machine_unit_pk_fk_system_unit_id, system_unit.pk_system_unit_id AS system_unit_pk_system_unit_id, system_unit.fk_organization_id AS pollux(# system_unit_fk_organization_id, system_unit.u_system_unit_name AS system_unit_u_system_unit_name, system_unit.in_ipv4 AS system_unit_in_ipv4, system_unit.p_system_unit_type AS system_unit_system_unit_type, system_unit.i_version AS pollux(# system_unit_i_version, machine_unit.e_machine_type AS machine_unit_e_machine_type, machine_unit.e_printer_type AS machine_unit_e_printer_type pollux(# FROM system_unit JOIN machine_unit ON system_unit.pk_system_unit_id = machine_unit.pk_fk_system_unit_id pollux(# WHERE
Re: [sqlalchemy] strange behavior in count with custom mapper option
there's your two rows:
Row (None, 24769797950537732L, datetime.datetime(2015, 7, 15, 17, 49,
57, 410290, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-180,
name=None)), datetime.datetime(2015, 7, 15, 17, 49, 57, 410305,
tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-180, name=None)), 0L,
24769797950537768L, 24769797950537768L, 24769797950537729L,
u'Workstation Super Legal do Richard', '10.0.0.2', 2, 1, 50, 0)
Row (None, 24769797950537732L, datetime.datetime(2015, 7, 15, 17, 49,
57, 410290, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-180,
name=None)), datetime.datetime(2015, 7, 15, 17, 49, 57, 410305,
tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-180, name=None)), 0L,
24769797950537768L, 24769797950537768L, 24769797950537729L,
u'Workstation Super Legal do Richard', '10.0.0.2', 2, 1, 50, 0)
and you can see they both are the same. therefore count of 2, .all()
gives you one object.
On 7/15/15 3:58 PM, Richard Gerd Kuesters wrote:
right! sorry, now here we go (again):
(Pdb) import logging
(Pdb) logging.basicConfig()
(Pdb) logging.getLogger('sqlalchemy.engine').setLevel(logging.DEBUG)
(Pdb) session.query(MachineUnit).filter(MachineUnit.id_ ==
24769797950537768).count()
2015-07-15 16:56:44,565 INFO sqlalchemy.engine.base.Engine BEGIN
(implicit)
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
2015-07-15 16:56:44,566 INFO sqlalchemy.engine.base.Engine SELECT
count(*) AS count_1
FROM system_unit, (SELECT system_unit.fk_updated_by AS
system_unit_fk_updated_by, system_unit.fk_created_by AS
system_unit_fk_created_by, system_unit.dt_created_on AS
system_unit_dt_created_on, system_unit.dt_updated_on AS
system_unit_updated_on, system_unit.bi_rm_timestamp AS
system_unit_bi_rm_timestamp, machine_unit.pk_fk_system_unit_id AS
machine_unit_pk_fk_system_unit_id, system_unit.pk_system_unit_id
AS system_unit_pk_system_unit_id, system_unit.fk_organization_id
AS system_unit_fk_organization_id, system_unit.u_system_unit_name
AS system_unit_u_system_unit_name, system_unit.in_ipv4 AS
system_unit_in_ipv4, system_unit.p_system_unit_type AS
system_unit_system_unit_type, system_unit.i_version AS
system_unit_i_version, machine_unit.e_machine_type AS
machine_unit_e_machine_type, machine_unit.e_printer_type AS
machine_unit_e_printer_type
FROM system_unit JOIN machine_unit ON
system_unit.pk_system_unit_id = machine_unit.pk_fk_system_unit_id
WHERE machine_unit.pk_fk_system_unit_id = %(system_unit_id_1)s AND
system_unit.bi_rm_timestamp = %(rm_timestamp_1)s) AS anon_1
WHERE system_unit.bi_rm_timestamp = %(rm_timestamp_2)s
INFO:sqlalchemy.engine.base.Engine:SELECT count(*) AS count_1
FROM system_unit, (SELECT system_unit.fk_updated_by AS
system_unit_fk_updated_by, system_unit.fk_created_by AS
system_unit_fk_created_by, system_unit.dt_created_on AS
system_unit_dt_created_on, system_unit.dt_updated_on AS
system_unit_updated_on, system_unit.bi_rm_timestamp AS
system_unit_bi_rm_timestamp, machine_unit.pk_fk_system_unit_id AS
machine_unit_pk_fk_system_unit_id, system_unit.pk_system_unit_id
AS system_unit_pk_system_unit_id, system_unit.fk_organization_id
AS system_unit_fk_organization_id, system_unit.u_system_unit_name
AS system_unit_u_system_unit_name, system_unit.in_ipv4 AS
system_unit_in_ipv4, system_unit.p_system_unit_type AS
system_unit_system_unit_type, system_unit.i_version AS
system_unit_i_version, machine_unit.e_machine_type AS
machine_unit_e_machine_type, machine_unit.e_printer_type AS
machine_unit_e_printer_type
FROM system_unit JOIN machine_unit ON
system_unit.pk_system_unit_id = machine_unit.pk_fk_system_unit_id
WHERE machine_unit.pk_fk_system_unit_id = %(system_unit_id_1)s AND
system_unit.bi_rm_timestamp = %(rm_timestamp_1)s) AS anon_1
WHERE system_unit.bi_rm_timestamp = %(rm_timestamp_2)s
2015-07-15 16:56:44,566 INFO sqlalchemy.engine.base.Engine
{'rm_timestamp_1': 0, 'system_unit_id_1': 24769797950537768,
'rm_timestamp_2': 0}
INFO:sqlalchemy.engine.base.Engine:{'rm_timestamp_1': 0,
'system_unit_id_1': 24769797950537768, 'rm_timestamp_2': 0}
2015-07-15 16:56:44,567 DEBUG sqlalchemy.engine.base.Engine Col
('count_1',)
DEBUG:sqlalchemy.engine.base.Engine:Col ('count_1',)
2015-07-15 16:56:44,568 DEBUG sqlalchemy.engine.base.Engine Row (2L,)
DEBUG:sqlalchemy.engine.base.Engine:Row (2L,)
2L
(Pdb) len(session.query(MachineUnit).filter(MachineUnit.id_ ==
24769797950537768).all())
2015-07-15 16:57:06,070 INFO sqlalchemy.engine.base.Engine SELECT
system_unit.fk_updated_by AS system_unit_fk_updated_by,
system_unit.fk_created_by AS system_unit_fk_created_by,
system_unit.dt_created_on AS system_unit_dt_created_on,
system_unit.dt_updated_on AS system_unit_updated_on,
system_unit.bi_rm_timestamp AS system_unit_bi_rm_timestamp,
