sql server - Executing stored procedure via python doesn't modify data but via ssms or data studio works - Stack Overflo

admin管理员组

文章数量:1189002

I have a stored procedure in azure sql database which moves data from staging table to target table. When I run the procedure manually, data moves:

exec [Construction].[uspProcess] 'Equipment',26

So this procedure moves data for Equipment table from ConstructionStaging.Equipment to Construction.Equipment table.

And then I have python script to execute same procedure. It runs fine, but data is not modified. I get no error, just the data transfer doesn't happen.

config.py:

import os

def get_connection_string():
    IS_MANAGED_IDENTITY = os.getenv("IS_MANAGED_IDENTITY", "True").lower() == "true"

    if IS_MANAGED_IDENTITY:
        connection_string = (
        f"mssql+pyodbc://{DATABASE_NAME}?driver=ODBC+Driver+17+for+SQL+Server&Authentication=ActiveDirectoryMsi&autocommit=True"
    )
    else:
        DATABASE_SERVER_NAME = os.getenv("DATABASE_SERVER_NAME")
        DATABASE_NAME = os.getenv("DATABASE_NAME")
        DATABASE_USER_NAME = os.getenv("DATABASE_USER_NAME")
        DATABASE_USER_PASSWORD = os.getenv("DATABASE_USER_PASSWORD")

        connection_string = (
        f"mssql+pyodbc://{DATABASE_USER_NAME}:{DATABASE_USER_PASSWORD}@{DATABASE_SERVER_NAME}/{DATABASE_NAME}"
        "?driver=ODBC+Driver+17+for+SQL+Server&autocommit=True"
    )

    return connection_string

db.py:

def staging_to_target(table_name, batch_id):
    session = session_factory()  # Create a new session instance
    try:
       sql = text("EXEC SKUK_Lidat.uspProcessLidat @TableName = :table_name, @BatchId = :batch_id").execution_options(autocommit=True)
       session.execute(sql, {"table_name": table_name, "batch_id": batch_id})
       return True  # No need to commit if autocommit is enabled
   except Exception as e:
       print(f"Error calling uspProcessLidat: {e}")
       return False
   finally:
       session.close()  # Ensure session closure

Finally i am executing the procedure:

if staging_to_target(table_name, batch_id):
     logging.info(f"uspProcessLidat executed successfully for table: {table_name}.")
else:
     logging.error(f"uspProcessLidat execution failed for table: {table_name}.")

Please let me know where I am going wrong.

Thank you!

本文标签：