Efektywne aktualizowanie bazy danych przy użyciu SQLAlchemy ORM

Rozpoczynam nową aplikację i patrzę na użycie ORM - w szczególności SQLAlchemy.

Powiedzmy, że mam kolumnę „foo” w mojej bazie danych i chcę ją zwiększyć. W prostym sqlite jest to łatwe:

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

Znalazłem odpowiednik SQLAlchemy SQL-builder:

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)

To jest nieco wolniejsze, ale nie ma w nim dużo.

Oto moje najlepsze przypuszczenie dotyczące podejścia SQLAlchemy ORM:

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

Robi to dobrze, ale trwa to niecałe pięćdziesiąt razy dłużej, niż zbliżają się pozostałe dwa. Przypuszczam, że dzieje się tak dlatego, że musi przenieść wszystkie dane do pamięci, zanim będzie mógł z nimi pracować.

Czy istnieje sposób na wygenerowanie wydajnego kodu SQL przy użyciu ORM SQLAlchemy? Lub używasz innego ORM Pythona? A może powinienem po prostu wrócić do ręcznego pisania SQL?

ORM SQLAlchemy ma być używany razem z warstwą SQL, a nie ją ukrywać. Ale musisz pamiętać o jednej lub dwóch rzeczach, gdy używasz ORM i zwykłego SQL w tej samej transakcji. Zasadniczo z jednej strony modyfikacje danych ORM trafią do bazy danych tylko wtedy, gdy opróżnisz zmiany z sesji. Z drugiej strony, instrukcje manipulacji danymi SQL nie wpływają na obiekty w sesji.

Więc jeśli powiesz

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

zrobi to, co mówi, pobierze wszystkie obiekty z bazy danych, zmodyfikuje wszystkie obiekty, a kiedy nadejdzie czas, aby opróżnić zmiany w bazie danych, zaktualizuje wiersze jeden po drugim.

Zamiast tego powinieneś zrobić to:

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

Spowoduje to wykonanie jako jedno zapytanie, tak jak można się spodziewać, a ponieważ przynajmniej domyślna konfiguracja sesji powoduje wygaśnięcie wszystkich danych w sesji po zatwierdzeniu, nie ma żadnych starych problemów z danymi.

W prawie wydanej serii 0.5 możesz również użyć tej metody do aktualizacji:

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

To w zasadzie uruchomi tę samą instrukcję SQL, co poprzedni fragment kodu, ale także wybierze zmienione wiersze i wygaśnie wszystkie nieaktualne dane w sesji. Jeśli wiesz, że po aktualizacji nie używasz żadnych danych sesji, możesz również dodać synchronize_session=Falsedo instrukcji aktualizacji i pozbyć się tego wyboru.

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

Spróbuj tego =)

Istnieje kilka sposobów na AKTUALIZACJĘ przy użyciu narzędzia 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)

Oto przykład, jak rozwiązać ten sam problem bez konieczności ręcznego mapowania pól:

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://postgres@localhost: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()

Aby zaktualizować instancję Media, możesz zrobić coś takiego:

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

Po trudnych testach spróbuję:

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

(IIRC, commit () działa bez flush ()).

Zauważyłem, że czasami wykonanie dużego zapytania, a następnie iteracja w Pythonie może być do 2 rzędów wielkości szybsza niż wiele zapytań. Zakładam, że iteracja po obiekcie zapytania jest mniej wydajna niż iteracja po liście wygenerowanej przez metodę all () obiektu zapytania.

[Proszę zanotować komentarz poniżej - to wcale nie przyspieszyło sprawy].

Jeśli jest to spowodowane narzutem związanym z tworzeniem obiektów, prawdopodobnie nie można go w ogóle przyspieszyć za pomocą SA.

Jeśli dzieje się tak, ponieważ ładuje powiązane obiekty, możesz być w stanie zrobić coś z ładowaniem z opóźnieniem. Czy na podstawie odniesień powstaje wiele obiektów? (IE, zdobycie obiektu firmy powoduje również pobranie wszystkich powiązanych obiektów People).