Re: [sqlalchemy] Functions on column properties
not looking deeply but the hybrid you have in prop_3 doesn't seem to have
any relationship to the base set of rows you're getting from fractions.
it returns multiple rows because statement2 isn't using any aggregates.
How about a straight SQL string? what SQL do you expect? these are very
easy to link to a hybrid.
It's difficult to issue a straight SQL string for the hybrid property
itself because what I'm working with are group_by constructs which depend
on aggregate functions. So, the hybrid property would need to be aware of
its context. For example, in the code below, prop_3 now returns the
maximum of all fractions and is not responsive to the group_by that comes
later down the line:
ECHO = False
from sqlalchemy.orm import sessionmaker, aliased
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, select, func
from sqlalchemy.orm import relationship
from sqlalchemy.ext.hybrid import hybrid_property
engine = create_engine('sqlite:///:memory:', echo=ECHO)
Base = declarative_base()
class Fraction(Base):
__tablename__ = 'fractions'
id = Column(Integer, primary_key = True)
prop_1 = Column(Integer)
prop_2 = Column(Integer)
prop_3_num = Column(Integer)
prop_3_den = Column(Integer)
@hybrid_property
def prop_3(self) :
return 0
@prop_3.expression
def prop_3(self) :
alias_1 = aliased(Fraction)
alias_2 = aliased(Fraction)
statement1 = select([func.max(1.0 * alias_1.prop_3_num /
alias_1.prop_3_den).label('fmax')])
statement2 = select([alias_2.prop_3_num.label('prop_3_num_max'),
alias_2.prop_3_den.label('prop_3_den_max')]).\
where((1.0 * alias_2.prop_3_num /
alias_2.prop_3_den) == statement1)
return statement2
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
for x in range(10) :
session.add(Fraction(prop_1=x%2, prop_2=x%4, prop_3_num = x+1,
prop_3_den=x+2))
session.commit()
for x in session.query(Fraction.prop_1, Fraction.prop_2, Fraction.prop_3) :
print x
print
for x in session.query(Fraction.prop_1, Fraction.prop_2,
Fraction.prop_3).group_by(Fraction.prop_1, Fraction.prop_2) :
print x
***
the result is:
(0, 0, 10, 11)
(1, 1, 10, 11)
(0, 2, 10, 11)
(1, 3, 10, 11)
(0, 0, 10, 11)
(1, 1, 10, 11)
(0, 2, 10, 11)
(1, 3, 10, 11)
(0, 0, 10, 11)
(1, 1, 10, 11)
(0, 0, 10, 11)
(0, 2, 10, 11)
(1, 1, 10, 11)
(1, 3, 10, 11)
whereas I'd like for the local maxima to be chosen, meaning:
(0, 0, 9, 10)
(0, 2, 7, 8)
(1, 1, 10, 11)
(1, 3, 8, 9)
--
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] Functions on column properties
On 6/19/14, 4:09 AM, Mike Solomon wrote: It's difficult to issue a straight SQL string for the hybrid property itself because sorry, I meant, please write the query *that you really want* as a SQL string. Don't use SQLAlchemy. It's better to work in that direction. If you don't know what the SQL you want is, that's a different issue, I'd start on that part first. -- 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] Functions on column properties
Le jeudi 19 juin 2014 16:10:19 UTC+3, Michael Bayer a écrit : On 6/19/14, 4:09 AM, Mike Solomon wrote: It's difficult to issue a straight SQL string for the hybrid property itself because sorry, I meant, please write the query *that you really want* as a SQL string. Don't use SQLAlchemy. It's better to work in that direction. If you don't know what the SQL you want is, that's a different issue, I'd start on that part first. Ah, OK. The SQL below will group fractions based on tag and give the num(erator) and den(ominator) of the maximum for each group. In Python, I'd like to have a fraction class that has members tag and val where val is a hybrid_property combining num and den. I'd like to be able to do a query like session.query(Fraction.tag, func.max(Fraction.val)).group_by(Fraction.tag) and get the SQL below: CREATE TABLE fraction ( id Int, tag Int, num Int, den Int, PRIMARY KEY (id) ); SELECT DISTINCT fraction_a.tag, fraction_a.high, fraction_b.num, fraction_b.den FROM (SELECT fraction.tag, max(1.0 * fraction.num / fraction.den) AS high FROM fraction GROUP BY fraction.tag) AS fraction_a JOIN (SELECT fraction.tag, fraction.num, fraction.den FROM fraction) AS fraction_b ON fraction_a.tag = fraction_b.tag AND fraction_a.high = 1.0 * fraction_b.num / fraction_b.den; -- 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] Functions on column properties
On 6/19/14, 2:41 PM, Mike Solomon wrote:
Le jeudi 19 juin 2014 16:10:19 UTC+3, Michael Bayer a écrit :
On 6/19/14, 4:09 AM, Mike Solomon wrote:
It's difficult to issue a straight SQL string for the hybrid
property
itself because
sorry, I meant, please write the query *that you really want* as a
SQL
string. Don't use SQLAlchemy. It's better to work in that
direction.
If you don't know what the SQL you want is, that's a different issue,
I'd start on that part first.
Ah, OK. The SQL below will group fractions based on tag and give the
num(erator) and den(ominator) of the maximum for each group. In
Python, I'd like to have a fraction class that has members tag and val
where val is a hybrid_property combining num and den. I'd like to be
able to do a query like session.query(Fraction.tag,
func.max(Fraction.val)).group_by(Fraction.tag) and get the SQL below:
SELECT DISTINCT fraction_a.tag, fraction_a.high,
fraction_b.num, fraction_b.den
FROM
(SELECT fraction.tag, max(1.0 * fraction.num / fraction.den) AS high
FROM fraction
GROUP BY fraction.tag)
AS fraction_a JOIN
(SELECT fraction.tag, fraction.num, fraction.den
FROM fraction)
AS fraction_b
ON fraction_a.tag = fraction_b.tag
AND fraction_a.high = 1.0 * fraction_b.num / fraction_b.den;
So to the extent that 1.0 * num / den is a column-based expression you
like to use in your query, it's a good candidate for a hybrid or
column_property (deferred one in case you don't want to load it
unconditionally). But as far as the FROM clauses, when we work with
Query(), the FROM clauses are always distinct entities that we have to
combine together as we want, there's never any kind of implicit behavior
with that.
Here's the appropriate place to use column_property() (specifically
deferred() so that it doesn't get loaded by default) in terms of how the
query should come out:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Fraction(Base):
__tablename__ = 'fraction'
id = Column('id', Integer, primary_key=True)
tag = Column(Integer)
num = Column(Integer)
den = Column(Integer)
high = deferred(1.0 * num / den)
fraction_a = select([
Fraction.tag,
func.max(Fraction.high).label(high),
]).group_by(Fraction.tag).alias('fraction_a')
fraction_b = aliased(Fraction, name=fraction_b)
sess = Session()
q = sess.query(fraction_a, fraction_b.num, fraction_b.den).\
distinct().\
join(fraction_b, and_(
fraction_a.c.high == fraction_b.high,
fraction_a.c.tag == fraction_b.tag
)
)
print q
output:
SELECT DISTINCT fraction_a.tag AS fraction_a_tag, fraction_a.high AS
fraction_a_high, fraction_b.num AS fraction_b_num, fraction_b.den AS
fraction_b_den
FROM (SELECT fraction.tag AS tag, max((:param_1 * fraction.num) /
fraction.den) AS high
FROM fraction GROUP BY fraction.tag) AS fraction_a JOIN fraction AS
fraction_b ON fraction_a.high = (:param_2 * fraction_b.num) /
fraction_b.den AND fraction_a.tag = fraction_b.tag
--
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] Functions on column properties
Le jeudi 19 juin 2014 22:07:14 UTC+3, Michael Bayer a écrit : So to the extent that 1.0 * num / den is a column-based expression you like to use in your query, it's a good candidate for a hybrid or column_property (deferred one in case you don't want to load it unconditionally). But as far as the FROM clauses, when we work with Query(), the FROM clauses are always distinct entities that we have to combine together as we want, there's never any kind of implicit behavior with that. Thank you _very_ much for taking the time to write this informative answer. The pattern I'm talking about is one I'm using often, so I'll be looking for a way to automate it. If I find something worth contributing, I'll definitely send it to the list! Cheers, ~Mike -- 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] Functions on column properties
Le mercredi 18 juin 2014 22:03:33 UTC+3, Michael Bayer a écrit :
if you can show the SQL you expect that would help. it seems in your SO
question you want a subquery, you'd have to define that:
class Holder(..):
some_prop = column_property(select([func.max(1 * col1 / col2)]))
I tried this strategy, and the problem is that when it is used in
combination with other things it creates a cross product. In the below
example, I'd like for all the queries to return 10 results but the last one
returns 100 because it does not join the table generated in the max
statement with the Fraction table. Ideally, I'd like the last query to
only generate 10 rows where the last two columns in each row are all the
maximum numerator and denominator.
The sql I'd expect would be like that in my SO question: rows selected from
a table joined to itself.
ECHO = False
from sqlalchemy.orm import sessionmaker, aliased
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, select, func
from sqlalchemy.orm import relationship
from sqlalchemy.ext.hybrid import hybrid_property
engine = create_engine('sqlite:///:memory:', echo=ECHO)
Base = declarative_base()
class Fraction(Base):
__tablename__ = 'fractions'
id = Column(Integer, primary_key = True)
prop_1 = Column(Integer)
prop_2 = Column(Integer)
prop_3_num = Column(Integer)
prop_3_den = Column(Integer)
@hybrid_property
def prop_3(self) :
return 0
@prop_3.expression
def prop_3(self) :
alias_1 = aliased(Fraction)
alias_2 = aliased(Fraction)
statement1 = select([func.max(1.0 * alias_2.prop_3_num /
alias_2.prop_3_den).label('fmax')])
statement2 = select([(1.0 * alias_2.prop_3_num /
alias_2.prop_3_den).label('fdec'), alias_2.prop_3_num.label('max_num'),
alias_2.prop_3_den.label('max_den')])
return select([statement2.c.max_num.label('prop_3_max_num'),
statement2.c.max_den.label('prop_3_max_den')]).\
select_from(statement2).join(statement1,
onclause=statement1.c.fmax == statement2.c.fdec)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
for x in range(10) :
session.add(Fraction(prop_1=x%2, prop_2=x%4, prop_3_num = x+1,
prop_3_den=x+2))
session.commit()
# should only print two rows...don't know why it prints 3
for x in session.query(Fraction.prop_3) :
print x
for x in session.query(Fraction.prop_1) :
print x
for x in session.query(Fraction.prop_1, Fraction.prop_2) :
print x
for x in session.query(Fraction.prop_1, Fraction.prop_2, Fraction.prop_3) :
print x
--
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] Functions on column properties
On 6/18/14, 4:50 PM, Mike Solomon wrote:
Le mercredi 18 juin 2014 22:03:33 UTC+3, Michael Bayer a écrit :
if you can show the SQL you expect that would help. it seems in
your SO question you want a subquery, you'd have to define that:
class Holder(..):
some_prop = column_property(select([func.max(1 * col1 / col2)]))
I tried this strategy, and the problem is that when it is used in
combination with other things it creates a cross product. In the
below example, I'd like for all the queries to return 10 results but
the last one returns 100 because it does not join the table generated
in the max statement with the Fraction table. Ideally, I'd like the
last query to only generate 10 rows where the last two columns in each
row are all the maximum numerator and denominator.
The sql I'd expect would be like that in my SO question: rows selected
from a table joined to itself.
ECHO = False
from sqlalchemy.orm import sessionmaker, aliased
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, select, func
from sqlalchemy.orm import relationship
from sqlalchemy.ext.hybrid import hybrid_property
engine = create_engine('sqlite:///:memory:', echo=ECHO)
Base = declarative_base()
class Fraction(Base):
__tablename__ = 'fractions'
id = Column(Integer, primary_key = True)
prop_1 = Column(Integer)
prop_2 = Column(Integer)
prop_3_num = Column(Integer)
prop_3_den = Column(Integer)
@hybrid_property
def prop_3(self) :
return 0
@prop_3.expression
def prop_3(self) :
alias_1 = aliased(Fraction)
alias_2 = aliased(Fraction)
statement1 = select([func.max(1.0 * alias_2.prop_3_num /
alias_2.prop_3_den).label('fmax')])
statement2 = select([(1.0 * alias_2.prop_3_num /
alias_2.prop_3_den).label('fdec'),
alias_2.prop_3_num.label('max_num'), alias_2.prop_3_den.label('max_den')])
return select([statement2.c.max_num.label('prop_3_max_num'),
statement2.c.max_den.label('prop_3_max_den')]).\
select_from(statement2).join(statement1,
onclause=statement1.c.fmax == statement2.c.fdec)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
for x in range(10) :
session.add(Fraction(prop_1=x%2, prop_2=x%4, prop_3_num = x+1,
prop_3_den=x+2))
session.commit()
# should only print two rows...don't know why it prints 3
for x in session.query(Fraction.prop_3) :
print x
for x in session.query(Fraction.prop_1) :
print x
for x in session.query(Fraction.prop_1, Fraction.prop_2) :
print x
for x in session.query(Fraction.prop_1, Fraction.prop_2,
Fraction.prop_3) :
print x
not looking deeply but the hybrid you have in prop_3 doesn't seem to
have any relationship to the base set of rows you're getting from
fractions. it returns multiple rows because statement2 isn't using
any aggregates.
How about a straight SQL string? what SQL do you expect? these are
very easy to link to a hybrid.
--
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.
