CODEWITHSUNDEEP
pythonsql-serverpandasstored-procedurespymssql
asmgx
asmgx

Reputation: 7994

Execute stored procedure in python without return

I am using this code to execute SP in python

conn = pymssql.connect(server="myServer", database="myDB", port="1433", user="myUser", password="myPwd")
pd.read_sql("EXEC MySP", conn)
conn.close()

but I get this error

File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\sql.py", line 1469, in read_query columns = [col_desc[0] for col_desc in cursor.description]

TypeError: 'NoneType' object is not iterable

after doing some research I found the cause of the error is due to no returning value coming from the SP.

ok, my SP does not have any return value and I just want to execute this SP as is.

is there anyway I can do that without having this error?

Upvotes: 0

Views: 4403

Answers (2)

asmgx
asmgx

Reputation: 7994

Thanks kjmerf

I just added SELECT '' at the end of my SP and that fixed the problem.

I thought there could be another function other than read_sql in pandas that might fix this but my search found non.

Upvotes: 0

kjmerf
kjmerf

Reputation: 4335

Try this:

import sqlalchemy

connection_string = 'mssql+pymssql://{username}:{password}@{host}:{port}/{database}'.format(username = <username>, password = <password>, host = <host>, port = <port>, database = <database>)
engine = sqlalchemy.create_engine(connection_string)
connection = engine.raw_connection()

try:
    cursor = connection.cursor()
    cursor.callproc('<procedure_name>')
    cursor.close()
    connection.commit()
finally:
    connection.close()

You have to input username, password, etc. in the connection string and your procedure name as an argument in the callproc method.

Upvotes: 1

Related Questions