Hi Michael,
Thanks for the speedy reply!
That makes sense.
Ok - so in this case, how would we actually get sqlalchemy to return N
distinct 'content' items, rather than N content-plus-joined-table sets?
All the results have full primary keys, so its more likely they are being
de-duped.
Thank you,
Damian
On Tuesday, June 7, 2016 at 6:34:02 PM UTC+4, Mike Bayer wrote:
>
>
>
> On 06/07/2016 03:34 AM, Damian Dimmich wrote:
> > Hi,
> >
> > We've been staring at a problem for some time, where an sqlalchemy
> > generated query we run returns less objects when run with .all() than
> > the number given to us with a .count().
> >
> > The same, generated query which we get in our logs/from print(query)
> > returns the same number of rows as the count() when run through
> > DBSession.execute("...").
> >
> > This happens with both sqlalchemy 1.0.12 and 1.0.13
> >
>
>
> the Query.all() method, assuming you are querying for complete
> instances, will first off deduplicate a redundant instance, and secondly
> if a row does not contain a full primary key, no instance will be
> generated for that row. The difference in row count is probably due to
> one of these factors.
>
>
>
> >
> > So - running the below by hand using execute in the DBSession gives us
> > the correct row count:
> >
> > SELECT content.id AS content_id, content.title AS
> content_title,
> > content.subtitle AS content_subtitle, content.teaser AS content_teaser,
> > content.published_date AS content_published_date, content.published AS
> > content_published, content.created_date AS content_created_date,
> > content.microscription AS content_microscription,
> > content.product_category_id AS content_product_category_id,
> > content.discipline_type_id AS content_discipline_type_id,
> > content.research_approach_id AS content_research_approach_id,
> > content.weighting_action_id AS content_weighting_action_id,
> > content.weighting_id AS content_weighting_id, content.channel_id AS
> > content_channel_id, content.provider_id AS content_provider_id,
> > content.thumb_url AS content_thumb_url, content.pdf AS content_pdf,
> > content.meta AS content_meta, content.guide_price AS
> > content_guide_price, content.currency_id AS content_currency_id,
> > content.fade_pricing AS content_fade_pricing, content.min_fade_price AS
> > content_min_fade_price, content.is_research AS content_is_research,
> > content.non_material AS content_non_material, content.subscription_only
> > AS content_subscription_only FROM content
> > LEFT OUTER JOIN (content_sector_ass AS content_sector_ass_1
> > JOIN sectors AS sectors_1 ON sectors_1.id =
> > content_sector_ass_1.sector_id) ON content.id =
> > content_sector_ass_1.content_id
> > LEFT OUTER JOIN (content_asset_class_ass AS
> > content_asset_class_ass_1 JOIN asset_classes AS asset_classes_1 ON
> > asset_classes_1.id = content_asset_class_ass_1.asset_class_id) ON
> > content.id = content_asset_class_ass_1.content_id
> > LEFT OUTER JOIN (content_specialty_ass AS
> > content_specialty_ass_1 JOIN specialties AS specialties_1 ON
> > specialties_1.id = content_specialty_ass_1.specialty_id) ON content.id
> =
> > content_specialty_ass_1.content_id
> > WHERE
> > content.published = true
> > AND content.published_date <= '2016-06-07'
> > AND (
> > asset_classes_1.name = 'Equity'
> > OR
> > specialties_1.name = 'FundamentalGeneralEquity'
> > )
> > AND sectors_1.name = 'Consumer Discretionary'
> > ORDER BY content.published_date DESC, content.created_date DESC
> >
> > return 1663 rows.
> >
> > whereas running the sqlalchemy generated query that returns "Content"
> > objects:
> >
> > 2016-06-07 11:24:29,477 INFO [sqlalchemy.engine.base.Engine][waitress]
> >
> > SELECT content.id AS content_id, content.title AS content_title,
> > content.subtitle AS content_subtitle, content.teaser AS content_teaser,
> > content.published_date AS content_published_date, content.published AS
> > content_published, content.created_date AS content_created_date,
> > content.microscription AS content_microscription,
> > content.product_category_id AS content_product_category_id,
> > content.discipline_type_id AS content_discipline_type_id,
> > content.research_approach_id AS content_research_approach_id,
> > content.weighting_action_id AS content_weighting_action_id,
> > content.weighting_id AS content_weighting_id, content.channel_id AS
> > content_channel_id, content.provider_id AS content_provider_id,
> > content.thumb_url AS content_thumb_url, content.pdf AS content_pdf,
> > content.meta AS content_meta, content.guide_price AS
> > content_guide_price, content.currency_id AS content_currency_id,
> > content.fade_pricing AS content_fade_pricing, content.min_fade_price AS
> > content_min_fade_price, content.is_research AS content_is_research,
> > content.non_material AS content_non_material, content.subscription_only
> > AS content_subscription_only
> > FROM content
> >
> > LEFT OUTER JOIN (content_sector_ass AS content_sector_ass_1 JOIN
> > sectors AS sectors_1 ON sectors_1.id = content_sector_ass_1.sector_id)
> > ON content.id = content_sector_ass_1.content_id
> >
> > LEFT OUTER JOIN (content_asset_class_ass AS
> > content_asset_class_ass_1 JOIN asset_classes AS asset_classes_1 ON
> > asset_classes_1.id = content_asset_class_ass_1.asset_class_id) ON
> > content.id = content_asset_class_ass_1.content_id
> >
> > LEFT OUTER JOIN (content_specialty_ass AS content_specialty_ass_1
> > JOIN specialties AS specialties_1 ON specialties_1.id =
> > content_specialty_ass_1.specialty_id) ON content.id =
> > content_specialty_ass_1.content_id
> >
> > WHERE
> >
> > content.published = true
> >
> > AND content.published_date <= %(published_date_1)s
> >
> > AND (
> >
> > asset_classes_1.name = %(name_1)s
> >
> > OR specialties_1.name = %(name_2)s
> >
> > )
> >
> > AND sectors_1.name = %(name_3)s ORDER BY content.published_date
> > DESC, content.created_date DESC
> >
> > 2016-06-07 11:24:29,478 INFO [sqlalchemy.engine.base.Engine][waitress]
> >
> > {'name_2': 'FundamentalGeneralEquity',
> >
> > 'name_3': 'Consumer Discretionary',
> >
> > 'name_1': 'Equity',
> >
> > 'param_1': 24, 'published_date_1': datetime.date(2016, 6, 7)}
> >
> > returns 619 Content objects. This is computed by running len(result)
> > where result = query.all()
> >
> > I'm a bit stuck - not sure where to begin debugging. As far as I can
> > tell the query that is generated is correct, and the database returns
> > the correct set of results, however some of these seem to get 'lost'
> > when sqlalchemy assembles the result set.
> >
> > Any ideas?
> >
> > Damian
> >
> >
>
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
