检查SQLite中的数据是否满足“xy范围内的数字”之类的约束条件

Question

我正在编写一个Python脚本，它使用质量可疑的数据。数据存储在SQLite数据库中。在

我想要一个紧凑的方式来指定数据的约束。约束有两种类型：

1. 数据错误-将发出错误消息。
   • “A列必须是0-10范围内的整数”
   • “B列必须是非空字符串”，以此类推。在
2. 数据质量警告-“是否确定这是正确的将发出警告消息。约束条件应该是
   • “如果C列的默认值为0，则警告”—您确定打字员没有漏掉一个条目吗？在
   • “如果D列中的数字异常大（>1000），则发出警告”。在

理想情况下，我希望以人类可读的格式表达我的约束，例如：

'kV' MUST BE float IN RANGE 0-10
'Rating' SHOULD NOT BE DEFAULT 1.0
'Description' SHOULD NOT BE DEFAULT ""

。。。但我会对我目前的方法做任何改进（如下）。我很乐意接受一个解决方案，它涉及在Python或SQLite模式中强制约束。在

我现在使用的是：

^{pr2}$

输出示例：

kV = 11.0 : OK
kA1 = 1.0 : OK
kA1 = 1.0 : default value of 1.0 - make sure this is what you want.

编辑：我知道在Python中显式检查类型是不礼貌的。但是，为了使用数据的代码，我需要检查SQLite是否在列中存储了意外的东西（“hello world”在INT列中，等等）。记住数据的质量是可疑的，SQLite会很乐意将任何类型的数据放在任何列中。捕捉这些类型的数据输入错误是本代码的目标之一。在

Answer 1

如果您愿意使用稍微复杂一点的实现，可以使用字典：

规范可以是：

{
'kv':{'type':'float','range':(0,10)},
'Rating':{'not':1.0}
}

Answer 2

结合@onedaywhen使用SQL检查约束的思想和@ABS以更易读的方式定义约束的思想，我得出了如下结论。在

把它包装在一个类中可能不是特别有用（在这个例子中，它是check()函数的一个华丽的包装器），但这意味着我可以在以后将一些稍微好一点的输出格式放入其中。在

class Constraint:
    def __init__(self, table, column, constraint, error_or_warning):
        """
        examples:
            Constraint('Lib_Cable','kV', '> 0', 'error')
            Constraint('Lib_Cable','Insulation', '!= 0', 'warning')

        """
        self.table, self.column, self.constraint, self.error_or_warning = \
        table, column, constraint, error_or_warning

    def check (self, db_conn):
        c = db_conn.cursor()
        c.row_factory = sqlite3.Row

        query = "SELECT * FROM %(table)s WHERE NOT (%(column)s %(constraint)s)" % \
                {'table'      : self.table,
                 'column'     : self.column,
                 'constraint' : self.constraint }
        # [FIXME] start a transaction here - guard against novice SQL injections?
        res = c.execute(query)

        for row in res:
            print ( "%(error_or_warning)s: Row with key %(key)s in table %(table)s violates constraint %(column)s %(constraint)s)." % \
                {'key'        : row['KeyName'],
                 'table'      : self.table,
                 'column'     : self.column,
                 'constraint' : self.constraint,
                 'error_or_warning' : self.error_or_warning} )

        # [FIXME] discard transaction

Constraint_1 = Constraint('Lib_Cable', 'Insulation', '!= 0', 'error')        
Constraint_1.check(db_conn)

Answer 3

以下条款可能会引起注意：

Verbalizing Business Rules by Terry Halpin

Alethic rules impose necessities, which cannot, even in principle, be violated by the business, typically because of some physical or logical law. For example: each employee was born on at most one date; no product is a component of itself. Deontic rules impose obligations, which may be violated, ven though they ought not. For example: it is obligatory that each employee is married to at most one person; no smoking is permitted in any office.

从SQL的角度来看，编写一个查询来返回违反规则的数据，例如

SELECT * 
  FROM T 
 WHERE Column_A < 0

然后测试每个规则是否为空集。注意使它们细化，例如分别对Column_A < 0和{}分别进行测试。在