python - SQLAlchemy Bakery - 函数需要一定的数组大小

Question

我的 Python 项目中有一个奇怪的问题。它使用 SQLAlchemy 和 Bakery 来准备查询。我有一个函数，它接受连接 (db)、面包店和对象数组。

此函数在 for 循环中被其他函数调用多次，这是我的问题(至少是我所理解的):

• 假设它第一次收到一个包含两个元素的数组。
• 下次调用该函数时，该函数还将需要一个包含两个元素的数组

import sqlalchemy as sa
def cpe_filter(db, bakery, iterable):
    cpes = []

    try:
        query  = bakery(lambda s: s.query(Cpe))
        query += lambda y: y.filter(
            sa.or_(*[
                Cpe.cpe.like(sa.bindparam('cpe_{}'.format(i)))
                for i, _ in enumerate(iterable)
            ])
        )
        query += lambda y: y.filter_by(active=sa.bindparam('active'))

        cpes = query(db).params(active=True,
                                **{'cpe_{}'.format(i): e for i, e in enumerate(iterable)}) \
                        .all()
    except NoResultFound:
        log.info("Found no CPE matching list {}.".format(iterable))

如果下一个数组小于前一个数组，我会收到此类错误( Pastebin ):

[2018-12-17 16:35:16 - INFO/sqlalchemy.engine.base.Engine:1151] SELECT cpe.id AS cpe_id, cpe.active AS cpe_active, cpe.date_created AS cpe_date_created, cpe.timestamp AS cpe_timestamp, cpe.cpe_part_id AS cpe_cpe_part_id, cpe.device_id AS cpe_device_id, cpe.cpe AS cpe_cpe, cpe.match_nvd AS cpe_match_nvd
FROM cpe
WHERE (cpe.cpe LIKE %(cpe_0)s OR cpe.cpe LIKE %(cpe_1)s OR cpe.cpe LIKE %(cpe_2)s) AND cpe.active = %(active)s
[2018-12-17 16:35:16 - INFO/sqlalchemy.engine.base.Engine:1154] {'cpe_0': 'cpe:/o:sun:solaris', 'cpe_1': 'cpe:/a:tritreal:ted_cde', 'cpe_2': 'cpe:/o:hp:hp-ux', 'active': 1}
[2018-12-17 16:35:16 - INFO/sqlalchemy.engine.base.Engine:1151] SELECT cpe.id AS cpe_id, cpe.active AS cpe_active, cpe.date_created AS cpe_date_created, cpe.timestamp AS cpe_timestamp, cpe.cpe_part_id AS cpe_cpe_part_id, cpe.device_id AS cpe_device_id, cpe.cpe AS cpe_cpe, cpe.match_nvd AS cpe_match_nvd
FROM cpe
WHERE (cpe.cpe LIKE %(cpe_0)s OR cpe.cpe LIKE %(cpe_1)s OR cpe.cpe LIKE %(cpe_2)s) AND cpe.active = %(active)s
[2018-12-17 16:35:16 - INFO/sqlalchemy.engine.base.Engine:1154] {'cpe_0': 'cpe:/a:hp:dtmail', 'cpe_1': 'cpe:/a:university_of_washington:pine', 'cpe_2': 'cpe:/o:sco:unixware', 'active': 1}
[2018-12-17 16:35:16 - ERROR/scap.abc:66] An error has occurred during task execution.
Traceback (most recent call last):
  File "/root/.local/share/virtualenvs/scap-TS2Ah8Sl/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1127, in _execute_context
    context = constructor(dialect, self, conn, *args)
  File "/root/.local/share/virtualenvs/scap-TS2Ah8Sl/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 635, in _init_compiled
    grp, m in enumerate(parameters)]
  File "/root/.local/share/virtualenvs/scap-TS2Ah8Sl/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 635, in <listcomp>
    grp, m in enumerate(parameters)]
  File "/root/.local/share/virtualenvs/scap-TS2Ah8Sl/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 547, in construct_params
    % bindparam.key, code="cd3x")
