python - 使用 SQLAlchemy 和 Python 执行 SQL CTE

Question

我在主程序中有以下代码：

cleanDuplicatesAttendeesSQL = attendees.clean_duplicates()
sqlUtilities.databaseWorkerExecute(cleanDuplicatesAttendeesSQL)

attendees.clean_duplicates()设置变量cleanDuplicatesAttendeesSQL：

def clean_duplicates():
    return """
        WITH cte AS(
            SELECT [Sessions]
                ,[Confirmation #]
                ,[First Name]
                ,[Last Name]
                ,ROW_NUMBER() OVER (
                    PARTITION BY
                        [Sessions]
                        ,[Confirmation #]
                        ,[First Name]
                        ,[Last Name]
                    ORDER BY
                        [Sessions]
                        ,[Confirmation #]
                        ,[First Name]
                        ,[Last Name]
                    ) row_num
            FROM attendees
        )
        DELETE FROM cte
        WHERE row_num > 1"""

然后我传递cleanDuplicatesAttendeesSQL给以下函数以对数据库执行：

def databaseWorkerExecute (sql):
    engine = sqlalchemy_connect()
    try:

        engine.execute(sql)
        print("Execution successful.")
    except SQLAlchemyError as e:
        error = str(e.__dict__['orig'])
        print (error)
        sys.exit("Execution was not sucessful. Exiting the program...")

这个函数不会出错，每次我都会得到一个“执行成功”。但是，当我查看数据库中的表时，我仍然看到重复项。我成功地使用相同databaseWorkerExecute的功能来执行其他操作，例如更新、插入和删除。我怎样才能让 CTE 也能正常工作？

SQL输出截图：

标签： pythonsql-serversqlalchemy