Home > Net >  How do I single commit for multiple queries using pyodbc
How do I single commit for multiple queries using pyodbc

Time:09-24

I am trying to insert data in to AZURE SQL table by using pyodbc. here I am using three SQL queries, when I tried to insert data in to table only last sql statement is inserting.

def dbQuery_Multiple_Row(sql,li):
  global cursor, conn
  cursor, conn =db_connection_dev01()
  try:
    cursor.execute(sql)
  except Exception as e:
    li.append("Error-" str(e))
sql1 = "insert into stars.sampledata values ('Suneel Kummar I', 2, 'Sullurepeta')"
sql2 = "insert into stars.sampledata values ('Suneel Kummar Il', 3, 'Sullurepeta')"
sql3 = "insert into stars.sampledata values ('Suneel Kummar Ill', 4, 'Sullurepeta')"

import pyodbc
sql = [sql1, sql2, sql3]
li = []
dbQuery_Multiple_Row(sql1, li)
dbQuery_Multiple_Row(sql2, li)
dbQuery_Multiple_Row(sql3, li)
conn.commit()
conn.close()

My task is I should commit once for all sql queries, but commit is happening for only last SQL query. Please help me out. Thanks in advance.

CodePudding user response:

Your problem is db_connection_dev01 which opens a new connection.
So, each call to dbQuery_Multiple_Row opens a connection, but commit is performed only for the last.
It explains why you see the results of the 3rd query only. To fix the problem you need to use the same connection.
One way for fix is to open it before dbQuery_Multiple_Row and pass it as an argument

  • Related