I moved a working Python script from one server to another, but now it's suddenly not working

Question

I moved a working Python script to a new server and I am suddenly getting errors.

I installed Python and all it's dependicies on the new server, but for some reason I get this error:

Exception has occurred: ObjectNotExecutableError
Not an executable object: 'EXECUTE dbo.sp_GatherInventory'
AttributeError: 'str' object has no attribute '_execute_on_connection'

The above exception was the direct cause of the following exception:

  File "E:\Project\Python\WriteInventory.py", line 23, in <module>
    rs = con.execute(qry)
         ^^^^^^^^^^^^^^^^

sqlalchemy.exc.ObjectNotExecutableError: Not an executable object: 'EXECUTE dbo.sp_GatherInventory'

Here is the problem part of the code:

# imports
from sqlalchemy import create_engine
from sqlalchemy.engine import URL
import pyodbc
import pandas as pd
import csv
import configparser
import sqlalchemy as sa
import xlwings as xw
import pysftp


connection_string = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=mainDB;DATABASE=Inventory;"
connection_url = URL.create(
    "mssql+pyodbc", query={"odbc_connect": connection_string})

engine = sa.create_engine(connection_url)

qry = "EXECUTE dbo.sp_GatherInventory"
with engine.connect() as con:
    rs = con.execute(qry)

df = pd.read_sql_query(qry, engine)

Nothing has changed on the database and that stored procedure still exists.

I'm not sure why it's not working on this new server.

Does anyone have any ideas?

Thanks!

Answer 1

It's likely that the previous installion ran with SQLAlchemy < 2.0, but the new server has installed SQLAlchemy >= 2.0.

SQLAlchemy 2.0 requires that raw text queries be wrapped with sqlachemy.text, so you need to do

qry = sa.text("EXECUTE dbo.sp_GatherInventory")