How to use Executemany in my situation?
How can I get these sequence of SQL statements? to 开发者_Go百科work? I have previously only dealt with single select
statements and cursor.execute
worked fine for that. I'm not sure what to do in this case now. The error I am getting is format requires a mapping
args = {
"timepattern" : timepattern,
"datestart_int" : datestart_int,
"dateend_int" : dateend_int
}
sql = ( "CREATE TEMPORARY TABLE cohort_users (user_id INTEGER); "
"INSERT INTO cohort_users (user_id) "
"SELECT id FROM users WHERE registered_at BETWEEN %(datestart_int)s AND %(dateend_int)s; "
"SELECT 1, FROM_UNIXTIME(%(dateend_int)s, %(timepattern)s) "
"UNION ALL "
"SELECT (COUNT(DISTINCT x.user_id)/(SELECT COUNT(1) FROM cohort_users)), "
" FROM_UNIXTIME((%(dateend_int)s + (7 * 24 * 60 * 60)), %(timepattern)s) "
"FROM cohort_users z INNER JOIN actions x ON x.user_id = z.id "
"WHERE x.did_at BETWEEN (%(datestart_int)s + (7 * 24 * 60 * 60)) AND (%(dateend_int)s + (7 * 24 * 60 * 60)) "
"DROP TABLE cohort_users; "
)
cursor.executemany(sql,args)
Let's assume that your database software supports argument placeholders of the form %(name)s.
Let's also assume that it supports multiple statements in one "operation". Note: the 3rd statement (SELECT ... UNION ALL SELECT ...) is missing a semicolon at the end.
In that case, all you need to do is use cursor.execute(sql, args)
... executemany()
is used with a sequence of args (e.g. to do multiple INSERTs).
For portability and ease of debugging, it would be preferable to do the four statements one at a time.
Using triple quotes (and structured indenting instead of wide indenting) will make your SQL easier to read:
sql = """
CREATE TEMPORARY TABLE cohort_users (user_id INTEGER);
INSERT INTO cohort_users (user_id)
SELECT id
FROM users
WHERE registered_at BETWEEN %(datestart_int)s AND %(dateend_int)s
;
SELECT 1, FROM_UNIXTIME(%(dateend_int)s, %(timepattern)s)
UNION ALL
SELECT (COUNT(DISTINCT x.user_id)/(SELECT COUNT(1) FROM cohort_users)),
FROM_UNIXTIME((%(dateend_int)s + (7 * 24 * 60 * 60)), (timepattern)s)
FROM cohort_users z
INNER JOIN actions x ON x.user_id = z.id
WHERE x.did_at BETWEEN (%(datestart_int)s + (7 * 24 * 60 * 60))
AND (%(dateend_int)s + (7 * 24 * 60 * 60))
;
DROP TABLE cohort_users;
"""
executemany() is for executing the same single statement on multiple arguments.
What you want is the other way around : just call execute() on each statement.
精彩评论