That made difference, i think i got it working. I made a little example for study purposes. From test part you see i can fetch meaningful information with Country, CountryLanguage and Language objects. Country has many CountryLanguages (having Language object and is_primary field), CountryLanguage has only one Country but Language has many countries. Thank you Barry and Michael.
PS. http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_patterns_association might have an update time due to this. I couldnt get association table work without primary key field definitions, but on tutorial, its not mentioned. ################################################################## # Tables ################################################################## metadata = MetaData() countries_table = Table("countries", metadata, Column("id", Integer, primary_key=True), Column("alpha2", String(2)), Column("alpha3", String(3)), Column("name", Unicode(100)), ) countries_languages = Table("countries_languages", metadata, # either separate primary key or both link fields as primary_keys #Column("id", Integer, primary_key=True), Column("country_id", Integer, ForeignKey('countries.id'), primary_key=True), Column("language_id", Integer, ForeignKey('languages.id'), primary_key=True), Column("is_primary", Boolean, default=False), # only one can be default at time ) languages_table = Table("languages", metadata, Column("id", Integer, primary_key=True), Column("alpha2", String(2)), Column("name", Unicode(100)), ) metadata.create_all(engine) ################################################################## # Models ################################################################## class Country(object): def __init__(self, alpha2, alpha3, name): self.alpha2 = alpha2 self.alpha3 = alpha3 self.name = name def __repr__(self): return "<%s(%s, %s, %s)>" % (self.__class__, self.name, self.alpha2, self.alpha3) class Language(object): def __init__(self, alpha2, name): self.alpha2 = alpha2 self.name = name def __repr__(self): return "<%s(%s, %s)>" % (self.__class__, self.name, self.alpha2) class CountryLanguage(object): def __init__(self, language, is_primary = False): self.language = language self.is_primary = is_primary def __repr__(self): return "<%s(%s, %s)>" % (self.__class__, self.language, self.is_primary) ################################################################## # Mappers ################################################################## mapper(Country, countries_table, properties={ 'languages':relation(CountryLanguage, backref='country'), # many to many } ) mapper(CountryLanguage, countries_languages, properties={ 'language':relation(Language, backref="countries") } ) mapper(Language, languages_table) ################################################################## # Fixtures ################################################################## language_fi = Language('fi', 'Finnish') language_sv = Language('sv', 'Swedish') language_es = Language('es', 'Spain') language_en = Language('en', 'English') country_fi = Country('fi', 'fin', 'Finland') country_se = Country('se', 'swe', 'Sweden') clanguage_fi = CountryLanguage(language_fi, True) # making this mother language clanguage_sv = CountryLanguage(language_sv) country_fi.languages.append(clanguage_fi) country_fi.languages.append(clanguage_sv) # more straight form country_se.languages.append(CountryLanguage(language_sv, True)) # db_sess = Session() db_sess.save(country_fi) db_sess.save(country_se) db_sess.commit() ################################################################## # Test ################################################################## language = db_sess.query(Language).filter_by(name="Finnish").first() country = db_sess.query(Country).filter_by(alpha2="se").first() print language print country # get all Country and CountryLanguage countries = db_sess.query(Country) for country in countries: for language in country.languages: print language # get all Language and Country languages = db_sess.query(Language) for language in languages: for country in language.countries: print country # get all CountryLanguage and Country clanguages = db_sess.query(CountryLanguage) for clanguage in clanguages: print clanguage.language print clanguage.country --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---