CODEWITHSUNDEEP
pythonmysqlpandasread-sql
laggerok19
laggerok19

Reputation: 452

panda.to_sql to call stored procedure with parameters via mysql.connector

I've got some Connection class which has __enter__ and __exit__ methods to return mysql.connector.MySQLConnection instance.

What is required is to run mysql stored procedure and get data as dataframe. I've tried tons of ways using: ?, %s, :1, %(name)s and passing list, tuple, dict into parameters.

with Connection(**self._connection) as conn:
   df = pandas.read_sql("call stored_procedure (?);", conn, params=['test'])

I read Pandas read_sql with parameters, MySQL Stored Procedures, Pandas, and "Use multi=True when executing multiple statements" and few others and still can't find a solution as it always failed with "Not all parameters were used in the SQL statement" or asks to use Multi = True;

Of course we can read data with cursor, fetch it and pass into DataFrame constructor, but there has to be a way of using pandas.read_sql according to documentation.

Upvotes: 0

Views: 2772

Answers (2)

Nick Oetjen
Nick Oetjen

Reputation: 149

It is possible for odbc / SQL Server. Maybe you can adapt the call for mysql. Basically, just append your parameters:

# imports for SQL data part
import pyodbc
import pandas as pd

# Get connected

strconnODBC = ("Driver={SQL Server Native Client 11.0};"
            "Server=SERVERNAME;"
            "Database=DATABASENAME;"
            "Trusted_Connection=Yes")       # You may need to enter Login-info

cnn = pyodbc.connect(strconnODBC)


# Call a SQL Server Stored Procedure with Parameters
strMyPara='ShowMeResults'
strSQL="[dbo].[q_Your_SP] "
strSQL= strSQL + " @WhatYouWant='" + strMyPara + "'"

data = pd.read_sql(strSQL , cnn)  
print('Max of ' + strMyPara + ': ' + str(data['Result'].max()))

Upvotes: 0

laggerok19
laggerok19

Reputation: 452

Summary: you can't call SP in that way with parameters Proof: Pandas Docs

Upvotes: 1

Related Questions