How to use Executemany in my situation?

Question

How can I get these sequence of SQL statements? to 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)

score 2 · Accepted Answer · answered May 09 '11 at 12:02

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;
"""

I'm having this problem which I think might be documented here:http://bugs.mysql.com/bug.php?id=10327. The full SQL consists of a few more unions referencing the `cohort_users` table. — super9, May 10 '11 at 04:59
@Nai: That is nothing to do with the question that you asked. According to the link that you gave, the error message for multiple mentions of a temp table is `ERROR 1137: Can't reopen table: `. The message that you said that you got was `format requires a mapping`. Looks like you have moved forward to your *NEXT* problem. Please ignore the next problem when deciding which (if any) answer fits *THIS* question. — John Machin, May 10 '11 at 05:15
@Nai: I'm not a mysql expert ... the only thing that I can think of is to break that big SELECT into multiple SELECTs and do your own "UNION ALL" in Python. By the way, you had `z.id` where it should have been `z.user_id`. — John Machin, May 10 '11 at 05:41
ah thanks good spot :P. The workaround I found is to do this: `CREATE TABLE cohort_users ENGINE = MeMORY` FYI. — super9, May 10 '11 at 05:44

score 1 · Answer 2 · answered May 09 '11 at 11:12

1

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.

answered May 09 '11 at 11:12

bobflux

11,123
3
27
27

How to use Executemany in my situation?

2 Answers2