3

I have a table T with two columns: Tag (text) and Score (real). The Tags remain constant (and there can be several rows with the same Tag), while the Scores change often. The new values are presented in a python object like this (possibly with millions of items):

recs = [{'tag': t1, 'score': s1}, {'tag': t2, 'score': s2}]

I am currently using the following python code to update the scores in the table:

db_conn = psycopg2.connect(connect_string)
cursor = db_conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
q = "UPDATE T SET Score = %(score)s WHERE Tag = %(tag)s;"
cursor.executemany(q, recs)

While this works, it is quite slow. So, I am looking for a solution that improves the speed of the code above. Any recommendation is welcome.

happyhuman
  • 1,541
  • 1
  • 16
  • 30
  • You need to compile the "many statement" query first. – Torxed Dec 20 '16 at 19:21
  • How about "create index on T(tag)" and then "reindex table T" in psql? – stdunbar Dec 20 '16 at 19:21
  • 1
    Possible duplicate of [Inserting multiple rows using psycopg2](http://stackoverflow.com/questions/23321229/inserting-multiple-rows-using-psycopg2) – Torxed Dec 20 '16 at 19:22
  • 1
    Can you please provide your solution with some code? – happyhuman Dec 20 '16 at 19:26
  • @Torxed definitely not a duplicate. The link you provide talks about inserting, and he is asking about updating – Kalimantan Sep 21 '19 at 01:49
  • @Kalimantan Yea, but.. swap `INSERT` to `UPDATE` and the `psycopg2` functions are the same for both. It's called batched execution with prepared statements. You can even crate tables, update records, insert records, delete records with the same syntax. Hence, the possible duplicate. – Torxed Sep 21 '19 at 08:19

0 Answers0