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.

Improve this question
6
  • You need to compile the "many statement" query first. Commented Dec 20, 2016 at 19:21
  • How about "create index on T(tag)" and then "reindex table T" in psql? Commented Dec 20, 2016 at 19:21
  • 1
    Possible duplicate of Inserting multiple rows using psycopg2 Commented Dec 20, 2016 at 19:22
  • 1
    Can you please provide your solution with some code? Commented Dec 20, 2016 at 19:26
  • @Torxed definitely not a duplicate. The link you provide talks about inserting, and he is asking about updating Commented Sep 21, 2019 at 1:49
Related questions
3
Postgresql table update
0
Batch updating Updating over 200000 rows in a table using psycopg2
0
Update several hundred thousand rows in psql
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.