什么是通过python2.7中的SqlAlchemy检查数据库表中唯一值是否存在的好方法 [英] What is the good approach to check an existance of unique values in database table by SqlAlchemy in python2.7

问题描述

我在SqlAlchemy中使用 session.merge 来通过 UniqueConstraint 将数据插入表中列，例如，我的ORM类具有以下内容：

I use session.merge in SqlAlchemy to insert data to the table with UniqueConstraint, which is common for several columns, for example, my ORM class has the following:

UniqueConstraint("attr_1", "attr_2", "attr_3", name="attributes_uix")

我应该使用 filter 或 filter_by 和 _and 来检查是否要插入的数据违反了此唯一约束，还是通过 try捕获 IntegrityError 的正确方法-除了构造以外？

Should I use filter or filter_by with _and to check, if data, which I'm going to insert will violate this unique constraint, or is it the proper way to catch IntegrityError by try - except construction?

推荐答案

虽然没有SQL炼金术专家，但一段时间以来一直在解决这个问题和类似问题。我不确定在这种情况下是否有正确的方法。我可以为您描述的是在获取数据库异常和在代码中对其进行处理之间的权衡。

No an sql-alchemy expert but have struggled with this and similar questions for a while. I am not sure if there is a single right way in this case. What I can describe for you is what the tradeoff between grabbing db exceptions and handling it in your code are.

我个人更喜欢捕获数据库异常。这具有不运行额外查询（具有额外延迟等）的优点。它还具有尽可能多地重用现有框架的优点。

Personally I prefer to trap db exceptions. This has the advantage of not running extra queries (with extra latency, etc). It also has the advantage of reusing existing framework pieces as much as possible.

但是它有两个实际缺点。第一个可以在db设计级别上解决，它是包含NULL值的匹配不违反多列唯一约束。换句话说，如果我在（id，value）上有一个cnstraint，那么（1，null），（1，null）不能说是唯一的，因此，不会引发完整性异常。因此，首先值得检查一下是否可以避免此问题。如果不是这样，则需要找到一种解决方法，但是PostgreSQL提供了很多工具（例如，功能索引）。

However it has two real drawbacks. The first, which can be solved on the db design level, is that multi-column unique constraints are not violated by matches containing NULL values. In other words, if I have aa cnstraint on (id, value), then (1, null), (1, null) cannot be said to be non-unique and therefore no integrity exception is thrown. So first it is worth checking to see if you can avoid this problem. If not, you need to find a workaround, but PostgreSQL gives you pleny of tools there (for example, functional indexes).

第二个是捕获IntegrityError是一种钝器，您实际上无法解析可用于索引名称的任何错误消息，因为这在系统之间可能会有所不同，并且区域设置可能意味着消息可能会被翻译。因此，您依赖SQLSTATE，这不是很精确。即您没有良好的机器可读信息。

The second is that catching IntegrityError is a kind of a blunt instrument and you can't really parse any error message available for the index name because this may vary between systems and the locale can mean the message may be translated. So you are relying on SQLSTATE and this is not very precise. I.e. you don't have good machine readable information. about which constraint was voilated if there are multiple candidates.

然后的问题是在这些约束与重复的代码和查询之间。通常我认为抓住错误是次要的事情，但是由于权衡取舍，这并不是一个普遍的建议。

The question then is between these and duplicating code and queries. Usually I think catching the error is the lesser evil, but due to the tradeoffs it is not a universal recommendation.

这篇关于什么是通过python2.7中的SqlAlchemy检查数据库表中唯一值是否存在的好方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！