sqlobject-discuss — All things SQLObject-related

[SQLObject] A small patch around features I need

[G. <fra...@cl...>]

Clarisys patch for SQLOBject 0.3

author : Fran=E7ois Girault - fra...@cl...

Updated : 27/06/03


----------------------------------------------

INSTALL :=20

 - download SQLObject 0.3 and untar it in a directory
 - put the patch file in this dir and then type 'patch -p0 < patch_filename'
 - enjoy ;-)


----------------------------------------------

27/06/03 Changes :

 * id can now be passed at creation time when using db sequence is not appr=
opriate

    myNewEntity =3D Entity.new(id=3D25)

    if Entity._idName is defined, you can also use this value as key to spe=
cify the id :

    class Entity(SQLObject):
        _idName =3D 'code'
        ...
    myNewEntity =3D Entity.new(code=3D25) will work the same as previous de=
claration

 * object ids can be alphanumerical type. Note that you MUST specify id at =
creation time for the moment. Support for stored procedure to generate alph=
anumerical ids will come in a close future ;-)=20

 * relations between objects can use other fields than id (including alphan=
umerical ones). In _join class attribute you can add these parameter to Joi=
n children creation :

    * joinField let you specify the field to join within your class

    * otherField let you specify the field of the other table

   Note that the specified fields MUST be declared as alternateID

----------------------------------------------

Short sample

.../...

class Person(SQLObject):
	# Person table has a "serial" id field named 'id' in the table, but also a=
