0

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!

SkyeBoniwell
  • 6,345
  • 12
  • 81
  • 185
  • 3
    While the dependencies might be satisfied, make sure the versions are the same as well. If they aren't, the issue (and possible solution) seem to be similar to this [ObjectNotExecutableError when executing any SQL query using AsyncEngine](https://stackoverflow.com/a/69491015/2840436) – micromoses May 22 '23 at 22:49

1 Answers1

2

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")
snakecharmerb
  • 47,570
  • 11
  • 100
  • 153