Like all CRUD goes, I need to write some data to a table. when I write new data to the table, everything works like charm. the problem starts when I need to write data already existing in the table (actually updating some data with the same primary key). the data just doesn't seem to be written to the table! I started with trying to update the data with session.merge(), but later tried a more brute force approach, of querying for the same primary_key in the table, deleting it and the adding and flushing the changed objects. some where, if the basic add and flush failes the rest doesn't work. I'll be glad for a clue here.
The code: def flush(obj_Instance, id): """ taking care of the sqlalchemy flushing params: Instance: an object Instance to flush into id: the unique object instance id """ DBSession2.add(obj_Instance) try: try: DBSession2.flush() print ("flushed:", str(obj_Instance)) except (FlushError, IntegrityError) as err: DBSession2.rollback() if ('conflicts with persistent instance' in str(err)) or ('Duplicate key was ignored' in str(err)): transaction.begin() #my original slick take: DBSession2.merge(obj_instance) # but after it failed to update correctly I changed to a more brute force approach #DBSession2.flush() #to save the merge #from here on trying to brute force it #saving for further reference - another try newInstance = deepcopy(obj_Instance) print ("deleting: %s" % id) DBSession2.query(type(obj_Instance)).filter_by(ids = id).delete() DBSession2.flush() #at this point, I was so desperate for this to work I literated the code with flush commands. DBSession2.add(newInstance) DBSession2.flush() return else: raise #handling the case of the same key problem isn't the source of conflicts except Exception as err: # supposed to find out the error type and message # the code doesn't get here, only in real exceptions it was planned to catch, 3 rows in 10,000 uploaded to the db #TODO: make this less general and more specific print str(err) write_log(num=id, msg="some sql or sqlalchemy error use num %s as id identifier with object: %s" % (id, obj_Instance.name), timestamp= datetime.now(), errtype="sql error", log=str(err)) DBSession2.rollback() transaction.begin() maybe this try/fail/rollback/merge or delete/insert new pattern is wrong (also I think pythonic - try and ask forgiveness,but that would be for mike to judge) using sqlalchemy 0.7.3 vs mssql 2005 with pyodbc 2.1.11 and tg 2.1 (the transaction manager comes with tg and I think is based transaction) -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.