[sqlalchemy] Functions on column properties
Hey all,
First and foremost, thank you for this wonderful library! This is my first
post to the list and I am very grateful for those who have taken the time
to make sqlalchemy.
I am using SQLalchemy as a backend for an object-oriented language I am
developing. The way it works is that it is developed in python and
SQLalchemy outputs SQL that represents the entire logic of the program.
The ORM has been a great way to do this, but there is one hurdle that I
continue to face in my development strategy: the creation of subqueries
that behave like classes.
For example, if I have a class representing a table:
class Holder(Base) :
id = Column('id', Integer, primary_key=True)
house = Column('house', Integer)
team = Column('team', Integer)
duration_num = Column('duration_num', Integer)
duration_den = Column('duration_num', Integer)
duration = column_property(1 * duration_num / duration_den)
what would be nice is to do something like:
query(Holder.house, Holder.team,
MAX(Holder.duration)).group_by(Holder.house, Holder.team)
where MAX was intelligent enough to expand the query into two queries on
Holder joined to each other: one that found the max of duration and another
that did a join to the original table to get the corresponding num and den.
I discuss this in the stack overflow
post:
http://stackoverflow.com/questions/24257066/sql-legality-of-including-ungrouped-columns-in-group-by-statement.
I've currently been doing this type of joining by hand, but that weds my
abstraction of the column property to its internals. What would be great
is to be able to define aggregate functions on column properties and what
behavior they'd result in. Perhaps this is easy and I've missed how to do
it, but so far I haven't found any good solutions. Any help would be
appreciated!
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
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
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
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.
