Как правильно работать с базой данных

@VistaSV30 · Регистрация: 10.04.2012

Студворк — интернет-сервис помощи студентам

Добрый день!
Скажите как правильнее (питоничнее) сделать.
Нужно провести анализ по базе данных, которая содержится в отдельных файлах с данными за каждый год, всего 11 файлов.
Каждая БД состоит из ~500 тыс. записей и 70 полей, типы данных: даты, текст, но в основном числа.

Подскажите пожалуйста, как лучше?
Объединить все базы в один фрейм или работать по отдельности?

Спасибо!

@dondublon · 22.02.2021, 18:55

Когда говорят о "базе данных в файлах", это означает текст в csv. Верно?

@VistaSV30 · 22.02.2021, 20:13 **[ТС]**

Да. Это выгрузки из БД в несколько сокращенном виде и сохраненные в формате CSV

@dondublon · 23.02.2021, 10:11

По обстоятельствам.
Данных многовато.
Если вы планируете с этими данными работать много - вам желательна БД. Либо работайте с той, откуда выгрузили, либо создавайте свою. Набор колонок надо будет унифицировать.
Если что-то разовое - можно обойтись пандасом.

@VistaSV30 · 23.02.2021, 11:25 **[ТС]**

dondublon, спасибо за ответ!
Работа с этими данными планируется надолго. Квартальные отчеты и т.п.
Колонки все одинаковые и соответственно все данные в них однотипные во всех файлах.

Я хотел использовать Pandas. Вы советуете, что-то другое?

@dondublon · 23.02.2021, 12:32

VistaSV30, пандас немного для других обстоятельств. Он запихивает всё в память, и всё работает быстро, пока у вас хватает памяти. А потом ему деваться некуда, и начинается подкачка.
БД (настоящие) в принципе заточены на то, что данных много и за один раз их в память не запихаешь, они умеют вытаскивать по кусочкам и освобождать память по требованию.

@VistaSV30 · 23.02.2021, 16:19 **[ТС]**

До этого я подобные отчеты делал с помощью Access -> Excel -> Word. Но всё равно оставалось много ручной работы, особенно с таблицами.
Сейчас пробую заменить Excel на Python. Все получается достаточно хорошо. Ну рисунки вставляю макросом из самого Word, а остальное всё получается как мне надо.

При загрузке в Python почти все колонки базы данных автоматически получают тип Object и вся таблица за один только год занимает в памяти почти 2 Гб, но после преобразования типов объем памяти снижается до 150 - 170 Мб.

Дальше для выполнения расчетов и построения графиков компьютеру требуется время, пока еще точно сказать не могу сказать сколько, только начинаю делать. Но в Access на все расчеты уходило не меньше 40 минут, и это в основном только с данными за 2 последних года.

Ведь Pandas и Numpy, насколько я понял, создавались для работы с большими данными? Или Вы советуете использовать что-то другое?

Добавлено через 29 минут

Сообщение от dondublon

запихивает всё в память

R тоже так делает, но он то точно создавался для статистических вычислений по большим данным

@dondublon · 23.02.2021, 17:19

Сообщение от VistaSV30

Ведь Pandas и Numpy, насколько я понял, создавались для работы с большими данными? Или Вы советуете использовать что-то другое?

Я посоветовал выше.
"Большие данные" - понятие растяжимое. Есть отдельное понятие "big data", тут numpy вообще не при чём. Ну, почти.
Numpy становится нужен тогда, когда тесно в рамках питоновских списков. То есть практически сразу, как только начинается работа с числами

Но ограничения на память никто не отменял, и numpy\pandas вам тут чуда не сотворят. Они работают в памяти, оперативная загрузка\забывание данных по запросу - за пределами их компетенции.

Добавлено через 6 минут

Сообщение от VistaSV30

R тоже так делает, но он то точно создавался для статистических вычислений по большим данным

Нет. Он создавался, как язык для статистики. Он не занимается хранением-загрузкой.

@VistaSV30 · 23.02.2021, 19:13 **[ТС]**

То есть, получается нужно делать так же как я раньше делал по схеме Access -> Excel -> Word. В Access создавать выборки, выгружать их в формате csv и потом докручивать их в Python?

Жалко, хотелось, то всё по новому в Python сделать...

Добавлено через 27 минут
Подумал сейчас. Видимо да, буду использовать старый проверенный метод. Буду выгружать в csv результаты запросов, а потом их уже обрабатывать в Python.

Плюсы такого решения:
- не нужно держать в памяти всю базу данных целиком;
- не нужно будет в Python делать какие-то подобия запросов SQL;

Минусы:
- придется не поленится, вернуться в Access и написать несколько десятков запросов
больше вроде бы минусов нет

@dondublon · 23.02.2021, 19:43

Сообщение от VistaSV30

То есть, получается нужно делать так же как я раньше делал по схеме Access -> Excel -> Word. В Access создавать выборки, выгружать их в формате csv и потом докручивать их в Python?

Нет. Excel тут ни к чему, а word вообще не для БД.
Access можно рассматривать, как СУБД, но она простая, "детская", для небольших объёмов. Для ваших - подойдёт.

Сообщение от VistaSV30

буду использовать старый проверенный метод. Буду выгружать в csv результаты запросов, а потом их уже обрабатывать в Python.

Ну вот зачем вам выгружать данные в текст, чтобы потом его героически загружать? Если у вас УЖЕ есть база? Настоящая. Сsv - это не база, это текстовый формат для обмена данными, не более того.

Из питона вы прекрасно можете обращаться к access напрямую.

@VistaSV30 · 23.02.2021, 20:00 **[ТС]**

База в Access более-менее нормализована. Есть таблицы с данными за каждый год в отдельных файлах Access, которые содержат кодовые значения. Есть таблицы с расшифровкой кодов.
Не проще ли будет в Access сделать выборки, сохранить их CSV (эти действия я пропишу на VBA) и потом уже из Python работать с файлами CSV?

Или в Python будет проще (или эффективнее) прописать SQL-запросы (ну или что-то подобное) ?

@u235 · 23.02.2021, 20:03

VistaSV30, может будет полезным: https://habr.com/ru/company/ruvds/blog/442516/

@VistaSV30 · 23.02.2021, 21:01 **[ТС]**

u235, спасибо за ссылку!
Свои данные я уже оптимизировал. Изначально таблица с данными за год занимала порядка 2 Gb, оптимизацией получается снизить объем необходимой памяти до 150 ... 190 Mb.
Сейчас вопрос стоит о выборе стратегии - загружать ли сразу все данные с 2010 по 2020 год в Python или загружать по частям по мере необходимости?

@dondublon · 24.02.2021, 09:15

Сообщение от VistaSV30

Не проще ли будет в Access сделать выборки, сохранить их CSV (эти действия я пропишу на VBA) и потом уже из Python работать с файлами CSV?

Нет, не проще, и точно не эффективнее.

Сообщение от VistaSV30

Или в Python будет проще (или эффективнее) прописать SQL-запросы (ну или что-то подобное) ?

Да, так.

@VistaSV30 · 24.02.2021, 10:03 **[ТС]**

Сообщение от dondublon

Да, так.

Спасибо за совет!
Читаю и пробую выполнять код.
Нужно использовать модуль adodbapi, авторы статьи говорят, что модуль не обновлялся с 2014 года.
adodbapi сейчас еще актуален? Или уже надо, что-то другое смотреть для работы с Access?

@dondublon · 24.02.2021, 11:13

VistaSV30, я точно не знаю, конкретно таким не занимался.
Выглядит действительно устаревшим в пользу ODBC, как минимум. Советую просто погуглить connect Python to MS Access.

@VistaSV30 · 24.02.2021, 12:49 **[ТС]**

Получилось подключиться к Access! Ура!!!

Python
1
2
3
4
5
6
7
8
9
10
11
12
13
14
import pyodbc as sqlMS
 
connStr = (
    r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
    r"DBQ=c:\Users\6\Documents\Python\db1.accdb;"
    )
 
conn = sqlMS.connect(connStr)
 
cursor = conn.cursor()
cursor.execute('select * from tbl_1')
   
for row in cursor.fetchall():
    print (row)

Прочитал здесь

Добавлено через 1 час 2 минуты
Написал громадный запрос на объединение с группировкой и условием.
И он выполнился!

Кликните здесь для просмотра всего текста

Python
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
import pyodbc as sqlMS
 
connStr = (
    r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
    r"DBQ=d:\FR\FR.accdb;"
    )
 
conn = sqlMS.connect(connStr)
cursor = conn.cursor()
 
