sqlobject-discuss

[SQLObject] Multiple relations between two tables

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

Hi,

How do I define two (or more) relationships between two tables in my 
schema? I have a currencies table and a currency_rates table. Before I 
try and add the relationships their definitions are;

"""
class Currencies(SQLObject):
	currencyCode=StringCol(length=6)
	currencyDesc=StringCol(length=255)
	exchanges=MultipleJoin('Exchanges', joinColumn='currency_id')
	countries=MultipleJoin('Countries', joinColumn='currency_id')

class CurrencyRates(SQLObject):
	rateDate=DateTimeCol()
	rate=Col()
"""

Now I get confused when I add the two relationships (currency from and 
currency to). I tried;

"""
class Currencies(SQLObject):
	currencyCode=StringCol(length=6)
	currencyDesc=StringCol(length=255)
	exchanges=MultipleJoin('Exchanges', joinColumn='currency_id')
	countries=MultipleJoin('Countries', joinColumn='currency_id')
	ratesFrom=MultipleJoin('CurrencyRates', joinColumn='currency_from_id')
	ratesTo=MultipleJoin('CurrencyRates', joinColumn='currency_to_id')

class CurrencyRates(SQLObject):
	rateDate=DateTimeCol()
	rate=Col()
	currencyFrom=ForeignKey('Currencies')
	currencyTo=ForeignKey('Currencies')
"""

But this causes an error on import;

"""
Traceback (most recent call last):
   File "classes.py", line 51, in ?
     class CurrencyRates(SQLObject):
   File "C:\PROGRA~2\PYTHON22\Lib\site-packages\SQLObject\SQLObject.py", 
line 233, in __new__
     newClass.addColumn(column)
   File "C:\PROGRA~2\PYTHON22\Lib\site-packages\SQLObject\SQLObject.py", 
line 503, in addColumn
     '_SO_class_%s' % column.foreignKey)
   File "C:\PROGRA~2\PYTHON22\Lib\site-packages\SQLObject\SQLObject.py", 
line 102, in addNeedSet
     getattr(obj, attr)(cls)
   File "C:\PROGRA~2\PYTHON22\Lib\site-packages\SQLObject\SQLObject.py", 
line 404, in __new__
     val._init(id, connection, selectResults)
   File "C:\PROGRA~2\PYTHON22\Lib\site-packages\SQLObject\SQLObject.py", 
line 662, in _init
     selectResults = (connection or 
self._connection)._SO_selectOne(self, dbNames)
   File 
"C:\PROGRA~2\PYTHON22\Lib\site-packages\SQLObject\DBConnection.py", line 
267, in _SO_selectOne
     return self.queryOne("SELECT %s FROM %s WHERE %s = %s" %
   File 
"C:\PROGRA~2\PYTHON22\Lib\site-packages\SQLObject\SQLBuilder.py", line 
126, in sqlRepr
     raise ValueError, "Unknown SQL builtin type: %s for %s" % \
ValueError: Unknown SQL builtin type: <class 
'SQLObject.SQLObject.MetaSQLObject'> for <class '__main__.Currencies'>
"""

Any suggestions? Apologies if this question has already been answered.

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

Re: [SQLObject] Multiple relations between two tables

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

This is the same bug that Javier was encountering.  There's a fix in
CVS, or if SF CVS is slow (which it has been) and you want immediate
satisfaction, use this replacement function in SQLObject.py:

def addNeedSet(obj, setCls, registry, attr):
    try:
        cls = findClass(setCls, registry=registry)
        if callable(getattr(obj, attr, None)):
            if not isinstance(getattr(obj, attr), type):
                # Otherwise we got a class, which means we probably
                # already set this column.
                getattr(obj, attr)(cls)
        else:
            setattr(obj, attr, cls)
        return
    except KeyError:
        pass
    q = needSet.setdefault(registry, {}).setdefault(setCls, [])
    q.append((obj, attr))



