Hi,
You're completely correct - it's a bit stupid if all I'm using is SQL.
Actually the rows in the table are just for storing things reliably, and
all the magic happens with python classes which are as you describe.
Thank you for the help, I'll give it a go.
On 15/03/2016 20:40, Christopher Lee wrote:
A relationship usually looks at the foreign keys on the tables you
specify and constructs the queries appropriately. The error you are
getting happens because there are multiple foreign keys between the
tables (in this case, the same table referencing itself... shudder...).
You need to tell each relationship which foreign key to use.
e.g.,:
contents = relationship(DbObject, foreign_keys=['location_id'], ...)
-----
Technical problems aside, your database schema has some pretty serious
flaws. Having a single "objects" table that can relate to itself in
all the ways in which things can relate to other things is a
logistical nightmare, both in terms of performance and clarity.
For example, your schema looks suspiciously like people, places and
things are all DBObjects. A place has an id, and x, y, z
coordinates. A thing in that location would have a foreign key to the
first record. Well, what does it mean if a thing has a location_id to
one set of coordinates, but has another set of coordinates in its x,
y, and z values? Similarly, it looks like things have an owner_id that
references another object, but what would it mean for a place to have
an owner, or a person? How can you tell what type of thing something
is? How would you query for all the people, or all the things?
A more sane schema might be something like:
class Person(base):
person_id = Column(Integer, primary_key=True)
class Place(base):
place_id = Column(Integer, primary_key=True)
x = Column(Float)
y = Column(Float)
z = Column(Float)
class Thing(base):
thing_id = Column(Integer, primary_key=True)
location_id = Column(Integer, ForeignKey(Place.place_id))
owner_id = Column(Integer, ForeignKey(Person.person_id))
etc.
On Tue, Mar 15, 2016 at 2:12 AM, 'Chris Norman' via sqlalchemy
<sqlalchemy@googlegroups.com <mailto:sqlalchemy@googlegroups.com>> wrote:
Hi,
On 14/03/2016 15:19, Mike Bayer wrote:
On 03/14/2016 11:15 AM, 'Chris Norman' via sqlalchemy wrote:
Hi all,
I've tried googling for this, and I get nothing. I have a
table to store
data about objects. Each object should have a location
property which
links back to the same table. Conversely, each object
should have a
contents property which shows all objects which have their
location set
to this object.
documentation for multiple relationships to the same table is at:
http://docs.sqlalchemy.org/en/rel_1_0/orm/join_conditions.html#handling-multiple-join-paths
hope this helps
Thanks for the docs, however I've read this page loads over the
past few days - since my google searches keep turning it up - and
it seemed like the right place to be.
I can't find anything in there that helps me. This isn't to say
it's not there, but my knowledge of SQL is fairly limited, and my
understanding of things like the relationship function aren't very
in depth.
Which bits should I specifically read to help?
Sorry if my questions seem a little stupid - as I say, databases
are something I use rather than understand. Recently I've been
using Django where everything is handled for me, so I'm still
struggling to come to grips with relationships and how they're
made up in the real (Django-free) world.
Thanks again.
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it,
send an email to sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy%2bunsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to a topic in the
Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/oXokasM5yI0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
