SQL iterate UPDATE from python list

Question

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()

you can do execute() and then commit() on the connection. which library do you use? — Je Je
– Je Je, Commented Jun 4, 2020 at 1:59
not sure what you are using but did that for someone else: stackoverflow.com/questions/62166481/… (just look at the insert thread and iterate through your list of variables) This code uses mysq-connector-python (MySQL native library) and works well. again depends which database and library you use0 — Je Je
– Je Je, Commented Jun 4, 2020 at 2:03
do you commit on the CONNECTION after this code? not familiar with the (EDITED) last bit — Je Je
– Je Je, Commented Jun 4, 2020 at 2:07

Je Je · Accepted Answer · 2020-06-04 02:35:08Z

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()

Collectives™ on Stack Overflow

SQL iterate UPDATE from python list

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related