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?

Martijn Pieters · Accepted Answer · 2012-08-25T17:18:04.737

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)

edited Aug 25 '12 at 17:18

answered Aug 25 '12 at 16:24

Martijn Pieters

1,048,767
296
4,058
3,343

thanks, but I just tried this with the same result- still returning `None`. – Yarin Aug 25 '12 at 16:28
I must say that I am very wary of web2py; for one, it'll do a `fetchall()` regardless of result set size. This will easily cause memory problems if your result set is large enough. – Martijn Pieters Aug 25 '12 at 16:34
Thanks for the update- the mulitiple execution may have been the problem, so I solved that by rolling it into a stored procedure. – Yarin Aug 25 '12 at 17:16
btw- as for being wary of web2py, not sure why- It's a great framework with a robust DAL. Your point about the `fetchall()` isn't something I know about, but seems a child-safety detail rather than something to dismiss a framework over. – Yarin Aug 25 '12 at 17:20
@Yarin: Well, it reinvents a lot of wheels as well in a monolithic codebase. – Martijn Pieters Aug 25 '12 at 17:21
2

Yes, but you don't have to attach all the wheels on your own or get them each serviced at a different service station. And some of them run faster and handle better than existing wheels. :-) – Anthony Aug 25 '12 at 23:08

Using the web2py DAL with temp tables

1 Answers1