sqlobject-discuss — All things SQLObject-related

[SQLObject] using alternateIDs as the joining id found in a Related Join's otherColumn

[Simon L. <sl...@re...>]

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

Re: [SQLObject] Subquery MAX().

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

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.

Re: [SQLObject] Subquery MAX().

[Ben T. <bt...@gm...>]

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!

Re: [SQLObject] Subquery MAX().

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

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.

Re: [SQLObject] Subquery MAX().

[Ben T. <bt...@gm...>]

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',))
    )
)
--

Re: [SQLObject] Subquery MAX().

[Ben T. <bt...@gm...>]

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?

Re: [SQLObject] Subquery MAX().

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

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.

Re: [SQLObject] Self LEFT JOIN.

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

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.

[SQLObject] Subquery MAX().

[Ben T. <bt...@gm...>]

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.

[SQLObject] phd.pp.ru => phdru.name

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

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.

Re: [SQLObject] Self LEFT JOIN.

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

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.

[SQLObject] Self LEFT JOIN.

[Ben T. <bt...@gm...>]

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.

Re: [SQLObject] SQLBuilder documentation

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

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.

Re: [SQLObject] SQLBuilder documentation

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

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.

Re: [SQLObject] SQLBuilder documentation

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

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.

Re: [SQLObject] SQLBuilder documentation

[Daniel F. <fet...@go...>]

>> > 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

Re: [SQLObject] SQLBuilder documentation

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

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.

Re: [SQLObject] SQLBuilder documentation

[Daniel F. <fet...@go...>]

> 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

[SQLObject] SQLBuilder documentation

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

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.

Re: [SQLObject] easy_install latest stable version

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

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.

Re: [SQLObject] easy_install latest stable version

[Daniel F. <fet...@go...>]

>> 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

Re: [SQLObject] easy_install latest stable version

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

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.

Re: [SQLObject] SQLObject 0.15.0

[Daniel F. <fet...@go...>]

On 12/6/10, Oleg Broytman <ph...@ph...> wrote:
> On Mon, Dec 06, 2010 at 06:46:45PM +0300, Oleg Broytman wrote:
>> 0.15
>
>    The branch 0.13 is no longer maintained.
>
>> What's New
>
>    Nothing changed since 0.15.0b1.


Oleg, I guess it's not intentional that

easy_install -U sqlobject

installs the 0.16dev version not the stable 0.15, is it?

Cheers,
Daniel



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

Re: [SQLObject] SQLObject 0.15.0

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

On Mon, Dec 06, 2010 at 06:46:45PM +0300, Oleg Broytman wrote:
> 0.15

   The branch 0.13 is no longer maintained.

> What's New

   Nothing changed since 0.15.0b1.

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

[SQLObject] SQLObject 0.15.0

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

Hello!

I'm pleased to announce version 0.15.0, the first stable release of branch
0.15 of SQLObject.


What is SQLObject
=================

SQLObject is an object-relational mapper.  Your database tables are described
as classes, and rows are instances of those classes.  SQLObject is meant to be
easy to use and quick to get started with.

SQLObject supports a number of backends: MySQL, PostgreSQL, SQLite,
Firebird, Sybase, MSSQL and MaxDB (also known as SAPDB).


Where is SQLObject
==================

Site:
http://sqlobject.org

Development:
http://sqlobject.org/devel/

Mailing list:
https://lists.sourceforge.net/mailman/listinfo/sqlobject-discuss

Archives:
http://news.gmane.org/gmane.comp.python.sqlobject

Download:
http://cheeseshop.python.org/pypi/SQLObject/0.15.0

News and changes:
http://sqlobject.org/News.html


What's New
==========

* Major API change: all signals are sent with the instance (or the class)
  as the first parameter. The following signals were changed:
  RowCreateSignal, RowCreatedSignal, DeleteColumnSignal.

* Major API change: post-processing functions for all signals are called
  with the instance as the first parameter. The following signals were
  changed: RowUpdatedSignal, RowDestroySignal, RowDestroyedSignal.

For a more complete list, please see the news:
http://sqlobject.org/News.html

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