python call stored procedure with two parameter

python call stored procedure with two parameter - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: python call stored procedure with two parameter (/thread-38300.html)

python call stored procedure with two parameter - mg24 - Sep-26-2022

Hi Team,

How to call below working stored procedure via python.

below stored procedure works in microsoft sql. when run manually.
EXEC dbo.unique_query @dbname= 'Database_India",@tablename = "States"

I have created cursor object, and attempted below code. no luck. plz assist.

params = ('ABC_Company','Employee")
sql = "Exec dbo.unique_query @dbname=?,@tablename=?"
#sql = "Exec unique_query @dbname=?,@tablename=?"

data = cursor.execute(sql,params)
cursor.commit()
print(data)

RE: python call stored procedure with two parameter - mg24 - Sep-26-2022

Hi Team,

I found a solution with some troubleshooting , code started working.

params = ('ABC_Company','Employee")
sql = "Exec unique_query ?,?"


cursor.execute(sql,params)
data = cursor.fetchall()

print(data)

RE: python call stored procedure with two parameter - deanhystad - Sep-26-2022

cursor.fetchall() will return all responses to the "execute" as a list. For your particular need it might be valid to use curser.fetchone().

If there are multiple responses you could also use cursor.execute(sql, params) as an iterator.

for reply in cursor.execute(sql, params):
    print(reply)

This last is a lazy iterator. It retrieves replies one at a time. That can be useful for some kinds of queries.

RE: python call stored procedure with two parameter - mg24 - Sep-27-2022

Hi Deanhystad,

Thanks for your help,

Can you help me I am using below piece of code, I need sql data with headers in it into csv files.
SQL Data size big one hence using csv modules.

to avoid memory issue , I am using fetchmany. is there anything to improve in my code.

cursor.execute('SELECT * FROM employee')
rows = cursor.fetchmany(10000)

import csv
cursor = connection.cursor()
cursor.execute('SELECT * FROM employee')
 
filename = "abc.csv"
 
 
# writing to csv file
with open(filename, 'w',newline = '') as csvfile:
    csvwriter = csv.writer(csvfile)
    csvwriter.writerow(col[0] for col in cursor.description)
    while True:
        rows = cursor.fetchmany(10000)
        print(rows)
        if len(rows) ==0:
            break
        else:
              for row in rows:
                 csvwriter.writerow(row)
    cursor.close()
    connection.close()

RE: python call stored procedure with two parameter - deanhystad - Sep-27-2022

I don't understand why you are writing the information to a file. Writing to a file and reading it back in to do whatever processing you plan to do feels like extra work with no return.