[sqlalchemy] Re: Modification tracking
I had to do this last week. I posted a recipe in this thread: https://groups.google.com/forum/#!topic/sqlalchemy/Xr1llnf5tzQ tracked objects inherit from RevisionObject, which adds 2 columns to the database: revision_id (INT) revision_history (HSTORE) it also adds 2 methods: generate_snapshot generate_diff i only track changes on certain elements, so I added a revision_columns attribute to the object. ( which should be a list of the attributes ). if you wanted all columns, you could just iterate over: sqlalchemy_orm.class_mapper(self.__class__).mapped_table.c If you want to go over the relationships, there's another attribute of the `mapped_table` that handles that. OR you could just have both objects inherit from something like this and then call generate_diff() on the sub objects. one last note: I only store data on the first access, on the edits I first store a snapshot as r0 , then store a diff as r1. that cuts down on duplication. -- 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/groups/opt_out.
[sqlalchemy] testing for an association proxy (possible bug and patch included)
I have this general structure:
class Person:
# orm relationships are preceded by (o)ne or (l)ist
o_Person2Address_ActiveShipping = sa.orm.relationship(
Person2Address, primaryjoin=and_( Person2Address.person_id==Person.id ,
Person2Address.role_id=='active-shipping' ), uselist=False )
active_shipping_address = association_proxy('
o_Person2Address_ActiveShipping', 'address')
class Person2Address:
address = sa.orm.relationship(Address,
primaryjoin=Person2Address.address_id==Address.id)
class Address:
pass
this works perfect when i have a Person2Address and address . I'd imagine
it works fine if the proxy is for an empty list too.
the problem is when o_Person2Address_ActiveShipping is an empty scalar
(from the uselist=False argument).
jim = dbSession.query( Person )
active_shipping = jim.o_Person2Address_ActiveShipping
type(active_shipping)
None
# this will raise an error
if jim.active_shipping_address :
# this will raise an error too
if jim.active_shipping_address
and jim.active_shipping_address.address :
print jim.active_shipping_address
that raises an error on the .active_shipping_address
File
/Users/jvanasco/webserver/environments/project-2.7.5/lib/python2.7/site-packages/sqlalchemy/ext/associationproxy.py,
line 241, in __get__
return self._scalar_get(getattr(obj, self.target_collection))
AttributeError: 'NoneType' object has no attribute 'media_asset'
i think a simple fix could be something like this ( line 240,
sqlalchemy/ext/associationproxy.py
)
if self.scalar:
-if not getattr(obj, self.target_collection)
-return self._scalar_get(getattr(obj, self.target_collection))
else:
if self.scalar:
+proxied = getattr(obj, self.target_collection)
+if not proxied :
+return None
+return self._scalar_get(proxied)
else:
--
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/groups/opt_out.
Re: [sqlalchemy] Feedback appreciated
On Friday, August 23, 2013 1:52:41 AM UTC+3, Michael Bayer wrote: wow that is quite a compliment, I of course have heard of Doctrine and met many PHP users who use it extensively. This is really amazing that you're A. using Python now B. using SQLAlchemy and C. writing full blown software for it, wow ! Thanks! These are all really interesting projects and I had a lot of thoughts looking at all of them just briefly. I also wonder at what points within here should/can some of this be part of SQLA itself, or not. Here's my notes: wtforms: 1. in all cases, use inspect(cls) to get at a Mapper. if on 0.7, use class_mapper(cls). but it would be great if you could target 0.8 and up as a lot of functions were added for exactly these kinds of use cases (See http://docs.sqlalchemy.org/en/rel_0_8/faq.html#how-do-i-get-a-list-of-all-columns-relationships-mapped-attributes-etc-given-a-mapped-class ) 2. ClassManager.values() is not terrible, but again isn't super public. you can use mapper.attrs as well as mapper.column_attrs and others as of 0.8. These are both valid points and I fully agree with you. versioning: 2. unit_of_work.py: you can tell if a statement is an INSERT/DELETE usually by looking at context.isinsert/context.isdelete, and also the table name you can get from context.statement.table (something like that). Similar things can be done where I see you're regexping the DELETE later on. Digging into the string is fine but once you're targeting the broad spectrum of scenarios, like users that are adding SQL comments and such to their SQL, backends that don't actually use SQL, you want to stick with inspecting the expression trees as much as possible. 3. make schema object names configurable, i.e. transaction_id Good points. I created issues for both. 4. This code looks great but I'd still be scared to use it, because versioning is such a particular thing, not to mention interactions with other schema complexities.But I don't say that to be discouraging, just to state how non-trivial a problem this is. When i do versioning for real, there's always weird quirks and things specific to the app, which are easier to hardcode in my versioning code rather than having to configure a 3rd party library to do it. it's why i kept it as just an example in SQLA itself, it's a huge job... but if you can make this extension successful, that'll be very impressive. In the docs it would be nice if I could see immediately what happens to the SQL schema when I use this. I will add this in the docs and I agree the whole thing is a little bit scary. :) I think we can make it a great tool though. One of the things I don't like about Hibernate Envers is its API. With Continuum I tried to get ideas for the API from the best versioning Ruby world has (especially papertrail). The schema Continuum generates is basically the same as the one Hibernate Envers generates (with a little bit different naming conventions). sqlalchemy_utils: 1. have coercion_listener configure itself? coercion_listener.configure(). since it's global usually, and you could always pass a target base class to configure() as an option. Good idea. 2. ScalarListType vs. Postgresql ARRAY ? same/better? should SLT use ARRAY on a PG backend ? Hmm I'm not sure about this yet. Its definately not better than using PostgreSQL ARRAY. ARRAY is better in many ways but its PostgreSQL specific. Maybe we could make ScalarListType use ARRAY on PostgreSQL by default (as you suggested). 3. operators for types! I see these are mostly string storage but you can start adding special operations as needed using TypeEngine.Comparator: http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#types-operators e.g. look at all the operations that pg.ARRAY has (contains, indexed access, concatenation, etc.). then you can make all these types *really* slick. EmailType already uses CaseInsensitiveComparator. I will add more of these as you suggested once I figure out what kind of operators each type needs. :) 4a. batch_fetch - h. I see the idea is avoid JOIN by just feeding the keys into an IN (caveat there, IN works well for small lists, but less so for large - Oracle at least limits their size to 1000, when I have to use batch IN I will actually batch within the IN itself in groups of 500 or so). You know you could build this as a loader strategy. an API overhaul of that system is coming up but the LoaderStrategy API shouldn't change much. Then you could just say query.options(batch_load_all(a.b.c)) like any other option. LoaderStrategy isn't an API that people use often but it is extensible, and 0.9 it's even nicer already, with more to come. I recently posted about it on the development list, if you want to check out sqlalchemy-devel (it's a pretty dead list but I'd value
[sqlalchemy] problems with temporary tables and commits
I'll try to make this succinct... I'm creating a temporary table and then doing a query with it that takes a good length of time. I found that the source tables going into the temporary table were being locked after the temporary table was created even though they were no longer needed for the second much longer query. Apparently create temporary table in MySQL doesn't autocommit and so the transaction is locking a lot more than needed. So... I tried committing right after creating the temporary table, but now I randomly lose the temporary table because SQLAlchemy sees the commit as a reason to return the connection back to the connection pool and then get back another connection on the next query. So, as the temporary table is persistent only on that connection I usually lose the table as I usually don't get the very same connection back from the pool. I'd like to be able to tell MySQL to commit after creating the temporary table so I can drop the locks used to fill that table, but I want to make sure SQLAlchemy doesn't let go of the connection and return it to the pool. -- 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/groups/opt_out.
Re: [sqlalchemy] testing for an association proxy (possible bug and patch included)
On Fri, Aug 23, 2013 at 12:11:39PM -0700, Jonathan Vanasco wrote: i think a simple fix could be something like this ( line 240, sqlalchemy/ext/associationproxy.py ) if self.scalar: -if not getattr(obj, self.target_collection) -return self._scalar_get(getattr(obj, self.target_collection)) else: if self.scalar: +proxied = getattr(obj, self.target_collection) +if not proxied : +return None +return self._scalar_get(proxied) else: We're monkey-patching AssociationProxy.__get__ with the same change since SQLA 0.5.x, so it would be nice to get it applied upstream... Maybe in 0.9? Gabor -- 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/groups/opt_out.
[sqlalchemy] OrderingList not updating position as expected
Hello, I've ran into a potential issue with OrderingList. When we are creating a new child, we'd like to be able to simply specify child.parent (or child.parent_id), and have child.position be updated appropriately when committed. This saves querying for the parent object, and then appending the child to the list. Here's a gist of what I'd like to be able to do: https://gist.github.com/zeckalpha/6324142 I'm on 0.8.2. Thanks, kms -- 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/groups/opt_out.
Re: [sqlalchemy] testing for an association proxy (possible bug and patch included)
On Fri, Aug 23, 2013 at 2:31 PM, Gombas, Gabor (IT) gabor.gom...@morganstanley.com wrote: On Fri, Aug 23, 2013 at 12:11:39PM -0700, Jonathan Vanasco wrote: i think a simple fix could be something like this ( line 240, sqlalchemy/ext/associationproxy.py ) if self.scalar: -if not getattr(obj, self.target_collection) -return self._scalar_get(getattr(obj, self.target_collection)) else: if self.scalar: +proxied = getattr(obj, self.target_collection) +if not proxied : +return None +return self._scalar_get(proxied) else: We're monkey-patching AssociationProxy.__get__ with the same change since SQLA 0.5.x, so it would be nice to get it applied upstream... Maybe in 0.9? The patch seems like surprising Python behavior to me. Traversing across a None is almost certainly a bug in regular code, and quashing that error by default feels dangerous. I would want this to raise by default (and I have found bugs because it did.) I think you could opt into this behavior by supplying an alternate, custom getter function that quashed None when creating the proxy. -- 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/groups/opt_out.
Re: [sqlalchemy] problems with temporary tables and commits
well a CREATE TABLE will autocommit but only if it's on an Engine that's not already in a transactionand when you use a Session it sets up a transaction that stays open until you say commit(). I can see the problem here, one way around is to actually bind the Session to a connection like this: conn = engine.connect() sess = Session(bind=conn) otherwise, just sending through the commit on the DBAPI connection directly might not be so terrible. If you said, session.connection().connection.commit() that should have the same effect. On Aug 23, 2013, at 4:32 PM, Tim Tisdall tisd...@gmail.com wrote: I knew I'd find a possible solution right after asking... Isn't that always the way? I found that I can do DBSession.execute(COMMIT) to get MySQL to commit the temporary table but SQLAlchemy/transaction doesn't seem to pick up on it and I don't lose the connection to the threadpool. This seems kind of a hack, but does anyone have a better solution? On Fri, Aug 23, 2013 at 4:12 PM, Tim Tisdall tisd...@gmail.com wrote: I'll try to make this succinct... I'm creating a temporary table and then doing a query with it that takes a good length of time. I found that the source tables going into the temporary table were being locked after the temporary table was created even though they were no longer needed for the second much longer query. Apparently create temporary table in MySQL doesn't autocommit and so the transaction is locking a lot more than needed. So... I tried committing right after creating the temporary table, but now I randomly lose the temporary table because SQLAlchemy sees the commit as a reason to return the connection back to the connection pool and then get back another connection on the next query. So, as the temporary table is persistent only on that connection I usually lose the table as I usually don't get the very same connection back from the pool. I'd like to be able to tell MySQL to commit after creating the temporary table so I can drop the locks used to fill that table, but I want to make sure SQLAlchemy doesn't let go of the connection and return it to the pool. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/9dfigjQt1Bw/unsubscribe. To unsubscribe from this group and all its topics, 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/groups/opt_out. -- 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/groups/opt_out. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] OrderingList not updating position as expected
On Aug 23, 2013, at 6:07 PM, Kyle Marek-Spartz zeckal...@gmail.com wrote: Hello, I've ran into a potential issue with OrderingList. When we are creating a new child, we'd like to be able to simply specify child.parent (or child.parent_id), and have child.position be updated appropriately when committed. This saves querying for the parent object, and then appending the child to the list. well in the first case, say we have this: s = Session.query(Slide).get(1) b = Bullet() b.slide = s when we load Slide, slide.bullets is unloaded. The orderinglist can't do its job here without emitting a SELECT for slide.bullets first. As it turns out, this doesn't occur in this case. Setting b.slide = someslide was optimized a long time ago to not emit a usually wasteful SELECT of the other side of the collection. If you were to say this: s = Session.query(Slide).get(1) s.bullets b = Bullet() b.slide = s then it sets position, because b.slide = s emits the backref to s.bullets.append and you get the ordering behavior. So when you say, this saves querying for the parent object, if you're looking for saving on the performance of a SELECT, you're not going to get that. If you're looking for that it should just work without any explicit code, then yes we need some extra help here. I'm not sure what we should do to orderinglist directly, this should at least be documented, but here is how you can make sure s.bullets is present: from sqlalchemy import event @event.listens_for(Bullet.slide, set) def ping_slide_bullets(target, value, oldvalue, initiator): value.bullets return value of course in your example, you need to keep that Slide object attached to the Session (don't keep closing the session and creating new ones) otherwise it can't emit the lazyload for bullets. For the second part of the example, that's a totally different thing, that's the I want to set bar.foo_id = 7 and have it act like bar.foo = Session.query(Foo).get(7)There's an FAQ entry which also refers to a wiki recipe that, using more events, can approximate this behavior for the typical case: http://docs.sqlalchemy.org/en/rel_0_8/faq.html#i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7 . signature.asc Description: Message signed with OpenPGP using GPGMail
