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

[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]

Самое быстрое по скорости работы решение чтения из листа 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]

cur = con.cur()

cur = con.cursor()  ?

[ost]

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

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

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

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

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

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

[economist]

Добрался до компа... Итак, если нужен самый быстрый способ вставки содержимого 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)