sqlalchemy.exc.InvalidRequestError: A value is required for bind parameter 'cpe_2' (Background on this error at: http://sqlalche.me/e/cd3x)

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

Traceback (most recent call last):
  File "/root/scap/project/scap/abc.py", line 64, in run
    self(*args, **kwargs)
  File "/root/scap/project/scap/tasks.py", line 362, in __call__
    q.cve_insert_or_update(self.db, self.bakery, self.parse(name))
  File "/root/scap/project/scap/queries.py", line 148, in cve_insert_or_update
    cpes = list(cpe_filter(db, bakery, cpes))
  File "/root/scap/project/scap/queries.py", line 68, in cpe_filter
    **{'cpe_{}'.format(i): e for i, e in enumerate(products)}) \
  File "/root/.local/share/virtualenvs/scap-TS2Ah8Sl/lib/python3.6/site-packages/sqlalchemy/ext/baked.py", line 457, in all
    return list(self)
  File "/root/.local/share/virtualenvs/scap-TS2Ah8Sl/lib/python3.6/site-packages/sqlalchemy/ext/baked.py", line 364, in __iter__
    return q._execute_and_instances(context)
  File "/root/.local/share/virtualenvs/scap-TS2Ah8Sl/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3018, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/root/.local/share/virtualenvs/scap-TS2Ah8Sl/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/root/.local/share/virtualenvs/scap-TS2Ah8Sl/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/root/.local/share/virtualenvs/scap-TS2Ah8Sl/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/root/.local/share/virtualenvs/scap-TS2Ah8Sl/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1132, in _execute_context
    None, None)
  File "/root/.local/share/virtualenvs/scap-TS2Ah8Sl/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/root/.local/share/virtualenvs/scap-TS2Ah8Sl/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/root/.local/share/virtualenvs/scap-TS2Ah8Sl/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
    raise value.with_traceback(tb)
  File "/root/.local/share/virtualenvs/scap-TS2Ah8Sl/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1127, in _execute_context
    context = constructor(dialect, self, conn, *args)
  File "/root/.local/share/virtualenvs/scap-TS2Ah8Sl/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 635, in _init_compiled
    grp, m in enumerate(parameters)]
  File "/root/.local/share/virtualenvs/scap-TS2Ah8Sl/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 635, in <listcomp>
    grp, m in enumerate(parameters)]
  File "/root/.local/share/virtualenvs/scap-TS2Ah8Sl/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 547, in construct_params
    % bindparam.key, code="cd3x")
sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) A value is required for bind parameter 'cpe_2' [SQL: 'SELECT cpe.id AS cpe_id, cpe.active AS cpe_active, cpe.date_created AS cpe_date_created, cpe.timestamp AS cpe_timestamp, cpe.cpe_part_id AS cpe_cpe_part_id, cpe.device_id AS cpe_device_id, cpe.cpe AS cpe_cpe, cpe.match_nvd AS cpe_match_nvd \nFROM cpe \nWHERE (cpe.cpe LIKE %(cpe_0)s OR cpe.cpe LIKE %(cpe_1)s OR cpe.cpe LIKE %(cpe_2)s) AND cpe.active = %(active)s'] [parameters: [{'active': True, 'cpe_0': 'cpe:/a:university_of_washington:imap', 'cpe_1': 'cpe:/a:netscape:messaging_server'}]] (Background on this error at: http://sqlalche.me/e/cd3x)

正如您所看到的，该函数被调用了三次，前两次它工作没有任何问题(每次 3 个元素)，第三次它只有两个元素，根据错误，它期望第三个元素.

注意:可迭代对象大多数时候可以达到大约 50 个元素。

Answer 1

问题源于"Synopsis"下的观察4.和5.在烘焙查询文档中:

4. In the above code, even though our application may call upon search_for_user() many times, and even though within each invocation we build up an entirely new BakedQuery object, all of the lambdas are only called once. Each lambda is never called a second time for as long as this query is cached in the bakery.
5. The caching is achieved by storing references to the lambda objects themselves in order to formulate a cache key; that is, the fact that the Python interpreter assigns an in-Python identity to these functions is what determines how to identify the query on successive runs. For those invocations of search_for_user() where the email parameter is specified, the callable lambda q: q.filter(User.email == bindparam('email')) will be part of the cache key that’s retrieved; when email is None, this callable is not part of the cache key.

如果您使用 dis 检查您的 cpe_filter() 函数您会注意到 lambda 函数是常量，因此在调用之间保持其标识。正如引用文档中所解释的，SQLAlchemy 根据这些身份和调用来缓存查询

query += lambda y: y.filter(
    sa.or_(*[
        Cpe.cpe.like(sa.bindparam('cpe_{}'.format(i)))
        for i, _ in enumerate(iterable)
    ])
)

仅一次。换句话说，占位符将在您第一次调用 cpe_filter() 时基于 iterable 设置。仅当此查询已从缓存中逐出时，它们才会“重置”。

解决方案取决于您使用的 DBMS。例如，Postgresql 具有可以使用的 ANY 数组比较:

query += lambda y: y.filter(Cpe.cpe.like(sa.any_(sa.bindparam('cpe'))))

参数将作为

传递

# This relies on Psycopg2's lists adaptation:
# http://initd.org/psycopg/docs/usage.html#lists-adaptation
cpes = query(db).params(active=True, cpe=list(iterable)).all()

在 MS SQL Server 上，您也许可以创建 full-text index并使用CONTAINS :

query += lambda y: y.filter(func.contains(Cpe.cpe, sa.bindparam('cpe')))

绑定(bind)参数cpe应传递搜索条件，该条件必须由iterable构成:

search_cond = " OR ".join(iterable)
cpes = query(db).params(active=True, cpe=search_cond).all()

这当然要求 iterable 中的项目是有效的全文搜索词。