n alphanumerical code to link to Occupation (in this context you would say =
it's stupid but I need that in some cases)
	_columns =3D [ Col('code', alternateID=3DTrue), Col('firstname'), Col('las=
tname') ]
	_joins =3D [ RelatedJoin('Occupation', joinField=3D'code', joinColumn=3D'p=
erson_code', otherColumn=3D'occupation_code', intermediateTable=3D'person_o=
ccupation') ]

class Occupation(SQLObject):
	# code is alphanumerical
	_idName =3D 'code'
	_columns =3D [Col('label')]
	_joins =3D [RelatedJoin('Person', joinColumn=3D'person_code', otherColumn=
=3D'occupation_code', otherField=3D'code', intermediateTable=3D'person_occu=
pation')]

occupation =3D Occupation.new(code=3D'WLK', label=3D'Walk in the mountain')
# INSERT INTO occupation (label, code) VALUES ('Walk in the mountain', 'WLK=
')
pers.addOccupation(occupation)
# INSERT INTO person_occupation (person_code, occupation_code) VALUES ('ABC=
', 'WLK')
.../...

----------------------------------------------

Limitation :

  * for Postgresql usage only due to modification in DBConnection, and I ha=
ve no time to write & test with other DB (help welcome :) ).

Re: [SQLObject] Hook for .new() method?

[Edmund L. <el...@in...>]

Ian Bicking wrote:

> Just with new, like:
> 
> def new(cls, **kw):
>     obj = super(cls).new(**kw)
>     # ... do stuff ...
>     return obj
> new = classmethod(new)
> 
> 
> On Mon, 2003-06-09 at 00:06, Edmund Lian wrote:
> 
>>What's the best way to hook some code into the .new()? Basically, I need 
>>to run some code after an object is instantiated for the first time. Is 
>>this what the _init() method is for?


Hmmm... This doesn't seem to work. When I do this, I get an AttributeError:

     obj = super(cls).new(**kw)
AttributeError: 'super' object has no attribute 'new'

I know that there are some bugs in the super method of Python 2.2. Is 
this one of them? If so, how can I work around it?

...Edmund.

[SQLObject] Alphanumeric Object ID

[G. <fra...@cl...>]

Hello,

I'm creating an object mapping over an existing database which contains tab=
le with varchar as primary key and consequently as id.

I first tried to follow the common way and then I got the following type er=
ror :

Traceback (most recent call last):
  File "ORM.py", line 29, in ?
    print labo
  File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 837,=
 in _             _repr__
    ' '.join(['%s=3D%s' % (name, repr(value)) for name, value in self._repr=
Items()]))
TypeError: an integer is required


the original code in SQLObject.py was :

    def __repr__(self):
        return '<%s %i %s>' \
               % (self.__class__.__name__,
                  self.id,
                  ' '.join(['%s=3D%s' % (name, repr(value)) for name, value=
 in self._reprItems()]))

I've turned it into :

    def __repr__(self):
        return '<%s %s %s>' \
		....^^....

and then all work fine !

Is there a better way to do that (better than modifying the source code ;) =
? Is it so imperative to use integer as id (except for automatic id generat=
ion) ?

Thanks for any help and for the great work of the author !

--------------------
Fran=E7ois Girault

Re: [SQLObject] Specifying defaultOrder

[Ian B. <ia...@co...>]

On Thu, 2003-06-19 at 09:15, Nick wrote:
> el...@in... wrote:
> > Andy wrote:
> >>But - I'd like the records returned in reverse date order, which can be
> >>achieved in SQL with an order by clause like 'dateCol DESC'. I've tried
> >>this in my class definition but I get the following error;
> > You must be the 6th person to ask, I was the 5th, according to Luke. :-)
> > Sort order cannot currently be specified in SO.
> 
> Should say something about the direction that development should be taking.

Yeah, I'm planning on it Real Soon, I just have to look at the code. 
Probably options like:

defaultOrder='-dateCol'
defaultOrder=SQLBuilder.DESC(Something.dateCol)
sel = Something.select().reversed()
sel = Something.select(reversed=True)
# or descending=True?

Re: [SQLObject] Handling relations with multiple keys

[Ian B. <ia...@co...>]

On Fri, 2003-06-20 at 18:22, Bruno Trevisan wrote:
> Hi there
> 
> Continuing my experiences on using SQLObject I fall in another question:
> is it possible to have a object linked to some other using more than one
> table attribute as foreignKey ?
> 
> My problem is: I have a Product Class and this product has a SubFamily
> object which defines some extra product properties to me. It happens
> that to query products for its subfamily I have to do a comparsion using
> 2 fields from the subfamily table (subfamilly_id and family_id). Using
> the foreignKey Col Class atribute I'm able, AFAIK, to set only one
> field, which in my case isn't enough to pick the unique subfamily
> object.
> 
> How to handle this ?

I'm not entirely clear on your schema, but you probably should create
the getter yourself, something like:

    def _get_subFamily(self):
        return SubFamily.selectBy(subfamilyID=self.subfamilyID, 
                                  familyID=self.familyID)[0]

Or something like that... along those lines at least.

  Ian

[SQLObject] Handling relations with multiple keys

[Bruno T. <bt...@as...>]

Hi there

Continuing my experiences on using SQLObject I fall in another question:
is it possible to have a object linked to some other using more than one
table attribute as foreignKey ?

My problem is: I have a Product Class and this product has a SubFamily
object which defines some extra product properties to me. It happens
that to query products for its subfamily I have to do a comparsion using
2 fields from the subfamily table (subfamilly_id and family_id). Using
the foreignKey Col Class atribute I'm able, AFAIK, to set only one
field, which in my case isn't enough to pick the unique subfamily
object.

How to handle this ?

[]'s

 Bruno Trevisan

 bt...@as...          |=3D| Async Open Source |=3D| D. Alexandrina, 253=
4
 http://www.async.com.br/ |=3D| +55 16 261-2331   |=3D| 13566-290
                          |=3D| +55 16 9781-8717  |=3D| S=E3o Carlos, SP, B=
rasil

Re: [SQLObject] Examples of using 'func'

[Magnus <ma...@th...>]

At 16:50 2003-06-19 -0700, Martin Stoufer wrote:
>Thats what it was looking like from my failed attempts.  Since the date 
>field comes back with each of the records, I just iterate over the python 
>objects and extract the max date there.

After all, SQLObject is there to access instances of classes (i.e.
rows of tables). Nothing stops you from getting aggregated data
though another channel such as the DB-API. If you aggregate over a
large set of rows it should be much better from a performance point
of view to do the aggregate in the database rather than in Python.


--
Magnus Lycka (It's really Lyckå), ma...@th...
Thinkware AB, Sweden, www.thinkware.se
I code Python ~ The Agile Programming Language 

Re: [SQLObject] Examples of using 'func'

[Martin S. <MCS...@lb...>]

Thats what it was looking like from my failed attempts.  Since the date 
field comes back with each of the records, I just iterate over the 
python objects and extract the max date there.



Luke Opperman wrote:

>Martin -
>
>func won't help you here. for SQLObject select's, you only really have control
>over the where clause of the query. max (and other aggregates) are not allowed
>there in SQL in general, as far as I know (definitely not in Postgres). func
>is for accessing stored procs or built-in db functions.
>
>For the same reason you can't do outer joins in SQLObject, you can't currently
>use SQLObject.select() to just return a max value: it expects to return
>SQLObjects of the class doing the select. So, it would be conceivable to
>retrieve the object with the max value in it, although currently not using
>SQLBuilder (at least, I don't know of a way to do subselects with it):
>
>  p = Person.select("int_field = (select max(int_field) from person)")
>  # returns a list of Person objects, probably one but maybe zero if the table
>  # is empty, or more than one if int_field is not unique, so check!
>  if len(p):
>    max = p[0].intField
>
>Alternatively, you could retrieve the value using the raw connection, and then
>optionally retrieve person objects:
>
>  c = Person._connection.cursor()
>  c.execute("select max(int_field) from person")
>  max = c.fetchone()[0]  #i think? been a long time since i did DBAPI stuff
>  Person.select(Person.q.intField == max)
>
>I generally don't go down this route unless absolutely necessary.
>
>Neither of these is particularily pretty (one exposes raw SQL, the other raw db
>queries + SQL), but hope it helps a bit. And if you have ideas for cleaning
>this up, let us know. (I suppose the immediate fix is to have SQLBuilder
>support sub-selects...)
>
>- Luke
>
>Quoting Martin Stoufer <MCS...@lb...>:
>
>  
>
>>Any docs or examples out there of using 'func'. Namely, I'd like to see
>>how to find the max() of an integer field in a table meeting some query
>>criteria I define.
>>    
>>
>
>
>  
>

-- 
* Martin C. Stoufer              *
* DIDC/DSD/ITG                   *
* Lawrence Berkeley National Lab *
* MS 50B-2215 510-486-8662       *

Re: [SQLObject] Examples of using 'func'

[Luke O. <lu...@me...>]

Martin -

func won't help you here. for SQLObject select's, you only really have control
over the where clause of the query. max (and other aggregates) are not allowed
there in SQL in general, as far as I know (definitely not in Postgres). func
is for accessing stored procs or built-in db functions.

For the same reason you can't do outer joins in SQLObject, you can't currently
use SQLObject.select() to just return a max value: it expects to return
SQLObjects of the class doing the select. So, it would be conceivable to
retrieve the object with the max value in it, although currently not using
SQLBuilder (at least, I don't know of a way to do subselects with it):

  p = Person.select("int_field = (select max(int_field) from person)")
  # returns a list of Person objects, probably one but maybe zero if the table
  # is empty, or more than one if int_field is not unique, so check!
  if len(p):
    max = p[0].intField

Alternatively, you could retrieve the value using the raw connection, and then
optionally retrieve person objects:

  c = Person._connection.cursor()
  c.execute("select max(int_field) from person")
  max = c.fetchone()[0]  #i think? been a long time since i did DBAPI stuff
  Person.select(Person.q.intField == max)

I generally don't go down this route unless absolutely necessary.

Neither of these is particularily pretty (one exposes raw SQL, the other raw db
queries + SQL), but hope it helps a bit. And if you have ideas for cleaning
this up, let us know. (I suppose the immediate fix is to have SQLBuilder
support sub-selects...)

- Luke

Quoting Martin Stoufer <MCS...@lb...>:

> Any docs or examples out there of using 'func'. Namely, I'd like to see
> how to find the max() of an integer field in a table meeting some query
> criteria I define.

[SQLObject] Examples of using 'func'

[Martin S. <MCS...@lb...>]

Any docs or examples out there of using 'func'. Namely, I'd like to see 
how to find the max() of an integer field in a table meeting some query 
criteria I define.


-- 
* Martin C. Stoufer              *
* DIDC/DSD/ITG                   *
* Lawrence Berkeley National Lab *
* MS 50B-2215 510-486-8662       *

Re: [SQLObject] Specifying defaultOrder

[Luke O. <lu...@me...>]

> Is there any possibility of sorting on more than one column? It's pretty
> common to have to sort on more than one column...

It appears that CVS currently supports a list for SelectResults orderBy (and
_defaultOrder), and properly generates SQL for them. I haven't actually used
this in SQLObject yet however. Also, the Join sort implementation will break
if you do this, so that'll need to be fixed.

- Luke

Re: [SQLObject] Specifying defaultOrder

[<el...@in...>]

Luke,

> Can you think of better names than "_defaultOrderDirection" and
> "orderDirection"?

I feel that "sortOrder" is a little bit of an improvement over the other
alternatives.

Is there any possibility of sorting on more than one column? It's pretty
common to have to sort on more than one column...

...Edmund.

Re: [SQLObject] Specifying defaultOrder

[Luke O. <lu...@me...>]

> > Sort order cannot currently be specified in SO.
>
> Should say something about the direction that development should be taking.
>
> Nick

Alright, here's what it'll take (from current CVS) to get order direction:

Questions:

Can you think of better names than "_defaultOrderDirection" and
"orderDirection"?

Should the value be the strings "DESC"/"ASC", be case-insensitive, be some
python representation of these strings? Accept strings but turn them into some
python rep?

1. Modify SQLObject to specify _defaultOrderDirection = None in class vars.

2. Modify SQLObject.select() to take keyword "orderDirection", and pass to
SelectResults.

    def select(cls, clause=None, clauseTables=None,
               orderBy=NoDefault, orderDirection=NoDefault,
               groupBy=None, limit=None, lazyColumns=False):
        return SelectResults(cls, clause, clauseTables=clauseTables,
                     orderBy=orderBy, orderDirection=orderDirection,
                     groupBy=groupBy, limit=limit, lazyColumns=lazyColumns)

3. Modify SelectResults (SQLObject.py) class to set ops['orderDirection'] =
sourceClass._defaultOrderDirection if not specified.

        if self.ops.get('orderDirection', NoDefault) is NoDefault:
            self.ops['orderDirection'] = sourceClass._defaultOrderDirection

4. Modify DBConnection.whereClauseForSelect() to actually insert the DESC/ASC
word if ops['orderDirection'] is set.

     if order and ops.get('dbOrderBy'):
         q = "%s ORDER BY %s" % (q, ", ".join(clauseList(ops['dbOrderBy'])))
+        if ops.get('orderDirection',None):
+            q = "%s %s" % (q, ops['orderDirection'])

(Also, DBMConnection implements it's own iterSelect/DBMSelectResults,
presumably because it doesn't support ORDER BY natively. This will need to be
modified to swap comparisons much like Joins below.)

4. Modify SOJoin.__init__() to accept an orderDirection keyword, modify
SOJoin._applyOrder() to use defaultSortClass._defaultOrderDirection if None
given, and to use a reverseSorter that swaps the comparison if
orderDirection.upper() == 'DESC'

    def _applyOrderBy(self, results, defaultSortClass):
        if self.orderBy is NoDefault:
            self.orderBy = defaultSortClass._defaultOrder
+       if self.orderDirection is NoDefault:
+           self.orderDirection = defaultSortClass._defaultOrderDirection
        if self.orderBy is not None:
            def sorter(a, b, attr=self.orderBy):
                return cmp(getattr(a, attr),
                           getattr(b, attr))
+           def revSorter(a, b, attr=self.orderBy):
+               return cmp(getattr(a, attr), getattr(b, attr))
+           if self.orderDirection.upper() == 'DESC'
                results.sort(revSorter)
+           else:
+               results.sort(sorter)
        return results


And I think that should be everything. I haven't tested all of this yet, and it
can't be committed till Ian returns, but comments are welcome.

- Luke

Re: [SQLObject] Specifying defaultOrder

[Nick <ni...@dd...>]

el...@in... wrote:
> Andy wrote:
>>But - I'd like the records returned in reverse date order, which can be
>>achieved in SQL with an order by clause like 'dateCol DESC'. I've tried
>>this in my class definition but I get the following error;
> You must be the 6th person to ask, I was the 5th, according to Luke. :-)
> Sort order cannot currently be specified in SO.

Should say something about the direction that development should be taking.

Nick

Re: [SQLObject] Specifying defaultOrder

[<el...@in...>]

Andy wrote:

> But - I'd like the records returned in reverse date order, which can be
> achieved in SQL with an order by clause like 'dateCol DESC'. I've tried
> this in my class definition but I get the following error;

You must be the 6th person to ask, I was the 5th, according to Luke. :-)
Sort order cannot currently be specified in SO.

...Edmund.

Re: [SQLObject] Proper object destruction

[Luke O. <lu...@me...>]

Martin -

> The classes are defined in the same file of the WebKit servlet and
> whenever I run the servlet, I get:
>
> File "/usr/local/lib/python2.2/site-packages/SQLObject/SQLObject.py", line
> 170, in __new__
>     assert not classRegistry.has_key(className), "A database object by the
> name %s has already been created" % repr(className)
> AssertionError: A database object by the name 'Events' has already been
> created

Hmm, this error message is slightly misleading, it really ought to say that the
*class* Events has already been created. The reason for the problem is that
SQLObject keeps a module-level dictionary of classes, so that foreignKey and
Join class name references (specified as strings) can be resolved.

So the problem is that the Events class is being created by the metaclass
twice. Because python's import typically won't reload an import unless
explicitly told to, this usually happens because you have two distinct classes
with the same name. However, perhaps due to Webware's behavior of reloading
leaf-level servlets if they've changed, or another quirk of Webware's import
hooks, it would appear the events class is being reloaded.

I would recommend putting the SQLObjects in their own file and importing that
from your servlet (from DBObjects import * or similar).. You'll want to do
this soon anyways, since in order to resolve the name references mentioned
above, you'll need to be importing the object classes for any servlet that
uses them. (Alternatively, in your context's __init__.py you can import each
class to ensure they are all resolved before use, but either way you'll want
them in separate files.) The only downside is that if you aren't using OneShot
or AutoReload in Webkit for development, you'll need to restart the appserver
for every object change.

(It's also possible that you are doing something such as *defining* the Events
class in each servlet that might use it, which would be a simpler cause of
this problem, although less likely. Either way, the above reorganization
should take care of it.)

If this doesn't solve it, let us know.

- Luke

[SQLObject] Proper object destruction

[Martin S. <MCS...@lb...>]

I would like to congratulate Ian on developing such a useful work of 
code. Its slated to shrink some of our code base by 75%!
Huzzah.

Since someone on the Webware mail list turned me onto this project, I'm 
hoping someone here knows how to properly destroy objects that are being 
maintained in the WebKit servlet space. It is unclear to me if this is a 
WebKit issue, one of my python programming failings, or something 
overlooked by the SQLObject code?

The classes are defined in the same file of the WebKit servlet and 
whenever I run the servlet, I get:

File "/usr/local/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 170, in __new__
    assert not classRegistry.has_key(className), "A database object by the name %s has already been created" % repr(className)
AssertionError: A database object by the name 'Events' has already been created


Two problems exist. How to keep seperate the Events object between 
seperate (and possibly simultaneous) invocations of the servlet. If this 
is a WebKit issue, I will go hassle them instead :}

Your suggestions are humbly appreciated.

-- 
* Martin C. Stoufer              *
* DIDC/DSD/ITG                   *
* Lawrence Berkeley National Lab *
* MS 50B-2215 510-486-8662       *

[SQLObject] Specifying defaultOrder

[Andy T. <an...@ha...>]

I've got a table in my database which contains a date column. I'd like 
to be able to specify that rows are returned in date order, which can be 
done with the _defaultOrder special class variable.

But - I'd like the records returned in reverse date order, which can be 
achieved in SQL with an order by clause like 'dateCol DESC'. I've tried 
this in my class definition but I get the following error;

AssertionError: Strings in clauses are expected to be column 
identifiers.  I got: 'price_date DESC'

Is there a way to do this with SQLObject?

Regards,
Andy
-- 
--------------------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/

Re: [SQLObject] no attribute '_SO_autoInitDone'

[<fb...@fo...>]

On Tue, Jun 17, 2003 at 01:24:14PM -0300, Bruno Trevisan wrote:
> While trying to use an object created by a SQLObject subclass I'm
> getting an exception like:
> 
>     AttributeError: 'Product' object has no attribute '_SO_autoInitDone'

I also got this error when running with Python 2.2.0. If I remember it
correctly, it went away sometime with Ian's updates. Could you try the
latest CVS of sqlobject?

ciao
-- 
Fran Barknecht

Re: [SQLObject] Having trouble with one to many joins

[Andy T. <an...@ha...>]

Luke Opperman wrote:
[snip]
> 
> Quoting Andy Todd <an...@ha...>:
> 
> 
>>Hi,
>>
>>This is probably a stupid question, but I couldn't find anything in the
>>mailing list archives.
>>
>>I've got two tables in a parent-child relationship and I thought I had
>>specified the joins correctly but I am getting no joy.
>>
>>Table 1 (the parent) is called PORTFOLIOS, here is my class definition;
>>
>>class Portfolio(SQLObject):
>>     _table='portfolios'
>>     _columns=[Col('portfolioCode'),
>>               Col('portfolioDesc'),
>>              ]
>>     _joins=[MultipleJoin('Holding'),
>>            ]
>>
>>The child table is called HOLDINGS, here is its class definition;
>>
>>class Holding(SQLObject):
>>     _table='holdings'
>>     _columns=[Col('portfolioID', foreignKey='Portfolio'),
>>               Col('stockID', foreignKey='Stock'),
>>               Col('buyDate'),
>>               Col('buyQuantity'),
>>               Col('buyPrice'),
>>               Col('currentQuantity'),
>>              ]
>>
>>The tables are already populated and I can fetch rows from either table
>>successfully, either with index values, select or selectBy methods. But
>>the join is a bit shonky, my Holding objects contain a reference to the
>>correct portfolio object and a portfolioID attribute, but the Portfolio
>>objects do not contain any references to holdings.
>>
>>I suspect the problem lies in my choice of names. Because the table is
>>called portfolios, the class Portfolio and the foreign key column
>>portfolio_id I believe the foreign key is being traversed correctly. Can
>>anyone suggest what I have done wrong and, more importantly, what I can
>>do to get this working properly.
>>
>>Thanks in advance,
>>Andy
>>--
>>
> 
> --------------------------------------------------------------------------------
> 
>> From the desk of Andrew J Todd esq - http://www.halfcooked.com/
>>
>>
>>

Thank you Luke and Edmund,

By following your advice I found my problem and have solved it. By 
turning debug on I discovered that the link from Portfolio to Holding 
was expecting a column called portfolios_id.

I fixed this by changing the _joins attribute of my Portfolio class to;

     _joins=[MultipleJoin('Holding', joinColumn='portfolio_id')]

Which sorted out the SQL that is generated. I had, incorrectly, assumed 
that because the class was called portfolio that was being used to 
generate the foreign key column name in the child table, but it was 
being overriden by my explicit use of _table.

Regards,
Andy
-- 
--------------------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/

[SQLObject] no attribute '_SO_autoInitDone'

[Bruno T. <bt...@as...>]

Hi

While trying to use an object created by a SQLObject subclass I'm
getting an exception like:

    AttributeError: 'Product' object has no attribute '_SO_autoInitDone'

Since I'm new to SQLObject and I have some ugly hacks in my classes like:

    class Product(SQLObject, Model):

        _table =3D 'Produtos'
        _idName =3D 'produto_id'

        _columns =3D [
            Col('code', dbName=3D'codigo'), # product code
            Col('famID', dbName=3D'fam_id', foreignKey=3D'Family'),
            Col('subID', dbName=3D'sub_id'),
            Col('description', dbName=3D'descricao'),
            Col('alt_description', dbName=3D'descricao_alt'),
            Col('unit', dbName=3D'unidade'),
        ]

        def __init__(self, *args):
            Model.__init__(self)
            # Im doing this here because product subfamily is an
            # atribute which has a multiple key (famID and subID) and (I
            # think) SQLObject doesn't support it.
            self.sub =3D SubFamily.select(
                                AND(
                                    SubFamily.q.famID =3D=3D self.famID,
                                    SubFamily.q.subID =3D=3D self.subID))[0=
]
            SQLObject.__init__(self, *args)

Im wondering if I'm not doing things that could be done different
(better) with support from SQLObject.

many thanks,

 Bruno Trevisan

 bt...@as...          |=3D| Async Open Source |=3D| D. Alexandrina, 253=
4
 http://www.async.com.br/ |=3D| +55 16 261-2331   |=3D| 13566-290
                          |=3D| +55 16 9781-8717  |=3D| S=E3o Carlos, SP, B=
rasil

Re: [SQLObject] Having trouble with one to many joins

[Luke O. <lu...@me...>]

Since your class definitions look broadly correct, I'm guessing it is a
case-sensitivity issue (MySQL has case sensitive tablenames on some platforms,
Postgres is always insensitive, not sure about others), or a problem with the
SQLObject version you're using not properly reading _table... I haven't
followed changes to that portion of the code in a while, although I know Ian's
been fixing up various bugs regarding Styles etc.

What database, OS, and version of SQLObject are you using? Have you tried
explicitly setting the joinMethodName and joinColumn arguments of the Join? If
you turn on debug=True for the connection you are using, the SQL that is
printed to stdout should provide a pretty clear picture of where the problem
is, so this will probably the most helpful info in addition to version info.

- Luke

Quoting Andy Todd <an...@ha...>:

> Hi,
>
> This is probably a stupid question, but I couldn't find anything in the
> mailing list archives.
>
> I've got two tables in a parent-child relationship and I thought I had
> specified the joins correctly but I am getting no joy.
>
> Table 1 (the parent) is called PORTFOLIOS, here is my class definition;
>
> class Portfolio(SQLObject):
>      _table='portfolios'
>      _columns=[Col('portfolioCode'),
>                Col('portfolioDesc'),
>               ]
>      _joins=[MultipleJoin('Holding'),
>             ]
>
> The child table is called HOLDINGS, here is its class definition;
>
> class Holding(SQLObject):
>      _table='holdings'
>      _columns=[Col('portfolioID', foreignKey='Portfolio'),
>                Col('stockID', foreignKey='Stock'),
>                Col('buyDate'),
>                Col('buyQuantity'),
>                Col('buyPrice'),
>                Col('currentQuantity'),
>               ]
>
> The tables are already populated and I can fetch rows from either table
> successfully, either with index values, select or selectBy methods. But
> the join is a bit shonky, my Holding objects contain a reference to the
> correct portfolio object and a portfolioID attribute, but the Portfolio
> objects do not contain any references to holdings.
>
> I suspect the problem lies in my choice of names. Because the table is
> called portfolios, the class Portfolio and the foreign key column
> portfolio_id I believe the foreign key is being traversed correctly. Can
> anyone suggest what I have done wrong and, more importantly, what I can
> do to get this working properly.
>
> Thanks in advance,
> Andy
> --
>
--------------------------------------------------------------------------------
>  From the desk of Andrew J Todd esq - http://www.halfcooked.com/
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: INetU
> Attention Web Developers & Consultants: Become An INetU Hosting Partner.
> Refer Dedicated Servers. We Manage Them. You Get 10% Monthly Commission!
> INetU Dedicated Managed Hosting http://www.inetu.net/partner/index.php
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>


--
 i find your contempt for naked feet curious.

Re: [SQLObject] Having trouble with one to many joins

[<el...@in...>]

Andy,

> Table 1 (the parent) is called PORTFOLIOS, here is my class definition;
>
> class Portfolio(SQLObject):
>      _table='portfolios'
>      _columns=[Col('portfolioCode'),
>                Col('portfolioDesc'),
>               ]
>      _joins=[MultipleJoin('Holding'),
>             ]
>
> The child table is called HOLDINGS, here is its class definition;
>
> class Holding(SQLObject):
>      _table='holdings'
>      _columns=[Col('portfolioID', foreignKey='Portfolio'),
>                Col('stockID', foreignKey='Stock'),
>                Col('buyDate'),
>                Col('buyQuantity'),
>                Col('buyPrice'),
>                Col('currentQuantity'),
>               ]


I think you're having a problem with table names.

The portfolio object is going to expect a table named "holding", based on
the class definitions above. The portfolio holdings will then be available
via the attribute ".holdings"

You need to change the name of table holdings to "holding", or explicitly
specify the table's name in the portfolio class definition. You may also
want to explicitly name the join column's name if you do the latter. I've
found that under certain circumstances (can't remember what), SO will
incorrectly guess the join column names if you explicitly name the other
tables.

...Edmund.

[SQLObject] Having trouble with one to many joins

[Andy T. <an...@ha...>]

Hi,

This is probably a stupid question, but I couldn't find anything in the 
mailing list archives.

I've got two tables in a parent-child relationship and I thought I had 
specified the joins correctly but I am getting no joy.

Table 1 (the parent) is called PORTFOLIOS, here is my class definition;

class Portfolio(SQLObject):
     _table='portfolios'
     _columns=[Col('portfolioCode'),
               Col('portfolioDesc'),
              ]
     _joins=[MultipleJoin('Holding'),
            ]

The child table is called HOLDINGS, here is its class definition;

class Holding(SQLObject):
     _table='holdings'
     _columns=[Col('portfolioID', foreignKey='Portfolio'),
               Col('stockID', foreignKey='Stock'),
               Col('buyDate'),
               Col('buyQuantity'),
               Col('buyPrice'),
               Col('currentQuantity'),
              ]

The tables are already populated and I can fetch rows from either table 
successfully, either with index values, select or selectBy methods. But 
the join is a bit shonky, my Holding objects contain a reference to the 
correct portfolio object and a portfolioID attribute, but the Portfolio 
objects do not contain any references to holdings.

I suspect the problem lies in my choice of names. Because the table is 
called portfolios, the class Portfolio and the foreign key column 
portfolio_id I believe the foreign key is being traversed correctly. Can 
anyone suggest what I have done wrong and, more importantly, what I can 
do to get this working properly.

Thanks in advance,
Andy
-- 
--------------------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/

[SQLObject] SQLBuilder when using non-standard column names

[xtian <xt...@to...>]

When I use SQLBuilder to construct a query, the SQL clause generated is 
incorrect for anything that has a column name that deviates from Ian's 
standard. A little digging shows that eg, Organisation.q is a SmartTable 
instance, which tweaks any attribute access (like salespersonID) to 
replace camelCaseNames with corresponding underscore_names. This is the 
default attribute-to-column name mapping in SQLObject, but our database 
doesn't use this convention.

I can simply tweak the module to stop Organisation.q from being a 
SmartTable (and be explicit about the column names) - but is there any 
simple way to have SQLBuilder get the dbName from the corresponding 
SQLObject column attribute?

Thanks
Christian