[sqlalchemy] Checking active transactions
In TurboGears 1, requests are encapsulated between a session.begin() and session.commit() call (or session.rollback(), if there was an error). Starting with SA 0.4.3, the commit() raises an exception if the transaction has been already rolled back in the request (explicitly or due to an error). So TurboGears needs to check whether the transaction is active before the session.commit() call. We currently do this by storing the transaction as the return value of session.begin() and then checking transaction.is_active (or transaction.session.transaction for earlier SA versions). This gets complicated since the transaction may be restarted during a request and can change. So I think it would be handy to tell directly from the session whether the transaction is active or not. We are using *scoped* sessions in TruboGears, and the problem is that the transaction attribute is not available in ScopedSessions, so I can not check for session.transaction.is_active. I can get the original Session by calling session, so session().transaction.is_active would work, but it does not feel right. I guess it is by intent that the session attributes are not visible in scoped sessions. So how about adding a property is_active to the Session that would call session.transaction.is_active? Then you could do: if session.is_active: session.commit() -- Christoph --~--~-~--~~~---~--~~ 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: Checking active transactions
On Feb 22, 2008, at 5:29 AM, Christoph Zwerschke wrote: In TurboGears 1, requests are encapsulated between a session.begin() and session.commit() call (or session.rollback(), if there was an error). Starting with SA 0.4.3, the commit() raises an exception if the transaction has been already rolled back in the request (explicitly or due to an error). So TurboGears needs to check whether the transaction is active before the session.commit() call. We currently do this by storing the transaction as the return value of session.begin() and then checking transaction.is_active (or transaction.session.transaction for earlier SA versions). This gets complicated since the transaction may be restarted during a request and can change. So I think it would be handy to tell directly from the session whether the transaction is active or not. We are using *scoped* sessions in TruboGears, and the problem is that the transaction attribute is not available in ScopedSessions, so I can not check for session.transaction.is_active. I can get the original Session by calling session, so session().transaction.is_active would work, but it does not feel right. I guess it is by intent that the session attributes are not visible in scoped sessions. So how about adding a property is_active to the Session that would call session.transaction.is_active? Then you could do: if session.is_active: session.commit() thats fine...but also why cant you just say try: session.commit() except: session.rollback(); raise ? --~--~-~--~~~---~--~~ 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: Checking active transactions
Michael Bayer schrieb: thats fine...but also why cant you just say try: session.commit() except: session.rollback(); raise ? This would raise an error when the session is already inactive. I don't want an error in this case. On the other hand, if I remove the raise statement, then errors on commit would be swallowed. I thought about catching only InvalidRequestError, but this might also catch too much. -- Christoph --~--~-~--~~~---~--~~ 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: Update master-detail problem
is the SQL emitted ? No, It isn't. By example, if I modify master record data usr1.name = new name and modify any addresses object (like first example) the sql statement (UPDATE) is generated for users table but not for addresses table. is it possible your session is in an open transaction and you need to commit() ? No. also, if the email_address attribute of addr is already [EMAIL PROTECTED], no SQL will be emitted for that either since nothing has changed. Yes, I know. Maybe I don't be clear in my example, but addr.email_address have not assigned the same value. The idea is what this attribute change, but not update is generated. BTW, I do a simple (and quickly) test using large_collection.py example. This use sqlite engine and in this case all apperar work fine. (Repeat, It was a quickly test). Perhaps, it's a firebird engine problem. --~--~-~--~~~---~--~~ 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: Audit log client side
check MapperExtensions, u have pre+post ins/upd/del hooks there. u may or may not have a mapper for the log-table. On Friday 22 February 2008 21:21:52 Marco De Felice wrote: Hi I'm thinking about a simple client side table audit with SA. Given the audit log pattern: http://martinfowler.com/ap2/auditLog.html wouldn't it be simple to adapt it to a mapped class? I was thinking of a log() function inside the mapped class that if called saves data into a second table (could it be in a distinct log database?) with the same columns + timestamp and current user columns. Is there a way to automate this function at the orm level without defining additional mappers? I'm sorry for the vagueness but I'm really just beginning to explore this. Thanks all --~--~-~--~~~---~--~~ 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: Audit log client side
and, i do have bitemporal pattern implemented at http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc/timed2/ it is not at all optimized but is correct. check MapperExtensions, u have pre+post ins/upd/del hooks there. u may or may not have a mapper for the log-table. On Friday 22 February 2008 21:21:52 Marco De Felice wrote: Hi I'm thinking about a simple client side table audit with SA. Given the audit log pattern: http://martinfowler.com/ap2/auditLog.html wouldn't it be simple to adapt it to a mapped class? I was thinking of a log() function inside the mapped class that if called saves data into a second table (could it be in a distinct log database?) with the same columns + timestamp and current user columns. Is there a way to automate this function at the orm level without defining additional mappers? I'm sorry for the vagueness but I'm really just beginning to explore this. Thanks all --~--~-~--~~~---~--~~ 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] Audit log client side
Hi I'm thinking about a simple client side table audit with SA. Given the audit log pattern: http://martinfowler.com/ap2/auditLog.html wouldn't it be simple to adapt it to a mapped class? I was thinking of a log() function inside the mapped class that if called saves data into a second table (could it be in a distinct log database?) with the same columns + timestamp and current user columns. Is there a way to automate this function at the orm level without defining additional mappers? I'm sorry for the vagueness but I'm really just beginning to explore this. Thanks all --~--~-~--~~~---~--~~ 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: Update master-detail problem
On Feb 22, 2008, at 1:30 PM, maxi wrote: BTW, I do a simple (and quickly) test using large_collection.py example. This use sqlite engine and in this case all apperar work fine. (Repeat, It was a quickly test). Perhaps, it's a firebird engine problem. that would be my next guess. have you tried sqlalchemy version 0.4.3, or can you provide a full example of what you're doing ? --~--~-~--~~~---~--~~ 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: several unions
On Feb 22, 2008, at 6:10 PM, naktinis wrote: Hello, I was wondering if it's possible to do several unions using SQLAlchemy. I had no problems writing a sqlalchemy query with one union, but after aplying the second one it returned 'CompoundSelect' object has no attribute 'union', which probably means the returned object with union applied cannot have another union applied on top of it. However, MySQL syntax supports several unions (it looks like SELECT smth1 WHERE condition1 UNION SELECT smth2 WHERE condition2 UNION SELECT smth3 WHERE condition3 ... ) So, is there a way to something similar using SQLAlchemy? yeah i actually updated the docs on this one to favor using the standalone union() construct which allows better control over nesting and such (particularly in combination with other compound operators like intersect, except_ ): union(select(), select(), select(), ) --~--~-~--~~~---~--~~ 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] Reflecting tables with foreign keys
I'm running SA 0.4.3 on Win XP, accessing a MSSQL database using pyodbc, and I want to autoload some table definitions. In the Metadata documentation, I read: Note that if a reflected table has a foreign key referencing another table, the related Table object will be automatically created within the MetaData object if it does not exist already. This doesn't seem to work. When I reflect a table with FKs, it comes in OK, and the metadata seems to know about the FK, but eval'ing 'referenced_table' in meta.tables (as in the example) returns False. Also, reflecting the referenced table without the autoload=True parameter doesn't work -- the table I get is empty (but it does come in OK if I use autoload). I also tried using meta.table_iterator, and got a message like Could not find table xxx with which to generate a foreign key. The table its asking for is indeed referenced, and if I autoload it (and all the other referenced tables), the iterator works fine. I can understand that it might not be a good idea, when autoloading a table, to try to bring in the entire FK tree with it; in my database, doing that on some tables would bring in several dozen other tables. I'm really just trying to reconcile what I read in the docs with how the code works. Thanks for any good words, -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ 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: Reflecting tables with foreign keys
hi don - heres a script using SQLite which illustrates how the foreign key reflection works. This same sort of thing should be working on MS-SQL as well but I dont have access to an MS-SQL server here to test. If the example below is not working for MS-SQL, please file a trac ticket - we have some MS-SQL developers who can take a look. - mike from sqlalchemy import * engine = create_engine('sqlite:///', echo=True) metadata = MetaData(engine) Table('t1', metadata, Column('id', Integer, primary_key=True), Column('name', String(60))) Table('t2', metadata, Column('id', Integer, primary_key=True), Column('t1_id', Integer, ForeignKey('t1.id')), Column('name', String(60))) metadata.create_all() meta2 = MetaData(engine) # reflect t2, t1 gets loaded too t2 = Table('t2', meta2, autoload=True) assert 't1' in meta2.tables assert meta2.tables['t1'].c.id.primary_key # reflect an entire DB meta3 = MetaData(engine) meta3.reflect() print [t for t in meta3.tables] --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---