Hi!
On Tue, Dec 15, 2015 at 03:49:29PM -0800, jos...@mailmag.net wrote:
> So I've been absolutely loving sqlobject, until I get to actually get to
> making relations between tables. At this point, I feel that the
> 'documentation' covers some points, but really doesn't explain other
> bits very well at all.
Patches for documentation will be gladly accepted, as well as patches
for code and for tests.
> So I have 2 problems:
>
> The first is that when running my program the first time, it fails to
> create all the tables (using .createTable(ifNotExists = True)) and only
> several tables are generated, before it errors out with:
>
> > sqlobject.dberrors.OperationalError: Can't create table
> > `rainmeter`.`#sql-65c_209` (errno: 150 "Foreign key constraint is
> > incorrectly formed")
>
> Then running it again causes the rest of the tables to be created.
>
> Here's the relevant parts of my class definitions:
>
> > class Table_Sensors(sqlobject.SQLObject):
> > name = sqlobject.StringCol(length=32)
> > group = sqlobject.ForeignKey("Table_Groups")
>
> > class Table_Groups(sqlobject.SQLObject):
> > name = sqlobject.StringCol(length=32)
> > sensors = sqlobject.MultipleJoin("Table_Sensors")
> > Table_Sensors.createTable(ifNotExists = True)
> > Table_Groups.createTable(ifNotExists = True)
You create table in the wrong order. Table_Sensors has a ForeignKey
pointing to to Table_Groups so you must create Table_Groups first and
then Table_Sensors.
> Anyways, this is inconvenient, but it does work after running it again,
> so I can put up with it. The real problem I've been having is that I'm
> having trouble figuring out how to select all entries in Table_Sensors
> that point to a specific Table_Groups.
>
> Here's one example I've tried:
>
> > Table_Groups.select(Table_Groups.q.name=="Feels_Like",
> > Table_Groups.q.sensors==Table_Sensors.q.group,
> > orderBy=Table_Sensors.q.sort_order)
There is a bug in the query: it lists two conditions separated by a
comma. That doesn't work because the second condition becomes the second
parameter for .select() instead of being a part of the condition. You
must join conditions using either AND() from sqlbuilder or '&' operator:
Table_Groups.select(Table_Groups.q.name=="Feels_Like" &
Table_Groups.q.sensors==Table_Sensors.q.group,
orderBy=Table_Sensors.q.sort_order)
> But no matter what I try, even a much simpler query, It just spits back:
>
> > AttributeError: Table_Groups instance has no attribute 'sensors'
In SQL sense Table_Groups really doesn't have an attribute 'sensors'.
MultipleJoin doesn't add an attribute to SQL table. Instead it instructs
SQLObject: "when the user asks for Table_Groups.sensors select rows from
Table_Sensors that point to that Table_Groups' row".
So this query works:
Table_Groups.select(Table_Groups.q.name=="Feels_Like" &
Table_Groups.q.id==Table_Sensors.q.group,
orderBy=Table_Sensors.q.id)
BTW, there is no need to construct an SQL query in your case -- use
the power of SQLObject. If you want to select Table_Sensors for a group
just fetch the group and ask for its sensors.
First, let's fix the join, it should explicitly declare that it
points to 'id' column in the other table:
class Table_Groups(SQLObject):
name = StringCol(length=32)
sensors = MultipleJoin("Table_Sensors", joinColumn="id")
group = Table_Groups.select(Table_Groups.q.name=='Feels_Like')[0]
for sensor in group.sensors:
print sensor
> As a side-note, I think some of the things I'm trying to do, I could use
> j-magic for, but the section on that is incredibly short, and I haven't
> been able to get that to work either :/
Oleg.
--
Oleg Broytman http://phdru.name/ p...@phdru.name
Programmers don't die, they just GOSUB without RETURN.
------------------------------------------------------------------------------
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
