Issue 1:
Migrated issue, originally created by Anonymous

The code and docs use "relation" and "relationship" interchangeably as though they were the same thing, but in relational databases, a relation has a specific meaning (and is more commonly referred to as a table). This makes learning SQLAlchemy confusing for people familiar with the relational model terminology.

This patch renames relation to relationship in the code and docs to avoid this confusion. Any chance something like this could be applied in a future major version?

Attachments: relationship.patch.gz

Issue 2:
Describe the use case

Support for times when you want to insert into the database if records do not exist, or to overwrite them if they do.

Databases / Backends / Drivers targeted

I am specifically targeting SqlServer for my production, and SQLite for my tests

Example Use

https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15

Additional context

Here is the code that I have been using that has gotten the job done.

def _insert_or_update(self, insert_obj: DeclarativeMeta, identity_insert=False) -> None:
"""
A safe way for the sqlalchemy to insert if the record doesn't exist, or update if it does. Copied from
trafficstat.crash_data_ingester
:param insert_obj:
:param identity_insert:
:return:
"""
session = Session(bind=self.engine, future=True)
if identity_insert:
session.execute(text('SET IDENTITY_INSERT {} ON'.format(insert_obj.__tablename__)))

session.add(insert_obj)
try:
session.commit()
logger.debug('Successfully inserted object: {}', insert_obj)
except IntegrityError as insert_err:
session.rollback()

if '(544)' in insert_err.args[0]:
# This is a workaround for an issue with sqlalchemy not properly setting IDENTITY_INSERT on for SQL
# Server before we insert values in the primary key. The error is:
# (pyodbc.IntegrityError) ('23000', "[23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
# Cannot insert explicit value for identity column in table <table name> when IDENTITY_INSERT is set to
# OFF. (544) (SQLExecDirectW)")
self._insert_or_update(insert_obj, True)

elif '(2627)' in insert_err.args[0] or 'UNIQUE constraint failed' in insert_err.args[0]:
# Error 2627 is the Sql Server error for inserting when the primary key already exists. 'UNIQUE
# constraint failed' is the same for Sqlite
cls_type = type(insert_obj)

qry = session.query(cls_type)

primary_keys = [i.key for i in sqlalchemyinspect(cls_type).primary_key]
for primary_key in primary_keys:
qry = qry.filter(cls_type.__dict__[primary_key] == insert_obj.__dict__[primary_key])

update_vals = {k: v for k, v in insert_obj.__dict__.items()
if not k.startswith('_') and k not in primary_keys}
if update_vals:
qry.update(update_vals)
try:
session.commit()
logger.debug('Successfully inserted object: {}', insert_obj)
except IntegrityError as update_err:
logger.error('Unable to insert object: {}\nError: {}', insert_obj, update_err)

else:
raise AssertionError('Expected error 2627 or "UNIQUE constraint failed". Got {}'.format(insert_err)) \
from insert_err
finally:
if identity_insert:
session.execute(text('SET IDENTITY_INSERT {} OFF'.format(insert_obj.__tablename__)))
session.close()

I would open this as a PR, but I am not sure where this should go, and I am pretty sure its not up to the standards of this project.