1

MSSQL 2005 Server (IIRC) and Python module _mssql.. I have multiple inserts to a table with a single row, which is the primary key.. Now it is possible that I insert the same value multiple times, but I want to simply ignore the "Violation of PRIMARY KEY constraint". Other errors should be raised.

I'm aware that I can try: [...]INSERT[...] except: *magic*: pass but I couldn't find the exception's name which occurs..

edit:

I don't know whether this solution is acceptable but it's what I've got so far.

try:
    con.execute_query('INSERT [...]')
except _mssql.MssqlDatabaseException,e:
    if e.number == 2627:
        #primary key constraint
        pass
    else:
        raise
RF1991
  • 2,037
  • 4
  • 8
  • 17
Daedalus Mythos
  • 565
  • 2
  • 8
  • 24

1 Answers1

0

You need to do an UPSERT - which means update if it exists, or insert. As you are using a very old version of SQL Server, try this:

q = """IF EXISTS (SELECT PrimaryKeyField FROM SomeTable WHERE PrimaryKeyField = %s)
    UPDATE SomeTable
        SET
            SomeFieldA = %s,
            SomeFieldB = %s,
            SomeNumberA = %d
        WHERE
            PrimaryKeyField = %s
    ELSE
        INSERT INTO SomeTable (
            PrimaryKeyField,
            SomeFieldA,
            SomeFieldB,
            SomeNumberA
        ) VALUES (%s,%s,%s,%d)"""

cur.execute(q, ('a','b','c',1,'a','b','c',1))
con.commit()
Burhan Khalid
  • 169,990
  • 18
  • 245
  • 284
  • thank you for the suggestion, but as stated earlier any updates or checks whether the key already exists or not slow down the process and in my situation, unfortunately, it is unacceptable. So I'd need to ignore the error, as it doesn't add anything anyways in this case. – Daedalus Mythos Sep 25 '14 at 10:58
  • Please understand - primary keys are very important. You think its slow now? Wait till you start to query it and your keys are not accurate. – Burhan Khalid Sep 25 '14 at 11:01
  • yes I understand. The processing with this table has to be as fast as possible. non-unique primary keys are tried to be written multiple times (but are not written to the database ~ key constraint exception).. the filled table is later merged to a new table where they will be handled as they should. – Daedalus Mythos Sep 25 '14 at 11:25