从Django调用MySQL中的DATE_FORMAT（）失败

I can run SET statements to assign variables and use "transaction" to maintain it in the mySQL session, but when I include the function DATE_FORMAT like this:

我可以运行SET语句来分配变量,并使用“事务”来维护它在mySQL会话中,但是当我包含函数DATE_FORMAT时,如下所示:

cursor.execute("SET @dowToday:=CAST( DATE_FORMAT( NOW(), '%w' ) AS UNSIGNED);")

Django complains that

Django抱怨说

not enough arguments for format string in /usr/lib/pymodules/python2.6/MySQLdb/cursors.py in execute, line 151

执行中的/usr/lib/pymodules/python2.6/MySQLdb/cursors.py中的格式字符串参数不足,第151行

It doesn't help to remove the CAST or to play with or escape the quotes.

删除CAST或使用或转义引号无效。

Thoughts?

3 个解决方案

#1

I totally missed the point in my previous answer. This is a Django cursor. Not a "standard Python" one.

我完全错过了我之前回答中的观点。这是一个Django游标。不是“标准Python”之一。

According to the doc:

根据文件:

https://docs.djangoproject.com/en/dev/topics/db/sql/

Note that if you want to include literal percent signs in the query, you have to double them in the case you are passing parameters:

请注意,如果要在查询中包含文字百分号,则必须在传递参数的情况下将它们加倍:
cursor.execute("SELECT foo FROM bar WHERE baz = '30%%' and id = %s", [self.id])

I assume if this does not work, your could fallback to some simple-and-stupid trick:

我假设如果这不起作用,你可以回退到一些简单而愚蠢的伎俩:

cursor.execute("SET @dowToday:=CAST( DATE_FORMAT( NOW(), '%sw' ) AS UNSIGNED);", ['%'])

The %s will be replaced by the % passed as an argument...

%s将被作为参数传递的%替换...

#2

Are you sure the error is produced by that call?

你确定该电话会产生错误吗?

Looking at the code of cursor.py on my system, that leads to:

查看我的系统上的cursor.py代码,这会导致:

def execute(self, query, args=None):

    # ...

    if isinstance(query, unicode):
        query = query.encode(charset)
    if args is not None:
        query = query % db.literal(args)         # Line 151

According to that, this exception could only be thrown if args is not None -- which seems impossible considering the call:

根据这一点,只有当args不是None时才会抛出此异常 - 考虑到调用这似乎是不可能的:

cursor.execute("SET @dowToday:=CAST( DATE_FORMAT( NOW(), '%w' ) AS UNSIGNED);")

#3

My guess is that the issue is with the percent sign (%) in the query text. (Isn't that a bind variable placeholder in Django?) e.g. if we were going to use a bind variable, wouldn't that look something like this?

我的猜测是问题在于查询文本中的百分号(%)。 (这不是Django中的绑定变量占位符吗?)例如如果我们要使用绑定变量,那么看起来不会是这样吗?

SELECT 'foo' FROM DUAL WHERE 'a' = %(varname)s ;

I think maybe Django is scanning your SQL text and encountering %w and expecting that to be a bind variable. Either that, or it's running an sprintf style function, and encountering the %w and expecting to replace that placeholder with an argument value.

我想也许Django正在扫描你的SQL文本并遇到%w并期望它是一个绑定变量。或者,它正在运行sprintf样式函数,遇到%w并期望用参数值替换该占位符。

(I haven't tested; so this is just an idea, just a guess.)

(我没有测试过;所以这只是一个想法,只是一个猜测。)

As a guess to a workaround, maybe you double up the percent signs, the same we get % literals through an sprintf:

作为一种解决方法的猜测,也许你将百分号加倍,同样我们通过sprintf获得%literals:

  query("SELECT ... ,'%%w') ...");

If that doesn't work, then maybe it's a backslash character, the same we escape characters in a regular expression:

如果这不起作用,那么也许它是一个反斜杠字符,同样我们在正则表达式中转义字符:

  query("SELECT ... ,'\%w') ...");

(Or, you might need to double up the backslashes. These are just guesses based on conventions used by other software.)

(或者,您可能需要将反斜杠加倍。这些只是基于其他软件使用的惯例的猜测。)

#1