psycopg simplest problem

[Gerhard Haering]

On Fri, Jul 08, 2005 at 04:23:50PM +0200, Glauco wrote:
> [...]
> My problem is to do a middle layer over pycopg for eliminate type 
> casting problem in postgres in all direction.
> 
> i've resolved this doing a C extension in python and manipulating only 
> string and int in my application.
> 
> this is my example:
> import sqlvar
> 
> sql = """insert into mytable (myint, mytext, maydate)
>           values
>           (%d,%s,%s);""" % (sqlvar.number(myvalue1), 
> sqlvar.text(myvalue2), sqlvar.date(myvalue3) )
> 
> all problem concerning quoting, " ' -> ''", null, None, 0, empty string 
> is solved by the sqlvar lib. [...]

Instead of quoting Python values yourself appropriately for each type,
just let the DB-API module do its work. Use parametrized queries and
then supply the arguments to the query:

cur = con.cursor()
intVal = 42
textVal = "Jason's house"
dateVal = datetime.date(2005, 7, 8)
cur.execute("""
    insert into mytable(myint, mytext, mydate)
    values (%s, %s, %s)
    """, (intval, textVal, dateVal))

The execute(many) method has an optional second parameter, which is a
tuple of values for your parametrized query.

There are different styles to parametrize queries among the various
DB-API modules. psycopg uses the pyformat one, where you just use %s as
placeholders.

It will happily quote all Python values of types int, str, date, float,
etc.  for you. It's also possible to teach it how to quote other custom
data types, but you'll normally not need this.

HTH,

-- Gerhard
-- 
Gerhard Häring - gh at ghaering.de - Python, web & database development
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 196 bytes
Desc: Digital signature
URL: <http://mail.python.org/pipermail/python-list/attachments/20050708/9d282662/attachment.sig>