如何将 pandas DataFrame 插入到 Microsoft SQL Server 表？

问题描述：

我想将我的 pandas DataFrame 更新插入到 SQL Server 表中。这个问题在 PostgreSQL 中有一个可行的解决方案，但 T-SQL 没有相应ON CONFLICT的变体INSERT。我该如何在 SQL Server 中实现同样的功能？

解决方案 1：

更新，2022 年 7 月：您可以使用此功能构建 MERGE 语句并执行 upsert，从而节省一些输入。

SQL Server 提供了MERGE语句：

import pandas as pd
import sqlalchemy as sa

connection_string = (
    "Driver=ODBC Driver 17 for SQL Server;"
    "Server=192.168.0.199;"
    "UID=scott;PWD=tiger^5HHH;"
    "Database=test;"
    "UseFMTONLY=Yes;"
)
connection_url = sa.engine.URL.create(
    "mssql+pyodbc",
    query={"odbc_connect": connection_string}
)

engine = sa.create_engine(connection_url, fast_executemany=True)

with engine.begin() as conn:
    # step 0.0 - create test environment
    conn.exec_driver_sql("DROP TABLE IF EXISTS main_table")
    conn.exec_driver_sql(
        "CREATE TABLE main_table (id int primary key, txt varchar(50))"
    )
    conn.exec_driver_sql(
        "INSERT INTO main_table (id, txt) VALUES (1, 'row 1 old text')"
    )
    # step 0.1 - create DataFrame to UPSERT
    df = pd.DataFrame(
        [(2, "new row 2 text"), (1, "row 1 new text")], columns=["id", "txt"]
    )

    # step 1 - upload DataFrame to temporary table
    df.to_sql("#temp_table", conn, index=False, if_exists="replace")

    # step 2 - merge temp_table into main_table
    conn.exec_driver_sql(
        """\n        MERGE main_table WITH (HOLDLOCK) AS main
        USING (SELECT id, txt FROM #temp_table) AS temp
        ON (main.id = temp.id)
        WHEN MATCHED THEN
            UPDATE SET txt = temp.txt
        WHEN NOT MATCHED THEN
            INSERT (id, txt) VALUES (temp.id, temp.txt);
        """
    )

    # step 3 - confirm results
    result = conn.exec_driver_sql(
        "SELECT * FROM main_table ORDER BY id"
    ).fetchall()
    print(result)  
    # [(1, 'row 1 new text'), (2, 'new row 2 text')]