|
|
Subject: Re: problems w/ pyPgSQL and the Enterprise HOWTO - msg#00344
List: python.twisted
Sean Riley <sean@xxxxxxxxxxxxxxxxx> 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
Was this page helpful?
Thread at a glance:
Previous Message by Date:
click to view message preview
problem with long-running threads
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'
Next Message by Date:
click to view message preview
Re: SOCKS5 Client if anyone cares
On Mon, 24 Feb 2003 08:46:27 +0200
Dmitry Litovchenko <kwaker@xxxxxxx> wrote:
> Sorry no unit tests at this time, I have prepared SOCKS5 unit test at
> the very beginning but there were added several parameters so
> Protocol constructors don't start correctly. I will fix this
> eventually.
Basically ditch the regex, we have a command somewhere that does this,
don't remember wwhere (isIPAddress I think.) A quick visual scan of the
code seems ok - give us some tests and I'll check this in.
--
Itamar Shtull-Trauring http://itamarst.org/
http://www.zoteca.com -- Python & Twisted consulting
***> Stop the war: http://unitedforpeace.org/ <***
Previous Message by Thread:
click to view message preview
RE: problems w/ pyPgSQL and the Enterprise HOWTO
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@xxxxxxxxxxxxxxxxx
[mailto:twisted-python-admin@xxxxxxxxxxxxxxxxx]On Behalf Of Andrew
Bennetts
Sent: Sunday, February 23, 2003 10:18 PM
To: twisted-python@xxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxx
http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
Next Message by Thread:
click to view message preview
Re: problems w/ pyPgSQL and the Enterprise HOWTO
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
pgpKJzrbXm1tj.pgp
Description: PGP signature
|
|