[sqlalchemy] Exists clause on relations of different mapped objects
I'm trying to determine whether or not there are any value in one mapped object relation that correspond to another mapped object relation i.e A.x - represents a relation of x's on A B.x - represents a relation of x's on B They both use their own separate mapping tables for these relations. So I want to find out of any of A.x is also present in B.x The closes I've had to it working was in the follwing fashion, by doing the following; exists([1],from_obj=join(A_x_mapping_table,B_x_mapping_table, onclause=A_x_mapping_table.c.id==B_x_mapping_table.c.id)) I put this exists clause into an existing filter() but it didn't correlate the tables with those in the outer query (which I really need it to do!), so I tried using correlate function to correlate the table on the join() but it didn't seem to do anything (I've used it in other situations with success but not this time). Also , A.x.any(B.x.any()) seems to almost do what I want as well, but again, doesn't correlate to outer query and because I'm using 0.4 it does not have a correlate() method in ORM! Any other hints as to how I can do this ? Thanks -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] lazy = 'dynamic' and coercing AppenderQuery to a new object class
On Jul 27, 2010, at 1:23 PM, Zippy P wrote:
> Hi all,
>
> I have the following:
>
> class Device(Base):
> ...
> source_id = Column(BigInteger, primary_key=True)
> ...
>
> class EventQuery(Query):
> def custommethod():
> ...
>
> class Event(Base):
> __tablename__ = 'events'
> query = Session.query.property(query_cls = EventQuery)
> ...
> device_source_id = Column(BigInteger, ForeignKey("Device.source_id"))
> device = relationship(Device, backref=backref('events', lazy='dynamic'))
> ...
>
> Now, Device.events returns a query of type AppenderQuery, which is fine for
> some built-in methods like count() and all(). What I really need is for this
> to be a query of type EventQuery, so I can call Device.events.custommethod().
>
> 1) Is it possible to coerce AppenderQuery into a different Query class?
> 2) If not, is it appropriate to create a custom property on Device that will
> return an EventQuery containing the equivalent query, or is there a more
> proper way to do it?
relationship() and backref() accept query_class for this purpose.
>
> Thanks,
>
> S.
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@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 sqlalch...@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] how to translate this sql
On Jul 27, 2010, at 1:07 PM, Jon Nelson wrote: > > Michael - since VALUES is part of the SQL standard (as indicated here: > http://www.postgresql.org/docs/8.4/static/sql-values.html ) perhaps > values could be added to sqlalchemy in a future release? sure, something we could likely add to the expression.py package. > > > > -- > Jon > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@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 sqlalch...@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.
[sqlalchemy] lazy = 'dynamic' and coercing AppenderQuery to a new object class
Hi all,
I have the following:
class Device(Base):
...
source_id = Column(BigInteger, primary_key=True)
...
class EventQuery(Query):
def custommethod():
...
class Event(Base):
__tablename__ = 'events'
query = Session.query.property(query_cls = EventQuery)
...
device_source_id = Column(BigInteger, ForeignKey("Device.source_id"))
device = relationship(Device, backref=backref('events', lazy='dynamic'))
...
Now, Device.events returns a query of type AppenderQuery, which is fine for
some built-in methods like count() and all(). What I really need is for this
to be a query of type EventQuery, so I can call
Device.events.custommethod().
1) Is it possible to coerce AppenderQuery into a different Query class?
2) If not, is it appropriate to create a custom property on Device that will
return an EventQuery containing the equivalent query, or is there a more
proper way to do it?
Thanks,
S.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
[sqlalchemy] Re: SQLAlchemy NotImplementedError when doing a metadata create_all
Hi Mike, On Tue, 27 Jul 2010 09:07:15 -0400, Michael Bayer wrote: > On Jul 27, 2010, at 1:34 AM, Faheem Mitha wrote: >> My usage is slightly non-standard - a foreign key pointing to a >> foreign key. > that never worked, most likely. its very complicated to get the > types to propagate up a chain like that, and all on a deferred > execution, in a reasonable way. Right. >> Ok. Well, it should be removed from the docs, I guess. > it has. Its mentioned in one place with a huge caveat. Where are > you reading this ? When I started writing the application in question I was using 0.4, and I was reading Essential Sqlalchemy at the time too. That was November 2008. So probably either the 0.4 docs or Essential SQLA mentioned the option of leaving the type blank. I just changed the app to state the ForeignKey types explicitly. This kind of inference is handy, especially when one is changing types. but I suppose the db would catch inconsistencies anyway. Regards, Faheem. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] how to translate this sql
On Tue, Jul 27, 2010 at 11:35 AM, Jon Nelson wrote: > On Tue, Jul 27, 2010 at 11:08 AM, Michael Bayer > wrote: >> >> On Jul 27, 2010, at 11:49 AM, Jon Nelson wrote: >> >>> I have two questions: >>> >>> 1. I'm using postgresql, and I sometimes need to do column type >>> conversions. In postgresql, this is normally done with the ::FOO >>> operator where FOO is a data *type*. Somtimes, but not usually, these >>> data types are also available in function-like factories, but in this >>> case that doesn't help me. SHould I use the cast(some_column, 'int') >>> expression here? >> >> ::FOO in PG is their internal syntax for what CAST provides, yes. >> >>> >>> 2. Frequently, I have a list of things upon which to operate. This >>> list of things is not in a table, and I may want to call a function on >>> this list of things. If I were writing the sql directly, I'd do >>> something like this: >>> >>> select function_foo(BAR.x) FROM (select value1, value2, value3) BAR(x); >>> >>> What's the best way to emulate this with sqlalchemy? >> >> there's a recipe for VALUES at >> http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PGValues , the syntax you >> have above looks unfamiliar to me but VALUES will create a lexical "table" >> from a set of literals (or you can just modify that recipe to provide the >> exact syntax you want). > Never mind my previous request - I was using it wrong. Sigh. That recipe works perfectly. Michael - since VALUES is part of the SQL standard (as indicated here: http://www.postgresql.org/docs/8.4/static/sql-values.html ) perhaps values could be added to sqlalchemy in a future release? -- Jon -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] how to translate this sql
On Tue, Jul 27, 2010 at 11:08 AM, Michael Bayer wrote: > > On Jul 27, 2010, at 11:49 AM, Jon Nelson wrote: > >> I have two questions: >> >> 1. I'm using postgresql, and I sometimes need to do column type >> conversions. In postgresql, this is normally done with the ::FOO >> operator where FOO is a data *type*. Somtimes, but not usually, these >> data types are also available in function-like factories, but in this >> case that doesn't help me. SHould I use the cast(some_column, 'int') >> expression here? > > ::FOO in PG is their internal syntax for what CAST provides, yes. > >> >> 2. Frequently, I have a list of things upon which to operate. This >> list of things is not in a table, and I may want to call a function on >> this list of things. If I were writing the sql directly, I'd do >> something like this: >> >> select function_foo(BAR.x) FROM (select value1, value2, value3) BAR(x); >> >> What's the best way to emulate this with sqlalchemy? > > there's a recipe for VALUES at > http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PGValues , the syntax you > have above looks unfamiliar to me but VALUES will create a lexical "table" > from a set of literals (or you can just modify that recipe to provide the > exact syntax you want). For postgres, I'd guess that the subquery (SELECT value1, value2, ...) [AS] BAR(x) is fundamentally identical to: (VALUES (value1, value2, ...)) [AS] BAR(x) However, for some reason I'm only getting the *first* value from VALUES (only value1 is being sent to the function). The actual generated sql looks like this: SELECT some_function(subquery.column1) FROM (VALUES (value1, value2, ...)) AS subquery; Any idea what I might be doing wrong? It's not the function itself, as I tried several built-in functions. Real example: SELECT abs(numbers.column1) FROM (VALUES (-5, 0, 5, 10)) AS numbers; returns just one row. Indeed: SELECT numbers.column1 FROM (VALUES (-5, 0, 5, 10)) AS numbers; also returns just one row. Obviously, this isn't a sqlalchemy-specific thing but I'd still appreciate the help. -- Jon -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] Problem with correlate in a subquery
On Jul 27, 2010, at 12:03 PM, Jesse wrote: > I'm having a problem when using correlate in a subquery. I figured I > would throw it in here before logging a bug report incase this is just > user error. > > I have two tables with a many-to-one relationship. Estimate has a > list of EstimateLines on it. I want to select in the first > EstimateLine that does not have a blank description to use as the > description on Estimate. When getting a list of Estimates I want to > sort by the description. Here is the code I'm using: > > stmt = session.query(EstimateLine.description, > EstimateLine.estimate_id) > stmt = stmt.filter(EstimateLine.estimate_id == Estimate.id) > stmt = stmt.correlate(Estimate) > stmt = stmt.filter(EstimateLine.description != '') > stmt = stmt.order_by(EstimateLine.lineno) > stmt = stmt.limit(1) > stmt = stmt.subquery() > > query = session.query(Estimate.number, stmt.c.description) > query = query.order_by(stmt.c.description) > query.all() > > Unfortunately the query it produces does not run. It produces the > following: > SELECT estimates.number AS estimates_number, anon_1.description AS > anon_1_description > FROM estimates, (SELECT estimate_lines.description AS description, > estimate_lines.estimate_id AS estimate_id > FROM estimate_lines > WHERE estimate_lines.estimate_id = estimates.id AND > estimate_lines.description != ? ORDER BY estimate_lines.lineno > LIMIT 1 OFFSET 0) AS anon_1 ORDER BY anon_1.description > > Which gives an error of "no such column: estimates.id" (in SQLite). I > have a file for reproducing this. What I would actually like is the > subquery to be part of the SELECT statement and then order by it. > Suggestions? The SQL is actually exactly what you asked for.SQLite doesn't like your attempt to join two selectables in the FROM clause using correlation, instead of a regular join of "Estimates" and "stmt". Correlation implies you'd like your subquery in the columns or where clause. Such a subquery can only return exactly one row for one column, and in SQLA is referred to as a "scalar" selectable. Call as_scalar() on your subquery, and use the resulting object as your column expression. There is no .c. attribute. That said, your subquery would be simpler and probably more efficient if you kept it in the FROM clause and simply joined them together without using correlation (query.filter(stmt.c.estimate_id==Estimate.id), remove the filter() from stmt). As it stands, you will need to request your subquery once in the columns clause, implying that it is issued for every row (even though a decent query planner will realize that it can "unwrap" the correlation into a plain join), and then a second time in the ORDER BY clause, unless you give it a name (using label(), since an as_scalar() is a column expression), and then name the label in the order_by(). > > Thanks, > > Jesse > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@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 sqlalch...@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] how to translate this sql
On Jul 27, 2010, at 11:49 AM, Jon Nelson wrote: > I have two questions: > > 1. I'm using postgresql, and I sometimes need to do column type > conversions. In postgresql, this is normally done with the ::FOO > operator where FOO is a data *type*. Somtimes, but not usually, these > data types are also available in function-like factories, but in this > case that doesn't help me. SHould I use the cast(some_column, 'int') > expression here? ::FOO in PG is their internal syntax for what CAST provides, yes. > > 2. Frequently, I have a list of things upon which to operate. This > list of things is not in a table, and I may want to call a function on > this list of things. If I were writing the sql directly, I'd do > something like this: > > select function_foo(BAR.x) FROM (select value1, value2, value3) BAR(x); > > What's the best way to emulate this with sqlalchemy? there's a recipe for VALUES at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PGValues , the syntax you have above looks unfamiliar to me but VALUES will create a lexical "table" from a set of literals (or you can just modify that recipe to provide the exact syntax you want). -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] how to translate this sql
Your right you could use the cast operator so you could do something like: Session.query(Obj).filter(sqlalchemy.sql.cast(Obj.float_col, sqlalchemy.Integer)==1) Not sure about your second question. On 07/27/2010 08:49 AM, Jon Nelson wrote: I have two questions: 1. I'm using postgresql, and I sometimes need to do column type conversions. In postgresql, this is normally done with the ::FOO operator where FOO is a data *type*. Somtimes, but not usually, these data types are also available in function-like factories, but in this case that doesn't help me. SHould I use the cast(some_column, 'int') expression here? -- David Gardner Pipeline Tools Programmer Jim Henson Creature Shop dgard...@creatureshop.com -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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.
[sqlalchemy] Problem with correlate in a subquery
I'm having a problem when using correlate in a subquery. I figured I would throw it in here before logging a bug report incase this is just user error. I have two tables with a many-to-one relationship. Estimate has a list of EstimateLines on it. I want to select in the first EstimateLine that does not have a blank description to use as the description on Estimate. When getting a list of Estimates I want to sort by the description. Here is the code I'm using: stmt = session.query(EstimateLine.description, EstimateLine.estimate_id) stmt = stmt.filter(EstimateLine.estimate_id == Estimate.id) stmt = stmt.correlate(Estimate) stmt = stmt.filter(EstimateLine.description != '') stmt = stmt.order_by(EstimateLine.lineno) stmt = stmt.limit(1) stmt = stmt.subquery() query = session.query(Estimate.number, stmt.c.description) query = query.order_by(stmt.c.description) query.all() Unfortunately the query it produces does not run. It produces the following: SELECT estimates.number AS estimates_number, anon_1.description AS anon_1_description FROM estimates, (SELECT estimate_lines.description AS description, estimate_lines.estimate_id AS estimate_id FROM estimate_lines WHERE estimate_lines.estimate_id = estimates.id AND estimate_lines.description != ? ORDER BY estimate_lines.lineno LIMIT 1 OFFSET 0) AS anon_1 ORDER BY anon_1.description Which gives an error of "no such column: estimates.id" (in SQLite). I have a file for reproducing this. What I would actually like is the subquery to be part of the SELECT statement and then order by it. Suggestions? Thanks, Jesse -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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.
[sqlalchemy] how to translate this sql
I have two questions: 1. I'm using postgresql, and I sometimes need to do column type conversions. In postgresql, this is normally done with the ::FOO operator where FOO is a data *type*. Somtimes, but not usually, these data types are also available in function-like factories, but in this case that doesn't help me. SHould I use the cast(some_column, 'int') expression here? 2. Frequently, I have a list of things upon which to operate. This list of things is not in a table, and I may want to call a function on this list of things. If I were writing the sql directly, I'd do something like this: select function_foo(BAR.x) FROM (select value1, value2, value3) BAR(x); What's the best way to emulate this with sqlalchemy? -- Jon -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] Re: SQLAlchemy NotImplementedError when doing a metadata create_all
On Jul 27, 2010, at 1:34 AM, Faheem Mitha wrote: > My > usage is slightly non-standard - a foreign key pointing to a foreign > key. that never worked, most likely. its very complicated to get the types to propagate up a chain like that, and all on a deferred execution, in a reasonable way. > > Ok. Well, it should be removed from the docs, I guess. it has. Its mentioned in one place with a huge caveat. Where are you reading this ? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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.
