我的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个元素。你知道吗
问题源于观察结果4。和5。在烘焙查询文档的"Synopsis"下:
如果使用^{} 检查
cpe_filter()
函数,您会注意到lambda函数是常量,因此在调用之间保持它们的标识。如参考文档中所述,SQLAlchemy基于这些标识和调用缓存查询只有一次。换句话说,占位符将在您第一次基于
iterable
调用cpe_filter()
时设置。只有当此查询已从缓存中逐出时,它们才会被“重置”。你知道吗解决方案取决于所使用的DBMS。例如,Postgresql有
ANY
数组比较,可以使用:参数将作为
在MS SQL Server上,您可以创建full-text index并使用^{} :
bind参数
cpe
应该传递搜索条件,搜索条件必须由iterable
构成:当然,这需要
iterable
中的项目是有效的全文搜索词。你知道吗相关问题 更多 >
编程相关推荐