How to access a PostgreSQL database?

[Auke Jilderda]

Hi,

question on accessing databases:

I'm trying to build a script [1] to archive messages from a mailinglist 
into a GForge [2] server's web forums.  Effectively, this provides us 
with a central means of communication that can be accessed via web, 
mail, and news.  GForge uses mailman and postgresql.

It seemed a good idea to use the DB-API spec v2.0 with the PyGreSQL 
library but I can't seem to find any decent documentation or examples 
and am starting to run out of patience with the 'trial on error' 
approach.  Hopefully somebody can point me in the right direction. :-)

A couple of questions:
- What is the most elegant way to connect to a database?  I had expected
   by instantiating a db class which has a 'connect' method but can't
   find it.  Currently, this is how I do it (which works):

     # connect to database
     connection = pgdb.connect(host = 'localhost', user = 'postgres', \
                     database = 'alexandria')
     cursor = connection.cursor()
     ...
     # disconnect
     connection.close()

- How on Earth can I insert a tuple into a table with the proper types?
   I tried 'cursor.execute()' but keep running into typing issues.  (At
  the bottom of this post, I included the table's layout.

Any help is appreciated,


Auke

  1.  See attachment, bear in mind there are some ugly shortcuts in
      there.
  2.  http://gforge.org


The table 'forum' looks as follows (cut & paste from 'psql'):

alexandria=# \d forum
                             Table "public.forum"
       Column      |  Type   |                   Modifiers
------------------+---------+------------------------------------------------
  msg_id           | integer | not null default 
nextval('forum_pk_seq'::text)
  group_forum_id   | integer | not null default '0'
  posted_by        | integer | not null default '0'
  subject          | text    | not null default ''
  body             | text    | not null default ''
  date             | integer | not null default '0'
  is_followup_to   | integer | not null default '0'
  thread_id        | integer | not null default '0'
  has_followups    | integer | default '0'
  most_recent_date | integer | not null default '0'
Indexes: forum_pkey primary key btree (msg_id),
          forum_forumid_isfollto_mostrece btree (group_forum_id, 
is_followup_to, most_recent_date),
          forum_forumid_msgid btree (group_forum_id, msg_id),
          forum_forumid_threadid_mostrece btree (group_forum_id, 
thread_id, most_recent_date),
          forum_group_forum_id btree (group_forum_id),
          forum_threadid_isfollowupto btree (thread_id, is_followup_to)
Rules: forum_delete_agg,
        forum_insert_agg
Triggers: RI_ConstraintTrigger_18833,
           RI_ConstraintTrigger_18836,
           RI_ConstraintTrigger_18842,
           RI_ConstraintTrigger_18845,
           RI_ConstraintTrigger_18972,
           RI_ConstraintTrigger_18975,
           fmsg_agg_trig

alexandria=#
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: aukes-archiver.py
URL: <http://mail.python.org/pipermail/python-list/attachments/20030810/acf6da8f/attachment.ksh>