PYODBC script to do an insert if row doesn’t exist and update if row exists already

Question

I am trying to do an upsert in the SQL Server table using PYODBC. Please find the code below:

sql_insert = """
    UPDATE qch_FullCaseList
    SET
    [DecisionDate]=?,
    [CaseName]=?,
    [FileNum]=?,
    [CourtLocation]=?,
    [CourtName]=?,
    [CourtAbbrv]=?,
    [Judge]=?,
    [CaseLength]=?,
    [CourtCite]=?,
    [ParallelCite]=?,
    [CitedCount]=?,
    [UCN]=?
    WHERE [ReporterCite]=?                                      

    IF @@ROWCOUNT = 0
        INSERT INTO qch_FullCaseList
            (
            [ReporterCite],
            [DecisionDate],
            [CaseName],
            [FileNum],
            [CourtLocation],
            [CourtName],
            [CourtAbbrv],
            [Judge],
            [CaseLength],
            [CourtCite],
            [ParallelCite],
            [CitedCount],
            [UCN]
            )
            VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """

for index,row in df.iterrows():
    cursor.executemany(sql_insert,records)

Here is the error I am getting.

Traceback (most recent call last):
  File "C:\Users\saxenak\Music\Scripts\PyOdbcUpdateInsert.py", line 75, in <module>
    cursor.executemany(sql_insert,records)
pyodbc.ProgrammingError: ('The SQL contains 26 parameter markers, but 13 parameters were supplied', 'HY000')

I don't intend to use sqlalchemy. Thanks

Answer 1

The error is happening because you are providing 13 values, when you need 26. The repetition of the columns demand the same 13 twice, as. You could pass each value as a variable and use the variables in the update and insert, like this:

DECLARE 
    @DecisionDate VARCHAR(255), 
    @CaseName VARCHAR(255),
    @FileNum VARCHAR(255),
    @CourtLocation VARCHAR(255),
    @CourtName VARCHAR(255),
    @CourtAbbrv VARCHAR(255),
    @Judge VARCHAR(255),
    @CaseLength VARCHAR(255),
    @CourtCite VARCHAR(255),
    @ParallelCite VARCHAR(255),
    @CitedCount VARCHAR(255),
    @UCN VARCHAR(255)
    @ReporterCite VARCHAR(255)

SET @DecisionDate = ?
SET @CaseName = ?
SET @FileNum  = ?
SET @CourtLocation  = ?
SET @CourtName  = ? 
SET @CourtAbbrv  = ?
SET @Judge  = ?
SET @CaseLength = ?
SET @CourtCite  = ?
SET @ParallelCite  = ?
SET @CitedCount  = ?
SET @UCN  = ?
SET @ReporterCite  = ?

UPDATE qch_FullCaseList
    SET
    [DecisionDate]=@DecisionDate,
    [CaseName]=@CaseName,
    [FileNum]=@FileNum,
    [CourtLocation]=@CourtLocation,
    [CourtName]=@CourtName,
    [CourtAbbrv]=@CourtAbrev,
    [Judge]=@Judge,
    [CaseLength]=@CaseLength,
    [CourtCite]=@CourtCite,
    [ParallelCite]=@ParallelCite,
    [CitedCount]=@CitedCount,
    [UCN]=@UCN,
    WHERE [ReporterCite]=@ReporterCite                                      

    IF @@ROWCOUNT = 0
        INSERT INTO qch_FullCaseList
            (
            [ReporterCite],
            [DecisionDate],
            [CaseName],
            [FileNum],
            [CourtLocation],
            [CourtName],
            [CourtAbbrv],
            [Judge],
            [CaseLength],
            [CourtCite],
            [ParallelCite],
            [CitedCount],
            [UCN]
            )
            VALUES(
                @ReporterCite,
                @DecisionDate,
                @CaseName,
                @FileNum,
                @CourtLocation,
                @CourtName,
                @CourtAbrev,
                @Judge,
                @CaseLength,
                @CourtCite,
                @ParallelCite,
                @CitedCount,
                @UCN
            )