sqlobject-discuss Mailing List for SQLObject (Page 32)

> I'm using sqlobject-0.13.0-py2.7 on Windows 7.
Also, my database is SQLite3.

Here are the bare-bones of two classes which are related through a 
many-to-many relationship:

    class SignEntry(SQLObject):
         components = RelatedJoin("Component")

    class Component(SQLObject):
        code = StringCol(default="", unique=True)
        sign_entries = RelatedJoin("SignEntry")


With 'se' representing a particular 'SignEntry' object, the code 
'se.components' reveals the internals of that object:

    [<Component 7 code='48'>, <Component 8 code='24'>, <Component 8
    code='24'>]

Now, I wish to remove just one <Component 8 code='24'> from this object, 
but 'se.removeComponent(8)' removes both. Is there a (simple) way to 
achieve what I wish?

I'm using sqlobject-0.13.0-py2.7 on Windows 7.

Thanks in advance for any advice.

Best regards (and HAPPY NEW YEAR - ALMOST!),
Tim Grove

On Thu, Dec 30, 2010 at 12:26:50PM -0800, Simon Laalo wrote:
> I'm fairly new to SQLObject

   Welcome!

> I have a question about RelatedJoin: is it possible to use an alternateID as the data for the otherColumn.
> 
> I have something like the following
> 
> in the DB:
> users table with columns id ( int) and username (char 8)
> 
> roles table with columns id (int) and name (char 8)
> 
> user_roles table with columns username and role_id
> 
> in the model.py:
> class User(SQLObject):
>                 class sqlmeta:
>                                 table = 'users'
>                 username = UnicodeCol(alternateID=True, varchar=False, length=8,alternateMethodName='by_username')
> 
> 
> class Role(SQLObject):
>                 class sqlmeta:
>                                 table = 'roles'
>                 name = UnicodeCol(varchar=False, length=8)
> 
>                 users = RelatedJoin('User', intermediateTable='user_roles',
>                          joinColumn='role_id', otherColumn='username')
> 
> but this isn't working because when I try to get role.users it attempts to find users whose ID is their username.
> 
> Is there a way for this to work using the user's alternateID username in the join table?

   First, why do you want this at all? Why not allow SQLObject to do
internal referencing itself?
   Well, if you still want to do the work manually, well... it has to be
performed manually.
   RelatedJoin is many-to-many relation (BTW, do you need many-to-many
or one-to-many which is implemented by MultipleJoin) and works using an
intermediate table that stores references to both tables. By default the
intermediate table is created with INT columns to store references to
id's. If you want to store references to a UnicodeCol you have to create
the intermediate table yourself:
http://sqlobject.org/FAQ.html#how-can-i-define-my-own-intermediate-table-in-my-many-to-many-relationship
   And joinColumn has to be "username" in this case.

   I've never tried doing something like this, so I'm not sure it will
work.

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

Hi,

I'm fairly new to SQLObject and I have a question about RelatedJoin: is it possible to use an alternateID as the data for the otherColumn.

I have something like the following

in the DB:
users table with columns id ( int) and username (char 8)

roles table with columns id (int) and name (char 8)

user_roles table with columns username and role_id

in the model.py:
class User(SQLObject):
                class sqlmeta:
                                table = 'users'
                username = UnicodeCol(alternateID=True, varchar=False, length=8,alternateMethodName='by_username')


class Role(SQLObject):
                class sqlmeta:
                                table = 'roles'
                name = UnicodeCol(varchar=False, length=8)

                users = RelatedJoin('User', intermediateTable='user_roles',
                         joinColumn='role_id', otherColumn='username')

but this isn't working because when I try to get role.users it attempts to find users whose ID is their username.

Is there a way for this to work using the user's alternateID username in the join table?

Thanks in advance,
Slaalo

