0

I would like update my table through Python using SQLAlchemy. Since the table I would like to update is not in the default schema, I referred to this question to set the session by sess.execute("SET search_path TO client1").

The whole code example is shown as follows:

session = DBSession()
session.execute("SET search_path TO client1")
session.commit()
total_rows =  session.query(table).all()

for row in total_rows:
  try:
    row.attr1 = getAttr1()
    row.attr2 = getAttr2()
    session.commit()
  except Exception as inst:
    print(inst)
    session.rollback()

Though my code can update the table at the beginning, after several hundreds of iterations (around 500 maybe?) it will throw the exception that the relation table does not exist. My current solution is to iterate my code several times with 500 records updated each time. But I think it is not a perfect solution to this problem and I am still looking forward to finding out the reason that cause this exception.

akshat
  • 1,219
  • 1
  • 8
  • 24
natsuapo
  • 585
  • 9
  • 22
  • I am not sure what are you trying to do inside the for loop. It seems you are trying to update `attr1` and `attr2` without adding the object to the session. You might want to call `session.add(row)` before committing. – mad_ May 31 '18 at 13:39
  • Thank you for your suggestion. I have tried calling `session.add(row)` but the program still throws exception after several hundreds of iterations. – natsuapo May 31 '18 at 16:59
  • @mad_ Results of queries are bound to the session that produced them. No `add()` necessary. – Ilja Everilä May 31 '18 at 17:57
  • I was referring inside the loop where OP is trying to update attr1 with getAttr() function return value. If that makes sense. – mad_ May 31 '18 at 18:01

0 Answers0