Re: [sqlalchemy] update where using the ORM
On Saturday, April 26, 2014 9:12:22 PM UTC+1, Michael Bayer wrote: On Apr 26, 2014, at 3:26 PM, Tim Kersten t...@io41.com javascript: wrote: The resulting behaviour would be identical to using a version col id, but only for this transaction and the instance passed to the update_where() method, and instead of UPDATE ... WHERE pk = %s AND version = %s you'd have UPDATE ... WHERE pk = %s AND name = %s”. This is where it would have to go: https://bitbucket.org/zzzeek/sqlalchemy/src/146fbf6d26a8c4140a47aeb03131fdf81007b9a2/lib/sqlalchemy/orm/persistence.py?at=master#cl-308 Cool, thanks. where you can see that logic is wired to a single “expression”, which could be a SQL expression that gathers up lots of columns, but the expression is fixed. It isn’t derivable from all the attributes that have “changed”, and the logic here would need to be expanded into a much more elaborate, complicated, and non-performant system to support this case. For a feature to be added, it must attain a certain ratio of “impact on complexity” to “how many people will actually use it”. If the feature is very simple and non-intrusive, we can often add it even if only one person needs it. If the feature is very complex, we can add it only if this is an obvious need by a significant percentage of users. in many cases we add event hooks in areas that are to allow expansion of capabilities, but in the case of “persistence”, we already have before_update() and after_update(), adding more hooks into the construction of the actual SQL would be very complex and extremely specific to the mechanics; it would be brittle, unstable and difficult to use. IMHO the two existing approaches have no downsides: 1. repeatable read isolation (which can be set on a per-session or per-transaction basis. Why not just use it?) Are you saying there's a way to use the ORM with repeatable read isolation without potentially overwriting another user's changes? I'm not sure how (other than using version col id, in which case I don't need to use repeatable read isolation). 2. version columns, including version columns that can be *timestamps*. There is no need to go through an expensive (think TEXT/BLOB columns) and error prone (think floating points) comparison of every column if the UPDATE of a stale row is to be avoided - “stale” just means “our timestamp of the row is earlier than the timestamp that’s present”. Timestamps are only so granular though (depending on the database used) - two updates in very close succession may have the same timestamp set, so while it's unlikely to lead to data loss, it cannot guarantee it like a plain counter version column would. Advantages of using the above approach instead of version col id: - Much finer grain changes possible without raising an exception, The behavior where version misses are to be ignored is also quite unusual and I’ve never known anyone to want silent failure of an UPDATE statement like that. An entity update has a specific intent which is to target that entity; this is at the core of what an ORM is trying to do. You're right, an ORM is meant to target a specific entity. Whether the failure is silent or not doesn't play much role, in fact as you pointed out earlier it makes a lot of sense in an ORM for a failed update to raise an exception. -- 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.
[sqlalchemy] self referential hybrid expression
hi all, i have a class that have a self reference through *_parent*. this class is mapped against an selectable, not a class (this information is useful, lol) ok, so, using @hybrid_property, i can easily get the amount of children of this class: *@hybrid_property** **def child_count(self):** **kls = self.__class__** **session = object_session(self)** **sel = select(** ** [** ** func.count(kls.id_)** ** ],** ** and_(** ** self.id_ == kls._parent,** ** or_(** ** self.language_id == kls.language_id,** ** kls.language_id.is_(sql.null())** ** )** ** )** **)** **return session.execute(sel).scalar()* but, how can I transform it into an expression, so I can use it on a query, like *session.query(cls).filter(cls.child_count == 3).all()* ? i already did a lot of *@some_property.expression*, but I'm having a bit of a trouble on this one :) any help would be appreciated ... best regards, richard. -- 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.
[sqlalchemy] eagerloading on an already loaded object ?
I'm not sure if there is a trick to do this or not, but it's worth asking... I have an object that has been loaded into the Session, as per whatever eager-loading requirements : foo = dbSession.query( Foo ).filter(...).options(...).one() I'm now in a position where I need to access Foo.bar and Foo.bar.baz for bar in foo.bar : print foo.bar.baz.id Is there a way to eagerload foo.bar and foo.bar.baz into the already-loaded `foo` ? I'd prefer not to re-query for `foo` with different attributes, because it was loaded elsewhere with some specific needs. i just want to tell it to eagerload this collection/depth. -- 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] eagerloading on an already loaded object ?
it can be done with some API tinkering and I totally wrote this up for someone
recently, and cannot find it.
This is not a public thing and isn't covered by tests, however this should be a
first class feature, if someone wants to work on it or propose a ticket or
whatnot. The intricate part is sending the right kind of load option at the
end, here's a simple one.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = relationship(B)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('a.id'))
cs = relationship(C)
class C(Base):
__tablename__ = 'c'
id = Column(Integer, primary_key=True)
b_id = Column(Integer, ForeignKey('b.id'))
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
sess = Session(e)
sess.add_all([
A(bs=[
B(cs=[C(), C()]),
B(cs=[C(), C()])
])
])
sess.commit()
a1 = sess.query(A).first()
inspect(a1).load_options = inspect(a1).load_options.union([joinedload(B.cs)])
for b in a1.bs:
print b.cs
On Apr 28, 2014, at 1:08 PM, Jonathan Vanasco jonat...@findmeon.com wrote:
I'm not sure if there is a trick to do this or not, but it's worth asking...
I have an object that has been loaded into the Session, as per whatever
eager-loading requirements :
foo = dbSession.query( Foo ).filter(...).options(...).one()
I'm now in a position where I need to access Foo.bar and Foo.bar.baz
for bar in foo.bar :
print foo.bar.baz.id
Is there a way to eagerload foo.bar and foo.bar.baz into the already-loaded
`foo` ?
I'd prefer not to re-query for `foo` with different attributes, because it
was loaded elsewhere with some specific needs. i just want to tell it to
eagerload this collection/depth.
--
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.
--
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] eagerloading on an already loaded object ?
here's the ticket:
https://bitbucket.org/zzzeek/sqlalchemy/issue/3037/support-setting-load-options-on-instances
On Apr 28, 2014, at 1:38 PM, Michael Bayer mike...@zzzcomputing.com wrote:
it can be done with some API tinkering and I totally wrote this up for
someone recently, and cannot find it.
This is not a public thing and isn't covered by tests, however this should be
a first class feature, if someone wants to work on it or propose a ticket or
whatnot. The intricate part is sending the right kind of load option at the
end, here's a simple one.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = relationship(B)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('a.id'))
cs = relationship(C)
class C(Base):
__tablename__ = 'c'
id = Column(Integer, primary_key=True)
b_id = Column(Integer, ForeignKey('b.id'))
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
sess = Session(e)
sess.add_all([
A(bs=[
B(cs=[C(), C()]),
B(cs=[C(), C()])
])
])
sess.commit()
a1 = sess.query(A).first()
inspect(a1).load_options = inspect(a1).load_options.union([joinedload(B.cs)])
for b in a1.bs:
print b.cs
On Apr 28, 2014, at 1:08 PM, Jonathan Vanasco jonat...@findmeon.com wrote:
I'm not sure if there is a trick to do this or not, but it's worth asking...
I have an object that has been loaded into the Session, as per whatever
eager-loading requirements :
foo = dbSession.query( Foo ).filter(...).options(...).one()
I'm now in a position where I need to access Foo.bar and Foo.bar.baz
for bar in foo.bar :
print foo.bar.baz.id
Is there a way to eagerload foo.bar and foo.bar.baz into the already-loaded
`foo` ?
I'd prefer not to re-query for `foo` with different attributes, because it
was loaded elsewhere with some specific needs. i just want to tell it to
eagerload this collection/depth.
--
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.
--
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.
--
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] eagerloading on an already loaded object ?
thanks for the writeup. i'll tinker with some ideas in my downtime. for now, i'll stick with the secondary , non-Foo, query I have that pulls that bar/baz stuff. -- 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.
[sqlalchemy] Re: using SQLAlchemy on mobile devices?
For future browsers, apparently it can be done with Kivy, but I haven't tried it. Kivy looks very promising as a way to use Python on iOs and Android though! iain On Sun, Apr 27, 2014 at 10:30 AM, Iain Duncan iainduncanli...@gmail.comwrote: Sorry if this is a stupid question, but does anyone here know if any of the various Python-to-native-mobile platforms allow one to use sqlalchemy with sqlite for persistence? (Kivy? Pythonista? Whatever else is out there?) I'm wondering whether it's possible (yet?) to write an app that: - runs locally on iphone and android without data connection to a server - looks half decent - can use sqlalchemy for persistence thanks! Iain -- 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.
