Thread: [SQLObject] Creating table using "createTable" fails partly?
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: <ber...@de...> - 2005-07-25 15:21:54
|
I have a problem understanding the working of SQLObject. I have a small sample I use for learning SQLObject: >cat sample1.py import sqlobject conn = 'sqlite:///tmp/database.db' class _GLSQLObject(sqlobject.SQLObject): _connection = conn class sqlmeta: # style = MixedCaseStyle(longID=True) style = sqlobject.MixedCaseUnderscoreStyle(longID=True) class Project(_GLSQLObject): workpackages = sqlobject.RelatedJoin('Workpackage') name = sqlobject.StringCol() leader = sqlobject.ForeignKey('Person') steeringCommittee = sqlobject.RelatedJoin('Person') class Workpackage(_GLSQLObject): name = sqlobject.StringCol() class Person(_GLSQLObject): firstName = sqlobject.StringCol() def create(): Project.createTable() Workpackage.createTable() Person.createTable() if __name__ == '__main__': create() I call >rm /tmp/database.db ; python sample1.py >sqlite3 /tmp/database.db SQLite version 3.2.2 Enter ".help" for instructions sqlite> .schema CREATE TABLE person ( person_id INTEGER PRIMARY KEY, first_name TEXT ); CREATE TABLE project ( project_id INTEGER PRIMARY KEY, name TEXT, leader_id INT ); CREATE TABLE project_workpackage ( project_id INT NOT NULL, workpackage_id INT NOT NULL ); CREATE TABLE workpackage ( workpackage_id INTEGER PRIMARY KEY, name TEXT ); sqlite> Now I am missing a table "project_person": CREATE TABLE project_person ( project_id INT NOT NULL, person_id INT NOT NULL ); Is this a bug, or something I don't understand? Kind regards Berthold Höllmann -- __ Address: G / \ L Germanischer Lloyd phone: +49-40-36149-7374 -+----+- Vorsetzen 35 P.O.Box 111606 fax : +49-40-36149-7320 \__/ D-20459 Hamburg D-20416 Hamburg |
From: Oleg B. <ph...@ph...> - 2005-07-25 15:47:23
|
On Mon, Jul 25, 2005 at 05:08:55PM +0200, Berthold H?llmann wrote: > Now I am missing a table "project_person": print Project._getJoinsToCreate() [<sqlobject.joins.SORelatedJoin object at 0x4088b54c>] Seems like a bug. It should return a list of two elements. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: John P. <joo...@gm...> - 2005-07-25 16:20:11
|
Do you need a related join in the Person class as well? My guess is that would make the example work. John On 25/07/05, Oleg Broytmann <ph...@ph...> wrote: >=20 > On Mon, Jul 25, 2005 at 05:08:55PM +0200, Berthold H?llmann wrote: > > Now I am missing a table "project_person": >=20 > print Project._getJoinsToCreate() >=20 > [<sqlobject.joins.SORelatedJoin object at 0x4088b54c>] >=20 > Seems like a bug. It should return a list of two elements. >=20 > Oleg. > -- > Oleg Broytmann http://phd.pp.ru/ ph...@ph... > Programmers don't die, they just GOSUB without RETURN. >=20 >=20 > ------------------------------------------------------- > SF.Net email is sponsored by: Discover Easy Linux Migration Strategies > from IBM. Find simple to follow Roadmaps, straightforward articles, > informative Webcasts and more! Get everything you need to get up to > speed, fast. http://ads.osdn.com/?ad_id=3D7477&alloc_id=3D16492&op=3Dclic= k > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > |
From: <bh...@de...> - 2005-07-25 20:01:47
|
John Page <joo...@gm...> writes: > Do you need a related join in the Person class as well? > My guess is that would make the example work. But that won't make sense, because i also need a link from Workpackage to Person as well, as in other classes. Alone for the "Project" class. Does the related join from "Person" point to the Project.leader or one member of Project.steeringCommittee? Regards Berthold > > John > > On 25/07/05, Oleg Broytmann <ph...@ph...> wrote: >>=20 >> On Mon, Jul 25, 2005 at 05:08:55PM +0200, Berthold H?llmann wrote: >> > Now I am missing a table "project_person": >>=20 >> print Project._getJoinsToCreate() >>=20 >> [<sqlobject.joins.SORelatedJoin object at 0x4088b54c>] >>=20 >> Seems like a bug. It should return a list of two elements. >>=20 >> Oleg. --=20 "Es gelten die Regeln der christlichen Seefahrt: Rot und Gr=C3=BCn markiert das sichere Fahrwasser, Schwarz und Gelb markieren Untiefen und Wracks." Christa Sager, Bundestagsfraktionsvorsitzende B=C3=BCndnis 90/Gr=C3=BCne |
From: John P. <joo...@gm...> - 2005-07-26 09:49:18
|
On 25/07/05, Berthold H=F6llmann <bh...@de...> wrote: > John Page <joo...@gm...> writes: >=20 > > Do you need a related join in the Person class as well?=20 > > My guess is that would make the example work. >=20 > But that won't make sense, because i also need a link from Workpackage > to Person as well, as in other classes. Alone for the "Project" class.=20 > Does the related join from "Person" point to the Project.leader or one > member of Project.steeringCommittee? It sounds like you are misunderstanding Joins much as I did a couple of=20 weeks ago.=20 Their names really don't help one understand things. You need to choose what sort of join to use based on whether the=20 relationship is one-one, one-many or many-many. Not on how you think you=20 might want to access the data. i.e. Suppose every Project has many Person's and every Person might work on= =20 several Project's; then you have a many-many situation, which is implemented using RelatedJoin= s=20 at both ends. Suppose every Project has many Person's and every Person works on only one= =20 Project, then the relationship is one-many, which is implemented by giving= =20 each person a ForeignKey('Project'), and if you like you can give each=20 Project a MultipleJoin('Person') which gives you a list of the Person's wit= h=20 their foreignkey pointing to this Project. I know this results in some classes having fields that you think you might= =20 never use, but you have to live with that. To recap, if you have a RelatedJoin on one end, you need one on the other= =20 end too. If you have a ForeignKey at one end, you *can* have a MultipleJoin at the= =20 other end if you like. I hope this helps God bless, John |
From: michelts <mic...@gm...> - 2005-07-26 12:14:18
|
Hi John, I like your explanation about joins =3D), the wiki has something about joins but its based on the 0.5.x version, you could update the wiki with your explanation here... Regards, > > > Do you need a related join in the Person class as well?=20 > > > My guess is that would make the example work. > >=20 > > But that won't make sense, because i also need a link from Workpackage > > to Person as well, as in other classes. Alone for the "Project" class.= =20 > > Does the related join from "Person" point to the Project.leader or one > > member of Project.steeringCommittee? >=20 > It sounds like you are misunderstanding Joins much as I did a couple of > weeks ago.=20 > Their names really don't help one understand things. > You need to choose what sort of join to use based on whether the > relationship is one-one, one-many or many-many. Not on how you think you > might want to access the data. > =20 > i.e. Suppose every Project has many Person's and every Person might work= on > several Project's; > then you have a many-many situation, which is implemented using > RelatedJoins at both ends. > =20 > Suppose every Project has many Person's and every Person works on only o= ne > Project, then the relationship is one-many, which is implemented by givin= g > each person a ForeignKey('Project'), and if you like you can give each > Project a MultipleJoin('Person') which gives you a list of the Person's w= ith > their foreignkey pointing to this Project. > =20 > I know this results in some classes having fields that you think you mig= ht > never use, but you have to live with that. > =20 > To recap, if you have a RelatedJoin on one end, you need one on the othe= r > end too. > If you have a ForeignKey at one end, you *can* have a MultipleJoin at th= e > other end if you like. > =20 > I hope this helps > =20 > God bless, > John > =20 --=20 Michel Thadeu Sabchuk Curitiba - Brasil |
From: <ber...@de...> - 2005-07-26 13:08:09
|
John Page <joo...@gm...> writes: > On 25/07/05, Berthold Höllmann <bh...@de...> wrote: >> John Page <joo...@gm...> writes: >> >> > Do you need a related join in the Person class as well? >> > My guess is that would make the example work. >> >> But that won't make sense, because i also need a link from Workpackage >> to Person as well, as in other classes. Alone for the "Project" class. >> Does the related join from "Person" point to the Project.leader or one >> member of Project.steeringCommittee? > > It sounds like you are misunderstanding Joins much as I did a couple of > weeks ago. > Their names really don't help one understand things. > You need to choose what sort of join to use based on whether the > relationship is one-one, one-many or many-many. Not on how you think you > might want to access the data. > > i.e. Suppose every Project has many Person's and every Person might work on > several Project's; > then you have a many-many situation, which is implemented using RelatedJoins > at both ends. But each "Project" also has "Workpackage"s that have "Subproject"s. The "Workpackage"s and "Subproject"s have leader and deputy leaders that are "Person"s also. So where are the RelatedJoins in "Person" pointing to? To "Project", "Workpackage", or "Subproject"? The "Leader" in one "Workpackage" could be "Leader" for a "Subproject" also or "DeputyLeader" for another "Worpackage", or "Subproject". So it seems to me there is no easy way to model this depenedencies using SQLObject? Berthold > Suppose every Project has many Person's and every Person works on only one > Project, then the relationship is one-many, which is implemented by giving > each person a ForeignKey('Project'), and if you like you can give each > Project a MultipleJoin('Person') which gives you a list of the Person's with > their foreignkey pointing to this Project. > > I know this results in some classes having fields that you think you might > never use, but you have to live with that. > > To recap, if you have a RelatedJoin on one end, you need one on the > other end too. If you have a ForeignKey at one end, you *can* have > a MultipleJoin at the other end if you like. > > I hope this helps > > God bless, > John -- __ Address: G / \ L Germanischer Lloyd phone: +49-40-36149-7374 -+----+- Vorsetzen 35 P.O.Box 111606 fax : +49-40-36149-7320 \__/ D-20459 Hamburg D-20416 Hamburg |
From: John P. <joo...@gm...> - 2005-07-26 13:59:48
|
On 26/07/05, Berthold H=F6llmann <ber...@de...> wrote: > But each "Project" also has "Workpackage"s that have > "Subproject"s. The "Workpackage"s and "Subproject"s have leader and > deputy leaders that are "Person"s also. So where are the RelatedJoins in > "Person" pointing to? To "Project", "Workpackage", or "Subproject"? Wherever you tell them to point. e.g. RelatedJoin('Project') points to=20 Project relying on there being a RelatedJoin('Person') in Project for=20 symmetry. Oh, you probably want to specify a joinColumn If there is more than one column linking that table to this you can choose= =20 which you want to join to by adding joinColumn=3D'Column' as an argument to= =20 the join. e.g. see below. You don't need joinColumn if there is no=20 ambiguity. I think I am right about these, I haven't actually had occasion to use one. > The "Leader" in one "Workpackage" could be "Leader" for a "Subproject" > also or "DeputyLeader" for another "Worpackage", or "Subproject". So > it seems to me there is no easy way to model this depenedencies using > SQLObject? Sounds like a challenge! :-) I hadn't mentioned joinColumn before. These are only partial - I haven't put in all the relationships you would= =20 need, but hopefully there are enough to show how it is done. How about...? class Project(SQLObject): leader =3D ForeignKey('Person') deputyLeader =3D ForeignKey('Person') workers =3D RelatedJoin('Person') class Workpackage(SQLobject): parentProject =3D ForeignKey('Project') subProjects =3D MultipleJoin('Subproject') ... class Subproject(SQLObject): parentWorkpackage =3D ForeignKey('Workpackage') leader =3D ForeignKey('Person') ... class Person(SQLObject): leadsProjects =3D MultipleJoin('Project', joinColumn=3D'leader') deputyLeadsProjects =3D MultipleJoin('Project', joinColumn=3D'deputyLeader'= ) leadsSubprojects =3D MultipleJoin('Subproject') worksForProjects =3D RelatedJoin('Project') ... hopefully should be enough to see what is going on. Here the related joins between Person and Project do not need to be told=20 their joinColumn, because they can only link to each other. Or how about...? class Project(SQLObject): leader =3D ForeignKey('Person') deputyLeader =3D ForeignKey('Person') childProjects =3D MultipleJoin('Projects') parentProject =3D ForeignKey('Projects') class Person(SQLObject): leadsProjects =3D MultipleJoin('Project', joinColumn=3D'leader') deputyForProjects =3D MultipleJoin('Project', joinColumn=3D'deputyLeader') Here, I have assumed that workpackages are actually much like projects,=20 (probably not a valid assumption, but for an example), so I did a couple of= =20 joins to self in Project for the subproject relationships. The only=20 disadvantage is that in SQLObject there is no possibility of assigning Null= =20 value for the parentProject foreign key. One workaround: you could make it= =20 point to itself if there is no parent and then deal with itself turning up= =20 among its childProjects. A bit ugly in this case. Again, I hope this helps. God bless, John |
From: Oleg B. <ph...@ph...> - 2005-07-27 12:35:00
|
On Mon, Jul 25, 2005 at 05:08:55PM +0200, Berthold H?llmann wrote: > Now I am missing a table "project_person": def _getJoinsToCreate(cls): joins = [] for join in cls.sqlmeta._joinList: if not join: continue if not join.hasIntermediateTable(): continue if join.soClass.__name__ > join.otherClass.__name__: continue joins.append(join) return joins Does anyone have an idea what is going on with if join.soClass.__name__ > join.otherClass.__name__: ?! Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Kevin D. <da...@gm...> - 2005-07-27 16:05:22
|
On 7/27/05, Oleg Broytmann <ph...@ph...> wrote: > On Mon, Jul 25, 2005 at 05:08:55PM +0200, Berthold H?llmann wrote: > > Now I am missing a table "project_person": >=20 > def _getJoinsToCreate(cls): > joins =3D [] > for join in cls.sqlmeta._joinList: > if not join: > continue > if not join.hasIntermediateTable(): > continue > if join.soClass.__name__ > join.otherClass.__name__: > continue > joins.append(join) > return joins >=20 > Does anyone have an idea what is going on with > if join.soClass.__name__ > join.otherClass.__name__: > ?! I would hazard a guess that that is trying to protect against creating the table twice (on both sides of the join). It only does it for the class with the name that compares lower. Kevin |
From: Oleg B. <ph...@ph...> - 2005-07-27 13:21:46
|
On Wed, Jul 27, 2005 at 09:04:07AM -0400, Kevin Dangoor wrote: > > Does anyone have an idea what is going on with > > if join.soClass.__name__ > join.otherClass.__name__: > > ?! > > I would hazard a guess that that is trying to protect against creating > the table twice (on both sides of the join). It only does it for the > class with the name that compares lower. This means that RelatedJoin must be declared on both sides. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Kevin D. <da...@gm...> - 2005-07-27 16:32:12
|
On 7/27/05, Oleg Broytmann <ph...@ph...> wrote: > On Wed, Jul 27, 2005 at 09:04:07AM -0400, Kevin Dangoor wrote: > > > Does anyone have an idea what is going on with > > > if join.soClass.__name__ > join.otherClass.__name__: > > > ?! > > > > I would hazard a guess that that is trying to protect against creating > > the table twice (on both sides of the join). It only does it for the > > class with the name that compares lower. >=20 > This means that RelatedJoin must be declared on both sides. Yes. Or, you have to know to put it in the alphabetically lower class :) A better solution here would probably be to use a set rather than a list. Kevin |
From: Oleg B. <ph...@ph...> - 2005-07-27 17:12:36
|
On Wed, Jul 27, 2005 at 10:42:57AM -0400, Kevin Dangoor wrote: > A better solution here would probably be to use a set rather than a list. I don't see how a set can help here. We need not to create the intermediate table if it was already created. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Kevin D. <da...@gm...> - 2005-07-27 16:22:23
|
On 7/27/05, Oleg Broytmann <ph...@ph...> wrote: > On Wed, Jul 27, 2005 at 10:42:57AM -0400, Kevin Dangoor wrote: > > A better solution here would probably be to use a set rather than a lis= t. >=20 > I don't see how a set can help here. We need not to create the > intermediate table if it was already created. I was thinking that the whole point of the list was to keep track of what needs to be created, assuming it was difficult to determine what tables were already in the database. If it's easy to see if the table is already there, then indeed just don't create the table if it's already there! Kevin |
From: Oleg B. <ph...@ph...> - 2005-07-27 16:28:50
|
On Mon, Jul 25, 2005 at 05:08:55PM +0200, Berthold H?llmann wrote: > import sqlobject > > conn = 'sqlite:///tmp/database.db' > > class _GLSQLObject(sqlobject.SQLObject): > _connection = conn > class sqlmeta: > # style = MixedCaseStyle(longID=True) > style = sqlobject.MixedCaseUnderscoreStyle(longID=True) > > class Project(_GLSQLObject): > workpackages = sqlobject.RelatedJoin('Workpackage') > name = sqlobject.StringCol() > leader = sqlobject.ForeignKey('Person') > steeringCommittee = sqlobject.RelatedJoin('Person') > > class Workpackage(_GLSQLObject): > name = sqlobject.StringCol() > > class Person(_GLSQLObject): > firstName = sqlobject.StringCol() > > def create(): > Project.createTable() > Workpackage.createTable() > Person.createTable() > > if __name__ == '__main__': > create() The correct version of the program is: #! /usr/bin/env python import sqlobject conn = 'sqlite:/:memory:?debug=1' class _GLSQLObject(sqlobject.SQLObject): _connection = conn class sqlmeta: # style = MixedCaseStyle(longID=True) style = sqlobject.MixedCaseUnderscoreStyle(longID=True) class Project(_GLSQLObject): workpackages = sqlobject.RelatedJoin('Workpackage') name = sqlobject.StringCol() leader = sqlobject.ForeignKey('Person') steeringCommittee = sqlobject.RelatedJoin('Person') class Workpackage(_GLSQLObject): name = sqlobject.StringCol() projects = sqlobject.RelatedJoin('Project') class Person(_GLSQLObject): firstName = sqlobject.StringCol() projects = sqlobject.RelatedJoin('Project') if __name__ == '__main__': Project.createTable() Workpackage.createTable() Person.createTable() person1 = Person(firstName="Person One") proj1 = Project(name="Project One", leader=person1) person1.addProject(proj1) print person1.projects print proj1.workpackages print proj1.steeringCommittee Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: <ber...@xn...> - 2005-07-27 20:01:55
|
Oleg Broytmann <ph...@ph...> writes: > On Mon, Jul 25, 2005 at 05:08:55PM +0200, Berthold H?llmann wrote: >> import sqlobject ... >> create() > > The correct version of the program is: ... I implemented my program along the tips I got from John Page earlier, and it seems, my program starts to work. Thank you Berthold --=20 "Es gelten die Regeln der christlichen Seefahrt: Rot und Gr=C3=BCn markiert das sichere Fahrwasser, Schwarz und Gelb markieren Untiefen und Wracks." Christa Sager, Bundestagsfraktionsvorsitzende B=C3=BCndnis 90/Gr=C3=BCne |