On Mon, Jul 31, 2017 at 9:29 PM, Federico Delgado <[email protected]> wrote: > Please advice if there is a more performant or better looking solution for > my problem. Thank you.
that's the general way to turn a function into a thing you can select from, e.g. select().from(func.xyz()).alias(), the docs for this approach near the end of the section http://docs.sqlalchemy.org/en/latest/core/tutorial.html#functions. For Postgresql, there is a lot of request to handle functions that return scalars and result sets more smoothly and there are plans to eventually make that better, you may be able to get a more succinct SQL query if you play with some of the recipes at https://bitbucket.org/zzzeek/sqlalchemy/issues/3566/figure-out-how-to-support-all-of-pgs#comment-22842678 . > > On Monday, July 31, 2017 at 5:15:41 PM UTC-7, Federico Delgado wrote: >> >> Hi, >> >> I am having problems trying to figure out this. >> >> Using: SqlAlchemy 1.1.12 >> PostgreSQL: 9.6 >> Python: 2.7.13 >> >> I have a simple entity: >> >> class Package(Base): >> __tablename__ = 'package' >> id = Column(Integer, primary_key=True) >> name = Column(String(255)) >> >> >> and a function in my DB that performs some operations and returns just a >> list of IDs in a particular order that I have to maintain. This function is >> called 'calculate_rankings' and returns a table. The first lines are: >> >> CREATE OR REPLACE FUNCTION public.calculate_rankings(pattern text) >> RETURNS TABLE(id integer, weight bigint) >> LANGUAGE 'sql' >> >> >> So ideally I'd like to do something like (this is just a sample): >> >> my_function = func.calculate_rankings('somestring') >> session.query(Packages).filter(Packages.id == >> my_function.id).order_by(my_function.weight).all() >> >> I have tried multiple ways to accomplish this. Like using: >> >> my_function = select([column('id'), column('weight')]).select_from(func. >> calculate_rankings('somestring')) >> >> But that results in a 'select' that I cannot use inside the query. >> >> but the most I could get is using in_ to ensure I am getting the correct >> packages. For that I also had to drop the all important column weight. >> >> Maybe somebody can point me to the right resource? >> >> Thank you, >> >> Federico >> >> >> > -- > 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 [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