On Fri, Dec 24, 2010 at 11:56:01AM -0500, Ben Timby wrote:
> Oleg, once again, thank you.
> 
> I was looking for the MAX function and was unable to find it. Const is
> interesting as well, it takes the provided attr and transforms it to
> verbatim in the resulting SQL, neat.

   ``const`` and ``func`` are aliases for the same object! (-: Look them
up in sqlbuilder.py.

> The above works perfectly!

   You are welcome!

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

On Fri, Dec 24, 2010 at 10:44 AM, Oleg Broytman <ph...@ph...> wrote:
>   Oops, columns in Select cannot be strings - to allow strings in
> queries.
>
>   Use sqlbuilder.Select(sqlbuilder.func.MAX(sqlbuilder.const.serial)
>
> from sqlobject.sqlbuilder import Select, func, const
> Select(func.MAX(const.serial)
>
>   looks better for my eyes.

Oleg, once again, thank you.

I was looking for the MAX function and was unable to find it. Const is
interesting as well, it takes the provided attr and transforms it to
verbatim in the resulting SQL, neat.

The above works perfectly!

On Fri, Dec 24, 2010 at 10:09:49AM -0500, Ben Timby wrote:
> Thanks for your reply Oleg. I am sorry, but the above produces a bad query:
> 
> --
> query = Sync.select(
>     AND(
>         Sync.q.syncpoint==self,
>         Sync.q.serial==sqlbuilder.Select(MAX('serial'),
> Sync.q.syncpoint==self, staticTables=('sync',))
>     )
> )
> --
> SELECT sync.id, sync.syncpoint_id, sync.serial, sync.stamp,
> sync.status, sync.message FROM sync WHERE (((sync.syncpoint_id) = (1))
> AND ((sync.serial) = (SELECT 'max(serial)' FROM sync WHERE
> ((sync.syncpoint_id) = (1)))));
> --
> 
> As you can see, the 'max(serial)' ends up inside quotes within the
> subselect. I can't find any MAX aggregate function to use in place of
> the string...

   Oops, columns in Select cannot be strings - to allow strings in
queries.

   Use sqlbuilder.Select(sqlbuilder.func.MAX(sqlbuilder.const.serial)

from sqlobject.sqlbuilder import Select, func, const
Select(func.MAX(const.serial)

   looks better for my eyes.

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

The python code was wrong in my last email, it was what I THOUGHT it
should be, the python that produces the bad SQL is:

--
query = Sync.select(
    AND(
        Sync.q.syncpoint==self,
        Sync.q.serial==sqlbuilder.Select('max(serial)',
Sync.q.syncpoint==self, staticTables=('sync',))
    )
)
--

On Thu, Dec 23, 2010 at 5:58 PM, Oleg Broytman <ph...@ph...> wrote:
>    .select().max() produces a simple query SELECT MAX(). If you need a
> query with a subquery you need to add the subquery explicitly using
> sqlbuilder.Select():
>
> serial = Path.select(
>    Path.q.serial==Select('max(serial)', staticTables=['path']))
> return serial.getOne()

Thanks for your reply Oleg. I am sorry, but the above produces a bad query:

--
query = Sync.select(
    AND(
        Sync.q.syncpoint==self,
        Sync.q.serial==sqlbuilder.Select(MAX('serial'),
Sync.q.syncpoint==self, staticTables=('sync',))
    )
)
--

--
SELECT sync.id, sync.syncpoint_id, sync.serial, sync.stamp,
sync.status, sync.message FROM sync WHERE (((sync.syncpoint_id) = (1))
AND ((sync.serial) = (SELECT 'max(serial)' FROM sync WHERE
((sync.syncpoint_id) = (1)))));
--

As you can see, the 'max(serial)' ends up inside quotes within the
subselect. I can't find any MAX aggregate function to use in place of
the string...

Any additional hints?

On Wed, Dec 22, 2010 at 10:18:49PM -0500, Ben Timby wrote:
> What is the most expedient way to do the following?
> 
> serial = Path.selectBy().max('serial')
> return Path.getOne(serial=serial)
> 
> I would like some SQL like this:
> 
> select * from path where serial = (select max(serial) from path);

    .select().max() produces a simple query SELECT MAX(). If you need a
query with a subquery you need to add the subquery explicitly using
sqlbuilder.Select():

serial = Path.select(
    Path.q.serial==Select('max(serial)', staticTables=['path']))
return serial.getOne()

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

On Wed, Dec 22, 2010 at 10:44:49PM -0500, Ben Timby wrote:
> Oleg, I tried this out, my code is:
> 
> --
> joins = []
> alias1 = Path
> path_parts = path.split(os.sep)
> for depth, part in enumerate(path_parts):
>     alias2 = sqlbuilder.Alias(Path)
>     joins.append(sqlbuilder.LEFTJOINOn(alias1, alias2,
> AND(alias1.q.id==alias2.q.parent, alias2.q.path==part)))
>     alias1 = alias2
> print Path.select(join=joins)
> --
> 
> Which produces the following query:
> --
> SELECT path.id, path.parent_id, path.path, path.depth, path.child_name
> FROM path
> LEFT JOIN path  path_alias1
> ON (((path.id) = (path_alias1.parent_id))
> AND ((path_alias1.path) = (''))), path  path_alias1
> LEFT JOIN path  path_alias2
> ON (((path_alias1.id) = (path_alias2.parent_id))
> AND ((path_alias2.path) = ('home'))), path  path_alias2
> LEFT JOIN path  path_alias3
> ON (((path_alias2.id) = (path_alias3.parent_id))
> AND ((path_alias3.path) = ('btimby')))
> WHERE 1 = 1
> --
> 
> and the following error:
> --
> *** OperationalError: ambiguous column name: path_alias1.parent_id
> --
> 
> I think this is because of the redundant alias...
> --
> LEFT JOIN path  path_alias1
> ON (((path.id) = (path_alias1.parent_id))
> AND ((path_alias1.path) = (''))), path  path_alias1
> --
> 
> "path  path_alias1" shows up twice in the above snippet.
> 
> Can you spot my error? Thanks.

   Use LEFTJOIN(None, alias) to avoid table doubling. Something like
this:

joins = []
alias1 = Path
path_parts = path.split(os.sep)

for depth, part in enumerate(path_parts):
    alias2 = sqlbuilder.Alias(Path)

    joins.append(sqlbuilder.LEFTJOINOn(None, alias2,
        AND(alias1.q.id==alias2.q.parent, alias2.q.path==part)))

    alias1 = alias2

print Path.select(join=joins)

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

What is the most expedient way to do the following?

--
serial = Path.selectBy().max('serial')
return Path.getOne(serial=serial)
---

I would like some SQL like this:

--
select * from path where serial = (select max(serial) from path);
--

Thanks.

Hello! I need to inform people I'm changing my online identity. Domain
phd.pp.ru will die Dec 27 (I'll try to reregister it to extend its life
for a few months). My new personal domain will be phdru.name, primary
email will be "phd" (in case one makes a mistake and write "phdru" two
times - the address is an alias for "phd").

The new domain is already delegated, email and site works. I'm working
on changing all my email subscriptions.

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

Hi!

On Mon, Dec 20, 2010 at 09:46:16PM -0500, Ben Timby wrote:
> class Path(sqlobject.SQLObject):
>     parent = sqlobject.ForeignKey('SyncPath', cascade=True)

   As far as I understand from the following query, 'SyncPath' should be
just 'path', right?

>     path = sqlobject.StringCol(alternateID=True)
> 
> select p2.*
> from path as p0
> left join path as p1
> on p1.parent_id = p0.id
> and p0.path = 'home'
> left join path as p2
> on p2.parent_id = p1.id
> and p1.path = 'btimby'
> and p2.path = 'somefile';
> 
> How could I accomplish this in SQLObject? I would have code similar to
> the following, but not sure how to generate the aliases and multiple
> levels of joins...

   Aliases are created using sqlobject.sqlbuilder.Alias:

p1 = Alias(Path)

   This uses autogenerated numbers for alias naming. If you want to give
a specific name to an alias:

p1 = Alias(Path, 'p1')

   Now you can use the alias as a table: p1.q.parent_id == id.

>         sql = Path.select(sqlbuilder.LEFTJOINOn(

   The first parameter for .select() is a WHERE condition. JOINs are
provided in the 'join' parameter. Pass a JOIN or a list of JOINs:

Path.select(join=LEFTJOINOn(...))

Path.select(join=[LEFTJOINOn(...), LEFTJOINOn(...)])

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

I am using an adjacency list to store file system paths.

--
class Path(sqlobject.SQLObject):
    parent = sqlobject.ForeignKey('SyncPath', cascade=True)
    path = sqlobject.StringCol(alternateID=True)
--

I would like to retrieve an object given it's path in one fell swoop.
Below is an example...


path: /home/btimby/somefile

sql:

select p2.*
from path as p0
left join path as p1
on p1.parent_id = p0.id
and p0.path = 'home'
left join path as p2
on p2.parent_id = p1.id
and p1.path = 'btimby'
and p2.path = 'somefile';

How could I accomplish this in SQLObject? I would have code similar to
the following, but not sure how to generate the aliases and multiple
levels of joins...

--
def get_path(path):
    path_parts = path.split(os.sep)
    sql = Path
    for part in path_parts:
        sql = Path.select(sqlbuilder.LEFTJOINOn(
            sql,
            Path,
            sqlbuilder.AND(
                Path.q.id == Path.q.parent,
                Path.q.path == part
            )
        ))
    print sql
--

The above is a bit naive, but hopefully the solution is possible.

Thanks.

On Thu, Dec 16, 2010 at 03:37:43PM +0300, Oleg Broytman wrote:
>    I want to document:
> 
>  * Insert, Update and Delete

   + Union. Insert is documented meanwhile.

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

On Thu, Dec 16, 2010 at 12:23:58AM +0300, Oleg Broytman wrote:
> Hello. I just started to document SQLBuilder, the most underdocumented part
> of SQLObject
>    I plan to document it fully (though, perhaps, not in the greatest
> details)

   I want to document:

 * Insert, Update and Delete; mention Insert as the mean to do
   mass-insertion and made a link from the FAQ;
 * All kinds of JOINs (they are quite similar);
 * Alias and Outer for joining a table with itself.

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

On Thu, Dec 16, 2010 at 10:13:57AM +0100, Daniel Fetchinson wrote:
> Oh sorry, next time it will be the other way around. Or better yet

   No problem, I can always do patch -R :-)

> I'll check out trunk and use svn diff.

   That would be the best. Thank you in advance!

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

>> > Hello. I just started to document SQLBuilder, the most underdocumented
>> > part
>> > of SQLObject:
>> > http://svn.colorstudy.com/SQLObject/trunk/docs/SQLBuilder.txt
>> >    I plan to document it fully (though, perhaps, not in the greatest
>> > details) before releasing the trunk as the version 1.0 in the beginning
>> > of
>> > next year.
>> >    Any help will be appreciated. A few paragraphs of text, or just a
>> > sentence, a wording or grammar suggestion - anything, however small,
>> > would
>> > be helpful.
>>
>> This is really great Oleg!
>>
>> Here is a patch (coming from "diff -u SQLBuilder.new.txt
>> SQLBuilder.txt") with grammatical suggestions. I also attached the new
>> file along with the patch.
>
>    Thank you very much! Everything looks great except that you did diff in
> the reverse direction. (-:
>    I copied and renamed your full SQLObject.new.txt and committed it in the
> revision 4302.

Oh sorry, next time it will be the other way around. Or better yet
I'll check out trunk and use svn diff.

Cheers,
Daniel


-- 
Psss, psss, put it down! - http://www.cafepress.com/putitdown

On Wed, Dec 15, 2010 at 11:20:21PM +0100, Daniel Fetchinson wrote:
> > Hello. I just started to document SQLBuilder, the most underdocumented part
> > of SQLObject: http://svn.colorstudy.com/SQLObject/trunk/docs/SQLBuilder.txt
> >    I plan to document it fully (though, perhaps, not in the greatest
> > details) before releasing the trunk as the version 1.0 in the beginning of
> > next year.
> >    Any help will be appreciated. A few paragraphs of text, or just a
> > sentence, a wording or grammar suggestion - anything, however small, would
> > be helpful.
> 
> This is really great Oleg!
> 
> Here is a patch (coming from "diff -u SQLBuilder.new.txt
> SQLBuilder.txt") with grammatical suggestions. I also attached the new
> file along with the patch.

   Thank you very much! Everything looks great except that you did diff in
the reverse direction. (-:
   I copied and renamed your full SQLObject.new.txt and committed it in the
revision 4302.

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

> Hello. I just started to document SQLBuilder, the most underdocumented part
> of SQLObject: http://svn.colorstudy.com/SQLObject/trunk/docs/SQLBuilder.txt
>    I plan to document it fully (though, perhaps, not in the greatest
> details) before releasing the trunk as the version 1.0 in the beginning of
> next year.
>    Any help will be appreciated. A few paragraphs of text, or just a
> sentence, a wording or grammar suggestion - anything, however small, would
> be helpful.

This is really great Oleg!

Here is a patch (coming from "diff -u SQLBuilder.new.txt
SQLBuilder.txt") with grammatical suggestions. I also attached the new
file along with the patch.

Cheers,
Daniel

-- 
Psss, psss, put it down! - http://www.cafepress.com/putitdown

Hello. I just started to document SQLBuilder, the most underdocumented part
of SQLObject: http://svn.colorstudy.com/SQLObject/trunk/docs/SQLBuilder.txt
   I plan to document it fully (though, perhaps, not in the greatest
details) before releasing the trunk as the version 1.0 in the beginning of
next year.
   Any help will be appreciated. A few paragraphs of text, or just a
sentence, a wording or grammar suggestion - anything, however small, would
be helpful.

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

On Tue, Dec 07, 2010 at 04:30:26PM +0100, Daniel Fetchinson wrote:
> I just don't recall having this issue until now, I mean I always did
> "easy_install -U sqlobject" and did not end up with a dev version, but
> I might be wrong. Maybe I did but don't remember :)

   It could be I've messed download URLs. I don't know enough about
setuptools to be sure I fixed the problem with the latest commit.

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

>> easy_install -U sqlobject
>>
>> installs the 0.16dev version not the stable 0.15, is it?
>
> easy_install -U sqlobject
>
>    installs the most recent version it can find - which is the trunk. I
> don't know how to tell setuptools to distinguish stable and development
> branches, do you?
>
> easy_install sqlobject==0.15.0
>
>    is the only way I know.

This is what I did after all. And no, I'm not aware of another trick.
I just don't recall having this issue until now, I mean I always did
"easy_install -U sqlobject" and did not end up with a dev version, but
I might be wrong. Maybe I did but don't remember :)

This whole thing is not a big deal, as I've said I do "easy_install
sqlobject==0.15.0" and everything is fine.

Cheers,
Daniel

-- 
Psss, psss, put it down! - http://www.cafepress.com/putitdown

On Tue, Dec 07, 2010 at 01:41:09PM +0100, Daniel Fetchinson wrote:
> easy_install -U sqlobject
> 
> installs the 0.16dev version not the stable 0.15, is it?

easy_install -U sqlobject

   installs the most recent version it can find - which is the trunk. I
don't know how to tell setuptools to distinguish stable and development
branches, do you?

easy_install sqlobject==0.15.0

   is the only way I know.

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