[sqlalchemy] Re: associative table with extra field
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: associative table with extra field
On Oct 27, 2007, at 8:02 AM, mmstud wrote: I'm trying to implement associative table with extra field on join table, but faced problems... what is it, that im doing wrong here, because i get python error: try not using IPython. shells like IDLE etc. always seem to corrupt things. at the very least, youll get a real stack trace. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: associative table with extra field
I would've declared the relation from Country to CountryLanguage, not vice
versa, but I doubt that is the reason for your error.
Can you provide a full example which reproduces the error?
Barry
- Original Message
From: mmstud [EMAIL PROTECTED]
To: sqlalchemy sqlalchemy@googlegroups.com
Sent: Saturday, October 27, 2007 8:02:38 AM
Subject: [sqlalchemy] associative table with extra field
I'm trying to implement associative table with extra field on join
table, but faced problems... what is it, that im doing wrong here,
because i get python error:
.
.
.
2007-10-27 14:58:26,816 INFO sqlalchemy.engine.base.Engine.0x..d0 {}
2007-10-27 14:58:26,816 INFO sqlalchemy.engine.base.Engine.0x..d0
COMMIT
ERROR: Internal Python error in the inspect module.
Below is the traceback from this internal error.
Traceback (most recent call last):
File /usr/lib/python2.4/site-packages/IPython/ultraTB.py, line
462, in text
records = _fixed_getinnerframes(etb, context,self.tb_offset)
File /usr/lib/python2.4/site-packages/IPython/ultraTB.py, line
118, in _fixed_getinnerframes
records = inspect.getinnerframes(etb, context)
File inspect.py, line 804, in getinnerframes
framelist.append((tb.tb_frame,) + getframeinfo(tb, context))
File inspect.py, line 768, in getframeinfo
lines, lnum = findsource(frame)
File inspect.py, line 437, in findsource
if pat.match(lines[lnum]): break
IndexError: list index out of range
Unfortunately, your original traceback can not be constructed.
*
countries_table = Table(countries, metadata,
Column(id, Integer, primary_key=True),
Column(alpha2, String(2)),
Column(alpha3, String(3)),
Column(name, Unicode(100)),
)
# association table with additional field
countries_languages = Table(countries_languages, metadata,
Column(country_id, Integer, ForeignKey('countries.id')),
Column(language_id, Integer, ForeignKey('languages.id')),
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)),
)
class BaseObject(object):
def __init__(self):
self._repr_ = []
def __repr__(self):
from string import join
str = ', '.join('%s' % (self.__dict__[v]) for v in
self._repr_)
return %s(%s) % (self.__class__, str)
class NameObject(BaseObject):
def __init__(self, name):
self.name = name
BaseObject.__init__(self)
def __repr__(self):
self._repr_ = [name]
return BaseObject.__repr__(self)
class Country(NameObject):
def __init__(self, alpha2, alpha3, name):
NameObject.__init__(self, name)
self.alpha2 = alpha2
self.alpha3 = alpha3
class CountryLanguage(object):
def __init__(self, is_primary = False):
self.is_primary = is_primary
class Language(NameObject):
def __init__(self, alpha2, name):
NameObject.__init__(self, name)
self.alpha2 = alpha2
mapper(Country, countries_table, properties={
'post_offices':relation(PostOffice, backref='country'), # one to
many
'languages':relation(CountryLanguage, backref='country'), # many
to many
}
)
mapper(CountryLanguage, countries_languages, properties={
'language':relation(Language, backref=countries)
}
)
mapper(Language, languages_table)
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: associative table with extra field
That was a good tip. Now i get:
2007-10-27 22:49:25,454 INFO sqlalchemy.engine.base.Engine.0x..90 {}
2007-10-27 22:49:25,455 INFO sqlalchemy.engine.base.Engine.0x..90
COMMIT
Traceback (most recent call last):
File stdin, line 1, in ?
File fixtures.py, line 4, in ?
from mappers import *
File mappers.py, line 39, in ?
mapper(CountryLanguage, countries_languages, properties={
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/__init__.py, line
516, in mapper
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/mapper.py, line
152, in __init__
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/mapper.py, line
414, in _compile_tables
sqlalchemy.exceptions.ArgumentError: Could not assemble any primary
key columns for mapped table 'countries_languages'
I'll provide more full example tomorrow...
Thanks.
On Oct 27, 6:44 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Oct 27, 2007, at 8:02 AM, mmstud wrote:
I'm trying to implement associative table with extra field on join
table, but faced problems... what is it, that im doing wrong here,
because i get python error:
try not using IPython. shells like IDLE etc. always seem to corrupt
things. at the very least, youll get a real stack trace.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: associative table with extra field
You need to add an ID column to your countries_languages table, like this:
Column(id, Integer, primary_key=True),
Alternatively you could declare the combination country_id and language_id as
your primary key.
Barry
- Original Message
From: mmstud [EMAIL PROTECTED]
To: sqlalchemy sqlalchemy@googlegroups.com
Sent: Saturday, October 27, 2007 3:53:47 PM
Subject: [sqlalchemy] Re: associative table with extra field
That was a good tip. Now i get:
2007-10-27 22:49:25,454 INFO sqlalchemy.engine.base.Engine.0x..90 {}
2007-10-27 22:49:25,455 INFO sqlalchemy.engine.base.Engine.0x..90
COMMIT
Traceback (most recent call last):
File stdin, line 1, in ?
File fixtures.py, line 4, in ?
from mappers import *
File mappers.py, line 39, in ?
mapper(CountryLanguage, countries_languages, properties={
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/__init__.py, line
516, in mapper
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/mapper.py, line
152, in __init__
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/mapper.py, line
414, in _compile_tables
sqlalchemy.exceptions.ArgumentError: Could not assemble any primary
key columns for mapped table 'countries_languages'
I'll provide more full example tomorrow...
Thanks.
On Oct 27, 6:44 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Oct 27, 2007, at 8:02 AM, mmstud wrote:
I'm trying to implement associative table with extra field on join
table, but faced problems... what is it, that im doing wrong here,
because i get python error:
try not using IPython. shells like IDLE etc. always seem to corrupt
things. at the very least, youll get a real stack trace.
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
