Re: [sqlalchemy] How to select efficiently big amount of time series?

[mike bayer]

On 06/15/2017 11:14 PM, Julien MOLINA wrote:

Hi everybody,

I am working on a project where I save every minutes (24h/24, 365 days a 
year) physical data (temperature, pressure, flow) for an industrial 
application in a MySQL Database thanks to sqlalchemy.


I would like to extract the data to plot (pygal) the variables for a 
specific period ( typically 1 day or 1 week ). However, I don't want to 
select all the data but for example one every 10 minutes for daily plot, 
one every hour for weekly plot.


I try to use union or _or function inside filter but the computing times 
are quite long.


Do you have any idea of methods to do it as efficient as possible?


I'm not sure how union/or relate to this problem - as described, you 
would use a modulus function against the epoch of your timestamps. 
This can be made more efficient if you had access to a Postgresql-style 
functional index, however in MySQL, if you needed to do just these 
specific kinds of queries quite frequently, you could store the epochs 
and/or the modulus of them explicitly in the table so that a simple 
integer index could be made against them.


The query assuming you have regular DATETIME style columns:

SELECT * FROM table WHERE timestamp BETWEEN  AND  
AND (UNIX_TIMESTAMP(timestamp) / 60 % 10) == 0


that is, for each timestamp:

 / 60 == number of minutes since 1970
number of minutes since 1970 % 10 == value from 0 to 9

If you then added and populated a column in your table called 
"ten_minute_epoch" or something like that, you could avoid having to do 
the math operation on every row during your SELECT (e.g. it would be 
written at INSERT time), though this might be overkill.


you definitely want to do EXPLAIN on your query and make sure indexes 
are in place so that you are getting the most efficient use of them.






Thank you very much for your help,

Kind regards,

Julien

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and 
Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
description.

---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] How to select efficiently big amount of time series?

[Julien MOLINA]

Hi everybody,

I am working on a project where I save every minutes (24h/24, 365 days a 
year) physical data (temperature, pressure, flow) for an industrial 
application in a MySQL Database thanks to sqlalchemy.

I would like to extract the data to plot (pygal) the variables for a 
specific period ( typically 1 day or 1 week ). However, I don't want to 
select all the data but for example one every 10 minutes for daily plot, 
one every hour for weekly plot.

I try to use union or _or function inside filter but the computing times 
are quite long. 

Do you have any idea of methods to do it as efficient as possible?

Thank you very much for your help,

Kind regards,

Julien

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] query to get a single field from the last updated row

[sum abiut]

Hi,
i am having trouble trying to figure this out. I have a table something as
below. i want to write an sqlalchemy that query the table and then display
the email address from the row that was last updated. Please point me to
the right direction to help me accomplished that.





fname lname email phone
tom jon t...@mail.com 555
James peter t...@mail.com 555
john kas t...@mail.com 555
tom jon t...@mail.com 555

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] How to do scalar sub selects with the ORM correctly?

['mike waites' via sqlalchemy]

Ah right OK that make sense.  I was going to say that using undefer felt 
kind of odd given that there's no existing property so this makes much more 
sense.

Unfortunately I need to find a solution that works in the mean time until 
your patch is merged so whilst i have you maybe you can explain why my 
approach with bind params doesn't work (leveraging the approach in the post 
Simon) 
@declared_attr
def is_user_default(cls):
tbl = UserDefaultDashboard.__table__

user_default_stmt = select([tbl.c.id]).where(
tbl.c.id == cls.id
).where(
tbl.c.user_id == bindparam('request_user_id', lambda user_id: 
user_id or cls.user_id)
)
return orm.column_property(exists(user_default_stmt), deferred=True)


I've got a declared_attr setup.  Querying for this works exactly as i 
expect using `.params(request_user_id=user.id)` however, Creating a new 
instance of Dashboard and then accessing it's properties will cause this 
attr to be invoked with the bindparam being passed the lambda function 
directly.  At first i thought i could solve this by deferring the 
column_property but it seems whenever *any* property on the model is called 
is_user_default will be called as well??

Here's the query 

@classmethod
def get_user_dashboards(cls, user):

stmt = db.session.query(cls).join(
Person, Person.id == cls.user_id,
).join(
Company, Company.id == Person.company_id,
).filter(
or_(
cls.user_id == user.id,
and_(
cls.shared_with_company == true(),
Company.id == user.company.id
),
cls.shared_with.any(Person.id.in_([user.id]))
),
cls.is_deleted == false()
).params(
request_user_id=user.id
).options(
orm.undefer('is_user_default')
)
return stmt


Any point doing the following will raise a ProgammingError 

obj = Dashboard(**params)
session.add(obj)
session.commit()

