Including DB function calls in python MySQLdb executemany ()

Question

When I try to run expressions such as:

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

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

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

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

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

There must be some way to avoid NOW (), but I cannot figure out how to do this. Adding 'NOW ()' to the tuple does not work, because NOW () is quoted and interpreted by the database as a string, not a function call.

Working with this by using the current timestamp by default is not an option - this is an example, I need to do similar things using various db functions, and not just now.

Thank!

Answer 1

, , , , .

, SQL, connection.literal, :

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)

, (/usr/lib/pymodules/python2.6/MySQLdb/cursors.py) , MySQLdb cursors.executemany, , , , mixup - cursors.insert_values . (!)

---

oursql, MySQLdb, ,

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

, , oursql.