<p dir="auto"><strong>Migrated issue, originally created by Andrey Popp (<a href="https://github.com/andreypopp">@andreypopp</a>)</strong></p>
<p dir="auto">Sometimes I want to write select statement as raw SQL and then be able to do subselects from it or join it to other sqlalchemy constructs. I wrote basic sqlalchemy.sql construct to do that and wonder if it will be useful to have it in sqlalchemy (of course it's quick and dirty, but could be refactored into something more clean). See <a href="https://github.com/andreypopp/saext/blob/master/saext/tests/test_textual.txt">https://github.com/andreypopp/saext/blob/master/saext/tests/test_textual.txt</a> for test cases.</p>
<p dir="auto"><strong>Describe the bug</strong><br>
When I insert instances using bulk insert, first the instances are added without the relations, then added again. Which causes duplicates and incomplete rows</p>
<p dir="auto"><strong>Expected behavior</strong><br>
The instances should only appear once inside the database.</p>
<p dir="auto"><strong>To Reproduce</strong><br>
This is a very minimal version of my code, I kept the important pieces:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, Float
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from contextlib import contextmanager
from sqlalchemy.exc import IntegrityError, InvalidRequestError

Base = declarative_base()


class Characteristic(Base):
    __tablename__ = &quot;characteristic&quot;
    id = Column('id', Integer, primary_key=True)
    name = Column('name', String, index=True)
    key = Column('key', String, index=True)
    measurements = relationship(&quot;Measurement&quot;, backref=&quot;characteristic&quot;)

    def __repr__(self):
        return f&quot;&lt;Characteristic(id='{self.id}', name='{self.name}', key='{self.key}')&gt;&quot;


class Measurement(Base):
    __tablename__ = &quot;measurement&quot;
    measurement_id = Column('measurement_id', Integer, primary_key=True)
    index = Column('index', Integer)
    value = Column('value', Float)
    simulation_id = Column('simulation_id', Integer, ForeignKey('simulation.id'))
    characteristic_id = Column('characteristic_id', Integer, ForeignKey('characteristic.id'))

    def __repr__(self):
        return f&quot;&lt;Measurement(id='{self.measurement_id}', simulation_id='{self.simulation_id}, characteristic_id='{self.characteristic_id}', index='{self.index}', value='{self.value}')&gt;&quot;


class Simulation(Base):
    __tablename__ = &quot;simulation&quot;
    id = Column('id', Integer, primary_key=True)
    measurements = relationship(&quot;Measurement&quot;, backref=&quot;simulation&quot;)

    def __repr__(self):
        return f&quot;&lt;Simulation(id='{self.id}')&gt;&quot;


@contextmanager
def session_scope(sessionmaker, **kwargs):
    &quot;&quot;&quot;Provide a transactional scope around a series of operations.&quot;&quot;&quot;
    session = sessionmaker(**kwargs)
    try:
        yield session
        session.commit()
    except:
        session.rollback()
        raise
    finally:
        session.close()


def silent_insert(session, instance):
    try:
        session.add(instance)
        session.commit()
    except (IntegrityError,
            InvalidRequestError):
        print(&quot;silent_insert: Instance already present in the table&quot;)


def silent_bulk_insert(session, instances):
    try:
        session.bulk_save_objects(instances)
        session.commit()
    except (IntegrityError, InvalidRequestError):
        print(&quot;silent_bulk_insert: Instance already present in the table&quot;)


def find_instance(session, base: Base, column_to_value: dict):
    criterions = (getattr(base, column).like(value) for column, value in column_to_value.items())
    return session.query(base).filter(*criterions).first()


# Engine initialization
engine = create_engine(r'sqlite:///test.db', echo=True)
Session = sessionmaker(bind=engine)
Base.metadata.drop_all(bind=engine)

# Database Initialization
Base.metadata.create_all(bind=engine)
with session_scope(Session) as session:
    silent_insert(session, Simulation(id=1))
    silent_insert(session, Characteristic(name='drain', key='current'))

# Adding the data with bulk insert
with session_scope(Session) as session:
    instances = []
    simulation = find_instance(session, Simulation, {'id': 1})
    characteristic = find_instance(session, Characteristic, {'name': 'drain', 'key': 'current'})
    for i in range(5):
        instances.append(
            Measurement(**{'index': 1, 'value': 1, 'simulation': simulation, 'characteristic': characteristic}))

    silent_bulk_insert(session, instances)

# Checking the table
with session_scope(Session) as session:
    rows = session.query(Measurement).all()
    for row in rows:
        print(row)"><pre class="notranslate"><code class="notranslate">from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, Float
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from contextlib import contextmanager
from sqlalchemy.exc import IntegrityError, InvalidRequestError

Base = declarative_base()


class Characteristic(Base):
    __tablename__ = "characteristic"
    id = Column('id', Integer, primary_key=True)
    name = Column('name', String, index=True)
    key = Column('key', String, index=True)
    measurements = relationship("Measurement", backref="characteristic")

    def __repr__(self):
        return f"&lt;Characteristic(id='{self.id}', name='{self.name}', key='{self.key}')&gt;"


class Measurement(Base):
    __tablename__ = "measurement"
    measurement_id = Column('measurement_id', Integer, primary_key=True)
    index = Column('index', Integer)
    value = Column('value', Float)
    simulation_id = Column('simulation_id', Integer, ForeignKey('simulation.id'))
    characteristic_id = Column('characteristic_id', Integer, ForeignKey('characteristic.id'))

    def __repr__(self):
        return f"&lt;Measurement(id='{self.measurement_id}', simulation_id='{self.simulation_id}, characteristic_id='{self.characteristic_id}', index='{self.index}', value='{self.value}')&gt;"


class Simulation(Base):
    __tablename__ = "simulation"
    id = Column('id', Integer, primary_key=True)
    measurements = relationship("Measurement", backref="simulation")

    def __repr__(self):
        return f"&lt;Simulation(id='{self.id}')&gt;"


@contextmanager
def session_scope(sessionmaker, **kwargs):
    """Provide a transactional scope around a series of operations."""
    session = sessionmaker(**kwargs)
    try:
        yield session
        session.commit()
    except:
        session.rollback()
        raise
    finally:
        session.close()


def silent_insert(session, instance):
    try:
        session.add(instance)
        session.commit()
    except (IntegrityError,
            InvalidRequestError):
        print("silent_insert: Instance already present in the table")


def silent_bulk_insert(session, instances):
    try:
        session.bulk_save_objects(instances)
        session.commit()
    except (IntegrityError, InvalidRequestError):
        print("silent_bulk_insert: Instance already present in the table")


def find_instance(session, base: Base, column_to_value: dict):
    criterions = (getattr(base, column).like(value) for column, value in column_to_value.items())
    return session.query(base).filter(*criterions).first()


# Engine initialization
engine = create_engine(r'sqlite:///test.db', echo=True)
Session = sessionmaker(bind=engine)
Base.metadata.drop_all(bind=engine)

# Database Initialization
Base.metadata.create_all(bind=engine)
with session_scope(Session) as session:
    silent_insert(session, Simulation(id=1))
    silent_insert(session, Characteristic(name='drain', key='current'))

# Adding the data with bulk insert
with session_scope(Session) as session:
    instances = []
    simulation = find_instance(session, Simulation, {'id': 1})
    characteristic = find_instance(session, Characteristic, {'name': 'drain', 'key': 'current'})
    for i in range(5):
        instances.append(
            Measurement(**{'index': 1, 'value': 1, 'simulation': simulation, 'characteristic': characteristic}))

    silent_bulk_insert(session, instances)

# Checking the table
with session_scope(Session) as session:
    rows = session.query(Measurement).all()
    for row in rows:
        print(row)
</code></pre></div>
<p dir="auto"><strong>Error</strong><br>
This is the output of the console:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine ('drain', 'current', 1, 0)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine INSERT INTO measurement (&quot;index&quot;, value) VALUES (?, ?)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine ((0, 0.0), (1, 2.0), (2, 4.0), (3, 6.0), (4, 8.0))
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine INSERT INTO measurement (&quot;index&quot;, value, simulation_id, characteristic_id) VALUES (?, ?, ?, ?)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine (0, 0.0, 1, 1)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine INSERT INTO measurement (&quot;index&quot;, value, simulation_id, characteristic_id) VALUES (?, ?, ?, ?)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine (1, 2.0, 1, 1)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine INSERT INTO measurement (&quot;index&quot;, value, simulation_id, characteristic_id) VALUES (?, ?, ?, ?)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine (2, 4.0, 1, 1)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine INSERT INTO measurement (&quot;index&quot;, value, simulation_id, characteristic_id) VALUES (?, ?, ?, ?)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine (3, 6.0, 1, 1)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine INSERT INTO measurement (&quot;index&quot;, value, simulation_id, characteristic_id) VALUES (?, ?, ?, ?)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine (4, 8.0, 1, 1)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine COMMIT
2021-02-07 22:21:46,621 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2021-02-07 22:21:46,621 INFO sqlalchemy.engine.base.Engine SELECT measurement.measurement_id AS measurement_measurement_id, measurement.&quot;index&quot; AS measurement_index, measurement.value AS measurement_value, measurement.simulation_id AS measurement_simulation_id, measurement.characteristic_id AS measurement_characteristic_id 
FROM measurement
2021-02-07 22:21:46,621 INFO sqlalchemy.engine.base.Engine ()
&lt;Measurement(id='1', simulation_id='None, characteristic_id='None', index='0', value='0.0')&gt;
&lt;Measurement(id='2', simulation_id='None, characteristic_id='None', index='1', value='2.0')&gt;
&lt;Measurement(id='3', simulation_id='None, characteristic_id='None', index='2', value='4.0')&gt;
&lt;Measurement(id='4', simulation_id='None, characteristic_id='None', index='3', value='6.0')&gt;
&lt;Measurement(id='5', simulation_id='None, characteristic_id='None', index='4', value='8.0')&gt;
&lt;Measurement(id='6', simulation_id='1, characteristic_id='1', index='0', value='0.0')&gt;
&lt;Measurement(id='7', simulation_id='1, characteristic_id='1', index='1', value='2.0')&gt;
&lt;Measurement(id='8', simulation_id='1, characteristic_id='1', index='2', value='4.0')&gt;
&lt;Measurement(id='9', simulation_id='1, characteristic_id='1', index='3', value='6.0')&gt;
&lt;Measurement(id='10', simulation_id='1, characteristic_id='1', index='4', value='8.0')&gt;
2021-02-07 22:21:46,621 INFO sqlalchemy.engine.base.Engine COMMIT

Process finished with exit code 0"><pre class="notranslate"><code class="notranslate">2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine ('drain', 'current', 1, 0)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine INSERT INTO measurement ("index", value) VALUES (?, ?)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine ((0, 0.0), (1, 2.0), (2, 4.0), (3, 6.0), (4, 8.0))
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine INSERT INTO measurement ("index", value, simulation_id, characteristic_id) VALUES (?, ?, ?, ?)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine (0, 0.0, 1, 1)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine INSERT INTO measurement ("index", value, simulation_id, characteristic_id) VALUES (?, ?, ?, ?)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine (1, 2.0, 1, 1)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine INSERT INTO measurement ("index", value, simulation_id, characteristic_id) VALUES (?, ?, ?, ?)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine (2, 4.0, 1, 1)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine INSERT INTO measurement ("index", value, simulation_id, characteristic_id) VALUES (?, ?, ?, ?)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine (3, 6.0, 1, 1)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine INSERT INTO measurement ("index", value, simulation_id, characteristic_id) VALUES (?, ?, ?, ?)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine (4, 8.0, 1, 1)
2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine COMMIT
2021-02-07 22:21:46,621 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2021-02-07 22:21:46,621 INFO sqlalchemy.engine.base.Engine SELECT measurement.measurement_id AS measurement_measurement_id, measurement."index" AS measurement_index, measurement.value AS measurement_value, measurement.simulation_id AS measurement_simulation_id, measurement.characteristic_id AS measurement_characteristic_id 
FROM measurement
2021-02-07 22:21:46,621 INFO sqlalchemy.engine.base.Engine ()
&lt;Measurement(id='1', simulation_id='None, characteristic_id='None', index='0', value='0.0')&gt;
&lt;Measurement(id='2', simulation_id='None, characteristic_id='None', index='1', value='2.0')&gt;
&lt;Measurement(id='3', simulation_id='None, characteristic_id='None', index='2', value='4.0')&gt;
&lt;Measurement(id='4', simulation_id='None, characteristic_id='None', index='3', value='6.0')&gt;
&lt;Measurement(id='5', simulation_id='None, characteristic_id='None', index='4', value='8.0')&gt;
&lt;Measurement(id='6', simulation_id='1, characteristic_id='1', index='0', value='0.0')&gt;
&lt;Measurement(id='7', simulation_id='1, characteristic_id='1', index='1', value='2.0')&gt;
&lt;Measurement(id='8', simulation_id='1, characteristic_id='1', index='2', value='4.0')&gt;
&lt;Measurement(id='9', simulation_id='1, characteristic_id='1', index='3', value='6.0')&gt;
&lt;Measurement(id='10', simulation_id='1, characteristic_id='1', index='4', value='8.0')&gt;
2021-02-07 22:21:46,621 INFO sqlalchemy.engine.base.Engine COMMIT

Process finished with exit code 0
</code></pre></div>
<p dir="auto">I guess the error come from this line ?</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine ((0, 0.0), (1, 2.0), (2, 4.0), (3, 6.0), (4, 8.0))"><pre class="notranslate"><code class="notranslate">2021-02-07 22:21:46,611 INFO sqlalchemy.engine.base.Engine ((0, 0.0), (1, 2.0), (2, 4.0), (3, 6.0), (4, 8.0))
</code></pre></div>
<p dir="auto">Basically I would only want that inside my measurement database:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="&lt;Measurement(id='1', simulation_id='1, characteristic_id='1', index='0', value='0.0')&gt;
&lt;Measurement(id='2', simulation_id='1, characteristic_id='1', index='1', value='2.0')&gt;
&lt;Measurement(id='3', simulation_id='1, characteristic_id='1', index='2', value='4.0')&gt;
&lt;Measurement(id='4', simulation_id='1, characteristic_id='1', index='3', value='6.0')&gt;
&lt;Measurement(id='5', simulation_id='1, characteristic_id='1', index='4', value='8.0')&gt;"><pre class="notranslate"><code class="notranslate">&lt;Measurement(id='1', simulation_id='1, characteristic_id='1', index='0', value='0.0')&gt;
&lt;Measurement(id='2', simulation_id='1, characteristic_id='1', index='1', value='2.0')&gt;
&lt;Measurement(id='3', simulation_id='1, characteristic_id='1', index='2', value='4.0')&gt;
&lt;Measurement(id='4', simulation_id='1, characteristic_id='1', index='3', value='6.0')&gt;
&lt;Measurement(id='5', simulation_id='1, characteristic_id='1', index='4', value='8.0')&gt;
</code></pre></div>
<p dir="auto">And not the other rows</p>
<p dir="auto"><strong>Versions.</strong></p>
<ul dir="auto">
<li>OS: Windows 10</li>
<li>Python: 3.8</li>
<li>SQLAlchemy: 1.3.20</li>
<li>SQLite 3.33.0</li>
</ul>
<p dir="auto">Thanks!</p>