strSQL = '''
SELECT n2019.F5, n2019.[Count-F0], n2019.[Sum-F27], n2019.[Sum-F30]
FROM (
SELECT TOP 5 FR2019.F5, Count(FR2019.F0) AS [Count-F0], Sum(FR2019.F27) AS [Sum-F27], Sum(FR2019.F30) AS [Sum-F30]
FROM FR2019
WHERE (((FR2019.F4)=0))
GROUP BY FR2019.F5) AS n2019
 
UNION ALL
 
SELECT n2020.F5, n2020.[Count-F0], n2020.[Sum-F27], n2020.[Sum-F30]
FROM (
SELECT TOP 5 FR2020.F5, Count(FR2020.F0) AS [Count-F0], Sum(FR2020.F27) AS [Sum-F27], Sum(FR2020.F30) AS [Sum-F30]
FROM FR2020
WHERE (((FR2020.F4)=0))
GROUP BY FR2020.F5) AS n2020
'''
cursor.execute(strSQL)
   
for row in cursor.fetchall():
    print (row)

@dondublon · 24.02.2021, 13:31

Новые блоги и статьи Все статьи Все блоги /
Модель ЗдрввоСохранения 7: больше работников, больше ресурсов. anaschu 08.04.2026 работников и заданий может быть сколько угодно, но настроено всё так, что используется пока что только 20%	Дальние перспективы сервера - слоя сети с космологическим дизайном интефейса карты и логики. Hrethgir 07.04.2026 Дальнейшее ближайшее планирование вывело к размышлениям над дальними перспективами. И вот тут может быть даже будут нужны оценки специалистов, так как в дальних перспективах всё может очень сильно. . .	Горе от ума kumehtar 07.04.2026 Эта мне ментальная установка, что вот прямо сейчас, мол, мне для полного счастья не хватает (нужное вписать), и когда я этого достигну - тогда и полный кайф. Одна из самых сильных ловушек на пути. . . .	Использование значений реквизитов справочника в документе, с определенными условиями и правами Maks 07.04.2026 1. Контроль срока действия договора Алгоритм из решения ниже реализован на примере нетипового документа "ЗаявкаНаРаботу", разработанного в конфигурации КА2. Задача: уведомлять пользователя, если. . .
Доступность команды формы по условию Maks 07.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "СписаниеМатериалов", разработанного в конфигурации КА2. Задача: сделать доступной кнопку (команда формы "ЗавершитьСписание") при. . .	Уведомление о неверно выбранном значении справочника Maks 06.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "НарядПутевка", разработанного в конфигурации КА2. Задача: уведомлять пользователя, если в документе выбран неверный склад. . .	Установка Qt Creator для C и C++: ставим среду, CMake и MinGW без фреймворка Qt 8Observer8 05.04.2026 Среду разработки Qt Creator можно установить без фреймворка Qt. Есть отдельный репозиторий для этой среды: https:/ / github. com/ qt-creator/ qt-creator, где можно скачать установщик, на вкладке Releases:. . .	AkelPad-скрипты, структуры, и немного лирики.. testuser2 05.04.2026 Такая программа, как AkelPad существует уже давно, и также давно существуют скрипты под нее. Тем не менее, прога живет, периодически что-то не спеша дополняется, улучшается. Что меня в первую очередь. . .

@VistaSV30 988 / 332 / 79 Регистрация: 10.04.2012 Сообщений: 1,242 Записей в блоге: 4

	Как правильно работать с базой данных 21.02.2021, 16:08. Показов 1868. Ответов 17 Метки pandas, python (Все метки) Добрый день! Скажите как правильнее (питоничнее) сделать. Нужно провести анализ по базе данных, которая содержится в отдельных файлах с данными за каждый год, всего 11 файлов. Каждая БД состоит из ~500 тыс. записей и 70 полей, типы данных: даты, текст, но в основном числа. Подскажите пожалуйста, как лучше? Объединить все базы в один фрейм или работать по отдельности? Спасибо! 0

@dondublon 4653 / 2073 / 366 Регистрация: 17.03.2012 Сообщений: 10,183 Записей в блоге: 6
	22.02.2021, 18:55
	Когда говорят о "базе данных в файлах", это означает текст в csv. Верно? 0

@VistaSV30 988 / 332 / 79 Регистрация: 10.04.2012 Сообщений: 1,242 Записей в блоге: 4
	22.02.2021, 20:13 [ТС]
	Да. Это выгрузки из БД в несколько сокращенном виде и сохраненные в формате CSV 0

