[PATCH] modifies blends_prospective_gather.py: Update or Insert references in bibref table
---
udd/blends_prospective_gatherer.py | 81 ++++++++++++++++++++++++++++++++------
1 file changed, 68 insertions(+), 13 deletions(-)
diff --git a/udd/blends_prospective_gatherer.py b/udd/blends_prospective_gatherer.py
index f8cb64a..c66db80 100644
--- a/udd/blends_prospective_gatherer.py
+++ b/udd/blends_prospective_gatherer.py
@@ -18,6 +18,8 @@ from subprocess import Popen, PIPE
from debian import deb822
import email.Utils
+from collections import OrderedDict
+
from bibref_gatherer import upstream_reader
debug=0
@@ -49,6 +51,40 @@ def RowDictionaries(cursor):
result.append(resultrow)
return result
+def Upsert(rows, table, pk_fields, kwargs):
+ """Updates the specified relation with the key-value pairs in kwargs if a
+ a row matching the primary key value(s) already exists. Otherwise, a new row is inserted.
+
+ rows value is 1 or 0 (if row is to be updated: 1 else: 0)
+ table the table to use (not sanitized)
+ pk_fields tuple of field names which are part of the primary key
+ kwargs all key-value pairs to be set in the row
+ """
+
+ assert len(pk_fields) > 0
+ rel = table
+ fields = kwargs.keys()
+
+ # check if row is to be updated
+ if rows > 0:
+ where_args = [pk + '=' + kwargs[pk] for pk in pk_fields]
+ where = ' AND '.join(where_args)
+ set_clause = []
+ for f in fields:
+ if f not in pk_fields:
+ set_args = f + '=' + kwargs[f]
+ set_clause.append(set_args)
+ set_clause = ','.join(set_clause)
+ q = "UPDATE %s SET %s WHERE %s" %(rel, set_clause, where)
+
+ else:
+ fmt_args = ','.join(fields)
+ insert_args = [kwargs[f] for f in fields]
+ insert_args = ','.join(insert_args)
+ q = "INSERT INTO %s (%s) VALUES (%s)" %(rel, fmt_args, insert_args)
+
+ # print q
+ return q
class blends_prospective_gatherer(gatherer):
"""
@@ -415,19 +451,38 @@ class blends_prospective_gatherer(gatherer):
cur.execute("DEALLOCATE package_insert")
- # Inserting references should be save because above we are testing for existant table entries
- query = """PREPARE bibref_insert (text, text, text, text, int) AS INSERT INTO bibref
- (source, key, value, package, rank)
- VALUES ($1, $2, $3, $4, $5)"""
- cur.execute(query)
- bibquery = "EXECUTE bibref_insert (%(source)s, %(key)s, %(value)s, %(package)s, %(rank)s)"
- try:
- cur.executemany(bibquery, bibrefs)
- except ProgrammingError:
- print "Error while inserting references"
- raise
- cur.execute("DEALLOCATE bibref_insert")
-
+ # Upserting references: If record is not present then insert else update.
+ for ref in bibrefs:
+ cur.execute("PREPARE check_bibref_upsert (text, text, text, int) AS SELECT COUNT(*) FROM bibref WHERE source = $1 AND key = $2 AND package = $3 AND rank = $4 LIMIT 1")
+ check_upsert = "EXECUTE check_bibref_upsert (%(source)s, %(key)s, %(package)s, %(rank)s)"
+ cur.execute(check_upsert, ref)
+ rows = cur.fetchone()[0]
+ #if rows > 0:
+ # print "UPDATE: ",ref
+ #else:
+ # print "INSERT: ",ref
+
+ kwargs = OrderedDict()
+ kwargs['source'] = '$1'
+ kwargs['key'] = '$2'
+ kwargs['value'] = '$3'
+ kwargs['package'] = '$4'
+ kwargs['rank'] = '$5'
+ q = Upsert(rows, 'bibref', ('source', 'key', 'package','rank'), kwargs)
+ query = "PREPARE bibref_insert (text, text, text, text, int) AS " + q
+ #print "QUERY: ",query
+ cur.execute(query)
+ bibquery = "EXECUTE bibref_insert (%(source)s, %(key)s, %(value)s, %(package)s, %(rank)s)"
+
+ try:
+ cur.execute(bibquery, ref)
+ except ProgrammingError:
+ print "Error while inserting references"
+ raise
+
+ cur.execute("DEALLOCATE bibref_insert")
+ cur.execute("DEALLOCATE check_bibref_upsert")
+
cur.execute("ANALYZE %s" % my_config['table'])
if __name__ == '__main__':
--
1.9.1
Reply to: