[sqlalchemy] Re: Modifying metadata, autogenerating table definitions, etc.
i have moved the metadata autoloaddiff into own place under dbcook/misc/metadata/: http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc/ svn co https://dbcook.svn.sourceforge.net/svnroot/dbcook/trunk/dbcook/misc/ IMO, wiki's are ok for readymade/works-for-me things and explanations, but any live/maintanable code should stay in a repository. i'm planning to use that misc/ directory as repository for all sorts of recipes about dbcook/SA usage for various application field things. For now only the metadata/ is there - and once embedded into SA, it may disappear; from other things below, the bitemporal/ stuff is 100% ready but have to separate it - Ants, we could exchange some ideas about it later; multilang/ and nested_user_trans/ are invented but not written/ready, cache_results/ is sort of invented but far from ready (and may need triggers / in-SQL-funcs which aren't supported by SA yet). The rest is just eventualities. here the dbcook/misc/metadata/readme.txt: --- metadata/: (SA-only) metadata autoload and diff bitemporal/: (SA ~only) addon for objects that have 2-time-history + state(enabled/disabled) needs following properties per object: obj_id = ObjId() #incremented by special ObjectCounter time_valid = Date() time_trans = Date() disabled= Bool( default_value= False) multilang/: (dbcook/SA) addon for transparent multi-language textual properties, allowing for history nested_user_transactions/: (dbcook/SA) addon to support nested user transactions aspect (NOT nested DB transactions). needs following properties per object: transact= TransactID( default_value= 0) cache_results/: (dbcook/SA) addon for automaticaly-updated database-denormalisation caches of intermediate results, each one depending on particular pattern of usage. Wishful usage syntax (ALL else automatic): class Cache4averagePerson( Base): fieldname = cache_aggregator( klas.field, AggrFilterCriteria) #e.g. #age = cache_agregators.Average( Person.age, FilterCriteria1) #salary = cache_agregators.Sum( Person.salary, FilterCriteria2) may be invented or may not: Numerator - something that gives numbers/ids to objects in whatever special way. Think of invoices, incoming/outgoing/reference documents, inventories etc. multivalue - a value that is represented/representable in many currencies/ways/measurements. Think of money, items that can be measured either as volume or as weight (e.g. gas), etc. = ciao svil --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Newbie question about transactions
Thanks again.
Let me see if I understood this correctly.
What I tried to do initially is use implicit connections that I have
no control over. The transaction was started on my explicit connection
but the inserts used a different connection from the pool and
therefore were not aware of the transaction. Right?
On Jul 29, 12:12 am, Michael Bayer [EMAIL PROTECTED] wrote:
discussion starts here:
http://www.sqlalchemy.org/docs/dbengine.html#dbengine_connections
covers some options available to you including using a thread local
connection.
On Jul 28, 2007, at 5:07 PM, mc wrote:
Thanks.
Can you point me to the place in the doc where this is discussed?
I was under the impression that I had one connection only in this
scenario.
TIA
On Jul 28, 11:09 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Jul 28, 2007, at 2:54 PM, mc wrote:
md=BoundMetaData(db)
t=Table('test', md, autoload = True)
trans = cn.begin()
try:
insert(t).execute(id=332)
insert(t).execute(id=332)
trans.commit()
except:
trans.rollback()
raise
the statements must be executed relative to the connection which
contains the transaction:
cn.execute(insert(t), id=332)
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] howto map python-class and stored procedures
Hello, recently i started working with SQLAlchemy and i have one question regarding the stored procedures. On the following website, there is a quide of how to execute the stored procedures: http://groups.google.com/group/sqlalchemy/browse_thread/thread/d0b3ad0379606e81/eb1447a0fd3129cc?lnk=gstq=func+graphrnum=1 My question is how to map Python-class and stored procedures. For exapmple: class Person: givenName surName id and the procedures: getPerson( id ) setPerson( id, gName, sName ) createPerson( ) removePerson( id ) Can you please tell me if it is possible to use getPerson instead of select...; and setPerson... instead of update? Thank you for your help. Best regards, Peter --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Newbie question about transactions
On Jul 29, 2007, at 7:54 AM, mc wrote: Thanks again. Let me see if I understood this correctly. What I tried to do initially is use implicit connections that I have no control over. The transaction was started on my explicit connection but the inserts used a different connection from the pool and therefore were not aware of the transaction. Right? yup --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: querying many-to-many (WordPress)
On Jul 29, 2007, at 1:11 AM, Max Ischenko wrote:
Hello,
I'm using SQLAlchemy to access my WordPress database and I need to
query posts from particular category.
There is a many-to-many mapping between wp_posts and wp_categories
table, throught wp_post2cat table.
I was able to come up with the following code:
cats = self.meta.tables['wp_categories']
posts = self.meta.tables['wp_posts']
post2cat = self.meta.tables['wp_post2cat']
q = self.session.query(WordpressPost)
q = q.filter(WordpressPost.c.status=='publish')
q = q.filter(WordpressCategory.c.slug=='sitenews')
q = q.filter(post2cat.c.post_id==posts.c.ID)
q = q.filter(post2cat.c.category_id==cats.c.cat_ID)
you should be able to
session.query(WordpressPost).filter_by(status='publish').join
('categories').filter_by(slug='sitenews')
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: howto map python-class and stored procedures
On Jul 29, 2007, at 9:21 AM, peter wrote: Hello, recently i started working with SQLAlchemy and i have one question regarding the stored procedures. On the following website, there is a quide of how to execute the stored procedures: http://groups.google.com/group/sqlalchemy/browse_thread/thread/ d0b3ad0379606e81/eb1447a0fd3129cc?lnk=gstq=func+graphrnum=1 My question is how to map Python-class and stored procedures. For exapmple: class Person: givenName surName id and the procedures: getPerson( id ) setPerson( id, gName, sName ) createPerson( ) removePerson( id ) Can you please tell me if it is possible to use getPerson instead of select...; and setPerson... instead of update? the ORM was designed around a model of issuing direct SQL. if you have stored procedures that do persistence, then your ORM is partially embedded in your database. Its may be possible to get your getPerson function to work for ORM selects, by creating a select statement out of it and mapping to that (but that would require a getPerson() that returns lists of person rows). but for the update side, SA's ORM only knows INSERT/UPDATE/DELETE. youd have to issue those setPerson/createPerson statements using contstructed or textual SQL statements. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] PROPOSAL: Session
This would be a new name available in 0.4 which would produce the same Session that we are familiar with, except it would be by default transactional and autoflushing. The create_session() function stays around and does what it always did, producing a regular session which you flush(). Its currently implemented in the trunk, and we think we will probably release 0.4 as a beta since we really need to get you guys playing with it, but not in that i just put it on production and it broke kind of way. The transactional/autoflushing session would work like this: sess = Session() u = User(name='john') sess.save(u) # user is available immediately, as its flushed by # the time we query sess.query(User).filter_by(name='john').one() User(name='john') # persist data to the database permanently sess.commit() flush() is still there and does what it always did, should you want to call it explicitly. I also would be resurrecting a very old function that's just been hidden all this time, which I think might be nice, which is rollback(): # change john's name user.name = 'ed' # rollback changes. rolls back the transaction *and* in memory changes sess.rollback() user.name # user's name back to 'john'. works for collections too. 'john' # close the session sess.close() One caveat of the above is that, particularly with Postgres, you *really* need to commit or close the session when you're done, to release connection resources..postgres locks aggressively. Advantages to this new approach: - queries return everything you've just done in the session automatically without any need to call flush(); theres no more disconnect between the objects you save() and update() in the session and your queries. - this is how Hibernate has always worked, so its a pretty proven model (except for the rollback part) - in particular, a new kind of relation we have called a dynamic relation makes good use of this; its basically a Query tacked on to a special collection which receives only append() and remove() events. all iteration and array operations from this relation query the database immediately. - transactional behavior means the Session uses a single connection, then holds onto it for a large set of operations. this improves consistency and puts less stress on the connection pool. - the transaction also allows autoflush and rollback() to make more senseno need to worry that things were flushed too soon. Disadvantages: magical - now, you might have FlushErrors being raised when all you wanted to do was issue a Query. this might be confusing. - issues more SQL - if you are manipulating objects and querying as well, by the time you get to commit() you may have flushed many times, whereas previously you would only have flushed once at the end. - transactional - you really need to close() and/or commit() the session, if its going to continue hanging around; it references connection/transactional resources. Postgres in particular really likes to hang up if tables are still locked (although i notice this mostly with our unit tests which have to DROP tables a lot). - transactional again - a lot of you use MySQL with ISAM tables, these are not transactional. might get inconsistent results. while we have both Session() and create_session() in the trunk now, Session() would be what we document going forward. flags to make it act other ways are still available, like autoflush/transactional. any thoughts ? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
