I am trying to create an 'upsert' function for pypyodbc SQL Server. I have validated that the query built up will run in SSMS with the desired outcome, but when trying to execute and commit with pypyodbc I receive the following error: 'HY007', '[HY007] [Microsoft][ODBC SQL Server Driver]Associated statement is not prepared'.
Here is the upsert function:
def sql_upsert(sql_connection, table, key_field, key_value, **kwargs):
keys = ["{key}".format(key=k) for k in kwargs]
values = ["'{value}'".format(value=v) for v in kwargs.values()]
update_columns = ["{key} = '{value}'".format(key=k, value=v) for k, v in kwargs.items()]
sql = list()
#update
sql.append("UPDATE {table} SET ".format(table=table))
sql.append(", ".join(update_columns))
sql.append(" WHERE {} = '{}'".format(key_field, key_value))
sql.append(" IF @@ROWCOUNT=0 BEGIN ")
# insert
sql.append("INSERT INTO {table} (".format(table=table))
sql.append(", ".join(keys))
sql.append(") VALUES (")
sql.append(", ".join(values))
sql.append(")")
sql.append(" END")
query = "".join(sql)
print(query)
The function builds up a query string in a format based on this other thread How to insert or update using single query?
Here is an example of the output:
UPDATE test SET name='john' WHERE id=3012
IF @@ROWCOUNT=0 BEGIN
INSERT INTO test(name) VALUES('john')
END
The error message you cited is produced by the ancient "SQL Server" ODBC driver that ships as part of Windows. A more up-to-date driver version like "ODBC Driver 17 for SQL Server" should produce a meaningful error message.