1

I am trying to update if record exist and insert if no record is found. using below code

for index, row in df.iterrows():
                 
                 cols = "],[".join([str(i) for i in df.columns.tolist()])
                 cols = "([" + cols + "])"

                 ucols = "] = ?,[".join([str(i) for i in df.columns.tolist()])
                 ucols = "[" + ucols + "] = ?"

                 c.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;")
                 c.execute("BEGIN TRANSACTION;")
                 c.execute("UPDATE " + tblname + " SET" + ucols + " WHERE [TESTNUMBER]=" + str(row['TESTNUMBER']) + " AND [ROWNUM] =" + str(row['ROWNUM']) + ";", tuple(row))
                 sqlr = "IF @@ROWCOUNT = 0 " \
                        "BEGIN " \
                            "INSERT INTO " + tblname + cols +" VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?); " \
                        "END " \
                        "COMMIT TRANSACTION;"
                 c.execute(sqlr, tuple(row))

getting below error message {ProgrammingError}('25000', u'[25000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 2, current count = 1. (266) (SQLExecDirectW)')

no sure what i am doing wrong. appreciate your help

Krishna
  • 11
  • 2
  • pandas has a `to_sql` method that you use with pyodc `pd.to_sql` if you need to do it in batches use `chunk` see [here](https://stackoverflow.com/questions/35202981/optimal-chunksize-parameter-in-pandas-dataframe-to-sql) – Umar.H Oct 07 '20 at 16:15
  • Thanks for the response. The problem is i have to do update record if the row already exist. pd.to_sql only does insert of new records. – Krishna Oct 07 '20 at 16:25
  • see duplicated question - another option is inserting into a temp table and creating a store proc to load the delta of your src and trg – Umar.H Oct 07 '20 at 16:29
  • i don't have a say on the database side to create temp table unfortunately. was just looking for alternate solution to push the record to database. – Krishna Oct 07 '20 at 17:32
  • I would like to understand what's causing "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 2, current count = 1" this error. i am trying to implement an alternate solution which does not require a temp table. – Krishna Oct 07 '20 at 17:46
  • @Manakin can you please unmark this as duplicate question. – Krishna Oct 07 '20 at 17:47
  • I think you may need to set no count as 0 I can't test but will ask my colleagues tomorrow if I get a chance – Umar.H Oct 07 '20 at 17:53
  • 1
    Try putting the UPDATE and the INSERT in the same anonymous code block (`.execute` call). – Gord Thompson Oct 07 '20 at 18:15

0 Answers0