I think the DBAPI doesn't support several results set in a single statement.
If you cared to do those one line at a time you'd noticed that:
create temporary table tmp like people does not return results.
insert into tmp select * from people returns the just inserted values
select * from tmp returns your lines.
I think that as long as you have only one statement (and it's the last ) in
your executesql() wrapped string you'll be fine. If some of your lines
returns results, then the driver doesn't know what set to actually return.
Anyway splitting those lines in multiple db.executesql()s is working great.
On Saturday, August 25, 2012 5:58:40 PM UTC+2, Yarin wrote:
>
> I'm trying to execute some SQL from the DAL that relies on a temp table:
>
> CREATE TEMPORARY TABLE tmp LIKE people;
>
> INSERT INTO tmp SELECT * FROM people;
> INSERT INTO tmp SELECT * FROM people;
> SELECT * FROM tmp;
>
>
> This code works fine in a SQL panel, but fails when used in the DAL:
>
> 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)
>
>
> Results always returns None. No idea why or how to proceed.
>
--
