Re: [sqlalchemy] possible way of changing the relationship loading strategy in the runtime, on the class level or instance level
I tested on class level and the inspect function, sth like this:
inspect(FilmNode).add_property(children_lazy, relation(
Film,
remote_side=[FilmNode.id],
lazy='dynamic',
uselist=True
)
)
and
class FilmNode(Node):
children_lazy = relation(
'Film',
remote_side=[id],
lazy='dynamic',
uselist=True
)
It worked both way, I prefer the class level definition :-). By the way,
the *Film* and *FilmNode* are both sub class of Node, and FilmNode has one
-- many relationship to Film.
Thanks Michael!!
On Monday, May 11, 2015 at 11:23:17 PM UTC+8, Michael Bayer wrote:
On 5/11/15 11:16 AM, Geo wrote:
Ok, I got this working by adding the following code into the base class:
parent_lazy = relation(
'Node',
remote_side=[id],
backref=backref(
'children_lazy',
collection_class=ordering_list('position'),
order_by=[position],
cascade='all',
lazy='dynamic')
)
The I can use node.children_lazy to get a dynamic loader. I still have
one question, am I able to add this dynamic loader by sub classing the
Node? As the base class code is from the upstream, I'm wondering if I can
have this feature without touching the base class.
If the Node is mapped using the declarative system, then subclassing it is
going to create a new mapper which links to the original using single-table
mapper inheritance. You might be able to do that, though this would have
the wrinkle that the existing self-referential relationships on Node would
still return the superclass that does not have your attribute.
More directly would be to add the new property to the original mapper, but
that adds it to that mapping globally, the way to do that which is agnostic
of whether or not declarative is used is
inspect(Node).add_property(children_lazy, relationship(Node, ...)).
On Monday, May 11, 2015 at 10:54:24 PM UTC+8, Michael Bayer wrote:
On 5/11/15 5:50 AM, Geo wrote:
I have a base class node:
class Node:
parent = relation(
'Node',
remote_side=[id],
backref=backref(
'children',
collection_class=ordering_list('position'),
order_by=[position],
cascade='all'
)
)
by default the node.children give me a eager loading collection, and
what I want is to have a subclass of the Node but only change the loading
to lazy='dynamic' so on the sqlalchemy.orm.dynamic.AppenderQuery I can
control the loading on the runtime. Or If I can work on the instance level?
I read the documentation but I can't figure out setting the behaviour for
my case.
eager or lazy loading can be controlled using query loader options,
however the dynamic loader specifically isn't compatible with loader
options because it changes the behavior of the attribute at the instance
level using an alternate descriptor.Just build a separate relationship
that has dynamic as the loader strategy and use that one when you want
that behavior.
-- 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+...@googlegroups.com. To post to this group, send email to
sqlal...@googlegroups.com. Visit this group at
http://groups.google.com/group/sqlalchemy. For more options, visit
https://groups.google.com/d/optout.
-- 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+...@googlegroups.com
javascript:. To post to this group, send email to
sqlal...@googlegroups.com javascript:. Visit this group at
http://groups.google.com/group/sqlalchemy. For more options, visit
https://groups.google.com/d/optout.
--
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] SA doesn't update table
Well, my case is a bit different. I'm writing a nightly running batch script. And this script is not running inside pyramid context, which means it is not the model that called from the framework. Instead, I arrange it to run by system cron. But, I'm trying to utilize the pyramid environment settings, like the development.ini and production.ini to get the connection string and the contextual/thread-local session management object ZopeTransactionExtension*. *I'm not sure if this is the best practice of doing in this way*,* may be I should just use the plain session object. For my understanding, it's the framework's responsibility to commit or abort session if using the thread-local session. That is way I manually put them in the code. So anyway I would like to know the reason that why the SA doesn't do anything in this case.* *And I was suspecting it is the reason of the complex joins in usage, because I have other code that doing things in the same way, they are just some simple single table queries, so* * -- 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/-/1C4382KS8WoJ. 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] SA doesn't update table
Ok I found the solution, just move the first query into the transaction body: import transaction try: transaction.begin() x_members = session.query(Distributor)... for member in x_members: . except: transaction.abort() BTW, I'm using pyramid framework, which is using the following statement to init the session: DBSession = scoped_session(sessionmaker( extension=ZopeTransactionExtension())) -- 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/-/DAKuaGyKwM8J. 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.
[sqlalchemy] SA doesn't update table
I have a query to join another two querys which are written as subqueries: paid_120_count = session.query(Capital_invest.member_id, func.count().label(count)).\ join(Product, Capital_invest.prod_id==Product.prodid).\ filter(Product.price*payback_pc- Capital_invest.capital_payback=0).\ group_by(Capital_invest.member_id).subquery() buy_product_count = session.query(Capital_invest.member_id, func.count().label(count)).\ group_by(Capital_invest.member_id).subquery() x_members = session.query(Distributor).\ join(paid_120_count, paid_120_count.c.member_id==Distributor.id).\ join(buy_product_count, buy_product_count.c.member_id==Distributor.id).\ filter(paid_120_count.c.count==buy_product_count.c.count).\ filter(Distributor.quali_bonus==True) So the distributor is the center table joins two queries. The Query returns data without problem. But i can't update the result data subsequently, for example for member in x_members: member.name =x The sqlalchemy just simply do nothing. I have to do this: for member in x_members: member.name =x dist = session.query(Distributor).get(member.id) dist.name = x Is there something I missed for getting this issue? Thanks for someone pointing out the clue. -- 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.
[sqlalchemy] Having problem with constructing update ...from... with sqlalchemy
I have a tested update ... from.. statement, like so:
update distributors set lead_bonus = lead_bonus + a.sum_amt
from (
select target_member as id, sum(amount) as sum_amt
from bonus_gen_history
where bonus_type=2 and sub_type=1 and source_member in
(select id
from distributors
where (extract(epoch from (now()-reg_time))/60363))
group by target_member
) a
where a.id=distributors.id
I'm having problem of constructing this statement from sqlalchemy:
pass_pending_period = session.query(Distributor.id).filter((func.now()-
Distributor.reg_time)/360072).subquery()
sum_amt = session.query(Bonus_gen_history.target_member,
func.sum(Bonus_gen_history.amount).label('sm')).\
filter(Bonus_gen_history.bonus_type==BonusType.sponsorBonus).\
filter(Bonus_gen_history.tran_type==TranType.addPd).\
filter(Bonus_gen_history.source_member.in_(pass_pending_period)).
group_by(Bonus_gen_history.target_member).subquery()
q = session.query(Distributor).update({Distributor.sponsor_bonus:
Distributor.sponsor_bonus+(sum_amt.c.sm)})
I keep getting the following error message:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) missing FROM-
clause entry for table anon_1
LINE 1: ...s SET sponsor_bonus=(distributors.sponsor_bonus +
anon_1.sm)
^
'UPDATE distributors SET sponsor_bonus=(distributors.sponsor_bonus +
anon_1.sm)' {}
And I can't figure out how to add the FROM-clause in the above
query.
Can somebody help me out?
--
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.
[sqlalchemy] use session inside the class
I have a self-reference table,
class member:
id
upline_id = Column(Integer, ForeignKey('member.id'))
status
downlines = relationship('member',
backref=backref('upline', remote_side=id))
def setUpline(self, upline_id)
session = DBSession()
self.upline_id = upline_id
session.add(self)
session.flush()
self.upline.status = ''
So in the code, I use
member = member()
member.setUpline(uplin_id) to set the member's upline information, is
it safe to use
session.add(self)
session.flush()
like above? Coz I need to flush to the session to have the current
user object to be set, then I can update upline information.
--
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.
[sqlalchemy] Question about Many to One relationship
I have tables which linked as a one to many relationship: user ---(one to many)--- order --(many to one)--product Product has been populated and served as a lookup table. class User: member_id purchase_total order = relationship(Order) def add_product(self, orderObj): session = DBSession() session.add(orderObj) session.flush() self.purchase_total += orderObj.product.price self.order.append(orderObj) transaction.commit() class Product: product_id price class Order: order_id product_id (FK) member_id (FK) product = relationship(Product.) # user purchasing product user = session.query(User).get(id) newOrder = Order() newOrder.product_id = 1 user.add_product(newOrder) I'm wondering if the above coding is safe in a multi-thread application such as web app. Especially for the flush and session method. -- 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.
