[Sqlalchemy-users] simple object layer

[Aaron Bickell]

I recently ported a pretty large project over sqlalchemy, and it was pretty smooth, but I kept running into the same few minor snags.  Mainly, I found that I kept on wanting to use the lower level table api within the same transaction as mapped objects that I'm modifying.  Because modifications to the objects aren't persisted until objectstore.commit() is called, I had to do a lot of juggling to make this work.  What I came up with to solve this was a very thin, simple object layer on top of the table api that gives some nice object abstraction, but is able to act directly on the database rather than defer all operations to the objectstore.  I whipped up a module that lets you do the following, using a metaclass: class User(SimpleObject): class table: _table = user_table  # same old user table from all the examples u = User() u.user_name = 'jimbob' u.password = 'biscuits' u.insert() print u.user_id # <- the insert() populated the id (good for logging) users = User.select(User.user_name.like('jim%')) # the User class has attributes are the columns from the table  print users # a list of user objects u.user_name = 'steve-o' u.update() # updates the object with the new values u.delete() # delete the object i created User.delete_where(User.user_id<47)  # deletes a bunch of users, works the same for update u = User.get(109) # gets the user by the primary key This approach cuts out the objectstore which takes away the ability for sqlalchemy to manage relations for you, but because the objects have update/delete methods on the objects, so the problem is still manageable.  The User object could pretty easily have a method that fetches the objects related to this object (preferences, addresses, etc). This approach was particularly useful for my application because I was modifying/deleting a lot of data in any given transaction, and it filled the gap between mapped objects and tables very nicely for me.  Would others find this a useful abstraction of the table api?  Is there already a better way of doing this that I missed?  I'd gladly provide the code if anyone was interested (after i clean it up).  SQLAlchemy has been great to use, and solves a lot of the problems I had continually fought for a while. Aaron Bickell Research & Development Optio Software, Inc