如何使用SQLAlchemy在SQL Server中执行UPSERT

Question

根据这篇文章的答案：SQLAlchemy ON DUPLICATE KEY UPDATE

以及sqlalchemy文档：https://docs.sqlalchemy.org/en/14/dialects/mysql.html#sqlalchemy.dialects.mysql.Insert.on_duplicate_key_update

我都需要能够重建整个表，并且只需更新它。重建和插入工作正常，但我尝试在表上进行重复键更新，但运气不佳

运行以下代码：

engine = create_engine(SQL_SERVER_HOST, SQL_SERVER_PORT, SQL_SERVER_DB, user, pwd)

    table_name = "service_area_prediction"

    # ----- If overwrite, delete table and insert new table with values -----

    meta = sql.MetaData()

    sap_test = sql.Table(
    'sap_test', meta, 
    sql.Column('PredictionId', sql.VARCHAR(100), primary_key = True), 
    sql.Column('ContactID', sql.VARCHAR(100)), 
    sql.Column('login_id', sql.VARCHAR(100)), 
    sql.Column('local_time', sql.VARCHAR(100)),
    sql.Column('area1', sql.VARCHAR(100)),
    sql.Column('area2', sql.VARCHAR(100)),
    sql.Column('area3', sql.VARCHAR(100)),
    sql.Column('application', sql.VARCHAR(100)),
    sql.Column('updatetime', sql.DATE),
    )

    # ----- This part works fine -----
    if overwrite:
        try:
            sap_test.drop(engine)
        except Exception:
            print(f"{table_name} DOES NOT EXIST")

        # ----- Chunk up data and upload to SQL in sections -----
        data = output.to_dict(orient='records')
        data = chunks(data, 100)
        meta.create_all(engine)
        con = engine.connect()
        for d in data:
            stmt = insert(sap_test).values(d)
            con.execute(stmt)

    # ----- Issue is here -----
    else:
        # ----- Chunk up data and upload to SQL in sections -----
        data = output.to_dict(orient='records')
        data = chunks(data, 100)
        meta.create_all(engine)
        con = engine.connect()
        for d in data:
            stmt = insert(sap_test).values(d)
            update_stmt = stmt.on_duplicate_key_update(
                area1=stmt.inserted.area1,
                area2=stmt.inserted.area2,
                updatetime=stmt.inserted.updatetime,
                status='U')

            con.execute(update_stmt)

返回此错误：

AttributeError: 'MSSQLCompiler' object has no attribute 'visit_on_duplicate_key_update'

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

Traceback (most recent call last):
  File "daily_prediction.py", line 211, in <module>
    upload_to_sql(output, overwrite=False)
  File "/yousee_analytics_and_optimization/src/implementations/sqluploadv2.py", line 140, in upload_to_sql
    con.execute(update_stmt)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1115, in _execute_clauseelement
    compiled_sql = elem.compile(
  File "<string>", line 1, in <lambda>
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 481, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 487, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/mssql/base.py", line 1571, in __init__
    super(MSSQLCompiler, self).__init__(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 592, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 322, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 352, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 96, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 2511, in visit_insert
    post_values_clause = self.process(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 352, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 91, in _compiler_dispatch
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
sqlalchemy.exc.UnsupportedCompilationError: Compiler <sqlalchemy.dialects.mssql.base.MSSQLCompiler object at 0x7f732779db20> can't render element of type <class 'sqlalchemy.dialects.mysql.dml.OnDuplicateClause'> (Background on this error at: http://sqlalche.me/e/13/l7de)

我花了几天时间来解决这个问题，所以非常感谢您的帮助