# now accessing an attribute on obj
obj.user_id

ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'function' 
[SQL: 'SELECT EXISTS (SELECT user_default_dashboard.id \nFROM 
user_default_dashboard \nWHERE user_default_dashboard.id = dashboard.id AND 
user_default_dashb
oard.user_id = %(request_user_id)s) AS anon_1, dashboard.is_deleted AS 
dashboard_is_deleted, dashboard.name AS dashboard_name, 
dashboard.description AS dashboard_description, dashboard.user_id AS 
dashboard_user_id, dashboard.shared_with_c
ompany AS dashboard_shared_with_company, dashboard.id AS dashboard_id, 
dashboard.created_at AS dashboard_created_at, dashboard.updated_at AS 
dashboard_updated_at \nFROM dashboard \nWHERE dashboard.id = %(param_1)s'] 
[parameters: {'param_1
': 'fYW49zSa52J7cZcszR6A9V', 'request_user_id':  at 
0x7f72d7a451b8>}] 





On Thursday, 15 June 2017 18:22:16 UTC+1, Mike Bayer wrote:
>
>
>
> On 06/15/2017 11:09 AM, 'mike waites' via sqlalchemy wrote: 
> > Thanks Mike. 
> > 
> > I will definitely give this patch a try.  I think this is exactly what 
> > we need.  Simon, thanks so much for sharing that post as well, bindparam 
> > had completely escaped my attention and that will actually solve my 
> > immediate use case. 
> > 
> > @mike was just wondering if you could clarify something in your proposed 
> > update on 1.2 that you linked. 
> > 
> > | 
> > a1 =s.query(A).options(undefer(A.x,expression=literal(17))).first() 
> > | 
> > 
> > To me what this is doing is setting the property x on the Model A with 
> > the result of expression.  So given a case where we have a scalar sub 
> > query it might take the form of.. 
> > 
> > | 
> > a1 
> > 
> =s.query(Dashboard).options(undefer(Dashboard.is_user_default,expression=user_default_subquery).first()
>  
>
> > | 
> > 
> > Where is_user_default is not defined on the Dashboard Model currently 
> > but will be set once the query is evaluated with the result of whatever 
> > is set to expression? 
>
> The plan is that Dashboard will have an attribute is_user_default at all 
> times, that normally would just return None unless the query sets it to 
> have some value.The undefer() use above will be refined with a new 
> function with_expression() that is more specific to this setup. 
>
> There's not an option for the attribute to be added to the Dashboard 
> object *dynamically*, that is, "Dashboard.is_user_default" needs to be a 
> known name up front. 
>
>
>
>
> > 
> > On Thursday, 15 June 2017 14:53:26 UTC+1, Mike Bayer wrote: 
> > 
> > 
> > 
> > On 06/15/2017 08:25 AM, 'mike waites' via sqlalchemy wrote: 
> >  > Hi 
> >  > 
> >  > This is something i've been wrestling in different ways for some 
> > time so 
> >  > i thought it was time that i sought a definitive answer on what 
> the 
> >  > "proper" way to do this is with the ORM. 
> >  > 
> >  > Often you'll 

[sqlalchemy] Concise, Pythonic query syntax

[Bryan Jones]

All,

While working on my SQLAlchemy-based application, I noticed an opportunity 
to provide a more concise, Pythonic query syntax. For example, 
User['jack'].addresses produces a Query for the Address of a User named 
jack. I had two questions

   1. Has someone already done this? If so, would you provide a link?
   2. If not, would this be reasonable for inclusion in SQLAlchemy, either 
   as an ORM example, or as a part of the core code base? If so, I can submit 
   a pull request.

A quick comparison of this statement to the traditional approach:

User['jack']   .addresses
Query([]).select_from(User).filter(User.name == 'jack').join(Address).
add_entity(Address)

A few more (complete) examples of this approach:
# Ask for the full User object for jack.
User['jack'].to_query(session)
# Ask only for Jack's full name.
User['jack'].fullname.to_query(session)
# Get all of Jack's addresses.
User['jack'].addresses.to_query(session)
# Get just the email-address of all of Jack's addresses.
User['jack'].addresses.email_address.to_query(session)
# Get just the email-address j...@yahoo.com of Jack's addresses.
User['jack'].addresses['j...@yahoo.com'].to_query(session)
# Ask for the full Address object for j...@yahoo.com.
Address['j...@yahoo.com'].to_query(session)
# Ask for the User associated with this address.
Address['j...@yahoo.com'].user.to_query(session)
# Use a filter criterion to select a User with a full name of Jack Bean.
User[User.fullname == 'Jack Bean'].to_query(session)
# Use two filter criteria to find the user named jack with a full name of 
Jack Bean.
User['jack'][User.fullname == 'Jack Bean'].to_query(session)
# Look for the user with id 1.
User[1].to_query(session)

Tested on Python 3.6.1, Windows 10, SQLAlchemy 1.1.10. I've attached the 
code, and a HTML document of the code with helpful hyperlinks.

Bryan
-- 
Bryan A. Jones, Ph.D.
Associate Professor
Department of Electrical and Computer Engineering
231 Simrall / PO Box 9571
Mississippi State University
Mississippi State, MS 39762
http://www.ece.msstate.edu/~bjones
bjones AT ece DOT msstate DOT edu
voice 662-325-3149
fax 662-325-2298

Our Master, Jesus Christ, is on his way. He'll show up right on
time, his arrival guaranteed by the Blessed and Undisputed Ruler,
High King, High God.
- 1 Tim. 6:14b-15 (The Message)

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
# **
# QueryMaker - Proivde concise, Pythonic query syntax for SQLAlchemy
# **
from sqlalchemy import create_engine, ForeignKey, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta
from sqlalchemy.orm import sessionmaker, Query, relationship
from sqlalchemy.orm.attributes import InstrumentedAttribute
from sqlalchemy.orm.properties import ColumnProperty, RelationshipProperty
from sqlalchemy.orm.base import _generative
from sqlalchemy.sql.elements import ClauseElement
#
# QueryMaker
# ==
# This class provides a concise, Pythonic syntax for simple queries; for example, ``User['jack'].addresses`` produces a Query_ for the Address of a User named jack. Comparing this to a traditional query:
#
# .. code::
#
#   User['jack']   .addresses
#   Query([]).select_from(User).filter(User.name == 'jack').join(Address).add_entity(Address)
#
# See the demonstration_ for more examples.
class QueryMaker:
def __init__(self,
  # A `Declarative class `_ to query.
  declarative_class,
  # Optionally, begin with an existing query_.
  query=None):

# Keep track of which `Declarative class`_ we're querying.
self.declarative_class = declarative_class
# Keep track of the last selectable construct, to generate the select in ``go``.
self.select = declarative_class
# If it's not provied, create an empty `query `_; ``go`` will fill in the missing information. TODO: If a query was provided, could I infer the declarative_class based on what the left side of a join would be? There's a reset_joinpoint, but I want to find the current joinpoint. There's a _joinpoint 

Re: [sqlalchemy] How to do scalar sub selects with the ORM correctly?

[mike bayer]

On 06/15/2017 11:09 AM, 'mike waites' via sqlalchemy wrote:

Thanks Mike.

I will definitely give this patch a try.  I think this is exactly what 
we need.  Simon, thanks so much for sharing that post as well, bindparam 
had completely escaped my attention and that will actually solve my 
immediate use case.


@mike was just wondering if you could clarify something in your proposed 
update on 1.2 that you linked.


|
a1 =s.query(A).options(undefer(A.x,expression=literal(17))).first()
|

To me what this is doing is setting the property x on the Model A with 
the result of expression.  So given a case where we have a scalar sub 
query it might take the form of..


|
a1 
=s.query(Dashboard).options(undefer(Dashboard.is_user_default,expression=user_default_subquery).first()

|

Where is_user_default is not defined on the Dashboard Model currently 
but will be set once the query is evaluated with the result of whatever 
is set to expression?


The plan is that Dashboard will have an attribute is_user_default at all 
times, that normally would just return None unless the query sets it to 
have some value.The undefer() use above will be refined with a new 
function with_expression() that is more specific to this setup.


There's not an option for the attribute to be added to the Dashboard 
object *dynamically*, that is, "Dashboard.is_user_default" needs to be a 
known name up front.







On Thursday, 15 June 2017 14:53:26 UTC+1, Mike Bayer wrote:



On 06/15/2017 08:25 AM, 'mike waites' via sqlalchemy wrote:
 > Hi
 >
 > This is something i've been wrestling in different ways for some
time so
 > i thought it was time that i sought a definitive answer on what the
 > "proper" way to do this is with the ORM.
 >
 > Often you'll want to have some kind of scalar sub select that
computes a
 > column in your query.  This can be achieved easily by doing the
following
 >
 > |
 > session.query(Model,scalar_sub_query).filter(...)

this is likely another candidate for:


https://bitbucket.org/zzzeek/sqlalchemy/issues/3058/allow-to-inject-expression-based-columns




which is theoretically the very last bug I am to fix for 1.2 which I am
feverishly trying to get done.

if you can try out the patch there and see if it applies to your use
case too, then that's another vote for me to do this one.



 > |
 >
 > When passing additional column properties to the Query
constructor like
 > this results in SQLAlchemy giving you a named tuple back.  In
some cases
 > this is fine but notice the first argument passed was Model, this is
 > because we also want the ORM to do its normal thing of populating an
 > instance of Model for us from the result of the query.
 >
 > In some cases this can be worked around by using declared_attr.
  This
 > will allow us to define a field that is configured at import time
from a
 > function or something like that.  This, in my experience, is
suitable in
 > rare occasions.  The moment you need something that is not setup at
 > import time makes this unsuitable. IE suppose your column requires a
 > user id to figure out the result.
 >
 > The next thing that comes to mind is hybrid_property.  This is a lot
 > closer to what we want in most cases, the function is deferred until
 > query time however, to my knowledge there's no way to have
SQLAlchemy
 > automatically populate a Model instance property from a
hybrid_property
 > which result in use coming back to the original problem. Specifying
 > columns explicitly results in a named tuple when we simply want our
 > model instance populated.
 >
 > There's no doubt a way to do this but i've given up trying to
find it.
 >
 > Hope someone can help!
 >
 > --
 > SQLAlchemy -
 > The Python SQL Toolkit and Object Relational Mapper
 >
 > http://www.sqlalchemy.org/
 >
 > To post example code, please provide an MCVE: Minimal, Complete, and
 > Verifiable Example. See http://stackoverflow.com/help/mcve
 for a full
 > description.
 > ---
 > 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+...@googlegroups.com 
 > .
 > To post to this group, send email to sqlal...@googlegroups.com

 > .
 > Visit this group at https://groups.google.com/group/sqlalchemy
.
 > For more options, visit https://groups.google.com/d/optout

Partial rollouts

[Atli Thorbjornsson]

Hi guys, our SaaS application relies heavily on Alembic for both schema and 
data migration. (Thanks!)

We have a new requirement from our product team to support "partial rollouts 
and 
rollback". 
Up till this point upgrades to our service have been an "all or nothing" 
affair. Either all clients upgrade or none (and no rollbacks :))

We think the largest problem with this new approach will be training a new 
developer mindset but I'm wondering whether there any specific gotchas or 
tricks with Alembic that would be helpful.

Conceptually segregating new code/data to specific clients of our platform 
is straightforward. 
Our system is multi-tenant in that all clients are already segregated into 
"orgs" that have no connection between themselves. 
(Just to be clear, we're running on a single db, the multi-tenancy is 
enforced by logic in code)
So a partial rollout would mean deciding on what orgs receive the new 
code/data.

The conclusion we reached is that, moving forward, all deployment must now 
be phased. An example:

Say we have a table: conversation with fields (id, name, last_message_id)
Say we want to change it to (id, name, last_message_timestamp)

Phase1: Via alembic add new column conversation.last_message_timestamp.
Corresponding deployed code would now have to write to both last_message_id 
and last_message_timestamp while new behaviour would only display for 
"rolled out orgs".

Phase2: Once partial rollout is deemed successful complete the rollout via 
alembic by removing conversation.last_message. Corresponding code now only 
writes to last_message_timestamp.

Does that make sense, does anyone have any war stories they care to share?

Thanks,
Atli Thorbjornsson.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] How to do scalar sub selects with the ORM correctly?

