Thread: [SQLObject] How to provide default non-null values for foreign keys?
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Matthew W. <ma...@tp...> - 2008-07-23 19:50:58
|
I have an employees table and a departments table, and every employee has a foreign key to the departments table. 99% of the time, new employees belong to one particular department, "SALES". How can I make the SALES department the default department for new employees? I tried this: class Department(SQLObject): name = UnicodeCol(alternateID=True) class Employee(SQLObject): user_name = UnicodeCol(alternateID=True) department = ForeignKey(default=Department.byName("SALES") And it failed. Any ideas? Thanks in advance. Matt |
From: Oleg B. <ph...@ph...> - 2008-07-23 20:01:25
|
On Wed, Jul 23, 2008 at 07:50:47PM +0000, Matthew Wilson wrote: > I have an employees table and a departments table, and every employee > has a foreign key to the departments table. > > 99% of the time, new employees belong to one particular department, > "SALES". > > How can I make the SALES department the default department for new > employees? > > I tried this: > > class Department(SQLObject): > name = UnicodeCol(alternateID=True) > > class Employee(SQLObject): > user_name = UnicodeCol(alternateID=True) > department = ForeignKey(default=Department.byName("SALES") > > And it failed. In what way? For .byName() to work Department.name must be an alternateID column (which implies uniqueness). This works for me: class Department(SQLObject): name = StringCol(default=None, alternateID=True) Department.createTable() Department(name='Directorat') Department(name='Sales') class Employee(SQLObject): name = StringCol(default=None) department = ForeignKey('Department', default=Department.byName('Sales')) Employee.createTable() Employee(name='slave') Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Matthew W. <ma...@tp...> - 2008-07-23 21:04:26
|
On Wed 23 Jul 2008 04:01:19 PM EDT, Oleg Broytmann wrote: > In what way? For .byName() to work Department.name must be an > alternateID column (which implies uniqueness). > This works for me: > > class Department(SQLObject): > name = StringCol(default=None, alternateID=True) > > Department.createTable() > > Department(name='Directorat') > Department(name='Sales') > > class Employee(SQLObject): > name = StringCol(default=None) > department = ForeignKey('Department', default=Department.byName('Sales')) > > Employee.createTable() > Employee(name='slave') It fails in the instance where I'm importing a model with an empty database. So for example, my unit tests use a sqlite :memory: database, and this won't work. The byName lookup raises an SQLObjectNotFound exception. Sorry, I should have been more clear. Matt |
From: Oleg B. <ph...@ph...> - 2008-07-23 21:26:50
|
On Wed, Jul 23, 2008 at 09:04:10PM +0000, Matthew Wilson wrote: > The byName lookup raises an SQLObjectNotFound exception. I don't see how you can misinterpret this. There is no 'Sales' department in the DB, so why do you expect SQLObject magically finds one?! .byName() is an "active" operation, it cannot be done lazily. You have to know in advance the id of the 'Sales' department. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Luke O. <lo...@gm...> - 2008-07-23 23:59:23
|
As Oleg notes, your default argument call to byName is evaluated at class creation time ie, before you've created the Sales Department record. In the same way, if you had: when = DateTimeCol(default=now()) the default would always be the value returned by now() at the time the class is imported. However, default can also be a callable. In the datetime example, changing it to: when = DateTimeCol(default=now) will cause SQLObject to call now() separately for each insert (that needs the default for this column). So as long as the Sales department is created before you actually insert any Employees, something like this should work to make that byName call lazy: def get_sales_dept(): return Department.byName('SALES') class Employee(SQLObject): user_name = UnicodeCol(alternateID=True) department = ForeignKey(default=get_sales_dept) - Luke |
From: Matthew W. <ma...@tp...> - 2008-07-24 13:50:16
|
On Wed 23 Jul 2008 05:26:45 PM EDT, Oleg Broytmann wrote: > On Wed, Jul 23, 2008 at 09:04:10PM +0000, Matthew Wilson wrote: >> The byName lookup raises an SQLObjectNotFound exception. > > I don't see how you can misinterpret this. There is no 'Sales' > department in the DB, so why do you expect SQLObject magically finds one?! > .byName() is an "active" operation, it cannot be done lazily. You have > to know in advance the id of the 'Sales' department. So, if I wanted to make sure that some rows are always in the Sales table, is there a recommended approach? Matt |
From: Oleg B. <ph...@ph...> - 2008-07-24 15:03:51
|
On Thu, Jul 24, 2008 at 01:49:56PM +0000, Matthew Wilson wrote: > So, if I wanted to make sure that some rows are always in the Sales > table, is there a recommended approach? Depends on the applications and the DB. If you know the row is in the DB already just point to it using "...FOREIGN KEY REFERENCES...." (I mean SQL CREATE TABLE). If you aren't sure the row is there, I doubt there is a magical service in an SQL backend. You just have to check regularly if the row has been inserted. SQLObject can help a bit - Luke Opperman reminded how you can pass a callable as a default value - but that's all. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |