在python中包括DB函数调用MySQLdb executemany() [英] Including DB function calls in python MySQLdb executemany()

问题描述

当我尝试运行以下语句时:

When I try to run statements like:

cursor.executemany("""INSERT INTO `test` (`id`,`data`,`time_added`) 
                      VALUES (%s, %s, NOW())""", [(i.id, i.data) for i in items])

当MySQLdb扩展要插入的行的列表时，它似乎阻塞了NOW()，因为它将括号视为值块的末尾.也就是说，查询看起来像:

MySQLdb seems to choke on the ) in NOW(), when it expands the list of rows to be inserted because it sees that parenthesis as the end of the value block. That is, the queries look like:

('1', 'a', NOW(), ('2','b', NOW(), ('3','c',NOW())

MYSQL报告语法错误.相反，它们应如下所示:

And MYSQL reports a syntax error. Instead, they should look like:

('1', 'a', NOW()), ('2','b', NOW()), ('3','c',NOW())

应该有某种方法可以逃脱NOW()，但是我不知道如何去做.将'NOW()'添加到元组不起作用，因为DB会将NOW()引用并解释为字符串而不是函数调用.

There should be some way to escape the NOW(), but I can't figure out how. Adding 'NOW()' to the tuple doesn't work because then NOW() is quoted and interpreted by the DB as a string rather than a function call.

通过使用当前时间戳作为默认值来解决此问题不是一个选择-这是一个示例，我需要使用各种db函数(不仅是现在)来做这种事情.

Working around this by using current timestamp as default value is not an option -- this is an example, I need to do this sort of thing with a variety of db functions, not just now.

谢谢！

推荐答案

下面的方法远非理想之选，但不幸的是，这是我所知道的唯一方法.

The method below is far from ideal, but, unfortunately, it is the only way I know.

想法是使用connection.literal为您转义参数来手动构造SQL:

The idea is to manually construct the SQL, using connection.literal to escape the arguments for you:

cursor=connection.cursor()
args=[(1,'foo'),(2,'bar')]
sql=('INSERT INTO `foo` (`fooid`,`data`,`time_added`) VALUES '
     +','.join(
         ['(%s,%s,NOW())'%connection.literal(arg)
          for arg in args]))
cursor.execute(sql)

这看起来很可怕，并且可能会使您的皮肤爬行，但是如果您仔细查看MySQLdb在cursors.executemany中所做的事情(/usr/lib/pymodules/python2.6/MySQLdb/cursors.py)，我认为这与该函数的功能相同，减去了正则表达式cursors.insert_values不能正确解析嵌套括号的混淆. (噢！)

This looks horrible, and may make your skin crawl, but if you look under the hood (in /usr/lib/pymodules/python2.6/MySQLdb/cursors.py) at what MySQLdb is doing in cursors.executemany, I think this is along the same lines as what that function is doing, minus the mixup due the regex cursors.insert_values not correctly parsing the nested parentheses. (eek!)

我刚刚安装了oursql ，它是MySQLdb的替代方法，很高兴报告

I've just installed oursql, an alternative to MySQLdb, and am happy to report that

sql='INSERT INTO `foo` (`fooid`,`data`,`time_added`) VALUES (?,?,NOW())'
cursor.executemany(sql,args)

与oursql一起正常工作.

works as expected with oursql.

这篇关于在python中包括DB函数调用MySQLdb executemany()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！