nice explanation, Simon. 2013/8/15 Simon King <[email protected]>: > (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, "[email protected]" <[email protected]> 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, <[email protected]> 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 [email protected]. >> > To post to this group, send email to [email protected]. >> > 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 [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/groups/opt_out.
-- Mauricio de Abreu Antunes Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
