PROSAGA码农传奇-python-使SQLAlchemy错误更加用户友好和详细

0# 圈圈红 | 2019-08-31 10-32

1# 不想吃东西 | 2019-08-31 10-32

2# نسر الصحراء | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”>
  <P>
    SQLAlchemy包含一种允许使用的自定义DBAPI错误的机制
    <a href="https://docs.sqlalchemy.org/en/latest/core/events.html?highlight=handle_error#sqlalchemy.events.ConnectionEvents.handle_error" rel="nofollow noreferrer">
      了handle_error
    </A>
     事件钩子。我在Openstack中使用了这个API
    <a href="https://github.com/openstack/oslo.db" rel="nofollow noreferrer">
      oslo.db
    </A>
     可以在这个文件中看到：
    <a href="https://github.com/openstack/oslo.db/blob/master/oslo_db/sqlalchemy/exc_filters.py" rel="nofollow noreferrer">
      https://github.com/openstack/oslo.db/blob/master/oslo_db/sqlalchemy/exc_filters.py
    </A>
    。
  </p>
  <P>
    由于stackoverflow讨厌代码链接，这里是基于上述链接方法的POC：
  </p>
   <pre>
    <code>
      import collections
from sqlalchemy import event
from sqlalchemy import exc as sqla_exc
import re

class DuplicateKeyError(Exception):
    """Duplicate entry at unique column error."""

def __init__(self, columns=None, inner_exception=None, value=None):
        self.columns = columns or []
        self.value = value
        self.inner_exception = inner_exception

def __str__(self):
        return "Duplicate key for columns %s" % (
            self.columns,
        )

_registry = collections.defaultdict(lambda: collections.defaultdict(list))

def filters(ame, exception_type, regex):
    """Mark a function as receiving a filtered exception."""

def _receive(fn):
        _registry[ame][exception_type].extend(
            (fn, re.compile(reg))
            for reg in ((regex,) if not isinstance(regex, tuple) else regex)
        )
        return fn

return _receive

# each @filters() lists a database name, a SQLAlchemy exception to catch,
# and a list of regular expressions that will be matched.  If all the
# conditions match, the handler is called which then raises a nicer
# error message.

@filters(
    "sqlite",
    sqla_exc.IntegrityError,
    (
        r"^.*columns?(?P<columns>[^)]+)(is|are)\s+not\s+unique$",
        r"^.*UNIQUE\s+constraint\s+failed:\s+(?P<columns>.+)$",
        r"^.*PRIMARY\s+KEY\s+must\s+be\s+unique.*$",
    ),
)
def _sqlite_dupe_key_error(integrity_error, match, engine_name, is_disconnect):
    columns = []
    try:
        columns = match.group("columns")
        columns = [c.split(".")[-1] for c in columns.strip().split(", ")]
    except IndexError:
        pass

raise DuplicateKeyError(columns, integrity_error)

@filters(
    "mysql",
    sqla_exc.IntegrityError,
    r"^.*\b1062\b.*Duplicate entry '(?P<value>.*)'"
    r" for key '(?P<columns>[^']+)'.*$",
)
@filters(
    "postgresql",
    sqla_exc.IntegrityError,
    (
        r'^.*duplicate\s+key.*"(?P<columns>[^"]+)"\s*\n.*'
        r"Key\s+$(?P<key>.*)$=$(?P<value>.*)$\s+already\s+exists.*$",
        r"^.*duplicate\s+key.*\"(?P<columns>[^\"]+)\"\s*\n.*$",
    ),
)
def _default_dupe_key_error(
    integrity_error, match, engine_name, is_disconnect
):
    columns = match.group("columns")
    uniqbase = "uniq_"
    if not columns.startswith(uniqbase):
        if engine_name == "postgresql":
            columns = [columns[columns.index("_") + 1 : columns.rindex("_")]]
        else:
            columns = [columns]
    else:
        columns = columns[len(uniqbase) :].split("0")[1:]

value = match.groupdict().get("value")

raise DuplicateKeyError(columns, integrity_error, value)

def handler(context):
    """Iterate through available filters and invoke those which match.
    The first one which raises wins.
    """

def _dialect_registries(engine):
        if engine.dialect.name in _registry:
            yield _registry[engine.dialect.name]
        if "*" in _registry:
            yield _registry["*"]

for per_dialect in _dialect_registries(context.engine):
        for exc in (context.sqlalchemy_exception, context.original_exception):
            for super_ in exc.__class__.__mro__:
                if super_ in per_dialect:
                    regexp_reg = per_dialect[super_]
                    for fn, regexp in regexp_reg:
                        match = regexp.match(exc.args[0])
                        if match:
                            fn(
                                exc,
                                match,
                                context.engine.dialect.name,
                                context.is_disconnect,
                            )

if __name__ == '__main__':
    from sqlalchemy import Column, Integer, String, create_engine
    from sqlalchemy.orm import Session
    from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Company(Base):
        __tablename__ = "my_table"
        id = Column(Integer(), primary_key=True)
        name = Column(String(128), unique=True, nullable=False)
        slug = Column(String(128), unique=True, nullable=False)

def __init__(self, name, slug):
            self.name = name
            self.slug = slug

e = create_engine("sqlite://", echo=True)
    Base.metadata.create_all(e)
    event.listen(e, "handle_error", handler)

s = Session(e)
    s.add(Company("Test", "test"))
    s.commit()

s.add(Company("Test", "test"))
    s.commit()

</code>
  </pre>
  <P>
    运行它，我们看到：
  </p>
   <pre>
    <code>
      2019-03-13 09:44:51,701 INFO sqlalchemy.engine.base.Engine INSERT INTO my_table (name, slug) VALUES (?, ?)
2019-03-13 09:44:51,701 INFO sqlalchemy.engine.base.Engine ('Test', 'test')

2019-03-13 09:44:53,387 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
# ...
sqlite3.IntegrityError: UNIQUE constraint failed: my_table.slug

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
 # ...
__main__.DuplicateKeyError: Duplicate key for columns ['slug']

</code>
  </pre>
</DIV>