a self contained version of "before" is attached, seems to work as is (works in 0.8 too). Modify it to show me it not working.
output: SELECT t_ordered.pkg_name AS t_ordered_pkg_name, t_ordered.version AS t_ordered_version, t_ordered.revision AS t_ordered_revision, t_ordered."appType" AS "t_ordered_appType", t_ordered.environment_id AS t_ordered_environment_id, t_ordered.realized AS t_ordered_realized, t_ordered."user" AS t_ordered_user, t_ordered.status AS t_ordered_status FROM (SELECT packages.pkg_name AS pkg_name, packages.version AS version, packages.revision AS revision, app_definitions."appType" AS "appType", app_deployments.environment_id AS environment_id, (SELECT environments.environment FROM environments WHERE environments."environmentID" = app_deployments.environment_id) AS anon_1, app_deployments.realized AS realized, app_deployments."user" AS "user", app_deployments.status AS status FROM packages JOIN deployments ON packages.package_id = deployments.package_id JOIN app_deployments ON deployments."DeploymentID" = app_deployments."DeploymentID" JOIN app_definitions ON app_definitions."AppID" = app_deployments."AppID" WHERE app_deployments.status != :status_1 AND (SELECT environments.environment FROM environments WHERE environments."environmentID" = app_deployments.environment_id) = :param_1 ORDER BY app_deployments.realized DESC) AS t_ordered GROUP BY t_ordered."appType", t_ordered.environment_id, t_ordered.pkg_name HAVING t_ordered.status LIKE :status_2 AND unix_timestamp(t_ordered.realized) < unix_timestamp(now()) - :unix_timestamp_1 On 6/26/14, 7:41 PM, Ken Lareau wrote: > On Thu, Jun 26, 2014 at 1:45 PM, Mike Bayer <mike...@zzzcomputing.com > <mailto:mike...@zzzcomputing.com>> wrote: > > minimal mapping + the query against that mapping. > > Okay, for the other method I needed to change, I think I've put together > complete enough examples and have attached the files. The 'before' > file is what we have currently (and doesn't quite do the right thing as > it's only using the environment_id), and the 'after' file is what > seemingly > worked after my coworker and I tried a different route to avoid having > to use a subquery. One thing to note is there's a reciprocating rela- > tionship added on the Deployment class to the Package class which > initially caused a lovely stack overflow in Python until the joinedload > options were added; this may be a bad idea, but it was the only way > we knew how to get things working the way we needed. :) > > I tried to add all the imports as well in case there were any questions > that might come from the code itself; hopefully I didn't miss any, but > if there's still confusion, let me know. > > - Ken > > > > > On 6/26/14, 4:11 PM, Ken Lareau wrote: >> On Thu, Jun 26, 2014 at 5:57 AM, Mike Bayer >> <mike...@zzzcomputing.com <mailto:mike...@zzzcomputing.com>> wrote: >> >> >> On 6/25/14, 8:06 PM, Ken Lareau wrote: >>> On Wed, Jun 25, 2014 at 6:28 AM, Mike Bayer >>> <mike...@zzzcomputing.com <mailto:mike...@zzzcomputing.com>> >>> wrote: >>> >>> >>> On 6/25/14, 2:26 AM, Ken Lareau wrote: >>>> On Tue, Jun 24, 2014 at 3:35 PM, Mike Bayer >>>> <mike...@zzzcomputing.com >>>> <mailto:mike...@zzzcomputing.com>> wrote: >>>> >>>> >>>> On 6/23/14, 8:09 PM, Ken Lareau wrote: >>>> > >>>> > if apptier: >>>> > subq = ( >>>> > Session.query( >>>> > Package.pkg_name, >>>> > Package.version, >>>> > Package.revision, >>>> > AppDefinition.app_type, >>>> > AppDeployment.environment >>>> > ).join(Deployment) >>>> > .join(AppDeployment) >>>> > .join(AppDefinition) >>>> > .filter(Package.pkg_name == >>>> package_name) >>>> > .filter(AppDeployment.environment == >>>> env) >>>> > .filter(AppDeployment.status != >>>> 'invalidated')) >>>> > >>>> > [...] >>>> > >>>> > # The actual column name must be used in >>>> the subquery >>>> > # usage below; DB itself should be corrected >>>> > versions = (Session.query(subq.c.appType, >>>> > subq.c.version, >>>> > subq.c.revision) >>>> > .group_by(subq.c.appType, >>>> subq.c.environment) >>>> > .all()) >>>> > >>>> > The parts in the subquery work fine, but the >>>> access of the 'column' >>>> > in the final query leads to this: >>>> > >>>> > Traceback (most recent call last): >>>> > File "<stdin>", line 1, in <module> >>>> > File >>>> >>>> "/home/klareau/repos/git/tagopsdb/tagopsdb/deploy/deploy.py", >>>> > line 234, in find_deployed_version >>>> > .group_by(subq.c.appType, subq.c.environment) >>>> > File >>>> > >>>> >>>> "/home/klareau/.virtualenvs/tds/lib/python2.6/site-packages/sqlalchemy/util/_collections.py", >>>> > line 174, in __getattr__ >>>> > raise AttributeError(key) >>>> > AttributeError: environment >>>> > >>>> > This is not completely surprising, but I'm >>>> uncertain as the best way >>>> > to fix this... help? :) >>>> nothing is obviously wrong, assuming you are >>>> calling "subquery()" on >>>> subq at the end. It will be a select() construct >>>> which will have a >>>> column called ".environment" on the .c. collection >>>> because >>>> AppDeployment.environment is present. Can't say >>>> why this attribute is >>>> not here without full details. >>>> >>>> >>>> Oops there were some additional filters I left out, but >>>> this is added >>>> before the final query: >>>> >>>> subq = (subq.order_by(AppDeployment.realized.desc()) >>>> .subquery(name='t_ordered')) >>>> >>>> Not sure why it isn't working, but today my coworker an >>>> I massively >>>> rewrote one of the other methods to avoid this same >>>> issue, so maybe >>>> we should try the same for this one... though it would >>>> be nice to know >>>> what I goofed here, but not sure what additional >>>> information I can >>>> give that would help offhand... >>> >>> what are the names that are actually present on subq.c >>> ? "print list(subq.c)" should give an indication. >>> >>> >>> Adding that in to the method gave this: >>> >>> >>> deploy.find_deployed_version('tdstest', 'development', >>> version='9', apptier=True) >>> [Column('pkg_name', String(length=255), table=<t_ordered>, >>> nullable=False), Column('version', String(length=63), >>> table=<t_ordered>, nullable=False), Column('revision', >>> String(length=63), table=<t_ordered>, nullable=False), >>> Column('appType', String(length=100), table=<t_ordered>, >>> nullable=False), <sqlalchemy.sql.elements.ColumnClause at >>> 0x1d20e10; %(30521360 anon)s>] >>> Traceback (most recent call last): >>> File "<stdin>", line 1, in <module> >>> File >>> "/home/klareau/repos/git/tagopsdb/tagopsdb/deploy/deploy.py", line >>> 237, in find_deployed_version >>> .group_by(subq.c.appType, subq.c.environment) >>> File >>> >>> "/home/klareau/.virtualenvs/tds/lib/python2.6/site-packages/sqlalchemy/util/_collections.py", >>> line 174, in __getattr__ >>> raise AttributeError(key) >>> AttributeError: environment >> you need to put .label('environment') on that column before >> it finds its way into subq. I dont have the mappings here to >> review. >> >> for the next go around please just provide a one-file example >> of the mapping plus the query, thanks. >> >> Do you desire the example to be fully functional, or just have >> the relevant >> mapping and query? We ran into the same issue with another query and >> ended up completely reworking the code to avoid the subquery, but >> it in- >> volves quite a bit of various mappings and I'm uncertain if it's >> the proper >> way to move forward. I could try to post the before/after here, >> but if it >> needs to be able to to run standalone it may take me a bit of >> time to get >> it into a fully workable state (given how extensive it is). >> >> >> -- >> - 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 >> <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. > > > > > -- > - 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 > <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.
from sqlalchemy import * from sqlalchemy.dialects.mysql import INTEGER, SMALLINT, TIMESTAMP from sqlalchemy.ext.hybrid import hybrid_property from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base import datetime Base = declarative_base() class AppDefinition(Base): __tablename__ = 'app_definitions' id = Column(u'AppID', SMALLINT(display_width=2), primary_key=True) app_type = Column(u'appType', String(length=100), nullable=False) status = Column( Enum('active', 'inactive'), nullable=False, server_default='active' ) app_deployments = relationship('AppDeployment') class AppDeployment(Base): __tablename__ = 'app_deployments' id = Column(u'AppDeploymentID', INTEGER(), primary_key=True) deployment_id = Column( u'DeploymentID', INTEGER(), ForeignKey('deployments.DeploymentID', ondelete='cascade'), nullable=False ) app_id = Column( u'AppID', SMALLINT(display_width=6), ForeignKey('app_definitions.AppID', ondelete='cascade'), nullable=False ) user = Column(String(length=32), nullable=False) status = Column( Enum( 'complete', 'incomplete', 'inprogress', 'invalidated', 'validated', ), nullable=False ) environment_id = Column( u'environment_id', INTEGER(), ForeignKey('environments.environmentID', ondelete='cascade'), nullable=False ) realized = Column( TIMESTAMP(), nullable=False, server_default=func.current_timestamp() ) application = relationship('AppDefinition') deployment = relationship('Deployment') 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() class Deployment(Base): __tablename__ = 'deployments' id = Column(u'DeploymentID', INTEGER(), primary_key=True) package_id = Column( INTEGER(), ForeignKey('packages.package_id', ondelete='cascade'), nullable=False ) app_deployments = relationship('AppDeployment') class Environment(Base): __tablename__ = 'environments' id = Column(u'environmentID', INTEGER(), primary_key=True) environment = Column(String(length=15), nullable=False, unique=True) class Package(Base): __tablename__ = 'packages' id = Column(u'package_id', INTEGER(), primary_key=True) pkg_name = Column(String(length=255), nullable=False) version = Column(String(length=63), nullable=False) revision = Column(String(length=63), nullable=False) deployments = relationship('Deployment') Session = scoped_session(sessionmaker()) subq = (Session.query(Package.pkg_name, Package.version, Package.revision, AppDefinition.app_type, AppDeployment.environment_id, AppDeployment.environment, AppDeployment.realized, AppDeployment.user, AppDeployment.status) .join(Deployment) .join(AppDeployment) .join(AppDefinition) .filter(AppDeployment.status != 'invalidated') .filter(AppDeployment.environment == "some environment") .order_by(AppDeployment.realized.desc()) .subquery(name='t_ordered')) q = (Session.query(subq.c.pkg_name, subq.c.version, subq.c.revision, subq.c.appType, subq.c.environment_id, subq.c.realized, subq.c.user, subq.c.status) .group_by(subq.c.appType, subq.c.environment_id, subq.c.pkg_name) .having(and_(subq.c.status.like('%complete'), func.unix_timestamp(subq.c.realized) < func.unix_timestamp(func.now()) - datetime.timedelta(seconds=60))) ) print q