LibreOffice Basic. Быстрая вставка данных с листа Calc в базу данных

Автор ost, 5 марта 2021, 18:19

0 Пользователи и 1 гость просматривают эту тему.

ost

Доброго.
Подскажите способ быстрой вставки в базу данных (в моем случае SQLite3, доступ - ODBC-драйвер)

Делаю так

oBook = ThisComponent
sSheet = oBook.getCurrentController.getActiveSheet().Name

lFirstCol = 0
lFirstRow = 10
lLastCol = 15
lLastRow = 1000

'Данные листе Calc
oRangeData = oSheet.getCellRangeByPosition(lFirstCol, lFirstRow, lLastCol , lLastRow )
aData=oRangeData.getDataArray()

'=== Вставляем НАЧАЛО
'Готовим параметрический запрос вида "INSERT INTO <ИмяТаблицы> (Поле1, Поле2, Поле3....ПолеN) VALUES (?,?,? ...?N)"
'aHead1d - одномерный массив с наименованиями полей таблицы sSheet БД
sSql=MySet._StringForSqlIns("INSERT INTO", sSheet, aHead1d)

oStatement=oConn.PrepareStatement(sSql)
oStatement.EscapeProcessing = False
For i = LBound(aData) To UBound(aData)
aStr = aData(i)
For j = LBound(aStr) To UBound(aStr)
oStatement.setString(j + 1, aStr(j))
Next j
oStatement.executeUpdate()
Next i
'=== Вставляем КОНЕЦ


Но это жутко долго.
Научите побыстрее. Значительно побыстрее. Пожалуйста.

sokol92

Быстрый метод (на мой взгляд) - вывести диапазон в csv-файл средствами LO и затем средствами SQLite загнать в базу.
Владимир.

ost

Не подходит. Реализуемое будет использоваться людьми куда более непрограммистами, чем я.
Мне б какое-нибудь решение исключительно средствами Basic.

Видимо, решение где-то в районе управления транзакциями. Но в прошлый раз я это ниасилил. Сейчас времени мало. =(

sokol92

Я и имел в виду Basic. Перегоняем диапазон во временный файл формата .csv; генерируем sql-скрипт для утилиты командной строки SQLite и вызываем эту утилиту. Перекачка должна быть быстрой, правда, придется потрудиться над текстами макросов...
Владимир.

ost

Идея понятна. Осталось понять как ее реализовать. Вообще, рекордных темпов вставки не требуется. Требуется чтобы табличка на 10 столбцов и 10 тыс. строк вставлялась хотя бы минуты за 3-5. Если других вариантов не найдется, видимо, придется заморочиться с выгрузкой в текстовый файл. Хотя, очень не хочется.

add
Сейчас описанным способом таблица на 6 столбцов и 4,5 тыс строк вставляется на моей машине больше 10 минут. Грусть.

sokol92

Попробовал на "боевом" файле,  25000 строк, 30 столбцов, сохранение в csv-формате меньше 2 сек.
Владимир.

economist

Безсерверная крохотная SQLite - самая быстрая БД даже с HDD, а с SSD и NVMe она недостижима для всего остального (кроме RAM-баз типа Pandas, кстати, доступных и из LO).  

Рекорды скорости при INSERT/UPDATE/DELETE в базу SQLite - дает Python из под LO. Искать примеры кода по import sqlite3  

На 20-30% медленнее - это консольная sqlite3.exe

Еще на 10-20% медленнее - LO Base c его SQL (но все равно это быстрее FireBird/HSQLDB и "...всего что будет впредь")


Важно вот что проверить на своих условиях:

1) Индексы тормозят - часто быстрее удалить их/вставить данные/вернуть индексы. Вообще в 25% случаев индексы вредят.

2) Таблицу чаще быстрее дропнуть/импортнуть, чем делать INSERT/UPDATE (и это когда индексов нет вообще)

3) Используйте транзакции  BEGIN / COMMIT - ускорение в 2-5 раз

