[Tutor] Dynamic inserts into postgresql

[Danny Yoo]

On Sat, 6 Sep 2003, Peter Brown wrote:

> Dear Tutor,
>
> I feel like a dummy for asking this, but I have been working on it for 2
> days without resolution.
>
> I'm using pygresql module pg (not pgdb) to communicate to PostgreSQL


Hi Peter,

Out of curiosity, why not 'pgdb'?  The 'pgdb' module uses the standard
DB-API 2.0 described by:

    http://www.python.org/peps/pep-0249.html

and is the one that most Python programmers are familiar with.



> I can connect, open cursor, insert static data, select etc.
>
> But what I can't do is insert data that is not fixed ie using variables.
> eg statement = " INSERT INTO target VALUES (a, b)" ; res =
> db.query(statement)



I'm not sure how to do it with PostgreSQL's native interface, since I'm
more familiar with the DB API 2.0's.  Here's an example that uses 'pgdb'
and its prepared statement syntax:

###
>>> import pgdb
>>> conn = pgdb.connect(database='pub2')
>>> cursor = conn.cursor()
>>> cursor.execute("create table people (name varchar, email varchar)")
>>> conn.commit()
>>> cursor.execute("insert into people (name, email) values (%s, %s)",
...                 ("Danny", "dyoo"))
>>> conn.commit()
###


Please feel free to ask more questions about this.  Good luck!