ZSQL Methods and transaction control
Hi there, I am implementing a tool to handle userdata that is stored in a MySQL db. The underlaying logic of the stored procedures used to maintain the db content dictate that I have to control the transactions myself. I understand that there is a way to handle transaction controll in the ZMySQL methods. However I find no documentation at all on how to use it. Without using ZMySQL methods I would be using the following piece of code: db = mysql.connect(host="localhost" ...) cursor = db.cursor() query = "CALL insertUser('%s', '$xxx$', @id, @error);select @id, @error" % 'JohnTheUser' cursor.execute(query) # get the next resultset, it has the result of the select cursor.nextset() result = cursor.fetchall()[0] error = int(result[1]) if error: print "error %s when trying to add user %s" % (ERRRORS[error], 'JohnTheUser') db.rollback() continue #we are in a loop actually .. db.commit() How can I mimic that using ZSQL Methods thanks for any pointers Robert
robert rottermann schrieb:
Hi there,
I am implementing a tool to handle userdata that is stored in a MySQL db. The underlaying logic of the stored procedures used to maintain the db content dictate that I have to control the transactions myself.
Can you elaborate on that? I dont see a reason why you would commit around zopes transaction handling (which would actually be dangerous anyway - think of the retry mechanism in case of database conflicts)
I understand that there is a way to handle transaction controll in the ZMySQL methods. However I find no documentation at all on how to use it.
Well most databases accept "COMMIT" as sql command.
Without using ZMySQL methods I would be using the following piece of code: db = mysql.connect(host="localhost" ...) cursor = db.cursor() query = "CALL insertUser('%s', '$xxx$', @id, @error);select @id, @error" % 'JohnTheUser' cursor.execute(query) # get the next resultset, it has the result of the select cursor.nextset() result = cursor.fetchall()[0] error = int(result[1]) if error: print "error %s when trying to add user %s" % (ERRRORS[error], 'JohnTheUser') db.rollback() continue #we are in a loop actually
what does the loop?
.. db.commit() How can I mimic that using ZSQL Methods
Are you adding users in a series? What exactly are you doing? If its kind of migation, consider running it as a script with zopectl run migationscript.py once. There you could just write python code as you like w/o interfering w/ Zopes session handling. Regards Tino
Are you sure it doesn't do it out of the box? I know it does for ZPsycopgDA which is the Postgresq Zope database adapter. With that one I don't have to come near handling the SQL transactions. What Zope commits and rollsback is done for SQL too automatically. robert rottermann wrote:
Hi there,
I am implementing a tool to handle userdata that is stored in a MySQL db. The underlaying logic of the stored procedures used to maintain the db content dictate that I have to control the transactions myself. I understand that there is a way to handle transaction controll in the ZMySQL methods. However I find no documentation at all on how to use it.
Without using ZMySQL methods I would be using the following piece of code: db = mysql.connect(host="localhost" ...) cursor = db.cursor() query = "CALL insertUser('%s', '$xxx$', @id, @error);select @id, @error" % 'JohnTheUser' cursor.execute(query) # get the next resultset, it has the result of the select cursor.nextset() result = cursor.fetchall()[0] error = int(result[1]) if error: print "error %s when trying to add user %s" % (ERRRORS[error], 'JohnTheUser') db.rollback() continue #we are in a loop actually .. db.commit()
How can I mimic that using ZSQL Methods
thanks for any pointers Robert
_______________________________________________ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
-- Peter Bengtsson, work www.fry-it.com home www.peterbe.com hobby www.issuetrackerproduct.com
robert rottermann wrote at 2007-1-10 09:00 +0100:
... I am implementing a tool to handle userdata that is stored in a MySQL db. The underlaying logic of the stored procedures used to maintain the db content dictate that I have to control the transactions myself.
If you know what you do, then you can probably do something along the following lines. Almost surely, MySQL will provide some SQL command to control transaction. Maybe, they are called "commit" and "rollback" (as is the case for Postgres). In this case, you ZSQL method (or your stored procedure) can use it. In the ZSQL method, it could look like: --- some SQL --- <dtml-var sql_delimiter> commit -- Dieter
participants (4)
-
Dieter Maurer -
Peter Bengtsson -
robert rottermann -
Tino Wildenhain