0

So, when trying to read data from a stored procedure using pandas (into a data frame no less) I get the following error.

Pandas only support SQL Alchemy connectable

It is occurring on this line

 data = pd.read_sql_query(storedProc2, engine)

This seems to be a pretty common issue for people as I have found out, and after following the advice from multiples posts here, setting the connection string as suggested (see below for the code where I am setting it) but can't seem to figure out why it doesn't like what I am doing.

    import pyodbc
    import sys
    import os
    from mymodules import ODBC, SendEmail, MyLogging
    import pandas as pd
    import gc
    import sqlalchemy as sqlEngine
    import urllib

    cnxn = pyodbc.connect(driver="{ODBC Driver 17 for SQL Server}",server="server",database="db",uid="uid",pwd="pwd")
    cursor = cnxn.cursor()
    storedProc = "exec GetMaxValuesForETL"
    ODBC.RunSQL("CanvasReporting", "exec [dbo].[TruncateStageTablesETL]")
    for irow in cursor.execute(storedProc):
        strObj = " "+str(irow[0])
        strTbl = str(irow[2])
        cnxn2 = "driver=ODBC+Driver+17+for+SQL+Server; server=server; database=db; uid=uid; pwd=pwd"
        params = urllib.parse.quote_plus (cnxn2)
        conn_str = 'mssql+pyodbc:///?odbc_connect={}'.format(params)            
        engine = sqlEngine.create_engine(conn_str)
        storedProc2 = "exec "+strObj+" '"+str(irow[1])+"' "
        data = pd.read_sql_query(storedProc2, engine)
        data.to_sql(name=strTbl, schema='stage', con=cnxn, if_exists='append', index=False)
        del data
        qc.colect()

This is what I get from the command line as I am running the code.

C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy warnings.warn(

2022-07-25 15:37:09,831: CanvasReportingETL.py: INFO: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('HY000', '[HY000] [Microsoft][ODBC Driver 17 for SQL Server]Connection is busy with results for another command (0) (SQLExecDirectW)')

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Doug Coats
  • 6,255
  • 9
  • 27
  • 49
  • You know for as common as the error message seems to be I thought it was obvious but I guess not lol – Doug Coats Jul 25 '22 at 22:22
  • Firstly, make the question self contained i.e. repeat the title. Second always actually ask a question, we are real people here, while we don't want fluffy stuff, we want more than an error dump+code. – Dale K Jul 25 '22 at 22:26
  • Please [edit] your question to include the complete stack trace. – Gord Thompson Jul 25 '22 at 22:34
  • @gordThompson how do i get that for you? – Doug Coats Jul 25 '22 at 22:36
  • Error messages are usually written to the stderr device, or in the case of web applications they would be written to a log file. How do you run your code? – Gord Thompson Jul 25 '22 at 22:38
  • @GordThompson I run it from the commandline. I included the error output it gave me. I also log the same information in a log file. – Doug Coats Jul 25 '22 at 22:40
  • The error is caused by `.to_sql(…, con=cnxn, …)`, same as [this question](https://stackoverflow.com/q/69253030/2144390). – Gord Thompson Jul 25 '22 at 22:46
  • Oh shit i see it now, I need to do the sql alchemy style connection for both pd.read_sql_query and data.to_sql. OK that was painfully obvious and im dumb, thank you. – Doug Coats Jul 25 '22 at 22:51

0 Answers0