Эффективная загрузка данных из SQLite в Python: словарь списков без лишних проходов

Покажем, как читать данные из SQLite в Python сразу в словарь списков: пакетное fetchmany, транспонирование zip и ускорение в несколько раз без pandas.

При извлечении данных из SQLite напрашивается вариант: собрать список словарей — по одному словарю на строку. Это удобно и наглядно, но подходит не всегда. Если дальше вас ждут структуры с колонками как главным измерением, лучше сразу преобразовать результат в словарь списков. Ниже разберём небольшой пример, посмотрим, что даёт привычный подход, и перейдём к более эффективной колонковой загрузке с пакетным чтением.

Постановка задачи

Возьмём простую таблицу из my_db.db:

idx   speed   duration
 0     420       50
 1     380       40
 2     390       45

Загрузить её в Python в виде списка словарей несложно:

import sqlite3
base_path = folder_path  # только для примера; предполагается, что folder_path определён
db_conn = sqlite3.connect(base_path + 'my_db.db')
db_conn.row_factory = sqlite3.Row
db_cur = db_conn.cursor()
db_cur.execute('SELECT * FROM my_table')
row_objs = db_cur.fetchall()
rows_as_dicts = [dict(r) for r in row_objs]
db_cur.close()
db_conn.close()

Получим:

rows_as_dicts = [
  {'idx': 0, 'speed': 420, 'duration': 50},
  {'idx': 1, 'speed': 380, 'duration': 40},
  {'idx': 2, 'speed': 390, 'duration': 45}
]

Но целевая структура — словарь списков, организованный по столбцам:

cols_as_lists = {
  'idx': [0, 1, 2],
  'speed': [420, 380, 390],
  'duration': [50, 40, 45]
}

Что происходит на самом деле

Подход «список словарей» идёт по строкам и для каждой записи создаёт отдельный словарь, снова и снова обращаясь к одним и тем же ключам в Python. Если вам нужна колонковая раскладка, появляется лишняя работа: либо второй проход для разворота строк в столбцы, либо поиск по словарю для каждой ячейки во время загрузки. И то и другое добавляет накладные расходы. Альтернатива — собирать колонковую структуру сразу, во время чтения курсором.

Эффективный способ собрать словарь списков

Идея проста: читать результат порциями и для каждой порции одним шагом транспонировать данные из построчного вида в поколоночный. Связка fetchmany и компактного конвейера итераторов убирает повторные обращения к словарям на каждую ячейку и опирается на плотные проходы по данным. Такой способ показывал примерно пятикратное ускорение: он избегает повторных словарных обращений для каждого столбца в каждой строке и эффективнее использует итераторы.

import sqlite3
import collections
cn = sqlite3.connect(base_path + 'my_db.db')
cr = cn.cursor()
cr.execute('SELECT * FROM my_table')
# 'col_names' должен содержать имена столбцов в том же порядке, что и вывод SELECT
col_names = columns  # предполагается, что эта последовательность доступна вместе с курсором
store = collections.defaultdict(list)
while batch := cr.fetchmany(CHUNK_SIZE):
    for col_values, col_name in zip(zip(*batch), col_names):
        store[col_name].extend(col_values)
cr.close()
cn.close()
cols_as_lists = dict(store)

Так мы получаем нужный словарь списков прямо на этапе чтения — без промежуточного списка словарей строк.

Почему это важно

Для таблиц с большим числом строк поколоночная сборка уменьшает накладные расходы на стороне Python и заметно ускоряет загрузку. Плюс она сразу совпадает с потребителями колонкового формата, так что второй проход не нужен. Как отмечал один из практиков, в недавнем тесте прямая загрузка через pandas оказалась на порядок медленнее описанного подхода. Ещё одно наблюдение: можно идти из SQL сразу в pandas DataFrame, а DataFrame концептуально близок к словарю списков. Насколько такой компромисс уместен, зависит от ваших ограничений и нагрузки.

В недавнем тесте прямая загрузка через pandas была на порядок медленнее описанного подхода.

Можно перейти напрямую из SQL к pandas DataFrame, где DataFrame эквивалентен словарю списков.

Что стоит запомнить

Если вашему конвейеру выгоден формат «словарь списков», формируйте его сразу из курсора. Пакетное чтение с простым шагом транспонирования сохраняет код компактным и избавляет от поисков по ячейкам. Выбирая инструменты, учитывайте модель выполнения: удобство построчной обработки или пропускную способность поколоночной. Если важна скорость, попробуйте подход с пакетными итерациями на своих данных и держите его под рукой.