['mike waites' via sqlalchemy]

Thanks Mike.

I will definitely give this patch a try.  I think this is exactly what we 
need.  Simon, thanks so much for sharing that post as well, bindparam had 
completely escaped my attention and that will actually solve my immediate 
use case.

@mike was just wondering if you could clarify something in your proposed 
update on 1.2 that you linked.

a1 = s.query(A).options(undefer(A.x, expression=literal(17))).first()

To me what this is doing is setting the property x on the Model A with the 
result of expression.  So given a case where we have a scalar sub query it 
might take the form of..

a1 = s.query(Dashboard).options(undefer(Dashboard.is_user_default, 
expression=user_default_subquery).first()

Where is_user_default is not defined on the Dashboard Model currently but 
will be set once the query is evaluated with the result of whatever is set 
to expression?

On Thursday, 15 June 2017 14:53:26 UTC+1, Mike Bayer wrote:
>
>
>
> On 06/15/2017 08:25 AM, 'mike waites' via sqlalchemy wrote: 
> > Hi 
> > 
> > This is something i've been wrestling in different ways for some time so 
> > i thought it was time that i sought a definitive answer on what the 
> > "proper" way to do this is with the ORM. 
> > 
> > Often you'll want to have some kind of scalar sub select that computes a 
> > column in your query.  This can be achieved easily by doing the 
> following 
> > 
> > | 
> > session.query(Model,scalar_sub_query).filter(...) 
>
> this is likely another candidate for: 
>
>
> https://bitbucket.org/zzzeek/sqlalchemy/issues/3058/allow-to-inject-expression-based-columns
>  
>
> which is theoretically the very last bug I am to fix for 1.2 which I am 
> feverishly trying to get done. 
>
> if you can try out the patch there and see if it applies to your use 
> case too, then that's another vote for me to do this one. 
>
>
>
> > | 
> > 
> > When passing additional column properties to the Query constructor like 
> > this results in SQLAlchemy giving you a named tuple back.  In some cases 
> > this is fine but notice the first argument passed was Model, this is 
> > because we also want the ORM to do its normal thing of populating an 
> > instance of Model for us from the result of the query. 
> > 
> > In some cases this can be worked around by using declared_attr.  This 
> > will allow us to define a field that is configured at import time from a 
> > function or something like that.  This, in my experience, is suitable in 
> > rare occasions.  The moment you need something that is not setup at 
> > import time makes this unsuitable. IE suppose your column requires a 
> > user id to figure out the result. 
> > 
> > The next thing that comes to mind is hybrid_property.  This is a lot 
> > closer to what we want in most cases, the function is deferred until 
> > query time however, to my knowledge there's no way to have SQLAlchemy 
> > automatically populate a Model instance property from a hybrid_property 
> > which result in use coming back to the original problem. Specifying 
> > columns explicitly results in a named tuple when we simply want our 
> > model instance populated. 
> > 
> > There's no doubt a way to do this but i've given up trying to find it. 
> > 
> > Hope someone can help! 
> > 
> > -- 
> > SQLAlchemy - 
> > The Python SQL Toolkit and Object Relational Mapper 
> > 
> > http://www.sqlalchemy.org/ 
> > 
> > To post example code, please provide an MCVE: Minimal, Complete, and 
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> > description. 
> > --- 
> > 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+...@googlegroups.com  
> > . 
> > To post to this group, send email to sqlal...@googlegroups.com 
>  
> > . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] SQLAlchemy JTI subqueryload bug

