Re: [Pypgsql-users] Sample source code needed

[Gerhard <ger...@gm...>]

* Michael Watkins <wa...@tr...> [2002-07-04 09:22 -0700]:
> And here is a simple example using the DBAPI wrapper.
> purge_age = 43200               # seconds  (43200 = 12 hours)
> query_string = 'delete from pbsp where extract(epoch from (now() - 
>                   since)) >= %d' % (purge_age,)
> [...]
> cursor.execute(query_string)

In this case it works to use Python quoting. But it's good for you [tm]
to get used to the DB-API style of quoting:

query_string = 'delete from pbsp where extract(epoch from (now() -
                since)) >= %s'
...
cursor.execute(query_tring, (purge_age,))

Otherwise, you'll get bitten as soon as you use strings, or data types,
which need special quoting. Especially _never_ commit this sin:

cursor.execute("select foo from bar where baz='%s' % "Tom's Farm")

The resulting SQL would be "select foo from bar where baz='Tom's Farm'"
which isn't valid SQL because the ' would need to be escaped.

The DB-API form of quoting does it right:
>>> from pyPgSQL import PgSQL
>>> db = PgSQL.connect()
>>> db.conn.toggleShowQuery
'On'
>>> cursor = db.cursor()
QUERY: BEGIN WORK
>>> cursor.execute("select foo from bar where baz=%s", "Tom's Farm")
QUERY: DECLARE PgSQL_0811067C CURSOR FOR select foo from bar where baz='Tom\'s Farm'

Note that the string gets automatically quoted and the single quote gets
automatically escaped, too.

Gerhard
-- 
mail:   gerhard <at> bigfoot <dot> de       registered Linux user #64239
web:    http://www.cs.fhm.edu/~ifw00065/    OpenPGP public key id AD24C930
public key fingerprint: 3FCC 8700 3012 0A9E B0C9  3667 814B 9CAA AD24 C930
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))