CODEWITHSUNDEEP
pythonsqlpypyodbc
Alec Thomas
Alec Thomas

Reputation: 186

pypyodbc error 'Associated statement is not prepared'

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

Upvotes: 7

Views: 14025

Answers (3)

Sumit Paroliya
Sumit Paroliya

Reputation: 1

This error may also come when you don't give correct permissions to stored procedure Go the SQL server --> Right click on your sp-->properties-->permissions Add required users and roles which are going to execute this sp

This may help resolving the issue

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123849

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.

Upvotes: 6

zmbq
zmbq

Reputation: 39059

If you look here or here you'll see people complaining about this over a decade ago.

Apparently SQL Server's ODBC driver returns that error when you're executing two statements that fail due to a field value being too long, or perhaps due to foreign key violations.

Using SSMS to see which statement causes this problem, or better - stop using ODBC and use pymssql

Upvotes: 2

Related Questions