SQLAlchemy错误：“参数格式不能混合”输入变量时 [英] SQLAlchemy error: "argument formats can't be mixed" when inputting variables

问题描述

我有一个Python脚本，该脚本通过SQLAlchemy的connection.execute函数运行pgSQL文件。以下是Python中的代码块：

I have a Python script that runs a pgSQL file through SQLAlchemy's connection.execute function. Here's the block of code in Python:

results = pg_conn.execute(sql_cmd, beg_date = datetime.date(2015,4,1), end_date = datetime.date(2015,4,30))

这是其中之一在我的SQL中输入变量的区域：

And here's one of the areas where the variable gets inputted in my SQL:

WHERE
    (   dv.date >= %(beg_date)s AND
        dv.date <= %(end_date)s)

当我跑步时这，我得到一个神秘的python错误：

When I run this, I get a cryptic python error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) argument formats can't be mixed

…之后是大量令人讨厌的SQL查询转储。我之前已经使用相同的变量约定运行了这些确切的代码。为什么这次不能正常工作？

…followed by a huge dump of the offending SQL query. I've run this exact code with the same variable convention before. Why isn't it working this time?

推荐答案

我遇到了与Nikhil类似的问题。我有一个带有 LIKE 子句的查询，直到将其修改为包含绑定变量后，该子句才起作用，此时我收到以下错误：

I encountered a similar issue as Nikhil. I have a query with LIKE clauses which worked until I modified it to include a bind variable, at which point I received the following error:

DatabaseError: Execution failed on sql '...': argument formats can't be mixed

解决方案是不放弃LIKE子句。如果psycopg2根本不允许LIKE子句，那将是非常疯狂的事情。相反，我们可以使用 %% 转义文字％。例如，以下查询：

The solution is not to give up on the LIKE clause. That would be pretty crazy if psycopg2 simply didn't permit LIKE clauses. Rather, we can escape the literal % with %%. For example, the following query:

SELECT *
FROM people
WHERE start_date > %(beg_date)s
AND name LIKE 'John%';

需要修改为：

SELECT *
FROM people
WHERE start_date > %(beg_date)s
AND name LIKE 'John%%';

pscopg2文档中的更多详细信息： http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

More details in the pscopg2 docs: http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

这篇关于SQLAlchemy错误：“参数格式不能混合”输入变量时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！