[sqlalchemy] Re: How to lock database row on select?
Basil Shubin пишет: Hi friends! I have use SQLAlchemy 0.3 with MySQLdb 1.2.1 How I can lock row in database table when I select it? So nobody can access it in same time. BTW I use tables with InnoDB engine. Okey, I discover 'lockmode' parameter. And how I can use it (or anything else) to get this: if I have select the row, so NOBODY can access to it? Moreover there be should an exception raised, so I can catch it and show to user message box about that database entry is in use by somebody? Is this possible and how do you solve this task? I need it to disallow mutiple editing/deleting of same entry by different users in same time. Thanks! -- Basil Shubin Freelance Software Developer --~--~-~--~~~---~--~~ 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] A better CascadeOptions impl
Here's a better CascadeOptions implementation:
from sets import Set
class CascadeOptions(Set):
keeps track of the options sent to relation().cascade
def __init__(self, arg=):
values = util.Set([c.strip() for c in arg.split(',')])
if delete-orphan in values: self.add(delete-orphan)
if delete in values or self.delete_orphan or all in
values: self.add(delete)
if save-update in values or all in values: self.add
(save-update)
if merge in values or all in values: self.add(merge)
if expunge in values or all in values: self.add(expunge)
for name in (delete-orphan, delete, save-update,
merge, expunge):
setattr(self, name.replace(-, _), name in self)
# refresh_expire not really implemented as of yet
#self.refresh_expire = refresh-expire in values or all
in values
#def __contains__(self, item):
#return getattr(self, item.replace(-, _), False)
This does the item.replace(-, _) part once on initialization
rather than every time __contains__ is called. It works as long as
nothing ever tries to change the options after the object is
initialized.
~ Daniel
--~--~-~--~~~---~--~~
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] session.flush calls commit, and Binary issue
Using sqlalchemy rev 2102 is flush supposed to commit by itself? Also, looks like Binary fields get flushed every time, even when not changed.. Again, using wsgi middleware to automatically rollback if an exception occurs in my app, I have a use case where I absolutely must commit a transaction even if an exception occurs later. So I have a set of master/child tables, Shipment - Package I have session and a transaction (on which, begin has already been called). Here's what happens. now = datetime.datetime.now() shipment = session.query(Shipment).get_by() shipment.hash = '' for package in shipment.packages: package.void_requested = now print FLUSH 1 session.flush() print COMMIT 1 trans.commit() # some other non db stuff package.void_confirmed = now print FLUSH 2 session.flush() print COMMIT 2 trans.commit() (this causes uow to write out the entire shipment object, including large and unchanged pickle fields.. then write out package) FLUSH 1 2006-11-14 21:04:38,532 INFO sqlalchemy.engine.threadlocal.TLEngine.0x..94 UPDATE shipment SET quote_pickle=?, quote_hash=?, decoded_manifest=? snip 2006-11-14 21:04:38,837 INFO sqlalchemy.engine.threadlocal.TLEngine.0x..94 UPDATE package SET void_requested=? WHERE package.id = ? INFO:sqlalchemy.engine.threadlocal.TLEngine.0x..94:UPDATE package SET void_requested=? WHERE package.id = ? 2006-11-14 21:04:38,838 INFO sqlalchemy.engine.threadlocal.TLEngine.0x..94 [datetime.datetime(2006, 11, 14, 21, 4, 38, 518976), 200] INFO:sqlalchemy.engine.threadlocal.TLEngine.0x..94:[datetime.datetime(2006 , 11, 14, 21, 4, 38, 518976), 200] COMMIT 1 2006-11-14 21:04:38,850 INFO sqlalchemy.engine.threadlocal.TLEngine.0x..94 COMMIT INFO:sqlalchemy.engine.threadlocal.TLEngine.0x..94:COMMIT And then comes FLUSH2, like so: FLUSH 2 2006-11-14 21:04:39,544 INFO sqlalchemy.engine.threadlocal.TLEngine.0x..94 BEGIN INFO:sqlalchemy.engine.threadlocal.TLEngine.0x..94:BEGIN 2006-11-14 21:04:39,551 INFO sqlalchemy.engine.threadlocal.TLEngine.0x..94 UPDATE shipment SET quote_pickle=?, decoded_manifest=? WHERE shipment.id = ? INFO:sqlalchemy.engine.threadlocal.TLEngine.0x..94:UPDATE shipment SET quote_pickle=?, decoded_manifest=? WHERE shipment.id = ? (here it is, again writing out these unchanged shipment fields, shipment hasn't been touched between the 2 flushes) 2006-11-14 21:04:39,750 INFO sqlalchemy.engine.threadlocal.TLEngine.0x..94 UPDATE package SET void_confirmed=? WHERE package.id = ? INFO:sqlalchemy.engine.threadlocal.TLEngine.0x..94:UPDATE package SET void_confirmed=? WHERE package.id = ? 2006-11-14 21:04:39,750 INFO sqlalchemy.engine.threadlocal.TLEngine.0x..94 [datetime.datetime(2006, 11, 14, 21, 4, 39, 541557), 200] INFO:sqlalchemy.engine.threadlocal.TLEngine.0x..94:[datetime.datetime(2006 , 11, 14, 21, 4, 39, 541557), 200] 2006-11-14 21:04:39,755 INFO sqlalchemy.engine.threadlocal.TLEngine.0x..94 COMMIT INFO:sqlalchemy.engine.threadlocal.TLEngine.0x..94:COMMIT (see above, flush did a commit by itself) COMMIT 2 File '/usr/local/src/sqlalchemy/lib/sqlalchemy/engine/base.py', line 389 in commit raise exceptions.InvalidRequestError(This transaction is inactive) InvalidRequestError: This transaction is inactive engine strategy is threadlocal. Is this the way it's supposed to work? -- Brad Clements,[EMAIL PROTECTED](315)268-1000 http://www.murkworks.com AOL-IM or SKYPE: BKClements --~--~-~--~~~---~--~~ 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: autoincrement always True
autoincrement defaults to true. in previous versions of SA, this was not even an option; SERIAL and AUTO_INCREMENT were always used for integer primary key columns in postgres and mysql, respectively, which is why this is the default. note that autoincrement is meaningless for sqlite (which always auto-increments a singular integer primary key column), oracle (which has no auto-incrementing feature). --~--~-~--~~~---~--~~ 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: session.flush calls commit, and Binary issue
flush() starts its own transaction and commits it. if you have already opened a transaction on the current session, then it will nest inside the larger transaction. see the SessionTransaction documentation for details. Also, the pickle type not getting updated is something that was fixed in the 0.3 series, and we have unit tests that insure a non-changed pickle type does not trigger an update. I need a fully functioning test script showing me your schemas for this one. (using PickleType, correct?) additionally, if you call a flush() inside a larger transaction, and the flush fails, its going to roll the transaction back. not sure what functionality youre looking for , a flush that commits part of the way, screws up, and doesnt roll back the invalid data , so that you can commit it all ? are you sure you arent looking for a separate transaction to commit the data that must be committed ? --~--~-~--~~~---~--~~ 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] GUI + SQLAlchemy?
Hi friends! Is there standalone GUI application that using SQLAlchemy? Of course it should be open source apps. -- Basil Shubin Freelance Software Developer --~--~-~--~~~---~--~~ 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] Some questions about SqlAlchemy
Hi, I have some questions about SqlAlchemy. 1. I know I can define a relation on a Class and load records of the relation in only one select (eager loading), but I see SqlAlchemy uses a LEFT OUTER JOIN to join. If I would filter records by a WHERE CLAUSE on a relation, I would like to have an INNER JOIN, instead. How can I have an INNER JOIN ? Also, how can I specify a WHERE CLAUSE on a relation ? For Example: Class Orders: . Class OrderLine: SELECT FROM Orders INNER JOIN OrderLine ON . WHERE OrderLine.item = 'bicycle' 2. I would like to join tables that are stored into different databases. How can I do this ? For example: Orders on database 1 OrderLines on database 2 Thanks, Stefano --~--~-~--~~~---~--~~ 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: GUI + SQLAlchemy?
On 11/14/06, Basil Shubin [EMAIL PROTECTED] wrote: Is there standalone GUI application that using SQLAlchemy? Of course it should be open source apps. There is neither an open source nor a commercial GUI interface for SQLAlchemy. --~--~-~--~~~---~--~~ 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] redirect sqlalchemy log to stderr
Hello there, Is there any simple way to redirect sqlalchemy log to stderr? I did not find any examples for this. I'm developing a web application with sqlalchemy using web.py, and it's quite annoying to have debug output in stdout. Thanks, --- Regards, Dmitry --~--~-~--~~~---~--~~ 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: session.flush calls commit, and Binary issue
I am using PickleType, and Binary on the same table.. I'll see if I can come up with a small test. These fields are being written back every time any other field changes, even when they haven't changed. Regarding transactions. I thought session.flush was just UOW work and didn't actually do commits. I need a outside transaction wrapper that begins when a web request is received, and commits if the web request was processed w/o error. In that use case, session.flush should not actually commit it's changes. My second use case (described in the original message) is that occassionaly, within this transaction wrapper, I will need to make a commit once in a while. So write me up as really confused now.. I thought session was just UOW and didn't handle low-level transactions at all.. I am using explicit connections and sessions.. --~--~-~--~~~---~--~~ 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: session.flush calls commit, and Binary issue
let me tell you how pickletype works right now (by default, you can change it). there is a method on all TypeEngine objects called compare_values(). this method is now used by the attributes package when it checks if something changed. in most cases, the method uses a the == comparison. In the case of PickleType, by default it does something different: def compare_values(self, x, y): return self.pickler.dumps(x, self.protocol) == self.pickler.dumps(y, self.protocol) why does it do that ? because it is exhaustively detecting any change anyhwere within the pickled state. I would hypothesize that something is changing somewhere in your binary data. To turn the feature off, use the flag mutable=False when you declare your PickleType. Then it will use the old method, using the is operator (i.e. x is y). you can also subclass PickleType and add a comparison of some other method, i.e. using == for example. for your transaction issue, its important to understand the automatic nesting of transactions in sqlalchemy. if you begin a transaction on a resource, and then someone else also begins an transaction on the same resource, you control the transaction; the inner call gets a placeholder transactional object which is capable of rolling back the transaction, but not committing it. to commit the transaction, the original outermost caller that began it must commit. in the case of UOW, it begins its own transaction, and commits. this is essential for the flush() method to work properly, since it a). is issueing many SQL statements which must be in a transaction (therefore begin()) and b). has to have the data written to the DB when its done (therefore commit()). If you are using your own transaction on the same resource, then the UOW's transaction will nest into yours as described above. The transaction nesting can be used via the SessionTransaction interface or also through the Transaction object off of Connection. connection = e.connect() transaction = connection.begin() session = create_session(bind_to=connection) ... do stuff.. session.flush() transaction.commit() its all in the docs in the Sessions chapter. --~--~-~--~~~---~--~~ 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: How to lock database row on select?
Mike Bernson пишет:
use the lockmode='update' on a query object.
row_obj = query_obj.select(where, lockmode='update').execute()
self.localTowns = Table('local_towns', self.metadata,
Column('id', Integer, primary_key=True),
Column('title', Unicode(50)),
mysql_engine='InnoDB')
self.localTowns.create(checkfirst=True)
item = self.localTowns.select(self.localTowns.c.id==itemId,
lockmode='update').execute()
and I got this error :-(
File /usr/lib/python2.4/site-packages/sqlalchemy/sql.py, line 1332,
in select
return select([self], whereclause, **params)
File /usr/lib/python2.4/site-packages/sqlalchemy/sql.py, line 65,
in select
return Select(columns, whereclause = whereclause, from_obj =
from_obj, **kwargs)
TypeError: __init__() got an unexpected keyword argument 'lockmode'
--
Basil Shubin
Freelance Software Developer
--~--~-~--~~~---~--~~
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: How to lock database row on select?
Mike Bernson пишет:
use the lockmode='update' on a query object.
row_obj = query_obj.select(where, lockmode='update').execute()
self.localTowns = Table('local_towns', self.metadata,
Column('id', Integer, primary_key=True),
Column('title', Unicode(50)),
mysql_engine='InnoDB')
self.localTowns.create(checkfirst=True)
self.session = create_session()
mapper(dbsql.LocalTown, self.localTowns)
item =
self.session.query(dbsql.LocalTown).with_lockmode('update').execute().selectone(self.localTowns.c.id==itemId)
I got this:
File /home/bashu/work/devel/wxExpress/lib/database.py, line 95, in
GetLocalTown
item =
self.session.query(dbsql.LocalTown).with_lockmode('update').execute().selectone(self.localTowns.c.id==itemId)
TypeError: execute() takes at least 2 arguments (1 given)
--
Basil Shubin
Freelance Software Developer
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
