python-postgres-informix interaction problems

[Chris Gonnerman]

----- Original Message -----
From: "J Dubal" <dubal at khimjiramdas.com>


> Hello good people!
>
> We have developed Informix RDBMS based commercial applications in
> Informix-4gl. It is a good procedural language with embeded SQL. We
> are trying to rewrite some apps in Python with postgresql backend for
> licensing reasons. Python is good too. We are trying to do this with
> latest versions of RH linux (7.2), python 2.1 and postgres 7.1.3. We
> face following issues:
>
> 1. We have to read data from informix db, process it and insert into
> postgres. We are using informixdb module (DBAPI 1) which is bit dated.
> In Informix we 'select custno, custname, custaddr1, ... into progvar1,
> progvar2, ... from custmf where ...' In python, we say fetchone() and
> we get a list. We are then moving the elements of the list to named
> program variables thus:
>         pcustno = list1[0]
>         pcustname = list1[1]
>         pcustaddr1 = list1[2]
>         ... as many as reqd
> This way we give meaningful names to data items read from db and
> process further.
> Is there a better, more elegant/efficient way to to this?

No comment here, as I know little about the informixdb module.

> 2. To insert this data into postgres, we used to use pygres included
> in the postgres distribution (latest). We had to stop using this
> because we couldn't find a way to find out how many rows it have
> inserted, updated or deleted after such statements are executed ie we
> couldn't find a way to check the status or return code. So we switched
> over to PoPy which is DBAPI2 and where we could check number of rows
> affected. Is this right thing to do?

Absolutely (IMHO).  If it works for you, it's probably right.

> 3. In order to insert data into postgres, we have to compose an insert
> statement thus:
>         cur.execute("insert into custmf (custno, custname,
> custaddr1,...)
>             values (%i,'%s','%s'...)" %(pcustno,pcustname,
> pcustaddr1....))
> Is there a better way of doing this?

Yes!  You say that PoPy is DBAPI2; the cursor.execute() method supports
formatting in some way or another.  Read the DBAPI2 and PoPy documents.
In MySQLdb, you can do this (note the triple-quotes, you should be using
them also IMHO):

    cur.execute("""
        insert into custmf (custno, custname, custaddr1)
        values (%s,%s,%s)
    """, (pcustno,pcustname, pcustaddr1))

Note that comma!  You put a %s placeholder with no quoting whereever you
need a substitution and let the interface do the work!

As I said, read the PoPy docs as DBAPI2 allows several different ways to
do this autoquoting, and you need to know which one(s) it supports.

> 4. The above string substitute converts program variables holding null
> value into 'None' which goes into target table as string 'None'. To
> avoid this we parse all columns to check if they are 'None' and
> replace them by ''. Is there a better way to do this?

Ouch.  Try the parameter substitution method above and see what it does.
DB API 2.0 does not say how NULLs should be handled.

> 5. The above check works only on string variables. We have not found
> yet a way to handle nulls in numeric columns. Please suggest a
> solution.

Ouch ouch.

> 6. If the character columns happen to contain quotes, the above
> composed insert statement breaks. Please suggest a solution.

Here the parameter substitution should work OK; quoting of quotes should
be supported automatically if you let the interface handle the substitution.

> Any help is greatly appreciated.
> Thanks in advance.

Hope this helps.