@dondublon 4653 / 2073 / 366 Регистрация: 17.03.2012 Сообщений: 10,183 Записей в блоге: 6
	23.02.2021, 10:11
	По обстоятельствам. Данных многовато. Если вы планируете с этими данными работать много - вам желательна БД. Либо работайте с той, откуда выгрузили, либо создавайте свою. Набор колонок надо будет унифицировать. Если что-то разовое - можно обойтись пандасом. 0

@VistaSV30 988 / 332 / 79 Регистрация: 10.04.2012 Сообщений: 1,242 Записей в блоге: 4
	23.02.2021, 11:25 [ТС]
	dondublon, спасибо за ответ! Работа с этими данными планируется надолго. Квартальные отчеты и т.п. Колонки все одинаковые и соответственно все данные в них однотипные во всех файлах. Я хотел использовать Pandas. Вы советуете, что-то другое? 0

@dondublon 4653 / 2073 / 366 Регистрация: 17.03.2012 Сообщений: 10,183 Записей в блоге: 6
	23.02.2021, 12:32
	VistaSV30, пандас немного для других обстоятельств. Он запихивает всё в память, и всё работает быстро, пока у вас хватает памяти. А потом ему деваться некуда, и начинается подкачка. БД (настоящие) в принципе заточены на то, что данных много и за один раз их в память не запихаешь, они умеют вытаскивать по кусочкам и освобождать память по требованию. 0

@VistaSV30 988 / 332 / 79 Регистрация: 10.04.2012 Сообщений: 1,242 Записей в блоге: 4
	23.02.2021, 19:13 [ТС]
	То есть, получается нужно делать так же как я раньше делал по схеме Access -> Excel -> Word. В Access создавать выборки, выгружать их в формате csv и потом докручивать их в Python? Жалко, хотелось, то всё по новому в Python сделать... Добавлено через 27 минут Подумал сейчас. Видимо да, буду использовать старый проверенный метод. Буду выгружать в csv результаты запросов, а потом их уже обрабатывать в Python. Плюсы такого решения: - не нужно держать в памяти всю базу данных целиком; - не нужно будет в Python делать какие-то подобия запросов SQL; Минусы: - придется не поленится, вернуться в Access и написать несколько десятков запросов больше вроде бы минусов нет 0

@VistaSV30 988 / 332 / 79 Регистрация: 10.04.2012 Сообщений: 1,242 Записей в блоге: 4
	23.02.2021, 20:00 [ТС]
	База в Access более-менее нормализована. Есть таблицы с данными за каждый год в отдельных файлах Access, которые содержат кодовые значения. Есть таблицы с расшифровкой кодов. Не проще ли будет в Access сделать выборки, сохранить их CSV (эти действия я пропишу на VBA) и потом уже из Python работать с файлами CSV? Или в Python будет проще (или эффективнее) прописать SQL-запросы (ну или что-то подобное) ? 0

@u235 5517 / 2870 / 571 Регистрация: 07.11.2019 Сообщений: 4,761
	23.02.2021, 20:03
	VistaSV30, может будет полезным: https://habr.com/ru/company/ruvds/blog/442516/ 0

@VistaSV30 988 / 332 / 79 Регистрация: 10.04.2012 Сообщений: 1,242 Записей в блоге: 4
	23.02.2021, 21:01 [ТС]
	u235, спасибо за ссылку! Свои данные я уже оптимизировал. Изначально таблица с данными за год занимала порядка 2 Gb, оптимизацией получается снизить объем необходимой памяти до 150 ... 190 Mb. Сейчас вопрос стоит о выборе стратегии - загружать ли сразу все данные с 2010 по 2020 год в Python или загружать по частям по мере необходимости? 0

@dondublon 4653 / 2073 / 366 Регистрация: 17.03.2012 Сообщений: 10,183 Записей в блоге: 6
	24.02.2021, 11:13
	VistaSV30, я точно не знаю, конкретно таким не занимался. Выглядит действительно устаревшим в пользу ODBC, как минимум. Советую просто погуглить connect Python to MS Access. 1

@dondublon 4653 / 2073 / 366 Регистрация: 17.03.2012 Сообщений: 10,183 Записей в блоге: 6
	24.02.2021, 13:31
	2