1

I am using SQL server v12.0.2000.8 and trying to emit a delete statement in SQL from an sqlalchemy (v1.4.5) msserver+pyodbc connection. I have tried a number of things, inspired by sqlalchemy docs here and here and stackoverflow post. Still no success. My next step will be using sqlalchemy table methods, but I would like to understand what I am doing wrong as I have done inserts in a similar way to approach A below and they worked. Replacing the values directly in the string works, but obviously a no no.

  • A
deleted_records = ('some_pk_val',)
with mssql_pyodbc_engine.begin() as connection:
    for query_accs in slicer(deleted_records, chunk_size=1000):
        delete_statement1 = text(f"""
        DELETE FROM schema.table1
        WHERE pk_col IN ({', '.join(['?'] * len(deleted_records))});
        """)
        connection.execute(delete_statement1, *deleted_records)
        delete_statement2 = text(f"""
        DELETE FROM schema.table2
        WHERE pk_col IN ({', '.join(['?'] * len(deleted_records))});
        """)
        connection.execute(delete_statement2, *deleted_records)

DBAPIError: (pyodbc.Error) ('07002', '[07002] [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')
[SQL: 
      DELETE FROM schema.table1
      WHERE pk_col IN (?);
      ]
(Background on this error at: http://sqlalche.me/e/14/dbapi)
  • B
deleted_records = ('some_pk_val',)
with mssql_pyodbc_engine.begin() as connection:
    for query_accs in slicer(deleted_records, chunk_size=1000):
        delete_statement1 = text(f"""
        DELETE FROM schema.table1
        WHERE pk_col IN :delrecs;
        """)
        connection.execute(delete_statement1, {'delrecs': deleted_records})
        delete_statement2 = text(f"""
        DELETE FROM schema.table2
        WHERE pk_col IN :delrecs;
        """)
        connection.execute(delete_statement2, {'delrecs': deleted_records})

ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '@P1'. (102) (SQLExecDirectW)")
[SQL: 
        DELETE FROM schema.table1
        WHERE pk_col IN ?;
        ]
[parameters: (('some_pk_val',),)]
(Background on this error at: http://sqlalche.me/e/14/f405)
  • C
from sqlalchemy import bindparam
deleted_records = ('some_pk_val',)
with mssql_pyodbc_engine.begin() as connection:
    for query_accs in slicer(deleted_records, chunk_size=1000):
        delete_statement1 = text(f"""
        DELETE FROM schema.table1
        WHERE pk_col IN :delrecs;
        """)
        delete_statement1 = delete_statement1.bindparams(bindparam('delrecs', expanding=True))
        connection.execute(delete_statement1, {'delrecs': deleted_records})
        delete_statement2 = text(f"""
        DELETE FROM schema.table2
        WHERE pk_col IN :delrecs;
        """)
        delete_statement2 = delete_statement2.bindparams(bindparam('delrecs', expanding=True))
        connection.execute(delete_statement2, {'delrecs': deleted_records})

hangs

Thanks in advance

arp798
  • 13
  • 3
  • *"I am using SQL server v12.0.2000.8"* FYI, that's SQL Server 2014 RTM, there have been ~8 years of updates since then. Is there a reason you don't update your software? – Thom A Jan 28 '22 at 11:14
  • ha I have no power whatsoever over that... It's an ongoing uphill struggle. – arp798 Jan 28 '22 at 11:27

1 Answers1

0

I have solved my own issue. An update for any stuck with old SQL server versions. Calling the sqlalchemy delete function did not work either but an executemany did:

deleted_records = ('some_pk_val',)
with mssql_pyodbc_engine.begin() as connection:
    cur = connection.connection.cursor()
    cur.fast_executemany = True

    delete_stmt = """
    DELETE FROM schema.table1
    WHERE pk_col = ?;
    """
    cur.executemany(delete_stmt, [[acc] for acc in deleted_records])
    cur.close()
arp798
  • 13
  • 3