SQLAlchemy Bakery函数需要特定的数组大小

Question

我的Python项目中有一个奇怪的问题。它使用SQLAlchemy和Bakery来准备查询。我有一个函数，它接受连接（db）、bakery和一个对象数组。你知道吗

这个函数被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个元素），第三次调用只有两个元素，并且根据错误需要第三个元素。你知道吗

注：iterable大多数时候可以达到50个元素。你知道吗

Answer 1

问题源于观察结果4。和5。在烘焙查询文档的"Synopsis"下：

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.

如果使用^{}检查cpe_filter()函数，您会注意到lambda函数是常量，因此在调用之间保持它们的标识。如参考文档中所述，SQLAlchemy基于这些标识和调用缓存查询

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

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

解决方案取决于所使用的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并使用^{}：

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中的项目是有效的全文搜索词。你知道吗