Pypyodbc: Executing stored procedure in a loop where the store procedure is saved in a table

Question

I have a table which has data which looks like below

Name  Query   RunDate
SP    Some_sp 12/25/2017
Sp1   Some1_sp 12/25/2017
sp_2  Some2_sp 12/25/2107

Query column has the stored procedure to be executed.

def __init__(self):
    self.conn=pypyodbc.connect(connection)
    self.cursor=self.conn.cursor()
def getDatafromDB(self):
    sql = """Select * from table"""
    self.cursor.execute(sql)
    data=pd.DataFrame(self.cursor.fetchall())
    return data

I am querying that table and saving it as a pandas dateframe . Next step is for me to execute those stored procedure present in query column one after the other. Is there a way to do this using pypyodbc ?

shockwave · Answer 1 · 2017-12-30T04:09:27.773

Try this:

def getDatafromDB(self):
    sql = """Select Query from table"""
    self.cursor.execute(sql)
    data=self.cursor.fetchall()
    while data:
         print(data)
         if self.cursor.nextset():
             sql = "{call "+data+"(?)}"
             params = (3,)
             cursor = connection.cursor()
             rows = cursor.execute(sql, params).fetchall()
             print(rows)
         else:
             data= None

Pypyodbc: Executing stored procedure in a loop where the store procedure is saved in a table

1 Answers1