我如何得到完整性错误继续插入到db——python

Question

我有一个postgres数据库，看起来像这样:

                                                        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

它连接到我正在开发的rails应用程序和一个作为服务运行的python应用程序，我使用peewee作为python的形式。rails应用程序运行良好，我需要这个数据库上的每个索引。

python应用程序查看文件，并向db添加日期。我遇到的问题是，如果日期+小时存在，它会失败并结束python应用程序。我不想在数据库中复制，但如果抛出此完整性错误，我也希望应用程序继续运行。这是我到目前为止的代码:

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

它失败了(它应该):

重复键值违反了唯一约束"index_available_dates_on_unix_day_and_hour"DETAIL: Key (unix_day, hour)=(1370044800,10)已经存在。

我如何让我的代码显示"That failed?"哦……我就试试下一个。"

注意:我认为它失败的可能性大于成功的可能性。

编辑:选项1根据第一个答案:

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

这似乎不太好，因为我正在捕捉所有异常…此外，我必须分别分配每个成员，这似乎效率低下。它也会失败，因为数据库上有一个锁。

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
 ...

Answer 1

我这样做是为了方便使用

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

Answer 2

我认为，如果您使用单个函数(在本例中为insert_many)插入所有内容，那么在面对异常时将更加难以继续。我的建议是每次插入一个项目，在for循环中，并将每个单独的插入包装在try-except中，而不是整个东西。

Answer 3

我是这样做的(感谢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