英文:
SQL Merge Error using Python (import pyodbc)
问题
我在尝试合并和更新数据到一个我创建的SQLExpress表时遇到了错误:
错误:('SQL包含10个参数标记,但只提供了5个参数','HY000')
不确定我做错了什么,当我使用SSMS时,我可以合并和更新,但是在使用这个Python代码时,无法解决这个参数错误。
for i, row in df.iterrows():
cols = ', '.join(row.index)
vals = ', '.join(['?'] * len(row))
print(cols)
print(vals)
#query = f"INSERT INTO {table} ({cols}) VALUES ({vals});"
query = f"MERGE INTO {table} AS target " \
f"USING (VALUES ({vals})) AS source ({cols}) " \
f"ON (target.id = source.id) " \
f"WHEN MATCHED THEN " \
f" UPDATE SET {', '.join(f'target.{col} = source.{col}' for col in row.index)} " \
f"WHEN NOT MATCHED THEN " \
f"INSERT ({cols}) VALUES ({vals});"
print(query)
cursor.execute(query, tuple(row))
conn.commit()
print(f"Inserted/updated {cursor.rowcount} rows into {table}")
except pyodbc.Error as e:
print("Error:", e)
finally:
cursor.close()
conn.close()
在终端中,print(cols)、print(vals)和print(query)返回以下内容。
id, operationId, operation, name, externalId
?, ?, ?, ?, ?
MERGE INTO Activities AS target
USING (VALUES (?, ?, ?, ?, ?))
AS source (id, operationId, operation, name, externalId)
ON (target.id = source.id)
WHEN MATCHED THEN
UPDATE
SET target.id = source.id,
target.operationId = source.operationId,
target.operation = source.operation,
target.name = source.name,
target.externalId = source.externalId
WHEN NOT MATCHED THEN
INSERT (id, operationId, operation, name, externalId)
VALUES (?, ?, ?, ?, ?);
这里是表格和两行数据的示例。你在这里可能犯的错误是在cursor.execute(query, tuple(row))
中传递了一个元组,但似乎参数数量不匹配。请确保你的row
中包含了足够的参数以匹配SQL查询中的占位符。如果不匹配,你需要调整行数据的内容以确保与查询中的占位符数量相匹配。
英文:
I have an error when I am trying to merge and update data into a SQLExpress table I have create:
Error: ('The SQL contains 10 parameter markers, but 5 parameters were supplied', 'HY000')
Not sure what I am doing wrong, I can Merge and Update when I use SSMS. But can't get past this parametre error when using this Python code.
for i, row in df.iterrows():
cols = ', '.join(row.index)
vals = ', '.join(['?'] * len(row))
print(cols)
print(vals)
#query = f"INSERT INTO {table} ({cols}) VALUES ({vals});"
query = f"MERGE INTO {table} AS target " \
f"USING (VALUES ({vals})) AS source ({cols}) " \
f"ON (target.id = source.id) " \
f"WHEN MATCHED THEN " \
f" UPDATE SET {', '.join(f'target.{col} = source.{col}' for col in row.index)} " \
f"WHEN NOT MATCHED THEN " \
f"INSERT ({cols}) VALUES ({vals});"
print(query)
cursor.execute(query, tuple(row))
conn.commit()
print(f"Inserted/updated {cursor.rowcount} rows into {table}")
except pyodbc.Error as e:
print("Error:", e)
finally:
cursor.close()
conn.close()
the print(cols), print(vals) and print (query) return this in terminal.
id, operationId, operation, name, externalId
?, ?, ?, ?, ?
MERGE INTO Activities AS target
USING (VALUES (?, ?, ?, ?, ?))
AS source (id, operationId, operation, name, externalId)
ON (target.id = source.id)
WHEN MATCHED THEN
UPDATE
SET target.id = source.id,
target.operationId = source.operationId,
target.operation = source.operation,
target.name = source.name,
target.externalId = source.externalId
WHEN NOT MATCHED THEN
INSERT (id, operationId, operation, name, externalId)
VALUES (?, ?, ?, ?, ?);
here is the table and two rows of data
couple of rows of data
WHat am I doing wrong? Cheers in advance Dave
I tried using the SQlalchemy library and also asked good old GPT but didn't get very far.
答案1
得分: 1
将 source.*
列简单地格式化为 INSERT
子句,就像你在 UPDATE
子句中所做的那样。这样可以避免将两组 vals
参数发送到查询中。另外,考虑避免在括号内使用反斜杠行分隔符来连接行:
...
upd_cols = ', '.join(f'target.{col} = source.{col}' for col in row.index)
src_cols = ', '.join(f'source.{col}' for col in row.index)
query = (
f"MERGE INTO {table} AS target "
f"USING (VALUES ({vals})) AS source ({cols}) "
"ON (target.id = source.id) "
"WHEN MATCHED THEN "
f" UPDATE SET {upd_cols} "
"WHEN NOT MATCHED THEN "
f"INSERT ({cols}) VALUES ({src_cols});"
)
...
英文:
Simply format source.*
columns to INSERT
clause like you did in the UPDATE
clause. This avoids sending two set of vals
parameters into query. Also, consider avoiding backslash line breaks for parentheses to concatenate lines:
...
upd_cols = ', '.join(f'target.{col} = source.{col}' for col in row.index)
src_cols = ', '.join(f'source.{col}' for col in row.index)
query = (
f"MERGE INTO {table} AS target "
f"USING (VALUES ({vals})) AS source ({cols}) "
"ON (target.id = source.id) "
"WHEN MATCHED THEN "
f" UPDATE SET {upd_cols} "
"WHEN NOT MATCHED THEN "
f"INSERT ({cols}) VALUES ({src_cols});"
)
...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论