How correctly execute a MSSQL stored procedure with parameters in Python

Question

Currently i'm executing stored procedure that way:

engine = sqlalchemy.create_engine(self.getSql_conn_url())
query = "exec sp_getVariablesList @City = '{0}', @Station='{1}'".format(City, Station)
self.Variables = pd.read_sql_query(query, engine)

but at How set ARITHABORT ON at sqlalchemy was correctly noticed that that make that open to SQL injection. I tried different ways but without success. So how should I pass parameters to the MSSQL stored procedure to eliminate the risk of SQL injection? That can be with sqlalchemy or any other way.

What code have you tried and why didn't it work for you? (Error messages?) Have you tried reading through the pandas.read_sqlquery documentation yet? You can pass in parameters via the params tuple. — AlwaysLearning
– AlwaysLearning, Commented Mar 18, 2021 at 12:25
@AlwaysLearning I tried to do that as is described at docs.sqlalchemy.org/en/13/core/connections.html (Calling Stored Procedures) and received error: 'pyodbc.Cursor' object has no attribute 'callproc' — Krzysztof Krysztofczyk
– Krzysztof Krysztofczyk, Commented Mar 18, 2021 at 13:21

Gord Thompson · Accepted Answer · 2021-03-18 13:44:18Z

7

Write your SQL command text using the "named" paramstyle, wrap it in a SQLAlchemy text() object, and pass the parameter values as a dict:

import pandas as pd
import sqlalchemy as sa

connection_uri = "mssql+pyodbc://@mssqlLocal64"
engine = sa.create_engine(connection_uri)

# SQL command text using "named" paramstyle
sql = """
SET NOCOUNT ON;
SET ARITHABORT ON;
EXEC dbo.breakfast @name = :name_param, @food = :food_param;
"""
# parameter values
param_values = {"name_param": "Gord", "food_param": "bacon"}
# execute query wrapped in SQLAlchemy text() object
df = pd.read_sql_query(sa.text(sql), engine, params=param_values)

print(df)
"""
                           column1
0  Gord likes bacon for breakfast.
"""

answered Mar 18, 2021 at 13:44

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How correctly execute a MSSQL stored procedure with parameters in Python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related