Using a raw sql with SELECT statement in a function gives an error

Question

I am very confused with the behaviour here. When using the below code on its own with raw SQL command, I have no issue but as soon as i put it in a function and call it I get an exception error.

Outside of a function - absolutely fine!

engine = create_engine("mssql+pyodbc://{}:{}@{}/{}?driver={}".format(
        username, password, server, database, driver))

with engine.connect() as conn:
    result = conn.execute(text(find_user))

    try:
        for row in result:
        print(row)

Within function

find_user = f"SELECT * FROM UserTable where UserId = 001"

def db(sql_command):

    engine = create_engine("mssql+pyodbc://{}:{}@{}/{}?driver={}".format(
            username, password, server, database, driver), echo=True)

    with engine.begin() as conn:
        result = conn.execute(text(sql_command))
        print(result.rowcount)
        return result

user = db(find_user)
for row in user:
    print(row)

Here is the error I am getting:

(pyodbc.Error) ('HY010', '[HY010] [Microsoft][SQL Server Native Client 11.0]Function sequence error (0) (SQLFetch)')

I have tried .fetchall(), looked at the sqlalchemy documentation, searched the web and the trusted ChatGPT but I have come empty handed.

Using the same function with UPDATE statement works though!

Answer 1

Fetch all results into memory and return them from the function. You can do this using the .fetchall()

def db(sql_command):
    engine = create_engine("mssql+pyodbc://{}:{}@{}/{}?driver={}".format(
            username, password, server, database, driver), echo=True)

    with engine.begin() as conn:
        result = conn.execute(text(sql_command))
        print(result.rowcount)
        # Fetch all rows and return them
        rows = result.fetchall()
        return rows

user = db(find_user)
for row in user:
    print(row)