many to many fail

[Former user]

SQL SCRIPT:

create table languages (
    id          serial          not null,
    iso_code    char(2)         not null,
    language    varchar(100)    not null,

    constraint pk_language 
        primary key(id)
);

create table invasives (
    id      serial          not null,
    foobar  varchar(50),

    constraint pk_invasive
        primary key (id)
);

create table invasive_names (
    invasive_id integer not null,
    language_id integer not null,
    name        varchar(100) not null,


    constraint pk_invasive_names
        primary key(invasive_id, language_id)
);

insert into languages values (1, 'fr', 'French');
insert into languages values (2, 'de', 'German');
insert into languages values (3, 'en', 'English');

insert into invasives values (1, 'species 1');
insert into invasives values (2, 'species 2');
insert into invasives values (3, 'species 3');

insert into invasive_names values (1, 1, 'species 1 french');
insert into invasive_names values (1, 3, 'species 1 english');
insert into invasive_names values (3, 2, 'species 1 german');
insert into invasive_names values (3, 3, 'species 1 english');



#!python

from sqlalchemy import *

session = create_session()
db_engine = create_engine("postgres://mage:xxx@127.0.0.1:5432/alch")

meta = BoundMetaData(db_engine)

table_languages = Table('languages', meta,
    Column('id', Integer, primary_key=True),
    Column('iso_code', String(2)),
    Column('language', String(100)))

table_invasives = Table('invasives', meta,
    Column('id', Integer, primary_key=True),
    Column('foobar', String(50)))

table_invasive_names = Table('invasive_names', meta,
    Column('invasive_id', Integer, ForeignKey('invasives.id'), primary_key=True, nullable=False),
    Column('language_id', Integer, ForeignKey('languages.id'), primary_key=True, nullable=False),
    Column('name', String(50), nullable=False))

class Language(object):
    pass

class Invasive(object):
    pass

class InvasiveName(object):
    pass

mapper(InvasiveName, table_invasive_names)

mapper(Language, table_languages, properties = {
        'names' : relation(InvasiveName, backref='language', cascade='all, delete-orphan')
    }
)

mapper(Invasive, table_invasives, properties = {
        'names' : relation(InvasiveName, backref='invasive', cascade='all, delete-orphan')
    }
)

query_invasive_name = session.query(InvasiveName)
query_invasive = session.query(Invasive)
query_language = session.query(Language)

species = query_invasive.get(3)
lang_english = query_language.get_by_iso_code('en')
new_name = InvasiveName()
new_name.name='species 3 english NEW NAME'
new_name.language=lang_english
session.save(new_name)
species.names = []
species.names.append(new_name)
session.flush()

This script results in : sqlalchemy.exceptions.SQLError: (IntegrityError) ERROR: duplicate key violates unique constraint "pk_invasive_names"

INSERT INTO invasive_names (invasive_id, language_id, name) VALUES (3, 3, 'species 3 english NEW NAME') 'INSERT INTO invasive_names (invasive_id, language_id, name) VALUES (%(invasive_id)s, %(language_id)s, %(name)s)' {'invasive_id': 3, 'language_id': 3, 'name': 'species 3 english NEW NAME'}

[Former user]

SQL script

[Former user]

python script

[Mike Bayer]

OK with the above script, there is an InvasiveName object with a primary key of (3,3) already existing in the DB. when you create a new InvasiveName object, and then you associate it with the same Invasive and Language object, you are essentially creating a second InvasiveName object, in the same session, with column values which will, when it becomes persistent, come out as the same primary key. this is not allowed. SA assumes that theres only one object in the session for each particular primary key.

the pattern I thought we were doing here is the association object pattern, which is the only place SA knows how to treat a many-to-many association table as a mapped entity, like this:

mapper(Language, table_languages)

mapper(InvasiveName, table_invasive_names,properties={
    'language':relation(Language, cascade="all, delete-orphan")
})

mapper(Invasive, table_invasives, properties = {
        'names' : relation(InvasiveName, backref='invasive', cascade='all, delete-orphan', association=Language)
    }
)

[Mike Bayer]

great, we worked this out on IRC the association object is what he was looking for.