0

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.

laggerok19
  • 426
  • 8
  • 16
  • you call your stored proc but you need to actually select the data, where is the select statement? – Umar.H Mar 12 '20 at 20:13
  • you want to say that it's not possible to call SP via read_sql? – laggerok19 Mar 12 '20 at 20:16
  • I assume the proc returns a result set. Are there any other statements in the SQL that are returning rows/values – Peter Mar 12 '20 at 20:35
  • Re-reading https://pandas.pydata.org/pandas-docs/version/0.20.3/generated/pandas.read_sql.html again carefully, I stopped on phrase: **sql : string SQL query or SQLAlchemy Selectable (select or text object)** which could mean that you can't call sp in read_sql. – laggerok19 Mar 12 '20 at 20:38
  • a stored procedure isn't a table or a sample of data, it's a series of SQL statements that you execute, what you need is the output of the procedure, a dim/fact/stage/temp table of somekind. you need to select that. – Umar.H Mar 12 '20 at 20:45
  • that is exactly what SP is doing - just selecting a row from a table and returning them. – laggerok19 Mar 13 '20 at 08:11

2 Answers2

0

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

laggerok19
  • 426
  • 8
  • 16
0

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()))
Nick Oetjen
  • 129
  • 4