Issue 1:
Migrated issue, originally created by Anonymous

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'}

Attachments: db.sql | manytomany.py

Issue 2:
Migrated issue, originally created by Michael Bayer (@zzzeek)

as described at psycopg/psycopg2#491 (comment) we really need a hook to use psycopg2 batch mode so lets get that in.