Re: [sqlalchemy] A question regarding hybrid properties, relationships and schema changes
On Thu, Jun 26, 2014 at 7:47 PM, Ken Lareau klar...@tagged.com wrote:
On Jun 26, 2014 7:40 PM, Mike Bayer mike...@zzzcomputing.com wrote:
right, so a few emails ago I said:
you need to put .label('environment') on that column before it finds
its way into subq. I dont have the mappings here to review.
here's that:
@environment.expression
def environment(cls):
return select(
[Environment.environment]
).where(
Environment.id == cls.environment_id
).correlate(cls).label('environment')
Aha... thanks. Now I'm afraid to ask if you looked at the after file,
and if so, considered it insane. :)
- Ken
On 6/26/14, 9:50 PM, Ken Lareau wrote:
Done, new file attached (this gives the same error message as the one I
showed initially, at least on my system).
- Ken
--
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.
--
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.
Okay, trying the updated code worked with queries, but apparently not
with an insert. Here's the basic code involved:
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()
)
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).\
label('environment')
and
def _calculate_environment_id(environment):
return (Session.query(Environment.id)
.filter_by(environment=environment)
.one())[0]
def add_app_deployment(dep_id, app_id, user, status, environment):
Add a tier deployment for a given deployment ID
environment_id = _calculate_environment_id(environment)
app_dep = AppDeployment(
deployment_id=dep_id,
app_id=app_id,
user=user,
status=status,
environment_id=environment_id,
realized=func.current_timestamp()
)
# Commit to DB immediately
Session.add(app_dep)
Session.commit()
return app_dep
An actual call to add_app_deployment() results in this exception:
File util.pyx, line 91, in oursql._do_warnings_query
(oursqlx/oursql.c:3969)
sqlalchemy.exc.DBAPIError: (CollatedWarningsError) (None, 'query caused
warnings', [(class 'oursql.Warning', (uField 'environment' doesn't have
a default value, 1364L))]) u'INSERT INTO app_deployments (`DeploymentID`,
`AppID`, user, status, environment_id, realized) VALUES (?, ?, ?, ?, ?,
CURRENT_TIMESTAMP)' (4162L, 405L, 'klareau', 'inprogress', 1L)
I'm guessing something's still not quite right with the 'environment'
hybrid property
in the class? :-/
--
- 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.
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] A question regarding hybrid properties, relationships and schema changes
**SIGH** Please ignore last message... I had forgotten to actually
update the database schema itself. :( Pardon me while I go shoot
myself...
- Ken
On Tue, Jul 1, 2014 at 4:19 PM, Ken Lareau klar...@tagged.com wrote:
On Thu, Jun 26, 2014 at 7:47 PM, Ken Lareau klar...@tagged.com wrote:
On Jun 26, 2014 7:40 PM, Mike Bayer mike...@zzzcomputing.com wrote:
right, so a few emails ago I said:
you need to put .label('environment') on that column before it finds
its way into subq. I dont have the mappings here to review.
here's that:
@environment.expression
def environment(cls):
return select(
[Environment.environment]
).where(
Environment.id == cls.environment_id
).correlate(cls).label('environment')
Aha... thanks. Now I'm afraid to ask if you looked at the after file,
and if so, considered it insane. :)
- Ken
On 6/26/14, 9:50 PM, Ken Lareau wrote:
Done, new file attached (this gives the same error message as the one
I showed initially, at least on my system).
- Ken
--
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.
--
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.
Okay, trying the updated code worked with queries, but apparently not
with an insert. Here's the basic code involved:
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()
)
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).\
label('environment')
and
def _calculate_environment_id(environment):
return (Session.query(Environment.id)
.filter_by(environment=environment)
.one())[0]
def add_app_deployment(dep_id, app_id, user, status, environment):
Add a tier deployment for a given deployment ID
environment_id = _calculate_environment_id(environment)
app_dep = AppDeployment(
deployment_id=dep_id,
app_id=app_id,
user=user,
status=status,
environment_id=environment_id,
realized=func.current_timestamp()
)
# Commit to DB immediately
Session.add(app_dep)
Session.commit()
return app_dep
An actual call to add_app_deployment() results in this exception:
File util.pyx, line 91, in oursql._do_warnings_query
(oursqlx/oursql.c:3969)
sqlalchemy.exc.DBAPIError: (CollatedWarningsError) (None, 'query caused
warnings', [(class 'oursql.Warning', (uField 'environment' doesn't have
a default value, 1364L))]) u'INSERT INTO app_deployments (`DeploymentID`,
`AppID`, user, status, environment_id, realized) VALUES (?, ?, ?, ?, ?,
CURRENT_TIMESTAMP)' (4162L, 405L, 'klareau', 'inprogress', 1L)
I'm guessing something's still not quite right with the 'environment'
hybrid property
in the class? :-/
--
- Ken Lareau
--
- Ken Lareau
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and
Re: [sqlalchemy] A question regarding hybrid properties, relationships and schema changes
On Thu, Jun 26, 2014 at 5:57 AM, Mike Bayer 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
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
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.
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] A question regarding hybrid properties, relationships and schema changes
minimal mapping + the query against that mapping.
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
Re: [sqlalchemy] A question regarding hybrid properties, relationships and schema changes
On Thu, Jun 26, 2014 at 1:45 PM, Mike Bayer 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
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
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
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
Re: [sqlalchemy] A question regarding hybrid properties, relationships and schema changes
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
Re: [sqlalchemy] A question regarding hybrid properties, relationships and schema changes
Done, new file attached (this gives the same error message as the one I
showed initially, at least on my system).
- Ken
--
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,
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,
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
Re: [sqlalchemy] A question regarding hybrid properties, relationships and schema changes
right, so a few emails ago I said:
you need to put .label('environment') on that column before it finds
its way into subq. I dont have the mappings here to review.
here's that:
@environment.expression
def environment(cls):
return select(
[Environment.environment]
).where(
Environment.id == cls.environment_id
).correlate(cls).label('environment')
On 6/26/14, 9:50 PM, Ken Lareau wrote:
Done, new file attached (this gives the same error message as the one
I showed initially, at least on my system).
- Ken
--
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] A question regarding hybrid properties, relationships and schema changes
On Jun 26, 2014 7:40 PM, Mike Bayer mike...@zzzcomputing.com wrote:
right, so a few emails ago I said:
you need to put .label('environment') on that column before it finds
its way into subq. I dont have the mappings here to review.
here's that:
@environment.expression
def environment(cls):
return select(
[Environment.environment]
).where(
Environment.id == cls.environment_id
).correlate(cls).label('environment')
Aha... thanks. Now I'm afraid to ask if you looked at the after file,
and if so, considered it insane. :)
- Ken
On 6/26/14, 9:50 PM, Ken Lareau wrote:
Done, new file attached (this gives the same error message as the one I
showed initially, at least on my system).
- Ken
--
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.
--
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.
--
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] A question regarding hybrid properties, relationships and schema changes
On Tue, Jun 24, 2014 at 3:35 PM, Mike Bayer 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... -- - 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. 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] A question regarding hybrid properties, relationships and schema changes
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. -- - 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.
Re: [sqlalchemy] A question regarding hybrid properties, relationships and schema changes
Anyone? This has me dead in the water and nothing in the documentation
has been helping.
- Ken
On Mon, Jun 23, 2014 at 5:09 PM, Ken Lareau klar...@tagged.com wrote:
On Fri, Jun 20, 2014 at 3:46 PM, Ken Lareau klar...@tagged.com wrote:
On Fri, Jun 20, 2014 at 1:23 PM, Mike Bayer mike...@zzzcomputing.com
wrote:
On 6/20/14, 3:38 PM, Ken Lareau wrote:
So in the ongoing improvement of one of our internal databases, we
created
a new table named 'environments' whose SQLA code looks something like
this:
class Environment(Base):
__tablename__ = 'environments'
id = Column(u'environmentID', INTEGER(), primary_key=True)
environment = Column(String(length=15), nullable=False, unique=True)
env = Column(String(length=12), nullable=False, unique=True)
domain = Column(String(length=32), nullable=False, unique=True)
prefix = Column(String(length=1), nullable=False)
Two of our tables recently needed conversion to stop using their own
local
'environment' column to using this table. The first part's been put
in place
(a new foreign key for 'environment_id'), but to prevent large swaths
of code
from needing changes, a thought of using a hybrid property might allow
the
change to be hidden (until the code using it had been rewritten at
least).
My naive attempt was the following (just the relevant snippet):
environment_obj = relationship('Environment')
@hybrid_property
def environment(self):
return self.environment_obj.environment
Unfortunately (and in hindsight for obvious reasons), this code
doesn't work,
what does doesn't work mean here? This will work at the instance
level. At the query level, not so much, that's true, if you truly want no
code to change you'd need to implement an @expression here that's a little
inefficient, as it needs to do a correlated subq:
Yes, the doesn't work was specifically related to an attempt to use it
in
a query, which of course failed miserably. :)
class HasEnv(Base):
__tablename__ = 'has_env'
id = Column(INTEGER, primary_key=True)
environment_id = Column(ForeignKey('environments.environmentID'))
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()
s = Session()
print s.query(HasEnv).filter(HasEnv.environment == 'some env')
output:
SELECT has_env.id AS has_env_id, has_env.environment_id AS
has_env_environment_id
FROM has_env
WHERE (SELECT environments.environment
FROM environments
WHERE environments.environmentID = has_env.environment_id) = :param_1
wont perform well from a SQL perspective but will do the job...
This worked perfectly, thank you! This is honestly a stop-gap measure
to allow much of the code to be rewritten (after which it can be removed),
and for what it's being used for at the moment, it won't be too bad
regarding
performance.
but a very brief conversation with someone on the #sqlalchemy
channel on
Freenode indicated there was no way to do this and all the relevant
code
must be reworked. While it's only a few dozen places this occurs, I can
see
this coming up again in the future as further schema refactorings occur,
so
I turn to those with more expertise to find out if there is a way to
accomplish
what I desire, or if there's really no hope. :) Any insight would be
greatly
appreciated.
I don't know how to fix this issue with IRC and stackoverflow that
people constantly are getting bad information.
Heh, Sometimes I think I should know better about asking for help
on IRC, but sometimes I get lucky. In this case, I decided I might
have better luck on the mailing list after the answer I got on IRC
was very unsatisfactory. :)
Thanks for the help!
D'oh, seems like I spoke too soon; there's a specific instance where
this fails due to some subquery stuff I'm doing:
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)
Re: [sqlalchemy] A question regarding hybrid properties, relationships and schema changes
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. -- 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] A question regarding hybrid properties, relationships and schema changes
On Fri, Jun 20, 2014 at 3:46 PM, Ken Lareau klar...@tagged.com wrote:
On Fri, Jun 20, 2014 at 1:23 PM, Mike Bayer mike...@zzzcomputing.com
wrote:
On 6/20/14, 3:38 PM, Ken Lareau wrote:
So in the ongoing improvement of one of our internal databases, we
created
a new table named 'environments' whose SQLA code looks something like
this:
class Environment(Base):
__tablename__ = 'environments'
id = Column(u'environmentID', INTEGER(), primary_key=True)
environment = Column(String(length=15), nullable=False, unique=True)
env = Column(String(length=12), nullable=False, unique=True)
domain = Column(String(length=32), nullable=False, unique=True)
prefix = Column(String(length=1), nullable=False)
Two of our tables recently needed conversion to stop using their own
local
'environment' column to using this table. The first part's been put in
place
(a new foreign key for 'environment_id'), but to prevent large swaths
of code
from needing changes, a thought of using a hybrid property might allow
the
change to be hidden (until the code using it had been rewritten at
least).
My naive attempt was the following (just the relevant snippet):
environment_obj = relationship('Environment')
@hybrid_property
def environment(self):
return self.environment_obj.environment
Unfortunately (and in hindsight for obvious reasons), this code doesn't
work,
what does doesn't work mean here? This will work at the instance
level. At the query level, not so much, that's true, if you truly want no
code to change you'd need to implement an @expression here that's a little
inefficient, as it needs to do a correlated subq:
Yes, the doesn't work was specifically related to an attempt to use it in
a query, which of course failed miserably. :)
class HasEnv(Base):
__tablename__ = 'has_env'
id = Column(INTEGER, primary_key=True)
environment_id = Column(ForeignKey('environments.environmentID'))
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()
s = Session()
print s.query(HasEnv).filter(HasEnv.environment == 'some env')
output:
SELECT has_env.id AS has_env_id, has_env.environment_id AS
has_env_environment_id
FROM has_env
WHERE (SELECT environments.environment
FROM environments
WHERE environments.environmentID = has_env.environment_id) = :param_1
wont perform well from a SQL perspective but will do the job...
This worked perfectly, thank you! This is honestly a stop-gap measure
to allow much of the code to be rewritten (after which it can be removed),
and for what it's being used for at the moment, it won't be too bad
regarding
performance.
but a very brief conversation with someone on the #sqlalchemy channel
on
Freenode indicated there was no way to do this and all the relevant code
must be reworked. While it's only a few dozen places this occurs, I can
see
this coming up again in the future as further schema refactorings occur,
so
I turn to those with more expertise to find out if there is a way to
accomplish
what I desire, or if there's really no hope. :) Any insight would be
greatly
appreciated.
I don't know how to fix this issue with IRC and stackoverflow that people
constantly are getting bad information.
Heh, Sometimes I think I should know better about asking for help
on IRC, but sometimes I get lucky. In this case, I decided I might
have better luck on the mailing list after the answer I got on IRC
was very unsatisfactory. :)
Thanks for the help!
D'oh, seems like I spoke too soon; there's a specific instance where
this fails due to some subquery stuff I'm doing:
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):
[sqlalchemy] A question regarding hybrid properties, relationships and schema changes
So in the ongoing improvement of one of our internal databases, we created
a new table named 'environments' whose SQLA code looks something like
this:
class Environment(Base):
__tablename__ = 'environments'
id = Column(u'environmentID', INTEGER(), primary_key=True)
environment = Column(String(length=15), nullable=False, unique=True)
env = Column(String(length=12), nullable=False, unique=True)
domain = Column(String(length=32), nullable=False, unique=True)
prefix = Column(String(length=1), nullable=False)
Two of our tables recently needed conversion to stop using their own local
'environment' column to using this table. The first part's been put in
place
(a new foreign key for 'environment_id'), but to prevent large swaths of
code
from needing changes, a thought of using a hybrid property might allow the
change to be hidden (until the code using it had been rewritten at least).
My naive attempt was the following (just the relevant snippet):
environment_obj = relationship('Environment')
@hybrid_property
def environment(self):
return self.environment_obj.environment
Unfortunately (and in hindsight for obvious reasons), this code doesn't
work,
but a very brief conversation with someone on the #sqlalchemy channel on
Freenode indicated there was no way to do this and all the relevant code
must be reworked. While it's only a few dozen places this occurs, I can see
this coming up again in the future as further schema refactorings occur, so
I turn to those with more expertise to find out if there is a way to
accomplish
what I desire, or if there's really no hope. :) Any insight would be
greatly
appreciated.
--
- 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.
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] A question regarding hybrid properties, relationships and schema changes
On 6/20/14, 3:38 PM, Ken Lareau wrote:
So in the ongoing improvement of one of our internal databases, we created
a new table named 'environments' whose SQLA code looks something like
this:
class Environment(Base):
__tablename__ = 'environments'
id = Column(u'environmentID', INTEGER(), primary_key=True)
environment = Column(String(length=15), nullable=False, unique=True)
env = Column(String(length=12), nullable=False, unique=True)
domain = Column(String(length=32), nullable=False, unique=True)
prefix = Column(String(length=1), nullable=False)
Two of our tables recently needed conversion to stop using their own local
'environment' column to using this table. The first part's been put
in place
(a new foreign key for 'environment_id'), but to prevent large swaths
of code
from needing changes, a thought of using a hybrid property might allow the
change to be hidden (until the code using it had been rewritten at least).
My naive attempt was the following (just the relevant snippet):
environment_obj = relationship('Environment')
@hybrid_property
def environment(self):
return self.environment_obj.environment
Unfortunately (and in hindsight for obvious reasons), this code
doesn't work,
what does doesn't work mean here? This will work at the instance
level. At the query level, not so much, that's true, if you truly want
no code to change you'd need to implement an @expression here that's a
little inefficient, as it needs to do a correlated subq:
class HasEnv(Base):
__tablename__ = 'has_env'
id = Column(INTEGER, primary_key=True)
environment_id = Column(ForeignKey('environments.environmentID'))
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()
s = Session()
print s.query(HasEnv).filter(HasEnv.environment == 'some env')
output:
SELECT has_env.id AS has_env_id, has_env.environment_id AS
has_env_environment_id
FROM has_env
WHERE (SELECT environments.environment
FROM environments
WHERE environments.environmentID = has_env.environment_id) = :param_1
wont perform well from a SQL perspective but will do the job...
but a very brief conversation with someone on the #sqlalchemy channel on
Freenode indicated there was no way to do this and all the relevant code
must be reworked. While it's only a few dozen places this occurs, I
can see
this coming up again in the future as further schema refactorings
occur, so
I turn to those with more expertise to find out if there is a way to
accomplish
what I desire, or if there's really no hope. :) Any insight would be
greatly
appreciated.
I don't know how to fix this issue with IRC and stackoverflow that
people constantly are getting bad information.
--
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] A question regarding hybrid properties, relationships and schema changes
On Fri, Jun 20, 2014 at 1:23 PM, Mike Bayer mike...@zzzcomputing.com
wrote:
On 6/20/14, 3:38 PM, Ken Lareau wrote:
So in the ongoing improvement of one of our internal databases, we
created
a new table named 'environments' whose SQLA code looks something like
this:
class Environment(Base):
__tablename__ = 'environments'
id = Column(u'environmentID', INTEGER(), primary_key=True)
environment = Column(String(length=15), nullable=False, unique=True)
env = Column(String(length=12), nullable=False, unique=True)
domain = Column(String(length=32), nullable=False, unique=True)
prefix = Column(String(length=1), nullable=False)
Two of our tables recently needed conversion to stop using their own
local
'environment' column to using this table. The first part's been put in
place
(a new foreign key for 'environment_id'), but to prevent large swaths of
code
from needing changes, a thought of using a hybrid property might allow
the
change to be hidden (until the code using it had been rewritten at
least).
My naive attempt was the following (just the relevant snippet):
environment_obj = relationship('Environment')
@hybrid_property
def environment(self):
return self.environment_obj.environment
Unfortunately (and in hindsight for obvious reasons), this code doesn't
work,
what does doesn't work mean here? This will work at the instance
level. At the query level, not so much, that's true, if you truly want no
code to change you'd need to implement an @expression here that's a little
inefficient, as it needs to do a correlated subq:
Yes, the doesn't work was specifically related to an attempt to use it in
a query, which of course failed miserably. :)
class HasEnv(Base):
__tablename__ = 'has_env'
id = Column(INTEGER, primary_key=True)
environment_id = Column(ForeignKey('environments.environmentID'))
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()
s = Session()
print s.query(HasEnv).filter(HasEnv.environment == 'some env')
output:
SELECT has_env.id AS has_env_id, has_env.environment_id AS
has_env_environment_id
FROM has_env
WHERE (SELECT environments.environment
FROM environments
WHERE environments.environmentID = has_env.environment_id) = :param_1
wont perform well from a SQL perspective but will do the job...
This worked perfectly, thank you! This is honestly a stop-gap measure
to allow much of the code to be rewritten (after which it can be removed),
and for what it's being used for at the moment, it won't be too bad
regarding
performance.
but a very brief conversation with someone on the #sqlalchemy channel
on
Freenode indicated there was no way to do this and all the relevant code
must be reworked. While it's only a few dozen places this occurs, I can
see
this coming up again in the future as further schema refactorings occur, so
I turn to those with more expertise to find out if there is a way to
accomplish
what I desire, or if there's really no hope. :) Any insight would be
greatly
appreciated.
I don't know how to fix this issue with IRC and stackoverflow that people
constantly are getting bad information.
Heh, Sometimes I think I should know better about asking for help
on IRC, but sometimes I get lucky. In this case, I decided I might
have better luck on the mailing list after the answer I got on IRC
was very unsatisfactory. :)
Thanks for the help!
--
- 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.
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.
