Insert or replace in MSSQL in Python

Question

I have the following code in python:

import pyodbc

def insertPrintedPath(self, pth):

        con = pyodbc.connect('blabla')
        cur = con.cursor()
        tm = str(datetime.datetime.now())

        cur.execute("insert into dbo.printedPaths \
                    (pth, tm) values \
                    (?, ?)", pth, tm)
        cur.close()
        con.commit()
        con.close()

pth is unique in MSSQL DB. Could I use something like insert or replace in SQLite? which does not work in MSSQL.

This might help: https://stackoverflow.com/questions/1197733/does-sql-server-offer-anything-like-mysqls-on-duplicate-key-update — Nick, May 22 '20 at 13:44

Srinika Pinnaduwage · Accepted Answer · 2020-05-25T14:55:05.190

1

You can use Merge in MSSQL

So replace your

insert into dbo.printedPaths \
                    (pth, tm) values \

As follows;

MERGE INTO dbo.printedPaths WITH (HOLDLOCK) AS target USING (SELECT pth pth, tm) AS source (pth, tm) ON (target.pth = source.pth) WHEN MATCHED THEN UPDATE SET tm = tm WHEN NOT MATCHED THEN INSERT (pth, tm) VALUES (pth, tm);

def insertPrintedPath(self, pth):

        con = pyodbc.connect('blabla')
        cur = con.cursor()
        tm = str(datetime.datetime.now())

        cur.execute("    MERGE \
    INTO dbo.printedPaths WITH (HOLDLOCK) AS target \
    USING (SELECT pth, tm) AS source (pth, tm) \
    ON (target.pth = source.pth) \
    WHEN MATCHED  THEN UPDATE \
        SET tm = tm \
    WHEN NOT MATCHED \
        THEN INSERT (pth, tm) VALUES  \
                    (?, ?)", pth, tm)
        cur.close()
        con.commit()
        con.close()

edited May 25 '20 at 14:55

answered May 22 '20 at 14:04

Srinika Pinnaduwage

1,044
1
7
14

Thank you, I will try it on monday, because I'm on Linux now. – xralf May 22 '20 at 14:44
Could you please show the whole cur.execute() statement, I'm not quite clear about it. – xralf May 22 '20 at 14:47
@xralf, you better try it :) - Read the answer again and give it a shot. – Srinika Pinnaduwage May 22 '20 at 15:33
It ends with error: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '@P1'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)") – xralf May 25 '20 at 10:56
@xralf, In your question there is no '@P1', so the problem should be elsewhere. – Srinika Pinnaduwage May 25 '20 at 12:46
Here is my [question](https://stackoverflow.com/questions/62001156/pyodbc-incorrect-syntax-near-p1) with complete snippet. Actually, I solved it programmatically with select, delete and insert from Python code. But I'm still interested in the correct answer. – xralf May 25 '20 at 13:30
@xralf, even that doesn't have '@P1' ? :O When you pass just Insert statement, was it OK? – Srinika Pinnaduwage May 25 '20 at 14:14
@xralf, get the prepared SQL in python side, as https://stackoverflow.com/questions/7071166/print-the-actual-query-mysqldb-runs And show the SQL statement. – Srinika Pinnaduwage May 25 '20 at 14:33
@xralf, and it has something to with the syntax https://stackoverflow.com/questions/50728621/pyodbc-execute-variable-becomes-p1 (I'll correct my answer - accordingly) – Srinika Pinnaduwage May 25 '20 at 14:40
@xralf, try now – Srinika Pinnaduwage May 25 '20 at 14:44

Insert or replace in MSSQL in Python

1 Answers1