0

I am attempting to use pandas.read_sql_query to call a stored procedure in IBM's db2 and read the results into a dataframe. However when I do so, I receive the following error:

ibm_db_dbi::ProgrammingError: The last call to execute did not produce any result set.

I've called the procedure in IBM Data Studio, to confirm that it works as intended, yielding the anticipated approximately 1000 records. I've also manually queried the table using a select * from table, with read_sql_query from my script with success. Therefore I may conclude that the python script is properly configured to work with the database as is the procedure itself. The struggle seems to be in putting the two together. My code is below.

import ibm_db
import ibm_db_dbi
import pandas as pd

cnxn = ibm_db.connect('DATABASE=mydb;'
                'HOSTNAME=myHost;'
                'PORT=446;'
                'PROTOCOL=TCPIP;'
                'UID=myUser;'
                'PWD=myPassword;', '', '')
conn=ibm_db_dbi.Connection(cnxn)

sql = 'call myschema.getaccountnonrecurringpaging(20230509,0);'
df = pd.read_sql_query(sql, conn)

Package details are listed below:

  • python=3.10.4
  • pandas=1.5.3
  • ibm_db=3.1.1
  • operating system = Ubuntu 20.04
  • db2: v7r4
Improve this question
6
  • How do you return data from your stored procedure?
    – Lennart - Slava Ukraini
    Commented Jun 4, 2023 at 12:17
  • Please also specify the version of the SQLAlchemy
    – mao
    Commented Jun 5, 2023 at 8:02
  • With the above code, the callproc method in dbi will not run. See docs.sqlalchemy.org/en/14/core/connections.html (and search for callproc) . You should be able to convert the resulting rows into a dataframe.
    – mao
    Commented Jun 5, 2023 at 13:27
  • @Lennart-SlavaUkraini Thanks for your response; it's a result set in a cursor.
    – neanderslob
    Commented Jun 5, 2023 at 16:29
  • @neanderslob Please raise a new issue at github.com/ibmdb/python-ibmdb/issues . This was previously raised (but closed) at github.com/ibmdb/python-ibmdb/issues/272 (and I notice that the suggested code change in #272 still works). So it is wise to ask the devs to look again at it. As a workaround in the meantime you can use the callproc method, but the ibm_db_dbi devs should be asked formally to check the issue.
    – mao
    Commented Jun 5, 2023 at 21:15
Related questions
1863
Insert results of a stored procedure into a temporary table
1149
Search text in stored procedure in SQL Server
1480
How to deal with SettingWithCopyWarning in Pandas
Load 7 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.