0

I have a python script that is using the Psycopg adapter; I am parsing a JSON Array and inserting into my PostgreSQL database.

for item in data["SchoolJSONData"]:
mId = item.get("Id")
mNumofRooms = item.get("NumofRooms")
mFloors = item.get("Floors")

con = None
con = psycopg2.connect("dbname='database' user='dbuser'")
cur = con.cursor()
cur.execute('INSERT INTO Schools(Id, NumofRooms, Floors)VALUES(%s, %s, %s)',(mId, mNumofRooms, mFloors))
con.commit()

Everytime I run the script again, I get the following:

psycopg2.IntegrityError: duplicate key value violates unique constraint "schools_pkey"

How can I run the insert script so that it will ignore existing entries in the database?

EDIT: Thanks for the replies all... I am trying to NOT overwrite any data, only ADD (if the PK is not already in the table), and ignore any errors. In my case I will only be adding new entries, never updating data.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Bajan
  • 634
  • 3
  • 12
  • 30
  • 1
    What you want is called [upsert](https://wiki.postgresql.org/wiki/UPSERT). New version has a [syntax](http://www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT) for it. If you use older version, however, please see this thread — http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql. Basically, you want to catch exception, but I'm not sure, you should ignore it. If your application has newer data, wouldn't it be better to update data in DB? – Paul Jan 25 '16 at 19:52
  • Thanks @thatyoungman , although in my use case the inserts will never update, simply add a new row with data if the Primary Key is not already there. I will look into moving over to the newer version of Python and see if there is a way to achieve this. – Bajan Jan 25 '16 at 23:44
  • 1
    Well, your issue is not connected with python, my links refer to postgresql docs, and "new version" is PostgreSQL 9.5. – Paul Jan 26 '16 at 09:13

1 Answers1

0

There is no single one way to solve this problem. As well this problem has little to do with python. It is valid exception generated by the database ( not just postgre all databases will do the same ).

But you can try - catch this exception and continue smoothly later. OR
you can use "select count(*) where id = mId" to ensure it is not existing already.

Jay Kumar R
  • 537
  • 2
  • 7