On Wed, 2003-07-30 at 07:17, Andy Todd wrote:
> Hi,
> 
> How do I define two (or more) relationships between two tables in my 
> schema? I have a currencies table and a currency_rates table. Before I 
> try and add the relationships their definitions are;
> 
> """
> class Currencies(SQLObject):
> 	currencyCode=StringCol(length=6)
> 	currencyDesc=StringCol(length=255)
> 	exchanges=MultipleJoin('Exchanges', joinColumn='currency_id')
> 	countries=MultipleJoin('Countries', joinColumn='currency_id')
> 
> class CurrencyRates(SQLObject):
> 	rateDate=DateTimeCol()
> 	rate=Col()
> """
> 
> Now I get confused when I add the two relationships (currency from and 
> currency to). I tried;
> 
> """
> class Currencies(SQLObject):
> 	currencyCode=StringCol(length=6)
> 	currencyDesc=StringCol(length=255)
> 	exchanges=MultipleJoin('Exchanges', joinColumn='currency_id')
> 	countries=MultipleJoin('Countries', joinColumn='currency_id')
> 	ratesFrom=MultipleJoin('CurrencyRates', joinColumn='currency_from_id')
> 	ratesTo=MultipleJoin('CurrencyRates', joinColumn='currency_to_id')
> 
> class CurrencyRates(SQLObject):
> 	rateDate=DateTimeCol()
> 	rate=Col()
> 	currencyFrom=ForeignKey('Currencies')
> 	currencyTo=ForeignKey('Currencies')
> """
> 
> But this causes an error on import;
> 
> """
> Traceback (most recent call last):
>    File "classes.py", line 51, in ?
>      class CurrencyRates(SQLObject):
>    File "C:\PROGRA~2\PYTHON22\Lib\site-packages\SQLObject\SQLObject.py", 
> line 233, in __new__
>      newClass.addColumn(column)
>    File "C:\PROGRA~2\PYTHON22\Lib\site-packages\SQLObject\SQLObject.py", 
> line 503, in addColumn
>      '_SO_class_%s' % column.foreignKey)
>    File "C:\PROGRA~2\PYTHON22\Lib\site-packages\SQLObject\SQLObject.py", 
> line 102, in addNeedSet
>      getattr(obj, attr)(cls)
>    File "C:\PROGRA~2\PYTHON22\Lib\site-packages\SQLObject\SQLObject.py", 
> line 404, in __new__
>      val._init(id, connection, selectResults)
>    File "C:\PROGRA~2\PYTHON22\Lib\site-packages\SQLObject\SQLObject.py", 
> line 662, in _init
>      selectResults = (connection or 
> self._connection)._SO_selectOne(self, dbNames)
>    File 
> "C:\PROGRA~2\PYTHON22\Lib\site-packages\SQLObject\DBConnection.py", line 
> 267, in _SO_selectOne
>      return self.queryOne("SELECT %s FROM %s WHERE %s = %s" %
>    File 
> "C:\PROGRA~2\PYTHON22\Lib\site-packages\SQLObject\SQLBuilder.py", line 
> 126, in sqlRepr
>      raise ValueError, "Unknown SQL builtin type: %s for %s" % \
> ValueError: Unknown SQL builtin type: <class 
> 'SQLObject.SQLObject.MetaSQLObject'> for <class '__main__.Currencies'>
> """
> 
> Any suggestions? Apologies if this question has already been answered.
> 
> Regards,
> Andy

Re: [SQLObject] Multiple relations between two tables

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

Ian Bicking wrote:
> This is the same bug that Javier was encountering.  There's a fix in
> CVS, or if SF CVS is slow (which it has been) and you want immediate
> satisfaction, use this replacement function in SQLObject.py:
> 
> def addNeedSet(obj, setCls, registry, attr):
>     try:
>         cls = findClass(setCls, registry=registry)
>         if callable(getattr(obj, attr, None)):
>             if not isinstance(getattr(obj, attr), type):
>                 # Otherwise we got a class, which means we probably
>                 # already set this column.
>                 getattr(obj, attr)(cls)
>         else:
>             setattr(obj, attr, cls)
>         return
>     except KeyError:
>         pass
>     q = needSet.setdefault(registry, {}).setdefault(setCls, [])
>     q.append((obj, attr))
> 
> 
> 
> On Wed, 2003-07-30 at 07:17, Andy Todd wrote:
> 
>>Hi,
>>
>>How do I define two (or more) relationships between two tables in my 
>>schema? I have a currencies table and a currency_rates table. Before I 
>>try and add the relationships their definitions are;
>>
[snip]
>>

Thanks Ian, works like a charm. And thanks for SQLObject too, its a gem.

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