Встроенный в процессор SqlAlchemy итератор/генератор?

Ответ 1

Большинство реализаций DBAPI полностью буферизуют строки по мере их выборки - поэтому обычно, прежде чем SQLAlchemy ORM даже получит один результат, весь набор результатов находится в памяти.

Но тогда Query работает так, что он полностью загружает заданный набор результатов по умолчанию, прежде чем вернуть вам ваши объекты. Обоснование здесь касается запросов, которые представляют собой нечто большее, чем простые операторы SELECT. Например, в соединениях с другими таблицами, которые могут возвращать одну и ту же идентичность объекта несколько раз в одном наборе результатов (обычно при активной загрузке), полный набор строк должен находиться в памяти, чтобы можно было возвращать правильные результаты, в противном случае коллекции и т.д. может быть заполнен только частично.

Поэтому Query предлагает возможность изменить это поведение с помощью yield_per(). Этот вызов заставит Query выдавать строки в пакетах, где вы даете ему размер пакета. Как указано в документации, это уместно только в том случае, если вы не выполняете какую-либо активную загрузку коллекций, так что, в основном, если вы действительно знаете, что делаете. Кроме того, если нижележащие строки DBAPI предварительно буферизуют строки, это все равно приведет к дополнительным затратам памяти, поэтому подход масштабируется лишь немного лучше, чем без его использования.

Я почти никогда не пользуюсь yield_per(); вместо этого я использую лучшую версию подхода LIMIT, который вы предлагаете выше, используя оконные функции. У LIMIT и OFFSET огромная проблема: очень большие значения OFFSET приводят к тому, что запрос становится все медленнее и медленнее, так как OFFSET из N заставляет его перелистывать N строк - это похоже на выполнение одного и того же запроса пятьдесят раз вместо одного, каждый раз читая все большее и большее количество рядов. При подходе оконной функции я предварительно выбираю набор значений "окна", которые относятся к фрагментам таблицы, которую я хочу выбрать. Затем я генерирую отдельные операторы SELECT, которые каждое из них извлекает из одного из этих окон одновременно.

Подход к оконной функции - в вики, и я пользуюсь им с большим успехом.

Также обратите внимание: не все базы данных поддерживают оконные функции; вам нужен Postgresql, Oracle или SQL Server. ИМХО использование хотя бы Postgresql определенно стоит - если вы используете реляционную базу данных, вы можете использовать и лучшее.

Ответ 2

Я искал эффективный обход/пейджинг с помощью SQLAlchemy и хотел бы обновить этот ответ.

Я думаю, вы можете использовать вызов slice, чтобы правильно ограничить область запроса, и вы могли бы эффективно повторно использовать его.

Пример:

window_size = 10  # or whatever limit you like
window_idx = 0
while True:
    start,stop = window_size*window_idx, window_size*(window_idx+1)
    things = query.slice(start, stop).all()
    if things is None:
        break
    for thing in things:
        analyze(thing)
    if len(things) < window_size:
        break
    window_idx += 1

Ответ 3

Я не эксперт по базам данных, но при использовании SQLAlchemy в качестве простого уровня абстракции Python (т.е. без использования объекта запроса ORM) я нашел удовлетворительное решение для запроса таблицы из 300 миллионов строк без взрыва использования памяти...

Вот фиктивный пример:

from sqlalchemy import create_engine, select

conn = create_engine("DB URL...").connect()
q = select([huge_table])

proxy = conn.execution_options(stream_results=True).execute(q)

Затем я использую метод SQLAlchemy fetchmany(), чтобы перебрать результаты в бесконечном цикле while:

while 'batch not empty':  # equivalent of 'while True', but clearer
    batch = proxy.fetchmany(100000)  # 100,000 rows at a time

    if not batch:
        break

    for row in batch:
        # Do your stuff here...

proxy.close()

Этот метод позволил мне выполнять все виды агрегации данных без каких-либо опасных затрат памяти.

NOTE stream_results работает с Postgres и адаптером pyscopg2, но я предполагаю, что он не будет работать ни с DBAPI, ни с каким-либо драйвером базы данных...

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

Ответ 4

В духе ответа Джоэля я использую следующее:

WINDOW_SIZE = 1000
def qgen(query):
    start = 0
    while True:
        stop = start + WINDOW_SIZE
        things = query.slice(start, stop).all()
        if things is None:
            break
        for thing in things:
            yield(thing)
        start += WINDOW_SIZE

Ответ 5

Использование LIMIT/OFFSET плохо, потому что вам нужно найти все столбцы {OFFSET} раньше, поэтому чем больше OFFSET, тем длиннее запрос. Использование оконного запроса для меня также дает плохие результаты на большой таблице с большим объемом данных (вы слишком долго ждете первые результаты, что в моем случае это не очень хорошо для ответного веб-ответа).

Лучший подход, приведенный здесь fooobar.com/questions/100597/.... В моем случае я разрешил проблему, просто используя индекс в поле datetime и выбрав следующий запрос с datetime >= previous_datetime. Глупо, потому что раньше я использовал этот индекс в разных случаях, но думал, что для получения всех оконных запросов данных будет лучше. В моем случае я ошибся.

Ответ 6

AFAIK, первый вариант по-прежнему получает все кортежи из таблицы (с одним SQL-запросом), но строит ORM-презентацию для каждого объекта при итерации. Таким образом, он более эффективен, чем создание списка всех объектов перед итерацией, но вам все равно придется извлекать все (необработанные) данные в память.

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