SQL iterate UPDATE from python list

Do you guys know how to UPDATE a database from a python list without using cur.executemany? Using cur.executemany I have problems in API calls or something. What is another way to solve this below? Thank you!

import psycopg2 
conn = psycopg2.connect(DATABASE_URL) # Postgres
cur = conn.cursor()

list_account = [('Bob', 55), ('John', 10)]
cmd_type = ("""
  UPDATE db.table
  SET name = %s
  WHERE age = %s""")
cur = conn.cursor()
cur.executemany(cmd_type, list_people)

I know below is crazy ugly, but I did that.

for i in range(len(list_account)):
  cmd_type = ("""
    UPDATE db.table
    SET name = '{}'
    WHERE age = '{}'""".format(list_account[i][0], list_account[i][1]))

  cur = conn.cursor()
  cur.execute(cmd_type) 
  conn.commit()

Answer

Not an answer but I don’t want to edit your code as I don’t have Postgres installed yet.

It seems like with the library you use there is no optimization by using executemany(). So can you try the following code, which is just and transalte from what I usually do. I made comments in capitals as a few things do not make necessarly sense (but possible)

import psycopg2
DATABASE_URL='db'

conn = psycopg2.connect(DATABASE_URL) # Postgres
#ARE YOU SURE YOU DON'T NEED USERNAME, PASSWORD AND PORT?
cur = conn.cursor()

list_account = [('Bob', 55), ('John', 10)]

#IN YOU UPDATE STATEMENT YOU CALL THE DATABASE NAME: "db", AND THE TABLE 'table'
#ARE YOU SURE ABOUT THIS?
cmd_type = ("""
  UPDATE db.table
  SET name = %s
  WHERE age = %s""")
cur = conn.cursor()
for params in list_account:
    cur.execute(cmd_type, params)
    conn.commit()

using doc link

does this work?

if it doesn’t try this:

import psycopg2
DATABASE_URL='db'

conn = psycopg2.connect(DATABASE_URL) # Postgres
#ARE YOU SURE YOU DON'T NEED USERNAME, PASSWORD AND PORT?
cur = conn.cursor()

list_account = [('Bob', 55), ('John', 10)]

#IN YOU UPDATE STATEMENT YOU CALL THE DATABASE NAME: "db", AND THE TABLE 'table'
#ARE YOU SURE ABOUT THIS?
cmd_type = ("""
  UPDATE `table`
  SET name = %s
  WHERE age = %s""")
cur = conn.cursor()
for params in list_account:
    cur.execute(cmd_type, params)
    conn.commit()