Running multiple lines of SQL in executesql

[naveed]

I need to execute multiple lines of SQL in web2py, something like this:

    db.executesql("set @num := 0, @type := '';")\
    rows = db.executesql("select type, variety, price\
    from (\
       select type, variety, price,\
          @num := if(@type = type, @num + 1, 1) as row_number,\
          @type := type as dummy\
      from fruits\
      order by type, price\
    ) as x where x.row_number <= 2;")

But, when I run it I don't get expected results in web2py (the variables don't get initialized), even though it works from the mysql console. What am I doing wrong?

They don't work, even if I put the variable initialization in the same call. This piece of SQL is from here: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

[Niphlod]

executesql doesn't allow more than one statement per invocation.
You'll get the same issues running execute() with the underlying driver.
What are you really trying to execute isn't a query: it's more of a stored procedure that returns a set of results.

[naveed]

Ok, I moved it to a stored procedure and it works. Thanks for your help.

It would be nice to be able to run multiple queries in one call - I prefer to keep my code in code, not in the database, but I'm probably asking for too much. 

 

[Brian M]

Actually just surround your big long multi line SQL statement with triple quotes and it will work fine. I do that all the time. No need for the \ at each line break then either. You can declare and set your @variables all within one executesql query too.

[Naveed Ahmed]

Thank you, I tried this:

 

    db.executesql("""set @num := 0, @type := '';

    select type, variety, price
    from (
       select type, variety, price,
          @num := if(@type = type, @num + 1, 1) as row_number,
          @type := type as dummy
      from fruits
      order by type, price
    ) as x where x.row_number <= 2;""")

But it returns a null.

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/SQEWZzMQ4Gw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Brian M]

Strange. Does that same query work if you enter it directly in your DB's interface? I'm guessing from the link you provided that you're using MySQL; I've only done it with MS SQL and SQLite but I'd think that the triple quoting technique should work with other databases too.

On Wednesday, March 11, 2015 at 11:09:58 AM UTC-5, naveed wrote:

Thank you, I tried this:

 

    db.executesql("""set @num := 0, @type := '';
    select type, variety, price
    from (
       select type, variety, price,
          @num := if(@type = type, @num + 1, 1) as row_number,
          @type := type as dummy
      from fruits
      order by type, price
    ) as x where x.row_number <= 2;""")

But it returns a null.

 

 

[Naveed Ahmed]

Yes it works from the DB’s interface and yes, I’m using MySQL.