Thread: [SQLObject] Table referencing itself with foreign key. bug?
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Thanos T. <th...@si...> - 2005-03-02 13:18:12
|
Hello list. My problems boil down to this: Can a have a one-to-many relation from one table to itself? If yes, how?? It is a tree-like structure. The following doesn't work: import sqlobject __connection__ = "sqlite:///home/thanos/sql/content" class Section(sqlobject.SQLObject): parent = sqlobject.ForeignKey("Section") name = sqlobject.StringCol(notNone=True) children = sqlobject.MultipleJoin("Section", orderBy="prec") Section.createTable() k = Section(name='main', parent=None) l = Section(name='foo', parent=1) m = Section(name='bar', parent=1) print l print m print k print k.children The output is: <Section 2 name='foo' parentID=1> <Section 3 name='bar' parentID=1> <Section 1 name='main' parentID=None> Traceback (most recent call last): File "siansql.py", line 36, in ? print k.children File "<string>", line 1, in <lambda> File "/usr/local/lib/python2.3/site-packages/sqlobject/joins.py", line 128, in performJoin inst.id) File "/usr/local/lib/python2.3/site-packages/sqlobject/dbconnection.py", line 420, in _SO_selectJoin return self.queryAll("SELECT %s FROM %s WHERE %s = %s" % File "/usr/local/lib/python2.3/site-packages/sqlobject/dbconnection.py", line 217, in queryAll return self._runWithConnection(self._queryAll, s) File "/usr/local/lib/python2.3/site-packages/sqlobject/dbconnection.py", line 125, in _runWithConnection val = meth(conn, *args) File "/usr/local/lib/python2.3/site-packages/sqlobject/dbconnection.py", line 210, in _queryAll self._executeRetry(conn, c, s) File "/usr/local/lib/python2.3/site-packages/sqlobject/dbconnection.py", line 196, in _executeRetry return cursor.execute(query) File "/usr/obj/i386/py-sqlite-0.5.1/fake-i386/usr/local/lib/python2.3/site-packages/sqlite/main.py", line 244, in execute _sqlite.DatabaseError: no such column: section_id Is this a bug? -- Thanos Tsouanas .: Sians http://thanos.sians.org/ .: http://www.sians.org/ |
From: Oleg B. <ph...@ma...> - 2005-03-02 14:28:48
|
On Wed, Mar 02, 2005 at 03:18:07PM +0200, Thanos Tsouanas wrote: > class Section(sqlobject.SQLObject): > parent = sqlobject.ForeignKey("Section") > name = sqlobject.StringCol(notNone=True) > children = sqlobject.MultipleJoin("Section", orderBy="prec") [skip] > _sqlite.DatabaseError: no such column: section_id Try to decalre your column children = sqlobject.MultipleJoin("Section", joinColumn="parent", orderBy="prec") or children = sqlobject.MultipleJoin("Section", joinColumn="parentID", orderBy="prec") Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Thanos T. <th...@si...> - 2005-03-02 16:07:55
|
On Wed, Mar 02, 2005 at 05:28:31PM +0300, Oleg Broytmann wrote: > On Wed, Mar 02, 2005 at 03:18:07PM +0200, Thanos Tsouanas wrote: > > class Section(sqlobject.SQLObject): > > parent = sqlobject.ForeignKey("Section") > > name = sqlobject.StringCol(notNone=True) > > children = sqlobject.MultipleJoin("Section", orderBy="prec") > [skip] > > _sqlite.DatabaseError: no such column: section_id > > Try to decalre your column > > children = sqlobject.MultipleJoin("Section", joinColumn="parent", orderBy="prec") > or > children = sqlobject.MultipleJoin("Section", joinColumn="parentID", orderBy="prec") Thanks! This is how i have it right now, and it works only if i declare my foreign key as IntCol() and not as ForeignKey() Any idea why? I suspect that ForeignKey is not allowed to point to the same table it belongs. Any ieas? -- Thanos Tsouanas .: Sians http://thanos.sians.org/ .: http://www.sians.org/ |
From: Ian B. <ia...@co...> - 2005-03-02 17:06:23
|
Thanos Tsouanas wrote: > File "/usr/obj/i386/py-sqlite-0.5.1/fake-i386/usr/local/lib/python2.3/site-packages/sqlite/main.py", line 244, in execute > _sqlite.DatabaseError: no such column: section_id If sqlite is emiting the error, it would be useful to see the SQL (both the queries and the CREATE statement). -- Ian Bicking / ia...@co... / http://blog.ianbicking.org |
From: Oleg B. <ph...@ma...> - 2005-03-02 17:10:30
|
On Wed, Mar 02, 2005 at 06:07:48PM +0200, Thanos Tsouanas wrote: > On Wed, Mar 02, 2005 at 05:28:31PM +0300, Oleg Broytmann wrote: > > On Wed, Mar 02, 2005 at 03:18:07PM +0200, Thanos Tsouanas wrote: > > > class Section(sqlobject.SQLObject): > > > parent = sqlobject.ForeignKey("Section") > > > name = sqlobject.StringCol(notNone=True) > > > children = sqlobject.MultipleJoin("Section", orderBy="prec") > > [skip] > > > _sqlite.DatabaseError: no such column: section_id > > > > Try to decalre your column > > > > children = sqlobject.MultipleJoin("Section", joinColumn="parent", orderBy="prec") > > or > > children = sqlobject.MultipleJoin("Section", joinColumn="parentID", orderBy="prec") > > This is how i have it right now, and it works only if i declare > my foreign key as IntCol() and not as ForeignKey() > > Any idea why? I suspect that ForeignKey is not allowed to point > to the same table it belongs. I think SQLObject cannot constructs joins where two tables are actually the same table. Not a bug - a design issue. Hard to fix, I suspect. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Luke O. <lu...@me...> - 2005-03-02 18:58:32
|
For reference, one of my current projects uses exactly this child-parent relationship: parent = ForeignKey('Leaf') children = MultipleJoin('Leaf', joinColumn='parentID') Can't be much more help than that, this is in an old instance of SQLObject (0.5), so perhaps things have changed. - Luke >> This is how i have it right now, and it works only if i declare >> my foreign key as IntCol() and not as ForeignKey() >> >> Any idea why? I suspect that ForeignKey is not allowed to point >> to the same table it belongs. > > I think SQLObject cannot constructs joins where two tables are > actually the same table. Not a bug - a design issue. Hard to fix, I > suspect. > > Oleg. |