[mike bayer]

it's two bugs

https://bitbucket.org/zzzeek/sqlalchemy/issues/4011/joined-subclass-to-2-level-subquery-load

and reopened in master

https://bitbucket.org/zzzeek/sqlalchemy/issues/3963/aliased-vs-non-aliased-specificity-in

thanks!


On 06/15/2017 01:23 AM, Sherwin Yu wrote:



We found a bug involving filtering a JTI relation and subqueryloading. 
The emitted SQL for the subqueryload does not correctly join the child 
and parent JTI tables, resulting in a cross product.


Info:

  * psql (PostgreSQL) 9.5.7
  * SQLAlchemy 0.9.10

Suppose we have the following relations (see full repro insructions below):

  * Dept
  * Owner
  o dept_id
  * Milestone
  o owner_id
  * Sprint (subclass of Milestone)
  o id references milestone.id


When doing the following query:

Sprint.query.filter(Sprint.id.in_([1])).options(db.subqueryload(Sprint.owner).subqueryload(Owner.dept)).all()

We see the three following queries, the third of which has an incorrect 
inner SELECT:


SELECT sprint.id AS sprint_id, milestone.id AS milestone_id, 
milestone.milestone_type AS milestone_milestone_type, milestone.owner_id AS 
milestone_owner_id
FROM milestone JOIN sprint ON milestone.id = sprint.id
WHERE sprint.id IN (%(id_1)s)
INFO:sqlalchemy.engine.base.Engine:{'id_1': 1}

