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: stackoverflow.com/questions/1197733/…

Nick
– Nick

2020-05-22 13:44:17 +00:00
Commented May 22, 2020 at 13:44 — Nick
– Nick, Commented May 22, 2020 at 13:44

Srinika Pinnaduwage · Accepted Answer · 2020-05-25 14:55:05Z

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, 2020 at 14:55

answered May 22, 2020 at 14:04

Srinika Pinnaduwage

1,0421 gold badge7 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

xralf Over a year ago

Thank you, I will try it on monday, because I'm on Linux now.

xralf Over a year ago

Could you please show the whole cur.execute() statement, I'm not quite clear about it.

Srinika Pinnaduwage Over a year ago

@xralf, you better try it :) - Read the answer again and give it a shot.

xralf Over a year ago

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)")

Srinika Pinnaduwage Over a year ago

@xralf, In your question there is no '@P1', so the problem should be elsewhere.

|

Collectives™ on Stack Overflow

Insert or replace in MSSQL in Python

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related