<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<h1 dir="auto">SQL SCRIPT:</h1>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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(&quot;postgres://mage:xxx@127.0.0.1:5432/alch&quot;)

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()"><pre class="notranslate"><code class="notranslate">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()
</code></pre></div>
<p dir="auto">This script results in :<br>
sqlalchemy.exceptions.SQLError: (IntegrityError) ERROR:  duplicate key violates unique constraint "pk_invasive_names"</p>
<p dir="auto">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'}</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/258/db.sql">db.sql</a> | <a href="../wiki/imported_issue_attachments/258/manytomany.py">manytomany.py</a></p>
<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">as described at <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="193060614" data-permission-text="Title is private" data-url="https://github.com/psycopg/psycopg2/issues/491" data-hovercard-type="issue" data-hovercard-url="/psycopg/psycopg2/issues/491/hovercard?comment_id=276551038&amp;comment_type=issue_comment" href="https://github.com/psycopg/psycopg2/issues/491#issuecomment-276551038">psycopg/psycopg2#491 (comment)</a> we really need a hook to use psycopg2 batch mode so lets get that in.</p>