Ok..
Again, beginner at Python and SQLObject etc...
Lets say I have two tables in SQLite:
class system(SQLObject):
sysname = ForeignKey('sysname')
ans = StringCol()
notes = StringCol()
auditor = ForeignKey('uname')
complete = BoolCol()
status = ForeignKey('status')
req = ForeignKey('requirement')
class uname(SQLObject):
fname = StringCol()
lname = StringCol()
Notice that the system.auditor is a foreign key to the uname table
(uname.id).
Now playing around I noticed that I can do this:
>>> from sqlobject.sqlbuilder import *
>>> for system in system.select():
... print system.ans, system.auditor
...
This is complete. <uname 1 fname='Jeremy' lname='Finke'>
Yes. <uname 2 fname='Amy' lname='Finke'>
test <uname 1 fname='Jeremy' lname='Finke'>
Ok.. so it returns the system.auditor as a list(??). Great... Further
playing around I got this:
>>> for system in system.select():
... print system.ans, system.auditor.fname
...
This is complete. Jeremy
Yes. Amy
test Jeremy
Even better!! It automatically transverses the foreign keys and pulls
out the correct value. So, then I tried this:
>>> for system in system.select(system.q.auditor.fname=="Jeremy"):
... print system.ans, system.auditor.fname
...
Traceback (most recent call last):
File "<console>", line 1, in ?
File
"/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/site-packages/SQLObject-0.7.0-py2.4.egg/sqlobject/sqlbuilder.py",
line 362, in __getattr__
self.soClass.sqlmeta.columns[attr].dbName,
KeyError: 'auditor'
No go... It does not like following the object through the table on
the query. So then I tried this:
>>> for system in system.select(system.q.auditor=="1"):
... print system.ans, system.auditor.fname
...
Traceback (most recent call last):
File "<console>", line 1, in ?
File
"/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/site-packages/SQLObject-0.7.0-py2.4.egg/sqlobject/sqlbuilder.py",
line 362, in __getattr__
self.soClass.sqlmeta.columns[attr].dbName,
KeyError: 'auditor'
Looks like the same error to me. So, I eventually tried this:
>>> for system in system.select(system.q.auditorID=="1"):
... print system.ans, system.auditor.fname
...
This is complete. Jeremy
test Jeremy
Ahhh better results!!
I have the following questions:
Why can I refer to system.auditor.fname in results but not in the
query?
Why do I need the ID after the auditor in the query as in
system.q.auditorID? When I look at the raw db (sqllite), I actually
see auditor_ID.
Since I cannot specify system.q.auditor.fname=="Jeremy" in the query,
how do I do that? Do I need to first query the the uname table, store
that result in a variable, and then use that in the query on the system
table?
And finally... Instead of printing the results of the query can I store
those in a multidemensional array? Does python have such a concept?
Thanks!
Jeremy
