[Twisted-Python] problems w/ pyPgSQL and the Enterprise HOWTO
Hiya all, I'm playing around with the basics of Twisted.Enterprise, and am having some trouble. I seem to have fixed it, but am unsure why the example in the howto ( http://twistedmatrix.com/documents/howto/enterprise ) did not work off the bat. I'm using pyPgSQL to connect to postgres. Here goes: The howto suggests: <- snip -> class AgeDatabase(adbapi.Augmentation): def getAge(self, name): sql = """SELECT Age FROM People WHERE name = ?""" return self.runQuery(sql, name) <- snip -> Unfortunately, I get a TypeError which is only fixed by changing this to: <- snip -> class AgeDatabase(adbapi.Augmentation): def getAge(self, name): sql = """ SELECT "Age" FROM "People" WHERE "Name" = '%s'""" \ % name return self.runQuery(sql) <- snip -> FWIW, postgres requires you to enclose mixed-case relations in double-quotes (else it pretends they are all lowercase). I did have to make all of the modifications in this second version to the original query (whilst still including the ?, instead of %s) in order for the query to be valid. I don't think that this would change anything, but I suppose I could try it with all lowercase tables just for kicks.. Is this a problem with pyPgSQL? something else? pyPgSQL is the _recommended_ module in the enterprise howto, so I didn't expect to run into trouble with it.. Thanks in advance twisted.list! :) -Justin
On Sun, Feb 23, 2003 at 05:52:37PM -0600, Justin Ryan wrote:
Hiya all,
I'm playing around with the basics of Twisted.Enterprise, and am having some trouble. I seem to have fixed it, but am unsure why the example in the howto ( http://twistedmatrix.com/documents/howto/enterprise ) did not work off the bat. I'm using pyPgSQL to connect to postgres. Here goes:
The howto suggests:
<- snip ->
class AgeDatabase(adbapi.Augmentation): def getAge(self, name): sql = """SELECT Age FROM People WHERE name = ?""" return self.runQuery(sql, name)
<- snip ->
The howto also says: Also worth noting is that this example assumes that dbmodule uses the qmarks paramstyle (see the DB-API specification). This is the cause of the problem; pyPgSQL uses a different paramstyle. Maybe this should be made clearer, by inserting a # Assumes dbmodule.paramstyle == 'qmarks' into the example code, where people will read it.
Is this a problem with pyPgSQL? something else? pyPgSQL is the _recommended_ module in the enterprise howto, so I didn't expect to run into trouble with it..
Yeah, that is a good point. The howto should probably use examples that work with pyPgSQL, to save confusion. -Andrew.
The howto also says: Also worth noting is that this example assumes that dbmodule uses the qmarks paramstyle (see the DB-API specification).
This is the cause of the problem; pyPgSQL uses a different paramstyle.
Maybe this should be made clearer, by inserting a # Assumes dbmodule.paramstyle == 'qmarks' into the example code, where people will read it.
Perhaps, However not being familiar with the qmarks paramstyle, I noticed this bit but most likely skimmed over it because I didn't make the connection.. Learn something new every day.. Is the qmarks paramstyle a required part of the DB API 2.0 spec?
Is this a problem with pyPgSQL? something else? pyPgSQL is the _recommended_ module in the enterprise howto, so I didn't expect to run into trouble with it..
Yeah, that is a good point. The howto should probably use examples that work with pyPgSQL, to save confusion.
or at least distinguish. I assumed that the howto was using proper DB API syntax, and that pyPgSQL is not 100% compliant, but it would be nice to see the line drawn. I don't know that I've worked with a fully DB API compliant driver at this point ;p Thanks for the clarification... -Justin
On Sun, Feb 23, 2003 at 09:20:23PM -0600, Justin Ryan wrote:
The howto also says: Also worth noting is that this example assumes that dbmodule uses the qmarks paramstyle (see the DB-API specification).
This is the cause of the problem; pyPgSQL uses a different paramstyle.
Maybe this should be made clearer, by inserting a # Assumes dbmodule.paramstyle == 'qmarks' into the example code, where people will read it.
Perhaps, However not being familiar with the qmarks paramstyle, I noticed this bit but most likely skimmed over it because I didn't make the connection.. Learn something new every day..
Is the qmarks paramstyle a required part of the DB API 2.0 spec?
No, it's even worse than that: DB API 2.0 doesn't specify which paramstyle to use, it merely states that possible values are "qmark", "numeric", "named", "format", and "pyformat". So a DB API 2.0-compliant module is free to implement any of these it feels like, so long as it sets the correct paramstyle for it. Thus they've defined a spec which allows 5 incompatible flavours. As far as I can see, the only portable way to write queries is to format the queries yourself, which means handling correct quoting yourself. Yuck.
Is this a problem with pyPgSQL? something else? pyPgSQL is the _recommended_ module in the enterprise howto, so I didn't expect to run into trouble with it..
Yeah, that is a good point. The howto should probably use examples that work with pyPgSQL, to save confusion.
or at least distinguish. I assumed that the howto was using proper DB API syntax, and that pyPgSQL is not 100% compliant, but it would be nice to see the line drawn. I don't know that I've worked with a fully DB API compliant driver at this point ;p
Thanks for the clarification...
The howto is using proper DB API syntax. The problem is that being DB API 2.0 compliant doesn't help here; qmarks format strings are compliant -- but incompatible with pyformat format strings, which are also compliant. Twisted has nothing to do with this mess, and lets you and your db module sort it out between yourselves :) -Andrew.
yep, this really sucks. You cannot do parameterized SQL through pyPgSQL at all. Inside the driver, pyPgSQL just does a string subsitution for the query and submits the whole thing to the database. When you pass parameters to a SQL operation with pyPgSQL is has absolutely no effect on performance, so you might as well just build the entire SQL statement youself and pass it in as a single string. from PgSQL.py: def execute(self, query, *parms): . _qstr = query . self.res = self.conn.conn.query(_qstr % parms) Sean. -----Original Message----- From: twisted-python-admin@twistedmatrix.com [mailto:twisted-python-admin@twistedmatrix.com]On Behalf Of Andrew Bennetts Sent: Sunday, February 23, 2003 10:18 PM To: twisted-python@twistedmatrix.com Subject: Re: [Twisted-Python] problems w/ pyPgSQL and the Enterprise HOWTO On Sun, Feb 23, 2003 at 09:20:23PM -0600, Justin Ryan wrote:
The howto also says: Also worth noting is that this example assumes that dbmodule uses
the
qmarks paramstyle (see the DB-API specification).
This is the cause of the problem; pyPgSQL uses a different paramstyle.
Maybe this should be made clearer, by inserting a # Assumes dbmodule.paramstyle == 'qmarks' into the example code, where people will read it.
Perhaps, However not being familiar with the qmarks paramstyle, I noticed this bit but most likely skimmed over it because I didn't make the connection.. Learn something new every day..
Is the qmarks paramstyle a required part of the DB API 2.0 spec?
No, it's even worse than that: DB API 2.0 doesn't specify which paramstyle to use, it merely states that possible values are "qmark", "numeric", "named", "format", and "pyformat". So a DB API 2.0-compliant module is free to implement any of these it feels like, so long as it sets the correct paramstyle for it. Thus they've defined a spec which allows 5 incompatible flavours. As far as I can see, the only portable way to write queries is to format the queries yourself, which means handling correct quoting yourself. Yuck.
Is this a problem with pyPgSQL? something else? pyPgSQL is the _recommended_ module in the enterprise howto, so I didn't expect to run into trouble with it..
Yeah, that is a good point. The howto should probably use examples that work with pyPgSQL, to save confusion.
or at least distinguish. I assumed that the howto was using proper DB API syntax, and that pyPgSQL is not 100% compliant, but it would be nice to see the line drawn. I don't know that I've worked with a fully DB API compliant driver at this point ;p
Thanks for the clarification...
The howto is using proper DB API syntax. The problem is that being DB API 2.0 compliant doesn't help here; qmarks format strings are compliant -- but incompatible with pyformat format strings, which are also compliant. Twisted has nothing to do with this mess, and lets you and your db module sort it out between yourselves :) -Andrew. _______________________________________________ Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
Sean Riley <sean@twistedmatrix.com> wrote:
[...] When you pass parameters to a SQL operation with pyPgSQL is has absolutely no effect on performance, so you might as well just build the entire SQL statement youself and pass it in as a single string.
from PgSQL.py:
def execute(self, query, *parms): . _qstr = query . self.res = self.conn.conn.query(_qstr % parms)
That's because PostgreSQL started supporting prepared statements only very recently (in 7.3). I pondered adding support for them in pyPgSQL, but unfortunately to do so, I need to know the *PostgreSQL* type of the parameters in advance, which is not possible in the general case. Nothing's stopping you from using PREPARE and EXECUTE directly from pyPgSQL, though: cursor.execute(""" PREPARE my_query (int4, numeric) AS SELECT A, B, C FROM TABLENAME WHERE X=$1 AND Y=$2 """) then, later on call the prepared statement: cursor.execute("EXECUTE my_query (%s, %s)", (4, 5)) -- Gerhard
On Mon, Feb 24, 2003 at 03:18:02PM +1100, Andrew Bennetts wrote:
On Sun, Feb 23, 2003 at 09:20:23PM -0600, Justin Ryan wrote:
[snip]
Is the qmarks paramstyle a required part of the DB API 2.0 spec?
No, it's even worse than that: DB API 2.0 doesn't specify which paramstyle to use, it merely states that possible values are "qmark", "numeric", "named", "format", and "pyformat". So a DB API 2.0-compliant module is free to implement any of these it feels like, so long as it sets the correct paramstyle for it. Thus they've defined a spec which allows 5 incompatible flavours. As far as I can see, the only portable way to write queries is to format the queries yourself, which means handling correct quoting yourself. Yuck.
It occurs to me that this might be a useful place to apply localization, gettext style. ;) Now, if only there were an LC_SQL variable... Jp -- "Pascal is Pascal is Pascal is dog meat." -- M. Devine and P. Larson, Computer Science 340 -- up 15 days, 22:29, 4 users, load average: 0.03, 0.01, 0.00
I've been getting the following traceback in my log files... any ideas? I'm using a separate thread for a long-running-query... Traceback (most recent call last): File "/usr/local/lib/python2.2/site-packages/twisted/internet/default.py", line 121, in mainLoop File "/usr/local/lib/python2.2/site-packages/twisted/internet/base.py", line 343, in runUntilCurrent File "/usr/local/lib/python2.2/site-packages/twisted/internet/defer.py", line 162, in callback File "/usr/local/lib/python2.2/site-packages/twisted/internet/defer.py", line 213, in _startRunCallbacks --- <exception caught here> --- File "/usr/local/lib/python2.2/site-packages/twisted/internet/defer.py", line 227, in _runCallbacks File "server.py", line 13, in bad req.finish() File "/usr/local/lib/python2.2/site-packages/twisted/protocols/http.py", line 559, in finish exceptions.AttributeError: Request instance has no attribute 'channel'
On Tue, 25 Feb 2003 04:19:50 +0000 "Clark C. Evans" <cce@clarkevans.com> wrote:
I've been getting the following traceback in my log files... any ideas? I'm using a separate thread for a long-running-query...
File "server.py", line 13, in bad req.finish() File "/usr/local/lib/python2.2/site-packages/twisted/protocols/ht tp.py", line 559, in finish
exceptions.AttributeError: Request instance has no attribute 'channel'
You are calling request.finish() twice. Maybe your render method didn't return NOT_DONE_YET to indicate that it is not finishing immediately? -- Itamar Shtull-Trauring http://itamarst.org/ http://www.zoteca.com -- Python & Twisted consulting ***> Stop the war: http://unitedforpeace.org/ <***
On Tue, Feb 25, 2003 at 01:20:06PM -0500, Itamar Shtull-Trauring wrote: | You are calling request.finish() twice. Maybe your render method didn't | return NOT_DONE_YET to indicate that it is not finishing immediately? Quite right, thanks. I have another question, since my queries take some time the user can get bored and go backwards, click something else, or even close the browser altogether. Is there a way to detect this? It'd be nice to somehow cancel the current query executing, etc. I'm using PyPgSQL Best, Clark
The following patch seems to have solved the "symptom" below, but I wonder if I've just masked a problem. Clark --- http.py.orig Tue Feb 25 13:15:01 2003 +++ http.py Tue Feb 25 13:16:55 2003 @@ -556,8 +556,10 @@ self.write('') # log request - if hasattr(self.channel, "factory"): - self.channel.factory.log(self) + chan = getattr(self,"channel") + if chan: + fact = getattr(chan,"factory") + if fact: fact.log(self) self.finished = 1 if not self.queued: On Tue, Feb 25, 2003 at 04:19:50AM +0000, Clark C. Evans wrote: | I've been getting the following traceback in my log files... any ideas? | I'm using a separate thread for a long-running-query... | | Traceback (most recent call last): | File "/usr/local/lib/python2.2/site-packages/twisted/internet/default.py", | line 121, in mainLoop | | File "/usr/local/lib/python2.2/site-packages/twisted/internet/base.py", | line 343, in runUntilCurrent | | File | "/usr/local/lib/python2.2/site-packages/twisted/internet/defer.py", | line 162, in callback | | File | "/usr/local/lib/python2.2/site-packages/twisted/internet/defer.py", | line 213, in _startRunCallbacks | | --- <exception caught here> --- | File | "/usr/local/lib/python2.2/site-packages/twisted/internet/defer.py", | line 227, in _runCallbacks | | File "server.py", line 13, in bad | req.finish() | File | "/usr/local/lib/python2.2/site-packages/twisted/protocols/http.py", | line 559, in finish | | exceptions.AttributeError: Request instance has no | attribute 'channel' | | | | | _______________________________________________ | Twisted-Python mailing list | Twisted-Python@twistedmatrix.com | http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
participants (7)
-
Andrew Bennetts -
Clark C. Evans -
Gerhard Häring -
Itamar Shtull-Trauring -
Jp Calderone -
Justin Ryan -
Sean Riley