Re: [Twisted-Python] Strange error: SQL-Server tries to rollback
On Fri, Sep 10, 2010 at 06:54:34PM +0300, Pantelis Theodosiou wrote:
> sql = ''.join(
> [
> "INSERT INTO fts_data VALUES ("
> , "'" , serialNumber , "'"
> , "," , "'" , customerAccount , "'"
> , "," , "'" , dateAndTime , "'"
> , "," , camcount
> , "," , fpv
> , "," , "'" , reff , "'"
> , "," , "'" , restOfFrame , "'"
> , ") "
> ] )
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.
___
Twisted-Python mailing list
Twisted-Python@twistedmatrix.com
http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
Re: [Twisted-Python] Strange error: SQL-Server tries to rollback
On Fri, Sep 10, 2010 at 7:39 PM, Paul Goins wrote:
> 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.
>
> > try:
> > deferred = self.dbpool.runOperation(sql)
> > #print("DATA sent")
> > except Exception as e:
> > print("error in insertDATA")
> > print(e)
> > return
> > return deferred
>
> 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.
>
That is correct. I have only basic understanding of Deferreds and none about
the Errbacks. I realized from various errors that this exception was raised
when errors appeared in other parts of the code.
>
> 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
>
> ___
> Twisted-Python mailing list
> Twisted-Python@twistedmatrix.com
> http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
>
___
Twisted-Python mailing list
Twisted-Python@twistedmatrix.com
http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
Re: [Twisted-Python] Strange error: SQL-Server tries to rollback
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.
> try:
> deferred = self.dbpool.runOperation(sql)
> #print("DATA sent")
> except Exception as e:
> print("error in insertDATA")
> print(e)
> return
> return deferred
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
___
Twisted-Python mailing list
Twisted-Python@twistedmatrix.com
http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
Re: [Twisted-Python] Strange error: SQL-Server tries to rollback
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.
>
> ___
> Twisted-Python mailing list
> Twisted-Python@twistedmatrix.com
> http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
>
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
___
Twisted-Python mailing list
Twisted-Python@twistedmatrix.com
http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
Re: [Twisted-Python] Strange error: SQL-Server tries to rollback
On Sep 10, 2010, at 5:17 AM, Pantelis Theodosiou wrote:
> 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
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.___
Twisted-Python mailing list
Twisted-Python@twistedmatrix.com
http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
Re: [Twisted-Python] Strange error: SQL-Server tries to rollback
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 ___ Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
Re: [Twisted-Python] Strange error: SQL-Server tries to rollback
On 9/10/2010 11:17 AM, Pantelis Theodosiou wrote: > 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. 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 ___ Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
[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)
--- ---
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
___
Twisted-Python mailing list
Twisted-Python@twistedmatrix.com
http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