SELECT owner.id AS owner_id, owner.dept_id AS owner_dept_id, 
anon_1.milestone_owner_id AS anon_1_milestone_owner_id
FROM (SELECT DISTINCT milestone.owner_id AS milestone_owner_id
FROM milestone JOIN sprint ON milestone.id = sprint.id
WHERE sprint.id IN (%(id_1)s)) AS anon_1 JOIN owner ON owner.id = 
anon_1.milestone_owner_id ORDER BY anon_1.milestone_owner_id
INFO:sqlalchemy.engine.base.Engine:{'id_1': 1}

SELECT dept.id AS dept_id, owner_1.dept_id AS owner_1_dept_id
FROM (SELECT DISTINCT milestone.owner_id AS milestone_owner_id
FROM milestone, sprint
WHERE sprint.id IN (%(id_1)s)) AS anon_1 JOIN owner AS owner_1 ON owner_1.id = 
anon_1.milestone_owner_id JOIN dept ON dept.id = owner_1.dept_id ORDER BY 
owner_1.dept_id
INFO:sqlalchemy.engine.base.Engine:{'id_1': 1}

In particular, the inner SELECT on the third query, |SELECT DISTINCT 
milestone.owner_id AS milestone_owner_id FROM milestone, sprint WHERE 
sprint.id IN (%(id_1)s)| (corresponding to |subqueryload(Owner.dept)|) 
is selecting from tables milestone and sprint without specifying a join 
condition, which results in the a cross product of the two tables (the 
entire milestone table is returned along with sprint.id = 1).


