Sqlalchemy: массовое коррелированное обновление для таблиц ссылок

Question

У меня есть две связанные таблицы, уже заполненные данными (используя SQLAlchemy ORM). Однако отдельные записи еще не связаны, то есть столбец внешнего ключа остается пустым. Мне нужно массово обновить столбец внешнего ключа, основываясь на совпадении с другими столбцами.

Проиллюстрировать:

class Location(Base):
    __tablename__ = 'locations'
    id = Column(Integer, primary_key=True)
    x = Column(Float)
    y = Column(Float)


class Stopover(Base):
    __tablename__ = 'stopovers'
    id = Column(Integer, primary_key=True)
    x = Column(Float)
    y = Column(Float)
    location_id = Column(Integer, ForeignKey("locations.id"))
    location = relationship("Location", backref=backref("stopovers"))

По сути, мне нужно связать каждую из 20 000+ записей "Остановки" с "Местоположением", сопоставив столбцы "x" и "y", то есть массово обновить столбец location_id.

Этот код генерирует _location_id_ правильно:

for stpvr in session.query(Stopovers).all():
       stpvr.location_id = session.query(Location.id).\
                           filter_by(x=stpvr.x).\
                           filter_by(y=stpvr.y).one()[0]
       session.commit()

Однако, похоже, что это не работает - исследование базы данных через Sqliteman показывает, что location_ids не были обновлены. Кроме того, я предполагаю, что должен быть намного более изящный способ приблизиться к этому.

В документах я обнаружил, что Correlated Updates ближе всего к тому, что я ищу. Однако документы относятся только к языку выражений SQL, тогда как я использую ORM. Я новичок в SQLAlchemy, и мои попытки перевести документы в ORM не увенчались успехом.

Буду признателен за любую помощь в поиске наиболее элегантного способа выполнения этого массового обновления. Заранее спасибо.

Answer 1

SQLAlchemy работает в слоях. На базовом уровне SQLAlchemy предоставляет такие вещи, как унифицированный интерфейс для баз данных с использованием различных драйверов баз данных и реализацию пула соединений. Выше этого находится язык выражений SQL, позволяющий вам определять таблицы и столбцы вашей базы данных, используя объекты Python, а затем использовать эти объекты для создания выражений SQL, используя API, которые предоставляет вам SQLAlchemy. Тогда есть ОРМ. ORM основывается на этих существующих слоях, и поэтому, даже если вы используете ORM, вы все равно можете раскрыться, чтобы использовать API выражений. Вы даже выше этого уровня, используя декларативную модель (которая основана на ORM).

Большая часть API выражений основана на объекте таблицы SQLAlchemy и столбцах. Таблицы доступны для __table__ свойство сопоставленного класса, а столбцы доступны как свойства сопоставленного класса. Таким образом, даже если вы находитесь на декларативном уровне, вы все равно можете использовать большую часть того, что у вас есть, при использовании моделей, которые вы отображали с помощью декларативного. Итак, пример коррелированного запроса...

>>> stmt = select([addresses.c.email_address]).\
...             where(addresses.c.user_id == users.c.id).\
...             limit(1)
>>> conn.execute(users.update().values(fullname=stmt)) 

... можно перевести на декларативную модель ORM с помощью __table__ атрибутные и декларативные столбцы...

>>> stmt = select([Addresses.email_address]).\
...             where(Addresses.user_id == Users.id).\
...             limit(1)
>>> conn.execute(Users.__table__.update().values(fullname=stmt)) 

Вот что я думаю, что ваш коррелированный запрос будет выглядеть..

stmt = select([Location.id]).\
    where(and_(Location.x==Stopover.x, Location.y==Stopover.y)).limit(1)

conn.execute(Stopover.__table__.update().values(location_id=stmt)

Результирующий SQL:

UPDATE stopovers SET location_id=(SELECT locations.id 
FROM locations 
WHERE locations.x = stopovers.x AND locations.y = stopovers.y
LIMIT ? OFFSET ?)