Issue 1:
Migrated issue, originally created by Andrey Popp (@andreypopp)

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 https://github.com/andreypopp/saext/blob/master/saext/tests/test_textual.txt for test cases.

Issue 2:
Describe the bug

When I insert instances using bulk insert, first the instances are added without the relations, then added again. Which causes duplicates and incomplete rows

Expected behavior

The instances should only appear once inside the database.

To Reproduce

This is a very minimal version of my code, I kept the important pieces:

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"<Characteristic(id='{self.id}', name='{self.name}', key='{self.key}')>"

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"<Measurement(id='{self.measurement_id}', simulation_id='{self.simulation_id}, characteristic_id='{self.characteristic_id}', index='{self.index}', value='{self.value}')>"

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

def __repr__(self):
return f"<Simulation(id='{self.id}')>"

@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)

Error

This is the output of the console:

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 ()
<Measurement(id='1', simulation_id='None, characteristic_id='None', index='0', value='0.0')>
<Measurement(id='2', simulation_id='None, characteristic_id='None', index='1', value='2.0')>
<Measurement(id='3', simulation_id='None, characteristic_id='None', index='2', value='4.0')>
<Measurement(id='4', simulation_id='None, characteristic_id='None', index='3', value='6.0')>
<Measurement(id='5', simulation_id='None, characteristic_id='None', index='4', value='8.0')>
<Measurement(id='6', simulation_id='1, characteristic_id='1', index='0', value='0.0')>
<Measurement(id='7', simulation_id='1, characteristic_id='1', index='1', value='2.0')>
<Measurement(id='8', simulation_id='1, characteristic_id='1', index='2', value='4.0')>
<Measurement(id='9', simulation_id='1, characteristic_id='1', index='3', value='6.0')>
<Measurement(id='10', simulation_id='1, characteristic_id='1', index='4', value='8.0')>
2021-02-07 22:21:46,621 INFO sqlalchemy.engine.base.Engine COMMIT

Process finished with exit code 0

I guess the error come from this line ?

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))

Basically I would only want that inside my measurement database:

<Measurement(id='1', simulation_id='1, characteristic_id='1', index='0', value='0.0')>
<Measurement(id='2', simulation_id='1, characteristic_id='1', index='1', value='2.0')>
<Measurement(id='3', simulation_id='1, characteristic_id='1', index='2', value='4.0')>
<Measurement(id='4', simulation_id='1, characteristic_id='1', index='3', value='6.0')>
<Measurement(id='5', simulation_id='1, characteristic_id='1', index='4', value='8.0')>

And not the other rows

Versions.

OS: Windows 10

Python: 3.8

SQLAlchemy: 1.3.20

SQLite 3.33.0

Thanks!