我有一个数据帧,我上传到一个临时表。这将正确加载(我已将断点插入以进行检查)
然后,我运行了一个merge语句,但似乎什么也没有发生,行没有附加到目标表中(并且这些行在目标表中还不存在)
当我将merge语句复制到sqlserver并执行该语句时,它就会工作,我可以看到新行。因此,我有点不明白为什么在Python中运行该语句时该语句不起作用
我所有的代码都在下面
def my_function(database: str, df: pd.DataFrame) -> None:
params = urllib.parse.quote_plus("DRIVER={SQL Server};"
"SERVER=MY_PC\SQLEXPRESS;"
"DATABASE=" + database + ";"
"Trusted_Connection=yes")
engine = create_engine("mssql+pyodbc:///?odbc_connect={}".format(params))
cnxn = engine.connect()
q = """
CREATE TABLE #tdata(hq_id int,
time_stamp datetime,
pressure float,
p_atom float,
npt2 float,
qstate float)
"""
cnxn.execute(q)
# upload data into temp table
df.to_sql('tempdb..#tdata', cnxn, if_exists='replace', index=False)
# now do the merge
mq = """
MERGE p_region p
USING [tempdb..#tdata] d
ON p.hq_id = d.hq_id
and p.time_stamp = d.time_stamp
WHEN NOT MATCHED by TARGET
THEN INSERT (hq_id, time_stamp, pressure, p_atom, npt2, qstate)
VALUES(d.hq_id, d.time_stamp, d.pressure, d.p_atom, d.npt2, d.qstate);
"""
cnxn.execution_options(autocommit=True)
cnxn.execute(mq)
cnxn.close()
engine.dispose()
目前没有回答
相关问题 更多 >
编程相关推荐