4) Используйте опции журнала и всего прочего в PRAGMA - там можно увеличить скорость в 3-5 раз, не сходя с места

5) Если под Linux - стоит попробовать Ext3 и NFS (если по сети). Под виндой стоит глянуть на активность антивируса. Кошмарский и Иссыт - могут быть очень подозрительными. т.к. SQLite использует "указатели" внутри файла, это эвристика части антивирусов.    

Файл CSV "уходит" в таблицу SQLite размером 10000х10 за 1-4 секунды. Никаких "минут" не должно с SQLite наблюдаться в принципе.
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

economist

#7
Самое быстрое по скорости работы решение чтения из листа ODS и вставки в SQLite будет таким (псевдокод py-файла):


import pandas, sqlite3
df = pandas.read_excel('D:/base.ods')
con = sqlite3.connect('D:/base.sqlite')
cur = con.cursor()
cur.executescript('delete from Tab1') # empty table
df.to_sqlite(df, con)
con.close()
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

eeigor

#8
Цитата: economist от  5 марта 2021, 22:17cur = con.cur()
cur = con.cursor()  ?
Ubuntu 18.04 LTS • LibreOffice 7.3.5.2 Community

ost

economist,
много полезного от вас получаю, как, впрочем, от всех участников форума.
Спасибо.

-Python не знаю от слова совсем. =(

-Индексы в БД сейчас не используются

-Сохранять данные таблицы, затем удалять ее, заново создавать, добавлять в ранее сохраненные данные новую их порцию и затем импортировать обновленные данные в пустую таблицу представляется сложным (если не найду более простого варианта буду думать в эту сторону)

-С транзакциями и  PRAGMA по-разбираюсь.

Разбирался уже с транзакциями. Потуги здесь https://forumooo.ru/index.php/topic,6520.0.html
Но так и не сообразил как это использовать на практике. Попробую поковырять еще.

economist

#10
Добрался до компа... Итак, если нужен самый быстрый способ вставки содержимого ODS в SQLITE (даже из открытого в LO в открытую чем-то SQLITE, и даже по сети, и даже с облачного хранилища), то по прежнему Python - рулит.

Скорость: Считывает из ODS, полностью удаляет таблицу и записывает обратно 10000 х 10 (текст/числа) ровно за 5 секунд, и это без транзакций, PRAGMA и прочих оптимизаций, код - 7 коротких строк.

Питон настолько простой язык, что воспринимается как естественная речь. Вот 100% рабочий код:

import pandas, sqlite3
df = pandas.read_excel('D:/base.ods')
con = sqlite3.connect('D:/base.sqlite')
cur = con.cursor()
cur.executescript('delete from TABL1') # empty table or del some old rows
df.to_sql('TABL1', con, index=False)
con.close()  


Код помещаем в текстовый файл с раширением .py и запускаем. ВАЖНО: Если имена файлов русские или в тексте py кириллица в комментах, именах переменных (так можно и иногда и нужно для англобояк) - то файл .py д.б. в кодировке utf-8

В чем этот py-файл запустится? Есть 2 варианта (наверняка пригодятся оба):

1) можно заранее "починить" слегка сломанный разрабами встроенный в LO python:
- скачать внутрь LO, там где лежит python.exe вот этот скрипт https://bootstrap.pypa.io/get-pip.py и запустить его
- набрать в консоли pip install pandas odfpy

2) можно установить обычный python c офсайта www.python.org - и лучше той же версии/разрядности, например 3.8.4, что и python.exe из LO - и если чего-то в LO-питоне не хватает - можно просто скопировать файл/папку из обычного питона - в вылеченный LO-шный

Способов запуска py-скрипта из Basic скрипта много, все примеры есть на Форуме
а) script.invoke() для встроенного питона
б) shell('start *.py', ...)
в) shell('*.bat', ...) ' а тот уже дергает py
г) oSvc.execute("C:\PortableApps\LibreOfficePortable\App\libreoffice\program\python.exe", "load_statTXT.py", 0)

Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...