[Twisted-Python] Strange error: SQL-Server tries to rollback
I have a simple INSERT INTO statement sent to SQL-Server using pymssql module. I use the runOperation method to send the query and I get this strange error. The data goes through OK and is written in the database but SQL-Server tries to rollback. Perhaps this is a pymssql problem. In the http://code.google.com/p/pymssql/wiki/PymssqlExamples page, they state: import pymssql conn = pymssql.connect(host='SQL01', user='user', password='password', database='mydatabase') cur = conn.cursor() cur.execute('CREATE TABLE persons(id INT, name VARCHAR(100))') cur.executemany("INSERT INTO persons VALUES(%d, %s)", \ [ (1, 'John Doe'), (2, 'Jane Doe') ]) conn.commit() # you must call commit() to persist your data if you don't set autocommit to True But how can I use the con.commit method with runOperation and runQuery that I use in my application? thank you, Pandelis Theodosiou Here is the error: Traceback (most recent call last): File "/usr/lib/python2.6/threading.py", line 484, in run self.__target(*self.__args, **self.__kwargs) File "/usr/lib/python2.6/dist-packages/twisted/python/threadpool.py", line 210, in _worker result = context.call(ctx, function, *args, **kwargs) File "/usr/lib/python2.6/dist-packages/twisted/python/context.py", line 59, in callWithContext return self.currentContext().callWithContext(ctx, func, *args, **kw) File "/usr/lib/python2.6/dist-packages/twisted/python/context.py", line 37, in callWithContext return func(*args,**kw) --- <exception caught here> --- File "/usr/lib/python2.6/dist-packages/twisted/enterprise/adbapi.py", line 436, in _runInteraction conn.rollback() File "/usr/lib/python2.6/dist-packages/twisted/enterprise/adbapi.py", line 52, in rollback self._connection.rollback() File "/usr/lib/pymodules/python2.6/pymssql.py", line 496, in rollback raise OperationalError, "cannot roll back transaction: " + e[0] pymssql.OperationalError: cannot roll back transaction: SQL Server message 3903, severity 16, state 1, line 1: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. DB-Lib error message 3903, severity 16: General SQL Server error: Check messages from the SQL Server
On 9/10/2010 11:17 AM, Pantelis Theodosiou wrote:
I don't see any of the Twisted code you're trying; I only see a traceback and it doesn't really give enough information. Any chance you could give us a short, self-contained example which demonstrates the problem? (http://sscce.org/) - Paul Goins
Sorry, one more thing.
But how can I use the con.commit method with runOperation and runQuery that I use in my application?
I'm pretty sure ConnectionPools should take care of the commit for you, provided there was no error. If there was an error, then it attempts a rollback. This should be true for runQuery, runOperation and runInteraction. I think the one exception is runWithConnection which wraps and gives access to a (lightly wrapped) connection object, but I think most people don't really need this. Hope this helps. Again, please give an example of the problem if you can. - Paul Goins
On Sep 10, 2010, at 5:17 AM, Pantelis Theodosiou wrote:
I have no experience with MS SQL, only with PostgreSQL via psycopg2, but the idea is: you want to execute many statements, probably in a blocking fashion: runInteraction() you want to execute just one statement: runQuery()/runOperation() AFAIK the latter one will do a commit for you if it is needed. Anyway, for PostgreSQL I can specify isolation level I need.
I have no experience with MS SQL, only with PostgreSQL via psycopg2, but the
thnx to all. I used runOperation with the INSERTs and it was solved (or i think that was the cause). The part of code with the problem was: ----------------- from twisted.enterprise import adbapi import pymssql class DBAccess: def __init__(self): # SQL-Server myhost = "64.x.x.x:4000" mydatabase = "db" myuser = "user" mypassword = "password" try: self.dbpool = adbapi.ConnectionPool("pymssql" , host=myhost , user=myuser , password=mypassword , database=mydatabase , cp_noisy=False) print("db connection pool created") except Exception as e: print("error starting the ConnectionPool") print(e) def insertDATA(self , serialNumber , customerAccount , dateAndTime , camcount , fpv , reff , restOfFrame ): sql = ''.join( [ "INSERT INTO fts_data VALUES (" , "'" , serialNumber , "'" , "," , "'" , customerAccount , "'" , "," , "'" , dateAndTime , "'" , "," , camcount , "," , fpv , "," , "'" , reff , "'" , "," , "'" , restOfFrame , "'" , ") " ] ) #print(sql) try: deferred = self.dbpool.runOperation(sql) #print("DATA sent") except Exception as e: print("error in insertDATA") print(e) return return deferred ------------------ Anyway, I'm now stuck at other levels, and I'm kind of out of ideas and the customer wants (or has to) finish the whole project in a very, very short notice. Like yesterday. And my experience in Python and Twisted is not at the level to solve problems and fix bugs quickly. Since the program is written using Twisted, if anyone likes to step over, contact me directly and i'll provide details. Pandelis Theodosiou
Thanks for the code example. I can't offer to take this work over, but maybe I can make a comment. There's one part that stands out to me.
It seems like maybe you have a misunderstanding about how Deferreds generally work in Twisted. (Been there myself.) Basically, the above try/except block won't work to catch errors from most Deferreds... well, at least not without some extra magic. You really should read the Deferred section of the Twisted documentation to understand how errors are handled. Docs are here: http://twistedmatrix.com/documents/current/core/howto/defer.html ---- Basically, to "fix" the above code's error catching, you have two choices: 1. You can add an errback to the deferred. This is the "standard" Twisted way, and would replace the try/except block entirely. 2. You can use the @inlineCallbacks decorator (from twisted.internet.defer), and yield on the Deferred. This is easier, and it allows try/except blocks, but there's some gotchas. inlineCallbacks is what I used when I was learning Twisted, and you may want to try that for now. But please understand that it hides details about how Deferreds and callbacks really work. When you find time, read the Deferred docs. ---- Best of luck, - Paul Goins
On Fri, Sep 10, 2010 at 06:54:34PM +0300, Pantelis Theodosiou wrote:
This is unrelated to your Twisted problem, but *please* tell me you are not building SQL like this in production code. If you need a reminder, the best possible way to do this in Python[1] is: sqlPattern = """ INSERT INTO fts_data VALUES ( %s, %s, %s, %s, %s, %s, %s ); """ # if you're using a DB-API module directly: cursor.execute(sqlPattern, [serialNumber, customerAccount, dateAndTime, camcount, fpv, reff, restOfFrame]) # if you're using ADBAPI: deferred = pool.runOperation(sqlPattern, [serialNumber, customerAccount, dateAndTime, camcount, fpv, reff, restOfFrame]) Note that there's no "%" formatting operator between sqlPattern and the list of values to be substituted in; the substitution is done with all the proper quoting and escaping, not with Python's naïve formatting operator. Apologies if you already knew this, but apart from correctness it's one of the few security issues where doing things the safe way is actually easier than doing it the unsafe way - for a start, the SQL is all together in one string. :) [1]: The examples here use what DB-API calls the "format" quoting style. Not all DB-API modules use it, but pymssql and psycopg2 do.
On 9/10/2010 11:17 AM, Pantelis Theodosiou wrote:
I don't see any of the Twisted code you're trying; I only see a traceback and it doesn't really give enough information. Any chance you could give us a short, self-contained example which demonstrates the problem? (http://sscce.org/) - Paul Goins
Sorry, one more thing.
But how can I use the con.commit method with runOperation and runQuery that I use in my application?
I'm pretty sure ConnectionPools should take care of the commit for you, provided there was no error. If there was an error, then it attempts a rollback. This should be true for runQuery, runOperation and runInteraction. I think the one exception is runWithConnection which wraps and gives access to a (lightly wrapped) connection object, but I think most people don't really need this. Hope this helps. Again, please give an example of the problem if you can. - Paul Goins
On Sep 10, 2010, at 5:17 AM, Pantelis Theodosiou wrote:
I have no experience with MS SQL, only with PostgreSQL via psycopg2, but the idea is: you want to execute many statements, probably in a blocking fashion: runInteraction() you want to execute just one statement: runQuery()/runOperation() AFAIK the latter one will do a commit for you if it is needed. Anyway, for PostgreSQL I can specify isolation level I need.
I have no experience with MS SQL, only with PostgreSQL via psycopg2, but the
thnx to all. I used runOperation with the INSERTs and it was solved (or i think that was the cause). The part of code with the problem was: ----------------- from twisted.enterprise import adbapi import pymssql class DBAccess: def __init__(self): # SQL-Server myhost = "64.x.x.x:4000" mydatabase = "db" myuser = "user" mypassword = "password" try: self.dbpool = adbapi.ConnectionPool("pymssql" , host=myhost , user=myuser , password=mypassword , database=mydatabase , cp_noisy=False) print("db connection pool created") except Exception as e: print("error starting the ConnectionPool") print(e) def insertDATA(self , serialNumber , customerAccount , dateAndTime , camcount , fpv , reff , restOfFrame ): sql = ''.join( [ "INSERT INTO fts_data VALUES (" , "'" , serialNumber , "'" , "," , "'" , customerAccount , "'" , "," , "'" , dateAndTime , "'" , "," , camcount , "," , fpv , "," , "'" , reff , "'" , "," , "'" , restOfFrame , "'" , ") " ] ) #print(sql) try: deferred = self.dbpool.runOperation(sql) #print("DATA sent") except Exception as e: print("error in insertDATA") print(e) return return deferred ------------------ Anyway, I'm now stuck at other levels, and I'm kind of out of ideas and the customer wants (or has to) finish the whole project in a very, very short notice. Like yesterday. And my experience in Python and Twisted is not at the level to solve problems and fix bugs quickly. Since the program is written using Twisted, if anyone likes to step over, contact me directly and i'll provide details. Pandelis Theodosiou
Thanks for the code example. I can't offer to take this work over, but maybe I can make a comment. There's one part that stands out to me.
It seems like maybe you have a misunderstanding about how Deferreds generally work in Twisted. (Been there myself.) Basically, the above try/except block won't work to catch errors from most Deferreds... well, at least not without some extra magic. You really should read the Deferred section of the Twisted documentation to understand how errors are handled. Docs are here: http://twistedmatrix.com/documents/current/core/howto/defer.html ---- Basically, to "fix" the above code's error catching, you have two choices: 1. You can add an errback to the deferred. This is the "standard" Twisted way, and would replace the try/except block entirely. 2. You can use the @inlineCallbacks decorator (from twisted.internet.defer), and yield on the Deferred. This is easier, and it allows try/except blocks, but there's some gotchas. inlineCallbacks is what I used when I was learning Twisted, and you may want to try that for now. But please understand that it hides details about how Deferreds and callbacks really work. When you find time, read the Deferred docs. ---- Best of luck, - Paul Goins
On Fri, Sep 10, 2010 at 06:54:34PM +0300, Pantelis Theodosiou wrote:
This is unrelated to your Twisted problem, but *please* tell me you are not building SQL like this in production code. If you need a reminder, the best possible way to do this in Python[1] is: sqlPattern = """ INSERT INTO fts_data VALUES ( %s, %s, %s, %s, %s, %s, %s ); """ # if you're using a DB-API module directly: cursor.execute(sqlPattern, [serialNumber, customerAccount, dateAndTime, camcount, fpv, reff, restOfFrame]) # if you're using ADBAPI: deferred = pool.runOperation(sqlPattern, [serialNumber, customerAccount, dateAndTime, camcount, fpv, reff, restOfFrame]) Note that there's no "%" formatting operator between sqlPattern and the list of values to be substituted in; the substitution is done with all the proper quoting and escaping, not with Python's naïve formatting operator. Apologies if you already knew this, but apart from correctness it's one of the few security issues where doing things the safe way is actually easier than doing it the unsafe way - for a start, the SQL is all together in one string. :) [1]: The examples here use what DB-API calls the "format" quoting style. Not all DB-API modules use it, but pymssql and psycopg2 do.
participants (4)
-
Pantelis Theodosiou -
Paul Goins -
Tim Allen -
Yaroslav Fedevych