How do I get integrity error to continue inserting into db -- python?

Question

I have a postgres DB that looks like this:

                                                        Table "public.available_dates"
   Column   |            Type             |                          Modifiers                           | Storage | Stats target | Description
------------+-----------------------------+--------------------------------------------------------------+---------+--------------+-------------
 id         | integer                     | not null default nextval('available_dates_id_seq'::regclass) | plain   |              |
 unix_day   | integer                     |                                                              | plain   |              |
 hour       | integer                     |                                                              | plain   |              |
 created_at | timestamp without time zone |                                                              | plain   |              |
 updated_at | timestamp without time zone |                                                              | plain   |              |
Indexes:
    "available_dates_pkey" PRIMARY KEY, btree (id)
    "index_available_dates_on_unix_day_and_hour" UNIQUE, btree (unix_day, hour)
    "index_available_dates_on_unix_day" btree (unix_day)
Has OIDs: no

This is hooked into a rails app I'm developing and a python app that runs as a service, I use peewee as the orm for python. The rails app runs fine, and I need each of the indexes on this db.

The python application looks at files, and adds dates to the db. The problem I am having is if the date+hour exists, it fails and ends the python app. I don't want dupes in the db, but I also want the app to continue running if this integrity error is thrown. This is the code I have so far:

data_source = [{'unix_day': 1370044800, 'created_at': datetime.datetime(2014, 7, 14, 10, 12, 57, 488000), 'updated_at': datetime.datetime(2014, 7, 14, 10, 12, 57, 488000), 'hour': 1}, 
...
]

        try:
            with db.transaction():
                Available_Dates.insert_many(data_source).execute()
        except IntegrityError as e:
            print e
            db.rollback()
            pass
        else:
            db.commit()
        db.close()

Which fails (as it should):

duplicate key value violates unique constraint "index_available_dates_on_unix_day_and_hour" DETAIL: Key (unix_day, hour)=(1370044800, 10) already exists.

How do I get my code to say, "That failed? Oh well... I just try the next one."

Note: I expect it will fail more than it will succeed.

Edit: Option 1 as per first answer:

for data in data_source:
            try:
                av_date = Available_Dates()
                av_date.unix_day = data['unix_day']
                av_date.hour = data['hour']
                av_date.created_at = data['created_at']
                av_date.updated_at = data['updated_at']
                av_date.save()

            except Exception as e:
                pass

This seems not great as I'm catching all exceptions... Also, I have to assign every member separately which seems inefficient. It also fails because there is a lock on the db.

duplicate key value violates unique constraint "index_available_dates_on_unix_day_and_hour"
DETAIL:  Key (unix_day, hour)=(1370044800, 1) already exists.

current transaction is aborted, commands ignored until end of transaction block

current transaction is aborted, commands ignored until end of transaction block

current transaction is aborted, commands ignored until end of transaction block

current transaction is aborted, commands ignored until end of transaction block

current transaction is aborted, commands ignored until end of transaction block
 ...

It looks like you are catching the wrong exception, look up what error you should really catch(if your except line just says except Exception, e: it will run fine, but it is bad form because it will mask errors). Also why not do a check in the table to see if that record already exists first? — ZJS
– ZJS, Commented Jul 14, 2014 at 14:20
Options I see are to insert everything and get rid of my unique constraint and just clean up dupes later; do many inserts rather than a transaction; — Jeff
– Jeff, Commented Jul 14, 2014 at 14:24
That sounds slow and prone to error - bad option. Keep the try catch block after modifying to properly catch error. If you want to do bulk inserts you can use pandas or sets to check if it is already in your table. — ZJS
– ZJS, Commented Jul 14, 2014 at 14:33

ron_g · Accepted Answer · 2020-07-30 13:39:53Z

2

I'm doing this, in case it's useful

data = [(a,b), (a,b), (a,b)]

with database.atomic():
    # try bulk
    try:
        MyModel.insert_many(data, fields=[MyModel.field1, MyModel.field2]).execute()
    # if it fails, switch to individually adding (slower)
    except IntegrityError:
        for d in data:
            try:
                entry = MyModel(
                    field1=d[0],
                    field2=d[1],)
                entry.save()
            except IntegrityError:
                pass

answered Jul 30, 2020 at 13:39

ron_g

1,7332 gold badges25 silver badges43 bronze badges

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

Comments

TheSoundDefense · Accepted Answer · 2014-07-14 14:20:11Z

1

I think if you insert everything with a single function, insert_many in this case, that's going to make it more difficult to continue in the face of an exception. My suggestion would be to insert each item one at a time, in a for loop, and wrap each individual insert in a try-except instead of the whole thing.

answered Jul 14, 2014 at 14:20

TheSoundDefense

6,9851 gold badge32 silver badges43 bronze badges

Comments

Jeff · Accepted Answer · 2014-07-14 15:11:37Z

-1

Here is what I did (thanks to ZJS):

for data in data_source:
            try:
                exists = Available_Dates.get(Available_Dates.unix_day == data['unix_day'],
                                             Available_Dates.hour == data['hour'])
            except Available_Dates.DoesNotExist:
                av_date = Available_Dates.create(**data)
                print av_date.id, "added to the database."
            except Exception as e:
                print e, type(e)
                pass

answered Jul 14, 2014 at 15:11

Jeff

4,47316 gold badges68 silver badges120 bronze badges

Collectives™ on Stack Overflow

How do I get integrity error to continue inserting into db -- python?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related