Thread: [Pypgsql-users] Sample source code needed

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

RG9lcyBhbnlvbmUgaGF2ZSBhIHNhbXBsZSBzb3VyY2UgY29kZSBvbiBob3cgdG8gdXNlIHB5UGdT
UUwgPyBQbGVhc2Ugc2VuZCBtZSBpZiB5b3UgaGF2ZSAhDQogDQpSaXZhbGRvDQo=

* Ricardo Rivaldo <ri...@tr...> [2002-07-04 00:53 -0300]:
> Does anyone have a sample source code on how to use pyPgSQL? Please
> send me if you have!

Unfortunately, the code I have is a little hard to understand, or using
features that aren't even in CVS, yet [1].

All I can currently offer are the examples/ directory of the pyPgSQL
source distribution. Also, this entry of our FAQ might help:
http://pypgsql.sourceforge.net/pypgsql-faq.html#id2713923

I know the current state is suboptimal, and writing a manual has been on
my TODO list for a while.

If you're stuck, just ask here.

Gerhard

[1] I have started a little PostgreSQL admin utility in pyPgSQL/wxPython
just for fun.
-- 
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')))

On Wednesday, July 3, 2002, at 08:53  PM, Ricardo Rivaldo wrote:

> Does anyone have a sample source code on how to use pyPgSQL ? Please send 
> me if you have !
>
> Rivaldo
>

i import it, but i use the lowlevel libpq, not the higher level wrapper:

from pyPgSQL import libpq

db = libpq.PQconnectdb('dbname=shopip')
		qry = "SELECT * from %s" % tbl_name
qrslt = db.query(qry)
for x in range(qrslt.ntuples):
	for y in range(qrslt.nfields):
		data_val = qrslt.getvalue(x,y)

you could of course look at documentation? in Python library docs?

hth

pob

And here is a simple example using the DBAPI wrapper.

#!/usr/local/bin/python
# run via cron
# purge pop before smtp records past an expiry time from virtual.pbsp
# send basic statistics to root

from pyPgSQL import PgSQL

## delete aged records

purge_age = 43200               # seconds  (43200 = 12 hours)
query_string = 'delete from pbsp where extract(epoch from (now() - 
since)) >= %d' % (purge_age,)
dsn = 'localhost::virtual'      #dsn = 
'host:port:database:username:password' some can be blank

db =  PgSQL.connect(dsn)
cursor = db.cursor()
cursor.execute(query_string)
db.commit()

## spit out a simple report

query_string =  """select mailbox_idnr as id, count(*) as msg_count, 
sum(messagesize) as msg_size,
     userid from messages, users where user_idnr = mailbox_idnr
     group by mailbox_idnr, users.userid"""

cursor.execute(query_string)
result = cursor.fetchall()
print "System email statistics\n"
print "%-5s %5s %10s %s" % ('ID','Count','Size/K', 'Account')
for row in result:
         print "%-5s %5s %10s %s" % (row.id, row.msg_count, 
int(row.msg_size) / 1000, row.userid)

At 09:10 AM 7/4/2002 -0700, bob ackerman wrote:

>On Wednesday, July 3, 2002, at 08:53  PM, Ricardo Rivaldo wrote:
>
>>Does anyone have a sample source code on how to use pyPgSQL ? Please send 
>>me if you have !
>>
>>Rivaldo
>
>i import it, but i use the lowlevel libpq, not the higher level wrapper:
>
>from pyPgSQL import libpq
>
>db = libpq.PQconnectdb('dbname=shopip')
>                 qry = "SELECT * from %s" % tbl_name
>qrslt = db.query(qry)
>for x in range(qrslt.ntuples):
>         for y in range(qrslt.nfields):
>                 data_val = qrslt.getvalue(x,y)
>
>you could of course look at documentation? in Python library docs?
>
>hth
>
>pob
>
>
>
>-------------------------------------------------------
>This sf.net email is sponsored by:ThinkGeek
>Caffeinated soap. No kidding.
>http://thinkgeek.com/sf
>_______________________________________________
>Pypgsql-users mailing list
>Pyp...@li...
>https://lists.sourceforge.net/lists/listinfo/pypgsql-users
>
>

* 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')))

Yes, absolutely. I wrote a content management system using PyPgSQL and came 
to love the DB-API style. Makes life easy. And safe.

Apparently I've forgotten that for the quick reporting hack example I 
provided !

At 09:01 PM 7/4/2002 +0200, Gerhard =?iso-8859-15?Q?H=E4ring?= wrote:
>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,))