sqlobject-discuss

[SQLObject] Help with SQLBuilder NOTIN

[Jason H. <jas...@gm...>]

Hi,

This is on Python 2.5.5 with SQLObject 0.11.0 and MySQL  5.1.41-3 (on
Debian Squeeze).

I'm a little mystified about the SQLBuilder subselector NOTIN(). I
have the following MySQL query:

SELECT db_stock_code FROM stock WHERE
    (db_stock_code LIKE "XXX%")
    AND (id NOT IN (SELECT db_stock_id FROM cont_item));

...which works for me. "stock" is a very simple table (no joins or
anything), and "cont_item" is like:

class ContItem (SQLObject) :
    dbCont = ForeignKey('Cont')
    dbStock = ForeignKey('Stock')
    dbQuantity = IntCol()

(The actual table has "db_stock_id" and "db_cont_id" columns.) I tried
to use this SQLBuilder expression:

my_selection = dbase.Stock.select(
                    sqlobject.sqlbuilder.AND(
                        dbase.Stock.q.dbStockCode.startswith("XXX"),
                        sqlobject.sqlbuilder.NOTIN(
                                sqlobject.sqlbuilder.Outer(dbase.Stock).q.id,

sqlobject.sqlbuilder.Select(dbase.ContItem.q.dbStock,

dbase.ContItem)))

...but when it executes, I get:

[coming from that last line of my expression above]
  File "/usr/lib/python2.5/site-packages/sqlobject/main.py", line
1365, in select
    join=join, forUpdate=forUpdate)
  File "/usr/lib/python2.5/site-packages/sqlobject/sresults.py", line
36, in __init__
    for table in clauseTables:
TypeError: iteration over non-sequence

I'd like to point out that (a) the whole SELECT statement above
returns a few hundred results, and (b) the second clause to AND does
exclude some entries.

Please CC me on replies :)

Cheers,
Jason

Re: [SQLObject] Help with SQLBuilder NOTIN

[Oleg B. <ph...@ph...>]

On Tue, Mar 30, 2010 at 11:58:36AM +0800, Jason Heeris wrote:
> sqlobject.sqlbuilder.Select(dbase.ContItem.q.dbStock, dbase.ContItem)
[skip]
> TypeError: iteration over non-sequence

   The error I can see is the second parameter to Select(). You don't need
to pass the table class - Select derives it from the item ContItem.q.dbStock.
The second parameter to Select is WHERE clause; if you don't need it just
omit it.

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Help with SQLBuilder NOTIN

[Jason H. <jas...@gm...>]

On 30 March 2010 18:04, Oleg Broytman <ph...@ph...> wrote:
>   The error I can see is the second parameter to Select(). You don't need
> to pass the table class - Select derives it from the item ContItem.q.dbStock.
> The second parameter to Select is WHERE clause; if you don't need it just
> omit it.

Using:

my_selection = dbase.Stock.select(
                    sqlobject.sqlbuilder.AND(
                        dbase.Stock.q.dbStockCode.startswith("XXX"),
                        sqlobject.sqlbuilder.NOTIN(
                                sqlobject.sqlbuilder.Outer(dbase.Stock).q.id,

sqlobject.sqlbuilder.Select(dbase.ContItem.q.dbStock)))

... gives exactly the same error though.

Cheers,
Jason

Re: [SQLObject] Help with SQLBuilder NOTIN

[Oleg B. <ph...@ph...>]

On Wed, Mar 31, 2010 at 08:37:32AM +0800, Jason Heeris wrote:
> my_selection = dbase.Stock.select(
>                     sqlobject.sqlbuilder.AND(
>                         dbase.Stock.q.dbStockCode.startswith("XXX"),
>                         sqlobject.sqlbuilder.NOTIN(
>                                 sqlobject.sqlbuilder.Outer(dbase.Stock).q.id,
> 
> sqlobject.sqlbuilder.Select(dbase.ContItem.q.dbStock)))
> 
> ... gives exactly the same error though.

   The following code

from sqlobject import *
from sqlobject.sqlbuilder import *

__connection__ = "sqlite:/:memory:?debug=1"

class Stock(SQLObject) :
    dbStockCode = StringCol()

class ContItem(SQLObject) :
    dbStock = ForeignKey('Stock')
    dbQuantity = IntCol()

Stock.createTable()
ContItem.createTable()

print list(Stock.select(
    AND(Stock.q.dbStockCode.startswith("XXX"),
    NOTIN(Outer(Stock).q.id,
        Select(ContItem.q.dbStock)))))

   works for me; the query is

 3/QueryR  :  SELECT stock.id, stock.db_stock_code FROM stock WHERE ((stock.db_stock_code LIKE ('XXX%') ESCAPE '\') AND (stock.id NOT IN (SELECT cont_item.db_stock_id FROM cont_item)))

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Help with SQLBuilder NOTIN

[Jason H. <jas...@gm...>]

Aaaah, you'll laugh about this. In my transposition of the problem, I
failed to notice an errant ")". So the version I copied and pasted
into my email worked in the first place, but my actual code... did
something else.

Thank you very much for spelling it out, though. It's proved very much
worth my time to wrap my head around this, since every time I
accomplish something like this (over the previous "slurp and reduce"
approach), I reduce the time of about a dozen more function calls by
x50-100 (and there are several more such queries to go).

Thanks again :)

Jason

On 31 March 2010 17:44, Oleg Broytman <ph...@ph...> wrote:
> print list(Stock.select(
>    AND(Stock.q.dbStockCode.startswith("XXX"),
>    NOTIN(Outer(Stock).q.id,
>        Select(ContItem.q.dbStock)))))
>
>   works for me; the query is