(Sorry for any mistakes, I'm on my phone) Think of the database structure that you've got: your "creators" table has "id", "creator" and "company_id" columns. "company_id" is a foreign key pointing at the "id" column of the "companies" table.
This means that a single row in the "creators" table can only point at one company. Also, multiple creators can point at the same company. In other words, your many-to-one relationship is probably the opposite way round from the way you've intended. SQLAlchemy uses the foreign keys to determine the direction of the relationship, and so this is why Company.creator is a list, and you are only ever seeing a single value stored for Creator.companies. You probably want to remove the "creators.company_id" column and put a "creator_id" column on the "companies" table instead. This will mean that each company points at a single creator, but multiple companies may point at the same creator. In SQLAlchemy terms, Company.creator will now be a scalar value rather than a list, and Creator.companies will be a list. (Note that this means you'll need to change the code in your Company.__init__ method so that it assigns to self.creator rather than appending to it, and get rid of all the [0] indexing and so on) Hope that helps, Simon On 15 Aug 2013, at 02:12, "csdr...@gmail.com" <csdr...@gmail.com> wrote: Simon, your idea about putting together a script is a good one. Please see the attached. I think all these errors are related but I'm scratching my head about what the problem is. The reason I use self.creator[0] versus self.creator is for aesthetics. And, to your point about creator not being a list, SQLAlchemy is treating creator as a list-like object. Since any company can only have one creator, I wasn't concerned about indexing creator[0]. >>> a=session.query(Company).first() >>> a Company1, created by mike rather than >>> a=session.query(Company).first() >>> a Company1, created by [mike] >>> a.creator [mike] More info on creator: >>> type(a.creator) <class 'sqlalchemy.orm.collections.InstrumentedList'> >>> type(a.creator[0]) <class '__main__.Creator'> Chris On Wednesday, August 14, 2013 6:18:51 AM UTC-4, Simon King wrote: > > I think you may be confused about the relationship properties you have > here. As far as I can tell, a Creator can have many companies, but > each Company has only one creator, correct? So Company.creator should > only ever be an instance of Creator (or None), whereas > Creator.companies should be a list. > > In your __repr__ example: > > class Creator(Base): > def __repr__(self): > return '%s' % self.creator > > class Company(Base): > def __repr__(self): > return '%s, created by %s' % (self.company, self.creator[0]) > > Why are you using "self.creator[0]" here? self.creator is not a list, > it should either be an instance of Creator, or None. > > Overriding __repr__ is also a good way to make debugging difficult. > For example, if you had a list of Creator instances and you printed > them at the python prompt, it would just look like a list of strings. > When I want extra information from __repr__, I normally write it > something like this: > > def __repr__(self): > classname = type(self).__name__ > return '<%s name=%r>' % (classname, self.name) > > In your second example: > > >>> a=session.query(Creator).first() > >>> a[0].companies > >>> a.companies > > Query.first() returns a single value, not a list. So typing "a[0]" > doesn't make any sense. > > Please try to create a self-contained script that demonstrates your > problem. Here is a good example: > > https://groups.google.com/d/msg/sqlalchemy/jQtIRJXVfH8/LgwX-bomEIQJ > > Thanks, > > Simon > > On Wed, Aug 14, 2013 at 2:45 AM, <csd...@gmail.com <javascript:>> wrote: > > I'm afraid there are still some bugs in here that hopefully you can help > > with. > > > > class Creator(Base): > > __tablename__ = "creators" > > id = Column(Integer, primary_key = True) > > company_id = Column(Integer, ForeignKey('companies.id')) > > creator = Column(String(100), nullable=False, unique=True) > > def __init__(self, creator): > > self.creator = creator > > def __repr__(self): > > return '%s' % self.creator # otherwise returns a single entry > list > > for some reason (e.g. would display [user]) > > > > class Company(Base): > > __tablename__ = "companies" > > id = Column(Integer, primary_key = True) > > company = Column(String(100), unique=True, nullable=False) #might > want > > to revise string sizes at some point > > creator = relationship("Creator", backref="companies", > cascade="all") > > def __init__(self, company, creator): > > self.company = company > > #self.creator.append(Creator(creator)) > > existing_creator = > > session.query(Creator).filter_by(creator=creator).first() > > #self.creator.append(existing_creator or Creator(creator)) > > if existing_creator: > > print True > > self.creator.append(existing_creator) > > else: > > self.creator.append(Creator(creator)) > > def __repr__(self): > > return '%s, created by %s' % (self.company, self.creator[0]) > > > > > > > > 1) Weird __repr__ error: > > > > class Creator(Base): > > def __repr__(self): > > return '%s' % self.creator > > > > class Company(Base): > > def __repr__(self): > > return '%s, created by %s' % (self.company, self.creator[0]) > > > >>>> c=Company("Company1", "mike") > >>>> session.add(c) > >>>> c=Company("Company2", "mike") > > True > >>>> session.add(c) > >>>> c=Company("Company3", "john") > >>>> session.add(c) > >>>> c=Company("Company4", "mike") > > True > >>>> session.add(c) > >>>> session.query(Company).all() > > [Traceback (most recent call last): > > File "<stdin>", line 1, in <module> > > File "<stdin>", line 17, in __repr__ > > > > > > However, if I divide the query lines among every add() statement, there > is > > no __repr__ error. > > > >>>> c=Company("Company1", "mike") > >>>> session.add(c) > >>>> session.query(Company).all() > > [Company1, created by mike] > >>>> c=Company("Company2", "mike") > > True > >>>> session.add(c) > >>>> session.query(Company).all() > > [Company1, created by mike, Company2, created by mike] > >>>> c=Company("Company3", "john") > >>>> session.add(c) > >>>> session.query(Company).all() > > [Company1, created by mike, Company2, created by mike, Company3, created > by > > john] > >>>> c=Company("Company4", "mike") > > True > >>>> session.add(c) > >>>> session.query(Company).all() > > [Company1, created by mike, Company2, created by mike, Company3, created > by > > john, Company4, created by mike] > > > > > > 2) Creator.companies only shows the most recently added company: > > > >>>> session.query(Company).all() > > [Company1, created by mike, Company2, created by mike, Company3, created > by > > john, Company4, created by mike] > >>>> session.query(Creator).all() > > [mike, john] > >>>> a=session.query(Creator).first() > >>>> a[0].companies > >>>> a.companies > > Company4, created by mike > > > > > > 3) Weird Company.creator error: > > > >>>> session.query(Company).all() > > [Company1, created by mike, Company2, created by mike, Company3, created > by > > john, Company4, created by mike] > >>>> session.query(Company.creator).all() > > [(False,), (False,), (False,), (False,), (True,), (False,), (False,), > > (True,)] > >>>> a=session.query(Company).first() > >>>> a.creator > > [mike] > > > > Anyone have any ideas? > > > > -- > > 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+...@googlegroups.com <javascript:>. > > To post to this group, send email to sqlal...@googlegroups.com<javascript:>. > > > Visit this group at http://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/groups/opt_out. > > > > > -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. <SQLAlchemy bugs 08-14-13.py> -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
