Thanks Simon -- that's what I was missing. On Thursday, August 15, 2013 5:01:31 AM UTC-4, Simon King wrote: > > (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, "csd...@gmail.com <javascript:>" < > csd...@gmail.com <javascript:>> 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> 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. >> > To post to this group, send email to sqlal...@googlegroups.com. >> > 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+...@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. > > <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.
