Re: [sqlalchemy] with_polymorphic
On Apr 1, 2010, at 8:22 PM, Kent wrote: > What is the difference between: > > session.query(Employee).join([Engineer, Manager]).\ >filter(or_(Engineer.engineer_info=='w', > Manager.manager_data=='q')) > > and > > session.query(Employee).with_polymorphic([Engineer, Manager]).\ >filter(or_(Engineer.engineer_info=='w', > Manager.manager_data=='q')) the join to Engineer is going to create "select * from employees join (select * from employees join engineer ...) on ..." not what you want. this is another thing I covered at the tutorial this year. If OTOH you join to Engineer.__table__ and Manager.__table__, you'll get what you want (with_polymorphic is a convenience feature at this piont and not very flexible). > > ? > > -- > 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] array_agg() in ORM entities (0.5.8)?
On Apr 1, 2010, at 7:45 PM, David Gardner wrote: > I was trying to use the Postrges array_agg() aggregate function against a > column on a related table. > In this case I wanted a list of timestamps from a related table, but I get an > error that the list type is unhashable. > The SQL that SA generates is as expected, and if I replace func.array_agg > with something that > returns a hashable value like func.count() then it works as expected. > > Is this a requirement that entities must be hashable? If so can I write a > TypeDecorator that implements __hash__? oh. interesting problem, yeah. query is uniqing the values returned and assumes they are all hashable - it does this when any of the items in the row are full entities (i.e. your Task here). I don't know that there's a workaround for now other than using a TypeDecorator that turns the returned list into a tuple. > > My code looks something like this: > qry=session.query(Task, func.array_agg(TaskHistory.updated)) > qry=qry.join(Task.History) > qry=qry.filter(Task.priority<5).filter(Task.state!='Approved') > qry=qry.group_by(Task).order_by(Task.asset,Task.name) > results=qry.all() > --- > TypeError Traceback (most recent call last) > > /users/dgardner/src/pcs/asset/farm/ in () > > /usr/lib/pymodules/python2.6/sqlalchemy/orm/query.pyc in all(self) > 1265 > 1266 """ > -> 1267 return list(self) > 1268 > 1269 @_generative(_no_clauseelement_condition) > > /usr/lib/pymodules/python2.6/sqlalchemy/orm/query.pyc in instances(self, > cursor, _Query__context) > 1426 > 1427 if filter: > -> 1428 rows = filter(rows) > 1429 > 1430 if context.refresh_state and self._only_load_props and > context.refresh_state in context.progress: > > /usr/lib/pymodules/python2.6/sqlalchemy/util.pyc in unique_list(seq, > compare_with) > 1087 def unique_list(seq, compare_with=set): > 1088 seen = compare_with() > -> 1089 return [x for x in seq if x not in seen and not seen.add(x)] > 1090 > 1091 class UniqueAppender(object): > > TypeError: unhashable type: 'list' > > > -- > 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. > -- 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] with_polymorphic
What is the difference between: session.query(Employee).join([Engineer, Manager]).\ filter(or_(Engineer.engineer_info=='w', Manager.manager_data=='q')) and session.query(Employee).with_polymorphic([Engineer, Manager]).\ filter(or_(Engineer.engineer_info=='w', Manager.manager_data=='q')) ? -- 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: passing arguments to complicated "columns"
you can pass a lambda as the value for a bindparam() and it will be called at compile time. On Apr 1, 2010, at 6:34 PM, Kent Bower wrote: > Thanks for the info. > Still wondering, is there a way to tell the orm (a join criterion or a binary > expression) "use this function to find the param for the bind"? > > On Apr 1, 2010, at 6:11 PM, "Michael Bayer" wrote: > >> Kent wrote: >>> I believe (correct me if you know better) that an line subquery is >>> quite a bit less efficient than a join. That was my motivation for >>> avoiding that because there are so many rows and we've already seen >>> performance problems with our queries... >> >> with a high quality planner, it should not be. in any case, you're >> looking for an aggregate value (the lowest). So without fetching many >> rows, a subquery is unavoidable, though you have a choice whether you'd >> like it correlated within the columns query (the normal way), or as an >> additional selectable in the FROM clause (avoids correlation). >> >>> >>> In the end I'd like the object to have a "saleprice" attribute (either >>> column_property or, if possible, association_proxy) that is the single >>> saleprice for the given date and store. >>> >>> When using the association proxy, can I somehow specify the first item >>> in the list? >> >> no, if you truly wanted to retrieve the full list of related items and >> display the price of the lowest, you'd use an ordinary method on your >> class to do that. >> >> -- >> 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. > -- 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] array_agg() in ORM entities (0.5.8)?
I was trying to use the Postrges array_agg() aggregate function against a column on a related table. In this case I wanted a list of timestamps from a related table, but I get an error that the list type is unhashable. The SQL that SA generates is as expected, and if I replace func.array_agg with something that returns a hashable value like func.count() then it works as expected. Is this a requirement that entities must be hashable? If so can I write a TypeDecorator that implements __hash__? My code looks something like this: qry=session.query(Task, func.array_agg(TaskHistory.updated)) qry=qry.join(Task.History) qry=qry.filter(Task.priority<5).filter(Task.state!='Approved') qry=qry.group_by(Task).order_by(Task.asset,Task.name) results=qry.all() --- TypeError Traceback (most recent call last) /users/dgardner/src/pcs/asset/farm/ in () /usr/lib/pymodules/python2.6/sqlalchemy/orm/query.pyc in all(self) 1265 1266 """ -> 1267 return list(self) 1268 1269 @_generative(_no_clauseelement_condition) /usr/lib/pymodules/python2.6/sqlalchemy/orm/query.pyc in instances(self, cursor, _Query__context) 1426 1427 if filter: -> 1428 rows = filter(rows) 1429 1430 if context.refresh_state and self._only_load_props and context.refresh_state in context.progress: /usr/lib/pymodules/python2.6/sqlalchemy/util.pyc in unique_list(seq, compare_with) 1087 def unique_list(seq, compare_with=set): 1088 seen = compare_with() -> 1089 return [x for x in seq if x not in seen and not seen.add(x)] 1090 1091 class UniqueAppender(object): TypeError: unhashable type: 'list' -- 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] Before I send
Hello, I know there is documentation on this but I am still fuzzy on certain practices when using the session. In my application I have a function that returns a new non scoped session. In one method (method a) I get object o from this session. I call another method (method b) that needs to use o and potentially make changes to it. Is it best practice to pass the session and the object as a parameter to method b or should should I pass only the object and use session = Session.object_session(o) to grab that session? Is there some other option (like re-fetching that object every time which is what I am trying to avoid). -- 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: Sharding with _BindParamClause
On Mar 30, 4:42 pm, "Michael Bayer" wrote:
> George V. Reilly wrote:
> > We're using SQLAlchemy sharding to partition accounts across a couple
> > of databases. We want to add more partitions, but first we need to
> > eliminate some unnecessary cross-partition queries.
> >
> > This works well most of the time, but we're finding that some queries
> > do not have a "value". These are all of the form
> >
> > SELECT shopping_list_items.version AS shopping_list_items_version
> > FROM shopping_list_items
> > WHERE shopping_list_items.account_id = :param_1
> > AND shopping_list_items.shopping_list_item_id = :param_2
> >
> > and :param1 is of the form _BindParamClause(u'%(63636624 param)s',
> > None, type_=UUID())
> >
> > Typically, I'm seeing this come out of the innards of SQLAlchemy,
> > as one of several queries triggered by, say, a session.merge().
>
> The only Query() I can see getting generated that would have non-valued
> bindparams would be during a _get(). The values should be present in
> query._params. If you need more info I can dig in to recall how the keys
> of that dictionary are formatted in this case.
Thanks, Michael. Here's what I came up with:
class FindShardableId(sqlalchemy.sql.ClauseVisitor):
def __init__(self, ids, key_fields, get_shard, params):
self.ids = ids
self.key_fields = key_fields
self.get_shard = get_shard
self.params = params
def visit_binary(self, binary):
if not self._check_side(binary, binary.left,
binary.right):
# Lazy load properties tend to be reversed, with the
constant on the left
self._check_side(binary, binary.right, binary.left)
def _check_side(self, binary, side, other_side):
if isinstance(side, sqlalchemy.Column) and side.name in
self.key_fields:
if binary.operator == sqlalchemy.sql.operators.eq:
value = getattr(other_side, "value", None)
if (value is None and
isinstance(other_side,
sqlalchemy.sql.expression._BindParamClause)):
value = self.params.get(other_side.key)
if value is not None:
self.ids.append(self.get_shard(value))
return True
elif binary.operator ==
sqlalchemy.sql.operators.in_op:
for bind in other_side.clauses:
self.ids.append(self.get_shard(bind.value))
return True
class QuerySharder(object):
def sessionmaker(self, **sessionmaker_args):
Session = sqlalchemy.orm.sessionmaker(
class_ = sqlalchemy.orm.shard.ShardedSession,
**sessionmaker_args)
Session.configure(
shards=self.shards,
shard_chooser=self._shard_chooser,
id_chooser=self._id_chooser,
query_chooser=self._query_chooser)
return Session
def _query_chooser(self, query):
ids = []
if query._criterion is not None:
FindShardableId(
ids,
set(["account_id", "account_guid"]),
lambda account_id:
self.shard_manager.shard_id_from_guid(account_id),
query._params
).traverse(query._criterion)
if len(ids) == 0:
logging.warn("\n\n! Executing query against all
shards; "
"this may not be optimal:\n\t{0}\n
\tParams: {1}\n".format(
str(query), str(query._params)))
return self.shards.keys()
else:
return ids
I really don't like the
isinstance(other_side,
sqlalchemy.sql.expression._BindParamClause))
in the middle of _check_side. Is there a cleaner way to do this?
I found that a combination of
* the above _check_side and two-sided visit_binary
* doing a better job of declaring ForeignKey relationships in Columns
* some explicit primaryjoins in calls to relation()
cleaned up all the cases where SA wasn't providing the ids in queries
Perhaps the sharding sample in SA 0.6 could be expanded?
--
/George V. Reilly, Seattle
--
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 reflection
After 6 hours of searching and debugging,finally figured out the permission needed for table reflection, those are: "select", and "connect replication" for ORM to work, you need one more: "Alter any schema" to do add() or delete() you obviously need more permission, respectively. Those are specific permissions under MS Sql server 2005. When doing reflection and orm mapping, did not see any exception regarding permissions. Anyway, thank you Michael. You info helps. -- 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: passing arguments to complicated "columns"
Thanks for the info. Still wondering, is there a way to tell the orm (a join criterion or a binary expression) "use this function to find the param for the bind"? On Apr 1, 2010, at 6:11 PM, "Michael Bayer" wrote: Kent wrote: I believe (correct me if you know better) that an line subquery is quite a bit less efficient than a join. That was my motivation for avoiding that because there are so many rows and we've already seen performance problems with our queries... with a high quality planner, it should not be. in any case, you're looking for an aggregate value (the lowest). So without fetching many rows, a subquery is unavoidable, though you have a choice whether you'd like it correlated within the columns query (the normal way), or as an additional selectable in the FROM clause (avoids correlation). In the end I'd like the object to have a "saleprice" attribute (either column_property or, if possible, association_proxy) that is the single saleprice for the given date and store. When using the association proxy, can I somehow specify the first item in the list? no, if you truly wanted to retrieve the full list of related items and display the price of the lowest, you'd use an ordinary method on your class to do that. -- 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] Re: passing arguments to complicated "columns"
Kent wrote: > I believe (correct me if you know better) that an line subquery is > quite a bit less efficient than a join. That was my motivation for > avoiding that because there are so many rows and we've already seen > performance problems with our queries... with a high quality planner, it should not be. in any case, you're looking for an aggregate value (the lowest). So without fetching many rows, a subquery is unavoidable, though you have a choice whether you'd like it correlated within the columns query (the normal way), or as an additional selectable in the FROM clause (avoids correlation). > > In the end I'd like the object to have a "saleprice" attribute (either > column_property or, if possible, association_proxy) that is the single > saleprice for the given date and store. > > When using the association proxy, can I somehow specify the first item > in the list? no, if you truly wanted to retrieve the full list of related items and display the price of the lowest, you'd use an ordinary method on your class to do that. -- 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: passing arguments to complicated "columns"
in my example, I'd like to be able to say
session.query(Product).get('SKUA').saleprice
and get 32.99 as a result if user_store() function returns a 'EAST'
and today is 01-Apr-2009
I probably should mention that it is even slightly more complicated...
if there is no row selected from the promotional_prices table, then
the .saleprice attribute should revert to a product table
"regular_price" column.
All this is accomplished with a column_property, I think, as long as I
can work out how to tell the mapper it should call user_store() to
fill in part of the join criteria possible?
Even better, can I work that out as an association property?
On Apr 1, 4:35 pm, Kent wrote:
> I believe (correct me if you know better) that an line subquery is
> quite a bit less efficient than a join. That was my motivation for
> avoiding that because there are so many rows and we've already seen
> performance problems with our queries...
>
> In the end I'd like the object to have a "saleprice" attribute (either
> column_property or, if possible, association_proxy) that is the single
> saleprice for the given date and store.
>
> When using the association proxy, can I somehow specify the first item
> in the list?
>
> What about a mechanism for using a function as a param, like my
> user_store() example? Is that accomplished with either the
> association_proxy or column_property?
>
> On Apr 1, 4:19 pm, "Michael Bayer" wrote:
>
> > Kent wrote:
> > > Mike,
> > > Suppose you have a Product object mapped to a product table. Say
> > > there is a table for promotional_prices as well, which looks something
> > > like this:
>
> > > PRODUCTID STORE PRICE STARTDATE ENDDATE
> > > 'SKUA' 'WEST' 30.99 01-Aug-2009 10-Aug-2010
> > > 'SKUA' 'EAST' 35.99 01-Aug-2009 10-Aug-2010
> > > 'SKUA' 'EAST' 32.99 01-Jun-2010 11-Jun-2011
>
> > > What I'd like to accomplish is when a Product is queried from the
> > > database, the lowest price from the current store is included as a
> > > "column" attribute.
>
> > > I've considered using association proxy and also column_property
> > > (select([...])).
>
> > > Since a bunch of Products may be fetched at once, I'd prefer a join
> > > over an inline select, because I believe the database query will
> > > perform better.
>
> > this is most directly accomplished using column_property against a
> > correlated subquery. You could also have each product fetch the full
> > list of related products and pick that with the lowest price to have a
> > straight inner join, but then you're fetching more rows. Are you
> > trying to avoid using a correlated subquery to get the lowest related
> > price directly ?
>
> > > I need some advice because the "relation" join includes passing a
> > > STORE and CURRENT_DATE.
>
> > > Is there a way to set this up such that I can define a function which
> > > is called when the STORE param is needed.
>
> > > def user_store():
> > > return 'WEST' # obviously in real life not hard-coded
>
> > > And then tell the mapper to use the user_store() function when it
> > > needs to supply the STORE parameter.
>
> > > A further complication is that if there are multiple rows returned,
> > > I'd like the lowest price.
>
> > > Is something like this possible to achieve with association proxies or
> > > column_properties or both?
>
> > > --
> > > 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] Re: passing arguments to complicated "columns"
I believe (correct me if you know better) that an line subquery is quite a bit less efficient than a join. That was my motivation for avoiding that because there are so many rows and we've already seen performance problems with our queries... In the end I'd like the object to have a "saleprice" attribute (either column_property or, if possible, association_proxy) that is the single saleprice for the given date and store. When using the association proxy, can I somehow specify the first item in the list? What about a mechanism for using a function as a param, like my user_store() example? Is that accomplished with either the association_proxy or column_property? On Apr 1, 4:19 pm, "Michael Bayer" wrote: > Kent wrote: > > Mike, > > Suppose you have a Product object mapped to a product table. Say > > there is a table for promotional_prices as well, which looks something > > like this: > > > PRODUCTID STORE PRICE STARTDATE ENDDATE > > 'SKUA' 'WEST' 30.99 01-Aug-2009 10-Aug-2010 > > 'SKUA' 'EAST' 35.99 01-Aug-2009 10-Aug-2010 > > 'SKUA' 'EAST' 32.99 01-Jun-2010 11-Jun-2011 > > > What I'd like to accomplish is when a Product is queried from the > > database, the lowest price from the current store is included as a > > "column" attribute. > > > I've considered using association proxy and also column_property > > (select([...])). > > > Since a bunch of Products may be fetched at once, I'd prefer a join > > over an inline select, because I believe the database query will > > perform better. > > this is most directly accomplished using column_property against a > correlated subquery. You could also have each product fetch the full > list of related products and pick that with the lowest price to have a > straight inner join, but then you're fetching more rows. Are you > trying to avoid using a correlated subquery to get the lowest related > price directly ? > > > > > I need some advice because the "relation" join includes passing a > > STORE and CURRENT_DATE. > > > Is there a way to set this up such that I can define a function which > > is called when the STORE param is needed. > > > def user_store(): > > return 'WEST' # obviously in real life not hard-coded > > > And then tell the mapper to use the user_store() function when it > > needs to supply the STORE parameter. > > > A further complication is that if there are multiple rows returned, > > I'd like the lowest price. > > > Is something like this possible to achieve with association proxies or > > column_properties or both? > > > -- > > 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] passing arguments to complicated "columns"
Kent wrote: > Mike, > Suppose you have a Product object mapped to a product table. Say > there is a table for promotional_prices as well, which looks something > like this: > > PRODUCTID STORE PRICE STARTDATE ENDDATE > 'SKUA''WEST' 30.99 01-Aug-2009 10-Aug-2010 > 'SKUA''EAST'35.99 01-Aug-2009 10-Aug-2010 > 'SKUA''EAST'32.99 01-Jun-201011-Jun-2011 > > What I'd like to accomplish is when a Product is queried from the > database, the lowest price from the current store is included as a > "column" attribute. > > I've considered using association proxy and also column_property > (select([...])). > > Since a bunch of Products may be fetched at once, I'd prefer a join > over an inline select, because I believe the database query will > perform better. this is most directly accomplished using column_property against a correlated subquery. You could also have each product fetch the full list of related products and pick that with the lowest price to have a straight inner join, but then you're fetching more rows. Are you trying to avoid using a correlated subquery to get the lowest related price directly ? > > I need some advice because the "relation" join includes passing a > STORE and CURRENT_DATE. > > Is there a way to set this up such that I can define a function which > is called when the STORE param is needed. > > def user_store(): > return 'WEST' # obviously in real life not hard-coded > > And then tell the mapper to use the user_store() function when it > needs to supply the STORE parameter. > > > A further complication is that if there are multiple rows returned, > I'd like the lowest price. > > Is something like this possible to achieve with association proxies or > column_properties or both? > > -- > 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] passing arguments to complicated "columns"
Mike, Suppose you have a Product object mapped to a product table. Say there is a table for promotional_prices as well, which looks something like this: PRODUCTID STORE PRICE STARTDATE ENDDATE 'SKUA''WEST' 30.99 01-Aug-2009 10-Aug-2010 'SKUA''EAST'35.99 01-Aug-2009 10-Aug-2010 'SKUA''EAST'32.99 01-Jun-201011-Jun-2011 What I'd like to accomplish is when a Product is queried from the database, the lowest price from the current store is included as a "column" attribute. I've considered using association proxy and also column_property (select([...])). Since a bunch of Products may be fetched at once, I'd prefer a join over an inline select, because I believe the database query will perform better. I need some advice because the "relation" join includes passing a STORE and CURRENT_DATE. Is there a way to set this up such that I can define a function which is called when the STORE param is needed. def user_store(): return 'WEST' # obviously in real life not hard-coded And then tell the mapper to use the user_store() function when it needs to supply the STORE parameter. A further complication is that if there are multiple rows returned, I'd like the lowest price. Is something like this possible to achieve with association proxies or column_properties or both? -- 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 reflection
Tan Yi wrote: > Don't want to reask, but help me please. > Can somebody tell me what kind of permission (in terms of ms sql > server) do I need in order to do table reflection? > Thank you! if your application is forbidding it, SQL Server should be dumping an error indicating what specific table requires what permissions. But in general the user needs SELECT permission on the relevant "INFORMATION_SCHEMA" schema. > > -- > 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] Query help with DECODE using Oracle DB
Mark wrote:
> Hi,
>
> I have trouble forming the below SQL statement using SQLAlchemy orm:
>
> select *
> from (
> select
> c.id class_id,
> sum(decode(e.enrollment_status_id, 2, 1,null,0,0)) total,
> c.enrollment_limit enrollment_limit
> from classes c, enrollment e
> where c.id = e.class_id(+)
> group by c.id, c.enrollment_limit
> )
> where enrollment_limit <= total
>
> I know that center portion should be a subquery of sorts, but I don't
> know how to convert the decode into valid SQLalchemy ORM syntax.
a function like "decode" would be rendered using the "func" construct,
i.e. func.DECODE(*args...).
>
> I tried using SQL Expressions and this was what I got:
>
> connection = tkengine.connect()
> s = text("""\
> select c.id class_id, sum(decode(e.enrollment_status_id,
> 2, 1,null,0,0)) total, c.enrollment_limit enrollment_limit
> from classes c, enrollment e
> where c.id = e.class_id(+)
> group by c.id, c.enrollment_limit
> """)
>stmt = connection.execute(s)
>
> I would like to then use this subquery to obtain a list of classes
> that have enrollment_limit greater than (>) total.
>
> 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.
>
>
--
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 reflection
Don't want to reask, but help me please. Can somebody tell me what kind of permission (in terms of ms sql server) do I need in order to do table reflection? Thank you! -- 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: Upgraded to 0.6beta3, getting a lot of "SAWarning: Unicode type received non-unicode bind param"
Peteris Krumins wrote:
> I am not using Unicode type anywhere, all the columns the warnings
> originate from are String.
>
> Here is one example when the warnings occur. The table is 'downloads':
>
> downloads_table = Table('downloads', metadata,
> Column('download_id', Integer,primary_key=True),
> Column('title', String(128)),
> Column('filename', String(128)),
> Column('mimetype', String(64)),
> Column('timestamp', DateTime),
> Column('downloads', Integer),
> mysql_charset='utf8'
> )
>
> And it has the corresponding Download class mapped to it.
>
> Here is a code fragment that produces the warnings:
>
from xyzzy.models import Download, session
d = Download('file title', 'file_title.txt', 'text/plain')
session.add(d)
session.commit()
> /xyzzy/lib/python2.5/site-packages/SQLAlchemy-0.6beta3-py2.5.egg/
> sqlalchemy/engine/default.py:472: SAWarning: Unicode type received non-
> unicode bind param value 'file title'
> param.append(processors[key](compiled_params[key]))
> /xyzzy/lib/python2.5/site-packages/SQLAlchemy-0.6beta3-py2.5.egg/
> sqlalchemy/engine/default.py:472: SAWarning: Unicode type received non-
> unicode bind param value 'file_title.txt'
> param.append(processors[key](compiled_params[key]))
> /xyzzy/lib/python2.5/site-packages/SQLAlchemy-0.6beta3-py2.5.egg/
> sqlalchemy/engine/default.py:472: SAWarning: Unicode type received non-
> unicode bind param value 'text/plain'
> param.append(processors[key](compiled_params[key]))
>
>
> I looked into it a bit more and noticed that I have "convert_unicode"
> parameter for create_engine() set.
>
> engine = create_engine(
> config['database_uri'],
> convert_unicode=True,
> echo=config['database_echo'],
> pool_recycle=3600
> )
>
> I removed "convert_unicode=True" and I don't get any warnings anymore.
>
>
> Any comments?
just that the behavior is documented here:
http://www.sqlalchemy.org/docs/reference/sqlalchemy/connections.html?highlight=create_engine#sqlalchemy.create_engine
its generally not a recommended practice to use convert_unicode with
create_engine, and better to use the Unicode type specifically in those
places where you'd want to accommodate non-ascii strings.
>
>
> Sincerely,
> P.Krumins
>
>
>
>
> On Apr 1, 5:35 am, Michael Bayer wrote:
>> would have to see how you've set things up in order for that to occur.
>> If you use the Unicode type with default settings, the behavior is the
>> same on 0.5 and 0.6 - Python unicodes are expected and a warning is
>> emitted otherwise. With String,VARCHAR, CHAR, etc., this is not the
>> case and plain strings can be passed without warning.
>>
>> On Mar 31, 2010, at 5:59 PM, Peteris Krumins wrote:
>>
>> > Hi all,
>>
>> > I upgraded to SA 0.6beta3 and suddenly I am getting a lot of
>> > "SAWarning: Unicode type received non-unicode bind param value"
>> > warnings for the code that worked OK on 0.5.x. The warnings occur even
>> > for values that are plain ascii (no code points above 127).
>>
>> > Does it mean I'll have to make sure my code uses Python unicode
>> > strings everywhere, even if it they are ascii?
>>
>> > Here are some of the warnings:
>>
>> > /xyzzy/lib/python2.5/site-packages/SQLAlchemy-0.6beta3-py2.5.egg/
>> > sqlalchemy/dialects/mysql/base.py:960: SAWarning: Unicode type
>> > received non-unicode bind param value '1'
>>
>> > /xyzzy/lib/python2.5/site-packages/SQLAlchemy-0.6beta3-py2.5.egg/
>> > sqlalchemy/engine/default.py:472: SAWarning: Unicode type received
>> non-
>> > unicode bind param value 'application/pdf'
>>
>> > /xyzzy/lib/python2.5/site-packages/SQLAlchemy-0.6beta3-py2.5.egg/
>> > sqlalchemy/engine/default.py:472: SAWarning: Unicode type received
>> non-
>> > unicode bind param value '1fd67ac4162561def609a4862677cdbc'
>>
>> > Sincerely,
>> > P.Krumins
>>
>> > --
>> > 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
>> athttp://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.
>
>
--
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] Query help with DECODE using Oracle DB
Hi,
I have trouble forming the below SQL statement using SQLAlchemy orm:
select *
from (
select
c.id class_id,
sum(decode(e.enrollment_status_id, 2, 1,null,0,0)) total,
c.enrollment_limit enrollment_limit
from classes c, enrollment e
where c.id = e.class_id(+)
group by c.id, c.enrollment_limit
)
where enrollment_limit <= total
I know that center portion should be a subquery of sorts, but I don't
know how to convert the decode into valid SQLalchemy ORM syntax.
I tried using SQL Expressions and this was what I got:
connection = tkengine.connect()
s = text("""\
select c.id class_id, sum(decode(e.enrollment_status_id,
2, 1,null,0,0)) total, c.enrollment_limit enrollment_limit
from classes c, enrollment e
where c.id = e.class_id(+)
group by c.id, c.enrollment_limit
""")
stmt = connection.execute(s)
I would like to then use this subquery to obtain a list of classes
that have enrollment_limit greater than (>) total.
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.
[sqlalchemy] SQLAlchemy Tables Definitoons as soaplib Complex Types, help!
Hi, I'm trying to implement a soap interface that will allow me to pass pure sqlalchemy objects as a reponse. I'm using the declarative based table definition and a soaplib library to do that. The project is a pylons project. The problem is that when i do: from project.model.meta import Base # declarative base from soaplib.serializers.clazz import ClassSerializer from soaplib.serializers.primitive import Integer class Resource(Base, ClassSerializer): # ...field definitions follow... # then i define soap types of the fields class types: id = Integer subid = Integer I had a problem with multiple __metaclass__ definitions, which i overcame with this snippet: http://code.activestate.com/recipes/204197-solving-the-metaclass-conflict/ so I added: __metaclass__ = classmaker() after: class Resource(Base, ClassSerializer): and the problem was solved. But now in my controller, when I try to return this object: @soap_method(soap_str, _returns = Resource) def get(self, name): agent = db_session.query(Resource).filter(Resource.name==name).first() return agent I get an error: type object 'Resource' has no attribute 'soap_members' which is set by the soaplib's ClassSerializer's __metaclass__ in theory (I looked through the code). So it seems that the multiple __metaclass__ hack (mentioned above) is not firing the __call__ method of ClassSerializer's __metaclass__ Does any one have any idea how to overcome that? Is it even possible? -- 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 reflection
Wondering if I need the creating table, or creating views priviledge to reflect tables. -- 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: Upgraded to 0.6beta3, getting a lot of "SAWarning: Unicode type received non-unicode bind param"
I am not using Unicode type anywhere, all the columns the warnings
originate from are String.
Here is one example when the warnings occur. The table is 'downloads':
downloads_table = Table('downloads', metadata,
Column('download_id', Integer,primary_key=True),
Column('title', String(128)),
Column('filename', String(128)),
Column('mimetype', String(64)),
Column('timestamp', DateTime),
Column('downloads', Integer),
mysql_charset='utf8'
)
And it has the corresponding Download class mapped to it.
Here is a code fragment that produces the warnings:
>>> from xyzzy.models import Download, session
>>> d = Download('file title', 'file_title.txt', 'text/plain')
>>> session.add(d)
>>> session.commit()
/xyzzy/lib/python2.5/site-packages/SQLAlchemy-0.6beta3-py2.5.egg/
sqlalchemy/engine/default.py:472: SAWarning: Unicode type received non-
unicode bind param value 'file title'
param.append(processors[key](compiled_params[key]))
/xyzzy/lib/python2.5/site-packages/SQLAlchemy-0.6beta3-py2.5.egg/
sqlalchemy/engine/default.py:472: SAWarning: Unicode type received non-
unicode bind param value 'file_title.txt'
param.append(processors[key](compiled_params[key]))
/xyzzy/lib/python2.5/site-packages/SQLAlchemy-0.6beta3-py2.5.egg/
sqlalchemy/engine/default.py:472: SAWarning: Unicode type received non-
unicode bind param value 'text/plain'
param.append(processors[key](compiled_params[key]))
I looked into it a bit more and noticed that I have "convert_unicode"
parameter for create_engine() set.
engine = create_engine(
config['database_uri'],
convert_unicode=True,
echo=config['database_echo'],
pool_recycle=3600
)
I removed "convert_unicode=True" and I don't get any warnings anymore.
Any comments?
Sincerely,
P.Krumins
On Apr 1, 5:35 am, Michael Bayer wrote:
> would have to see how you've set things up in order for that to occur. If
> you use the Unicode type with default settings, the behavior is the same on
> 0.5 and 0.6 - Python unicodes are expected and a warning is emitted
> otherwise. With String,VARCHAR, CHAR, etc., this is not the case and plain
> strings can be passed without warning.
>
> On Mar 31, 2010, at 5:59 PM, Peteris Krumins wrote:
>
> > Hi all,
>
> > I upgraded to SA 0.6beta3 and suddenly I am getting a lot of
> > "SAWarning: Unicode type received non-unicode bind param value"
> > warnings for the code that worked OK on 0.5.x. The warnings occur even
> > for values that are plain ascii (no code points above 127).
>
> > Does it mean I'll have to make sure my code uses Python unicode
> > strings everywhere, even if it they are ascii?
>
> > Here are some of the warnings:
>
> > /xyzzy/lib/python2.5/site-packages/SQLAlchemy-0.6beta3-py2.5.egg/
> > sqlalchemy/dialects/mysql/base.py:960: SAWarning: Unicode type
> > received non-unicode bind param value '1'
>
> > /xyzzy/lib/python2.5/site-packages/SQLAlchemy-0.6beta3-py2.5.egg/
> > sqlalchemy/engine/default.py:472: SAWarning: Unicode type received non-
> > unicode bind param value 'application/pdf'
>
> > /xyzzy/lib/python2.5/site-packages/SQLAlchemy-0.6beta3-py2.5.egg/
> > sqlalchemy/engine/default.py:472: SAWarning: Unicode type received non-
> > unicode bind param value '1fd67ac4162561def609a4862677cdbc'
>
> > Sincerely,
> > P.Krumins
>
> > --
> > 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
> > athttp://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.