The temporary work around we found was to replace 
|.filter(Sprint.id.in_([1]))| to |.filter(Milestone.id.in_([1]))| , 
which then emits this query instead:


SELECT dept.id AS dept_id, owner_1.dept_id AS owner_1_dept_id
FROM (SELECT DISTINCT milestone.owner_id AS milestone_owner_id
FROM milestone
WHERE milestone.id IN (%(id_1)s)) AS anon_1 JOIN owner AS owner_1 ON owner_1.id 
= anon_1.milestone_owner_id JOIN dept ON dept.id = owner_1.dept_id ORDER BY 
owner_1.dept_id

Which avoids taking the cross product of milestone and sprint. (Note, 
this only works since sprint.id is the foreign key to milestone.id, so 
filtering by milestone.id is sufficient)



Repro

# Create some tables:
CREATE TABLE dept (id integer PRIMARY KEY);
CREATE TABLE owner (id integer PRIMARY KEY, dept_id integer, CONSTRAINT 
owner_dept_id_dept_id_fkey FOREIGN KEY (dept_id) REFERENCES dept (id));
CREATE TABLE milestone (id integer PRIMARY KEY, milestone_type VARCHAR(64) NOT 
NULL, owner_id integer, CONSTRAINT milestone_owner_id_owner_id_fkey FOREIGN KEY 
(owner_id) REFERENCES dept (id));
CREATE TABLE sprint (id integer PRIMARY KEY, CONSTRAINT 
sprint_id_milestone_id_fkey FOREIGN KEY (id) REFERENCES milestone (id));

class Milestone(db.Model):
 __tablename__ = 'milestone'
 id = db.Column(db.Integer, primary_key=True)

 milestone_type = db.Column(db.String(64), nullable=False)
 owner_id = db.Column(
 db.Integer,
 db.ForeignKey('owner.id', name='milestone_owner_id_owner_id_fkey'))

 owner = db.relationship('Owner', back_populates='milestone')

 __mapper_args__ = {
 'polymorphic_on': milestone_type,
 }


class Sprint(Milestone):
 __tablename__ = 'sprint'
 id = db.Column(db.Integer,
db.ForeignKey('milestone.id', 
name='sprint_id_milestone_id_fkey'),
primary_key=True)

 __mapper_args__ = {
 'polymorphic_identity': 'sprint'
 }


class Owner(db.Model):
 __tablename__ = 'owner'
 id = db.Column(db.Integer, primary_key=True)

 dept_id = db.Column(db.Integer,
 db.ForeignKey('dept.id', 
name='owner_dept_id_dept_id_fkey'))
 dept = db.relationship('Dept', back_populates='owner')
 milestone = db.relationship('Milestone', back_populates='owner')


class Dept(db.Model):
 __tablename__ = 'dept'
 id = db.Column(db.Integer, primary_key=True)
 owner = db.relationship('Owner', back_populates='dept')


Then insert some data:

d = Dept(); o = Owner(); o.dept = 

Re: [sqlalchemy] How to do scalar sub selects with the ORM correctly?

[mike bayer]

On 06/15/2017 08:25 AM, 'mike waites' via sqlalchemy wrote:

Hi

This is something i've been wrestling in different ways for some time so 
i thought it was time that i sought a definitive answer on what the 
"proper" way to do this is with the ORM.


Often you'll want to have some kind of scalar sub select that computes a 
column in your query.  This can be achieved easily by doing the following


|
session.query(Model,scalar_sub_query).filter(...)


this is likely another candidate for:

https://bitbucket.org/zzzeek/sqlalchemy/issues/3058/allow-to-inject-expression-based-columns

which is theoretically the very last bug I am to fix for 1.2 which I am 
feverishly trying to get done.


if you can try out the patch there and see if it applies to your use 
case too, then that's another vote for me to do this one.





|

When passing additional column properties to the Query constructor like 
this results in SQLAlchemy giving you a named tuple back.  In some cases 
this is fine but notice the first argument passed was Model, this is 
because we also want the ORM to do its normal thing of populating an 
instance of Model for us from the result of the query.


In some cases this can be worked around by using declared_attr.  This 
will allow us to define a field that is configured at import time from a 
function or something like that.  This, in my experience, is suitable in 
rare occasions.  The moment you need something that is not setup at 
import time makes this unsuitable. IE suppose your column requires a 
user id to figure out the result.


