Эффективное обновление базы данных с помощью SQLAlchemy ORM

Я запускаю новое приложение и смотрю на использование ORM - в частности, SQLAlchemy.

Скажем, у меня есть столбец "foo" в моей базе данных, и я хочу увеличить его. В прямом sqlite это легко:

db = sqlite3.connect('mydata.sqlitedb')
cur = db.cursor()
cur.execute('update table stuff set foo = foo + 1')

Я вычислил эквивалент SQL-Builder SQLAlchemy:

engine = sqlalchemy.create_engine('sqlite:///mydata.sqlitedb')
md = sqlalchemy.MetaData(engine)
table = sqlalchemy.Table('stuff', md, autoload=True)
upd = table.update(values={table.c.foo:table.c.foo+1})
engine.execute(upd)

Это немного медленнее, но там не так много.

Вот мое лучшее предположение для ORM-подхода SQLAlchemy:

# snip definition of Stuff class made using declarative_base
# snip creation of session object
for c in session.query(Stuff):
    c.foo = c.foo + 1
session.flush()
session.commit()

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

Есть ли способ генерировать эффективный SQL, используя ORM SQLAlchemy? Или с помощью любого другого ORM питона? Или я должен просто вернуться к написанию SQL вручную?

Ответ 1

SQLAlchemy ORM предназначен для использования вместе со слоем SQL, а не скрыть его. Но при использовании ORM и простого SQL в одной транзакции вам нужно учитывать одну или две вещи. В принципе, с одной стороны, модификации данных ORM будут попадать в базу данных только тогда, когда вы очищаете изменения от сеанса. С другой стороны, инструкции по обработке данных SQL не влияют на объекты, которые находятся в вашем сеансе.

Итак, если вы скажете

for c in session.query(Stuff).all():
    c.foo = c.foo+1
session.commit()

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

Вместо этого вы должны сделать это:

session.execute(update(stuff_table, values={stuff_table.c.foo: stuff_table.c.foo + 1}))
session.commit()

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

В почти выпущенной серии 0.5 вы также можете использовать этот метод для обновления:

session.query(Stuff).update({Stuff.foo: Stuff.foo + 1})
session.commit()

В основном будет выполняться тот же оператор SQL, что и предыдущий фрагмент, но также выберите измененные строки и истечет любые устаревшие данные в сеансе. Если вы знаете, что после обновления вы не используете данные сеанса, вы также можете добавить synchronize_session = False в оператор обновления и избавиться от этого выбора.

Ответ 2

session.query(Clients).filter(Clients.id == client_id_list).update({'status': status})
session.commit()

Попробуйте это =)

Ответ 3

Существует несколько способов ОБНОВЛЕНИЯ с помощью sqlalchemy

1) for c in session.query(Stuff).all():
       c.foo += 1
   session.commit()

2) session.query().\
       update({"foo": (Stuff.foo + 1)})
   session.commit()

3) conn = engine.connect()
   stmt = Stuff.update().\
       values(Stuff.foo = (Stuff.foo + 1))
   conn.execute(stmt)

Ответ 4

Без тестирования, я бы попробовал:

for c in session.query(Stuff).all():
     c.foo = c.foo+1
session.commit()

(IIRC, commit() работает без flush()).

Я обнаружил, что время от времени делает большой запрос, а затем итерация в python может быть на 2 порядка быстрее, чем много запросов. Я предполагаю, что итерация над объектом запроса менее эффективна, чем итерация по списку, сгенерированному методом all() объекта запроса.

[Пожалуйста, обратите внимание на комментарий ниже - это совсем не ускоряло ситуацию).

Ответ 5

Если это связано с накладными расходами с точки зрения создания объектов, то, вероятно, не может быть ускорено с SA.

Если это связано с загрузкой связанных объектов, вы можете сделать что-то с ленивой загрузкой. Есть ли много объектов, созданных из-за ссылок? (IE, получение объекта Company также получает все связанные объекты People).

Ответ 6

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

from sqlalchemy import Column, ForeignKey, Integer, String, Date, DateTime, text, create_engine
from sqlalchemy.exc import IntegrityError
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm.attributes import InstrumentedAttribute

engine = create_engine('postgres://[email protected]host:5432/database')
session = sessionmaker()
session.configure(bind=engine)

Base = declarative_base()


class Media(Base):
  __tablename__ = 'media'
  id = Column(Integer, primary_key=True)
  title = Column(String, nullable=False)
  slug = Column(String, nullable=False)
  type = Column(String, nullable=False)

  def update(self):
    s = session()
    mapped_values = {}
    for item in Media.__dict__.iteritems():
      field_name = item[0]
      field_type = item[1]
      is_column = isinstance(field_type, InstrumentedAttribute)
      if is_column:
        mapped_values[field_name] = getattr(self, field_name)

    s.query(Media).filter(Media.id == self.id).update(mapped_values)
    s.commit()

Итак, чтобы обновить экземпляр Media, вы можете сделать что-то вроде этого:

media = Media(id=123, title="Titular Line", slug="titular-line", type="movie")
media.update()