Re: [sqlalchemy] SELECT * FROM function() with declarative
ok, I think I found the solution. Thought I'd post what I did and what I
tried; partly because it might help someone else and partly because someone
may have suggestions for a better way.
This is what worked:
my_function = func.my_function(...args...)
func_subquery = select(['id'], from_obj=[my_function])
results =
session.query(Table1).join(Table2).filter(Table1.id.in_(func_subquery))
and results in a query that looks like this:
SELECT table1.id, table1.col1, table1.col2... FROM table1 JOIN table2 ON
table1.t2_id = table2.id WHERE table1.id IN( SELECT id FROM
my_function(...args...) )
This differs somewhat from what I was initially thinking:
SELECT table1.id, table1.col1, table1.col2... FROM (SELECT * FROM
my_function(...args...)) as table1 JOIN table2 ON table1.t2_id = table2.id
When I run EXPLAIN ANALYZE in PostgreSQL the IN() version seems to be
slightly more efficient according to the planner (and real run times are
more or less the same)
IN: Nested Loop (cost=12.75..889.97 rows=35432 width=222) (actual
time=42.200..42.209 rows=2 loops=1)
JOIN: Nested Loop (cost=0.25..4386.37 rows=1000 width=226) (actual
time=41.052..41.061 rows=2 loops=1)
-
Things I tried on the path to getting my answer:
-
I attempted to use a combination of a label and an alias with my function
(modelled after this:
http://docs.sqlalchemy.org/en/rel_0_7/orm/query.html?highlight=aliased#sqlalchemy.orm.aliased):
my_function = func.my_function(...args...).label(Table1.__tablename__)
my_function_alias = aliased(Table1, alias=my_function, adapt_on_names=True)
but that failed miserably. Using the labeled function in a regular core
select resulted in an AttributeError exception, so I think that might have
been part of the problem:
print select('*', from_obj=[my_function])
Traceback (most recent call last):
File stdin, line 1, in module
File /Library/Python/2.7/site-packages/sqlalchemy/sql/expression.py,
line 1790, in __str__
return unicode(self.compile()).encode('ascii', 'backslashreplace')
File /Library/Python/2.7/site-packages/sqlalchemy/sql/expression.py,
line 1778, in compile
return self._compiler(dialect, bind=bind, **kw)
File /Library/Python/2.7/site-packages/sqlalchemy/sql/expression.py,
line 1784, in _compiler
return dialect.statement_compiler(dialect, self, **kw)
File /Library/Python/2.7/site-packages/sqlalchemy/sql/compiler.py, line
277, in __init__
engine.Compiled.__init__(self, dialect, statement, **kwargs)
File /Library/Python/2.7/site-packages/sqlalchemy/engine/base.py, line
705, in __init__
self.string = self.process(self.statement)
File /Library/Python/2.7/site-packages/sqlalchemy/engine/base.py, line
724, in process
return obj._compiler_dispatch(self, **kwargs)
File /Library/Python/2.7/site-packages/sqlalchemy/sql/visitors.py, line
72, in _compiler_dispatch
return getter(visitor)(self, **kw)
File /Library/Python/2.7/site-packages/sqlalchemy/sql/compiler.py, line
873, in visit_select
froms = select._get_display_froms(existingfroms)
File /Library/Python/2.7/site-packages/sqlalchemy/sql/expression.py,
line 4807, in _get_display_froms
toremove = set(itertools.chain(*[f._hide_froms for f in froms]))
AttributeError: '_Label' object has no attribute '_hide_froms'
Using the aliased function would include the original table as well as the
aliased functioned, and without a join condition it would just do a
cartesian product:
results = session.query(my_function_alias).first()
SELECT table1.id, table1.col1, table1.col2... FROM table1, (SELECT * FROM
my_function(...args...))
So that didn't work either. After doing this I realized that if I have to
include the table and the function sub-select I might as well attempt it as
an IN(), and that is what brought me to my final answer.
Thanks again for your suggestion Mike, it definitely put me on the right
path to the solution.
On Thursday, September 20, 2012 7:10:52 PM UTC+2, Michael Bayer wrote:
you can make a function act like a table by creating a select() out of it,
check out the fifth example at
http://docs.sqlalchemy.org/en/rel_0_7/core/tutorial.html#functions
On Sep 20, 2012, at 1:09 PM, David McKeone wrote:
Hi M,
Is creating something in the database the only way to do it? How would I
coerce the view's return type into my object? How do I substitute the view
in the FROM part of my clause instead?
On Thursday, September 20, 2012 5:52:28 PM UTC+2, A.M. wrote:
On Sep 20, 2012, at 11:49 AM, David McKeone wrote:
I've googled around can't seem to find an answer to this, so hopefully
someone knows how to do it here.
I'm using PostgreSQL and I have a PL/PGSQL function that filters and
modifies a particular table based on a number of conditions and then
returns a set of rows as the result.
[sqlalchemy] SELECT * FROM function() with declarative
I've googled around can't seem to find an answer to this, so hopefully someone knows how to do it here. I'm using PostgreSQL and I have a PL/PGSQL function that filters and modifies a particular table based on a number of conditions and then returns a set of rows as the result. This pattern has allowed the system to use the functions as if they were tables so that joins can still be done on the resulting values. So instead of: SELECT * FROM table1 INNER JOIN table2 ON table1.t2_id = table2.id I do: SELECT * FROM my_function( ...args... ) as table1 INNER JOIN table2 ON table1.t2_id = table2.id That part works ok in plain SQL (and as well in the system I'm converting from) So now with SQLAlchemy I have my declarative definitions for those tables: class Table1(Base): __tablename__ = 'table1' id = Column() t2_id = Column(ForeignKey()) table2 = Relationship( ... ) # Join condition is specified explicitly class Table2(Base); __tablename__ = 'table2' id = Column() and I'm trying to figure out how I would execute a query that looks like this: result = session.query(Table1).join(Table1.table2).options(contains_eager(Table1.table2)) but using a function to 'fake' Table1 instead. So basically I'm attempting to get SQLAlchemy to treat the result of my function as if it was the normal Table1 object. I've tried using select_from() to inject my call to func.my_function() but that doesn't seem to work and since what I'm doing seems like it might be tricky (or not portable across SQL) I thought I'd ask if it's even possible. Thanks for any help! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/bYCl5PH0yNIJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] SELECT * FROM function() with declarative
On Sep 20, 2012, at 11:49 AM, David McKeone wrote: I've googled around can't seem to find an answer to this, so hopefully someone knows how to do it here. I'm using PostgreSQL and I have a PL/PGSQL function that filters and modifies a particular table based on a number of conditions and then returns a set of rows as the result. This pattern has allowed the system to use the functions as if they were tables so that joins can still be done on the resulting values. So instead of: SELECT * FROM table1 INNER JOIN table2 ON table1.t2_id = table2.id I do: SELECT * FROM my_function( ...args... ) as table1 INNER JOIN table2 ON table1.t2_id = table2.id That part works ok in plain SQL (and as well in the system I'm converting from) So now with SQLAlchemy I have my declarative definitions for those tables: class Table1(Base): __tablename__ = 'table1' id = Column() t2_id = Column(ForeignKey()) table2 = Relationship( ... ) # Join condition is specified explicitly class Table2(Base); __tablename__ = 'table2' id = Column() and I'm trying to figure out how I would execute a query that looks like this: result = session.query(Table1).join(Table1.table2).options(contains_eager(Table1.table2)) but using a function to 'fake' Table1 instead. So basically I'm attempting to get SQLAlchemy to treat the result of my function as if it was the normal Table1 object. I've tried using select_from() to inject my call to func.my_function() but that doesn't seem to work and since what I'm doing seems like it might be tricky (or not portable across SQL) I thought I'd ask if it's even possible. Thanks for any help! Perhaps the easiest way is to create a view: CREATE VIEW table1 AS SELECT * FROM my_function(...); Cheers, M -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] SELECT * FROM function() with declarative
you can make a function act like a table by creating a select() out of it, check out the fifth example at http://docs.sqlalchemy.org/en/rel_0_7/core/tutorial.html#functions On Sep 20, 2012, at 1:09 PM, David McKeone wrote: Hi M, Is creating something in the database the only way to do it? How would I coerce the view's return type into my object? How do I substitute the view in the FROM part of my clause instead? On Thursday, September 20, 2012 5:52:28 PM UTC+2, A.M. wrote: On Sep 20, 2012, at 11:49 AM, David McKeone wrote: I've googled around can't seem to find an answer to this, so hopefully someone knows how to do it here. I'm using PostgreSQL and I have a PL/PGSQL function that filters and modifies a particular table based on a number of conditions and then returns a set of rows as the result. This pattern has allowed the system to use the functions as if they were tables so that joins can still be done on the resulting values. So instead of: SELECT * FROM table1 INNER JOIN table2 ON table1.t2_id = table2.id I do: SELECT * FROM my_function( ...args... ) as table1 INNER JOIN table2 ON table1.t2_id = table2.id That part works ok in plain SQL (and as well in the system I'm converting from) So now with SQLAlchemy I have my declarative definitions for those tables: class Table1(Base): __tablename__ = 'table1' id = Column() t2_id = Column(ForeignKey()) table2 = Relationship( ... ) # Join condition is specified explicitly class Table2(Base); __tablename__ = 'table2' id = Column() and I'm trying to figure out how I would execute a query that looks like this: result = session.query(Table1).join(Table1.table2).options(contains_eager(Table1.table2)) but using a function to 'fake' Table1 instead. So basically I'm attempting to get SQLAlchemy to treat the result of my function as if it was the normal Table1 object. I've tried using select_from() to inject my call to func.my_function() but that doesn't seem to work and since what I'm doing seems like it might be tricky (or not portable across SQL) I thought I'd ask if it's even possible. Thanks for any help! Perhaps the easiest way is to create a view: CREATE VIEW table1 AS SELECT * FROM my_function(...); Cheers, M -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/4EQ3O5IWOX0J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
