Friday, May 7, 2010

PostgreSQL insert or update

What is the fastest way to do this for large numbers of entries? This is working for one entry... at least I thought it was until I tried it again.

#!/usr/bin/env python

import psycopg2
cx = psycopg2.connect("dbname='ais_test'")
cu = cx.cursor()

for i in range(5):
print i

cu.execute('''BEGIN;
SAVEPOINT sp1;
INSERT INTO vessel_name VALUES(12345678, 'RUST BUCKET1', 67);
ROLLBACK TO sp1;
UPDATE vessel_name SET name='RUST BUCKET', type_and_cargo=66 WHERE mmsi = 123456789;
COMMIT;''')

cx.commit()


This is based on http://www.postgresql.org/docs/8.3/interactive/sql-update.html