The next thing that comes to mind is hybrid_property.  This is a lot 
closer to what we want in most cases, the function is deferred until 
query time however, to my knowledge there's no way to have SQLAlchemy 
automatically populate a Model instance property from a hybrid_property 
which result in use coming back to the original problem. Specifying 
columns explicitly results in a named tuple when we simply want our 
model instance populated.


There's no doubt a way to do this but i've given up trying to find it.

Hope someone can help!

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and 
Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
description.

---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: How to do scalar sub selects with the ORM correctly?

[Simon King]

You might be interested in this thread:

https://groups.google.com/forum/#!msg/sqlalchemy/8z0XGRMDgCk/tazeIUsdDgAJ

Simon

On Thu, Jun 15, 2017 at 1:53 PM, 'mike waites' via sqlalchemy
 wrote:
> Interestingly I've just discovered the concept of Bundles.
> http://docs.sqlalchemy.org/en/latest/orm/loading_columns.html#column-bundles
>
> Im guessing this could be used to have it populate the model instance with
> the extra columns ?
>
>
> On Thursday, 15 June 2017 13:25:00 UTC+1, mike waites wrote:
>>
>> Hi
>>
>> This is something i've been wrestling in different ways for some time so i
>> thought it was time that i sought a definitive answer on what the "proper"
>> way to do this is with the ORM.
>>
>> Often you'll want to have some kind of scalar sub select that computes a
>> column in your query.  This can be achieved easily by doing the following
>>
>> session.query(Model,scalar_sub_query).filter(...)
>>
>> When passing additional column properties to the Query constructor like
>> this results in SQLAlchemy giving you a named tuple back.  In some cases
>> this is fine but notice the first argument passed was Model, this is because
>> we also want the ORM to do its normal thing of populating an instance of
>> Model for us from the result of the query.
>>
>> In some cases this can be worked around by using declared_attr.  This will
>> allow us to define a field that is configured at import time from a function
>> or something like that.  This, in my experience, is suitable in rare
>> occasions.  The moment you need something that is not setup at import time
>> makes this unsuitable. IE suppose your column requires a user id to figure
>> out the result.
>>
>> The next thing that comes to mind is hybrid_property.  This is a lot
>> closer to what we want in most cases, the function is deferred until query
>> time however, to my knowledge there's no way to have SQLAlchemy
>> automatically populate a Model instance property from a hybrid_property
>> which result in use coming back to the original problem. Specifying columns
>> explicitly results in a named tuple when we simply want our model instance
>> populated.
>>
>> There's no doubt a way to do this but i've given up trying to find it.
>>
>> Hope someone can help!
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Adding filters to association (secondary) relationships

[Simon King]

Actually I think in this case you could probably leave the "secondary"
argument as the association table itself, but change the join
condition via the "primaryjoin" parameter. Something like this
(completely untested):

class User(Base):
id = Column(Integer, primary_key=True)
managed_teams = relationship(
'Team',
secondary=user_team_association_table,
primaryjoin=sa.and_(user_team_association_table.c.user_id == id,

user_team_association_table.c.is_manager==sa.true()),
)

Simon

On Thu, Jun 15, 2017 at 12:26 PM,   wrote:
> Thanks Simon. While this seems to have worked, I only run into the next
> error. Mind you, I’m somewhat new to Alchemy and my SQL is rather rusty at
> the moment. My current approach
>
> managed_teams = relationship("Team",
>  secondary="join(user_team_association,
> user_team_association.c.is_manager==true)",
>  backref="managers")
>
> seems to be an incomplete join. I’ll look into this tomorrow…
> Jens
>
>
> On Thursday, June 15, 2017 at 6:25:00 PM UTC+10, Simon King wrote:
>>
>> Table objects put columns under the ".c" attribute, so you probably
>> need "user_team_association.c.is_manager".
>>
>> Simon
>>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: How to do scalar sub selects with the ORM correctly?

['mike waites' via sqlalchemy]

Interestingly I've just discovered the concept of Bundles. 
 http://docs.sqlalchemy.org/en/latest/orm/loading_columns.html#column-bundles

Im guessing this could be used to have it populate the model instance with 
the extra columns ?

