SQL合并错误使用Python(导入pyodbc)

huangapple go评论73阅读模式
英文:

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});"
)

...

huangapple
  • 本文由 发表于 2023年6月25日 23:49:26
  • 转载请务必保留本文链接:https://go.coder-hub.com/76551227.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定