[Q] PyGreSQL

[Gerhard Häring]

On Sat, Sep 29, 2001 at 09:24:58PM -0700, Seung-won Hwang wrote:
> Hi,
> 
> I have two questions on PyGreSQL:
> 
> (1) Could you let me know where the windows version of PyGreSQL is? I failed
> in finding one from web search.

On my homepage you can find one (see sig). I'd recommend you use my
Windows port of pyPgSQL instead, though. The win32 version is now
available from the official homepage
(http://www.sourceforge.net/projects/pypgsql/) and  I'm also a developer
in this project, so think of this as the officially supported one :-))

> (2) What's the easiest way to load tab-separated text data to the relational
> database? Any easier way than writing a program that calls "INSERT INTO" for
> every data value?

AFAIK, all the easy ways involve proprietary tools. Below I've put a
draft of a function that might do what you want. It's not perfect, and I
don't know how to handle things like auto-increment columns.

Gerhard

#############################################################################
#!/usr/bin/env python
import PgSQL

def importTable(cursor, tablename, filename):
    """Imports data from filename into the table tablename. Each line in the
    data file is one record. The fields are tab-seperated."""
    # Build a list of lists; the inner list contains the columns to be inserted
    f = open(filename, "r")
    entries = []
    for line in f.readlines():
	if line.strip() != "":
	    entries.append(line.strip().split("\t"))
    f.close()
    numcolumns = len(entries[0])
    # Build the SQL statement; using %s for any column type is pyPgSQL specific
    sql = "insert into %s values " % tablename
    sql += "(" + ", ".join(["%s"] * numcolumns) + ")"
    cursor.executemany(sql, entries)
    
db = PgSQL.connect()
cursor = db.cursor()
importTable(cursor, "test", "testdata.txt")
db.commit()
-- 
mail:   gerhard <at> bigfoot <dot> de       registered Linux user #64239
web:    http://www.cs.fhm.edu/~ifw00065/    OpenPGP public key id 86AB43C0
public key fingerprint: DEC1 1D02 5743 1159 CD20  A4B6 7B22 6575 86AB 43C0
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))