Using the web2py DAL with temp tables

Question

I'm trying to execute some raw SQL against a temp table through the web2py DAL, but my results are all returning None.

Here's the full function:

def test():

    db_test = DAL('mysql://root:root@localhost/test')

    sql = """CREATE TEMPORARY TABLE tmp LIKE people;
    INSERT INTO tmp SELECT * FROM people;
    INSERT INTO tmp SELECT * FROM people;
    SELECT * FROM tmp;"""

    results = db_test.executesql(sql)

Obviously the SQL is a simplification, but running the same SQL in a SQL pane returns the correct results. What do I need to do to get the DAL working with this?

Answer 1

You cannot execute multiple statements in one executesql call I suspect; web2py uses the DBAPI 2.0 .execute() call for sending these to the backend database and that usually supports only single statements:

db_test = DAL('mysql://root:root@localhost/test')

sqlddl = """CREATE TEMPORARY TABLE tmp LIKE people;
INSERT INTO tmp SELECT * FROM people;
INSERT INTO tmp SELECT * FROM people;"""

for statement in sqlddl.split(';'):
    db_test.executesql(statement.strip())

sqlselect = "SELECT * FROM tmp;"
results = db_test.executesql(sqlselect)