On Thursday, 15 June 2017 13:25:00 UTC+1, mike waites wrote:
>
> Hi
>
> This is something i've been wrestling in different ways for some time so i 
> thought it was time that i sought a definitive answer on what the "proper" 
> way to do this is with the ORM.
>
> Often you'll want to have some kind of scalar sub select that computes a 
> column in your query.  This can be achieved easily by doing the following
>
> session.query(Model,scalar_sub_query).filter(...)
>
> When passing additional column properties to the Query constructor like 
> this results in SQLAlchemy giving you a named tuple back.  In some cases 
> this is fine but notice the first argument passed was Model, this is 
> because we also want the ORM to do its normal thing of populating an 
> instance of Model for us from the result of the query.
>
> In some cases this can be worked around by using declared_attr.  This will 
> allow us to define a field that is configured at import time from a 
> function or something like that.  This, in my experience, is suitable in 
> rare occasions.  The moment you need something that is not setup at import 
> time makes this unsuitable. IE suppose your column requires a user id to 
> figure out the result.
>
> The next thing that comes to mind is hybrid_property.  This is a lot 
> closer to what we want in most cases, the function is deferred until query 
> time however, to my knowledge there's no way to have SQLAlchemy 
> automatically populate a Model instance property from a hybrid_property 
> which result in use coming back to the original problem. Specifying columns 
> explicitly results in a named tuple when we simply want our model instance 
> populated.
>
> There's no doubt a way to do this but i've given up trying to find it.  
>
> Hope someone can help!
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] How to do scalar sub selects with the ORM correctly?

['mike waites' via sqlalchemy]

Hi

This is something i've been wrestling in different ways for some time so i 
thought it was time that i sought a definitive answer on what the "proper" 
way to do this is with the ORM.

Often you'll want to have some kind of scalar sub select that computes a 
column in your query.  This can be achieved easily by doing the following

session.query(Model,scalar_sub_query).filter(...)

When passing additional column properties to the Query constructor like 
this results in SQLAlchemy giving you a named tuple back.  In some cases 
this is fine but notice the first argument passed was Model, this is 
because we also want the ORM to do its normal thing of populating an 
instance of Model for us from the result of the query.

In some cases this can be worked around by using declared_attr.  This will 
allow us to define a field that is configured at import time from a 
function or something like that.  This, in my experience, is suitable in 
rare occasions.  The moment you need something that is not setup at import 
time makes this unsuitable. IE suppose your column requires a user id to 
figure out the result.

The next thing that comes to mind is hybrid_property.  This is a lot closer 
to what we want in most cases, the function is deferred until query time 
however, to my knowledge there's no way to have SQLAlchemy automatically 
populate a Model instance property from a hybrid_property which result in 
use coming back to the original problem. Specifying columns explicitly 
results in a named tuple when we simply want our model instance populated.

There's no doubt a way to do this but i've given up trying to find it.  

Hope someone can help!

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Adding filters to association (secondary) relationships

[jens . troeger]

Thanks Simon. While this seems to have worked, I only run into the next 
error. Mind you, I’m somewhat new to Alchemy and my SQL is rather rusty at 
the moment. My current approach

managed_teams = relationship("Team",   
 
 secondary="join(user_team_association, 
user_team_association.c.is_manager==true)",
 backref="managers")   
 

seems to be an incomplete join. I’ll look into this tomorrow…
Jens


On Thursday, June 15, 2017 at 6:25:00 PM UTC+10, Simon King wrote:
>
> Table objects put columns under the ".c" attribute, so you probably 
> need "user_team_association.c.is_manager". 
>
> Simon 
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Adding filters to association (secondary) relationships

[Simon King]

Table objects put columns under the ".c" attribute, so you probably
need "user_team_association.c.is_manager".

Simon

On Thu, Jun 15, 2017 at 12:11 AM,   wrote:
> Thank you Mike, the composite joins look like something close to what I
> need. However, it seems that they too require a mapped class for the
> association table?
>
> I tried
>
> managed_teams = relationship("Team",
>
> secondary="join(user_team_association.is_manager==true)",
>  backref="managers")
>
> using the table name directly, but this gives me an error:
>
> AttributeError: 'Table' object has no attribute 'is_manager'
>
> which I suspect indicates that the join expected a mapped class rather than
> a table name?
>
> Thanks!
>
>
>
> On Thursday, June 15, 2017 at 7:05:45 AM UTC+10, Mike Bayer wrote:
>>
>>
>> On 06/14/2017 04:54 PM, jens.t...@gmail.com wrote:
>> > […]
>> >
>> > # Filtered association that lists only managed teams. The tuple maps
>> > # the column to the value and would translate to a WHERE.
>> >  managed_teams =relationship("Team",
>> >
>> > secondary=user_team_association_table,
>> >   backref="managers",
>> >   filter=("is_manager",True))
>>
>>
>> you use a custom primaryjoin / secondaryjoin for this
>>
>> see  http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html
>>
>>
>> http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#composite-secondary-joins
>>
>> etc
>>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.