[DB-SIG] http://www.python.org/topics/database/DatabaseAPI-2.0.html

[Gerhard Häring]

* Jekabs Andrushaitis <jekabs.andrusaitis@tietoenator.com> [2002-08-30 17:13 +0200]:
> If I understood the question here correctly - I am looking for answer to it
> too.
> I am using PostgreSQL however, but I have tables with fields whose
> default values are taken from sequence. Since I need to know the
> primary key value for record directly after insertion, I am fetching
> next value from sequence seperately, and only then inserting the row
> into table with primary key field value supplied.
> 
> Is there a way to easily fetch the field values for inserted row after
> insert statement execution?

I have exerimented with this, too and found two options, using pyPgSQL.
Here's one that is almost evil ;-) I'm assuming a table TEST, the table
definition doesn't really matter, as far as the primary id column is
called "id" and is the first column in the table definition. Here's the
server-side code.

create table test (
    id serial,
    name varchar(20),
    age int
);

create or replace function test_ins(varchar[]) returns integer as '
declare
  new_id integer;
  parms alias for $1;
  i int := 1;
  statement varchar(4000);
begin
  select nextval(\'test_id_seq\') into new_id;

  statement := \'insert into test values (\';
  statement := statement || to_char(new_id, ''9999999999999999999999'');
  while parms[i] loop
    statement := statement || '','' || parms[i];
    i := i + 1;
  end loop;
  statement := statement || '');'';

  execute statement;

  return new_id;
end;
' language 'plpgsql';

Now we need to call this PostgreSQL function from Python code.
Unfortunately, I had problems using cursor.callproc(), so for the
moment, I'm just using cursor.execute(). We need to pass /all/
parameters in the table definition, in the right order, quote them
appropriately so the server-side function can put them into the INSERT
statement, then wrap them up into a PostgreSQL VARCHAR array and send
them over:

    from pyPgSQL import PgSQL

    db = PgSQL.connect()
    cursor = db.cursor()
    cursor.execute("select test_ins(%s)", ([map(PgSQL._quote, ['Gerhard',
                                            25])]),)
    last_id = cursor.fetchone()[0]
    cursor.execute("select * from test where id=%s", (last_id,))
    print cursor.fetchone()
    db.commit()

I agree that this is not nice, but seems to work. Now for a less hackish
solution ;-) It's not completely finished, yet - I need to find a way to
properly implement getdb(), perhaps I'll build a context class or some
such:

from __future__ import nested_scopes

from pyPgSQL import PgSQL

db = PgSQL.connect()

def getdb():
    return db

def generic_insert(tablename, **kwargs):
    cursor = getdb().cursor()
    sql = "INSERT INTO %s(%s) VALUES" % \
        (tablename, ", ".join(kwargs.keys()))
    sql = sql + "(" + ",".join(["%s"] * len(kwargs) ) + ")"
    cursor.execute(sql, kwargs.values())

    cursor.execute("SELECT CURRVAL('%s_ID_SEQ')" % tablename)
    return cursor.fetchone()[0]

def generic_update(tablename, id, **kwargs):
    cursor = getdb().cursor()
    sets = ", ".join([k + "=%s" for k in kwargs.keys()])
    statement = "UPDATE %s SET %s WHERE ID=%%s" % \
        (tablename, sets)
    cursor.execute(statement, kwargs.values() + [id])

def generic_delete(tablename, id):
    cursor = getdb().cursor()
    statement = "DELETE FROM %s WHERE id=%%s" % tablename
    cursor.execute(statement, (id,))

def make_functions(tablename):
    globals()[tablename + "_INSERT"] = lambda **kwargs: generic_insert(tablename, **kwargs)
    globals()[tablename + "_UPDATE"] = lambda id, **kwargs: generic_update(tablename, id, **kwargs)
    globals()[tablename + "_DELETE"] = lambda id: generic_delete(tablename, id)
    
make_functions("TEST")

cursor = db.cursor()
cursor.execute("delete from test")
id = TEST_INSERT(name="fooz", age=26)
id = TEST_INSERT(name="foo", age=25)
TEST_UPDATE(id, name="bar")
TEST_DELETE(id)
cursor.execute("select * from test")
print cursor.fetchall()

HTH,

Gerhard
-- 
mail:   gerhard <at> bigfoot <dot> de       registered Linux user #64239
web:    http://www.cs.fhm.edu/~ifw00065/    OpenPGP public key id AD24C930
public key fingerprint: 3FCC 8700 3012 0A9E B0C9  3667 814B 9CAA AD24 C930
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))