[[ Экспорт запросов PostgreSQL в Excel файл ]]

PostgreSQLMicrosoft Excel

Экспорт запросов PostgreSQL в Excel файл

python3.x
python3-psycopg2
python3-xlsxwriter

Данная утилита сохраняет результаты SQL запросов к СУБД PostgreSQL в файл .xlsx.
Можно применять для создания: отчетов и т.п.
Каждый запрос сохраняется на отдельной странницы документа.


Установка скрипта

Сам скрипт размещен на GitHub


Настройка

Для начала работы необходимо настроить config.ini
Файл состоит из специальной секции default и секций страниц

config.ini

[default]
output = /tmp/1.xlsx
overwrite = on
;font_name = Liberation Sans
;font_size = 10

host = 127.0.0.1
port = 5432
base = [BASE]
user = [USER]
pass = [****]


[Тест]
query = SELECT 1 as test;

[Пользователи]
query = SELECT
         "public".users.userid,
         "public".users."name",
         "public".users.surname
        FROM
         "public".users
        WHERE
         "public".users."name" NOT LIKE 'A';

[Из другой базы]
host = 10.0.0.1
port = 5432
base = [BASE]
user = [USER]
pass = [****]
query = select ...;

[Несколько запросов]
; Разделитель ';\n'.
query = SELECT NULL as "Title text" limit 0;
        SELECT 0.1 as money, 'Alex' as "Test Name";
        SELECT NULL limit 0;
        SELECT NULL limit 0;
        SELECT NULL as "Other subject  title" limit 0;
        SELECT 11 as minute,  5 as second;


Исполнение

user@localhost:~$ ./psql2xlsx.py -h

user@localhost:~$ ./psql2xlsx.py
[..] Generated page :: Тест ...
[OK] PostgreSQL successfully connected
[..] Generated page :: Пользователи ...
[OK] PostgreSQL successfully connected
[..] Generated page :: Из другой базы ...
[OK] PostgreSQL successfully connected
[OK] Workbook saved :: /tmp/1.xlsx


Автоматизация

Создадим скрипт работающий по cron'y и отправляющий файл на почту.

#!/bin/bash
XLSX_FILE="/tmp/1.xlsx"
 
rm -f "${XLSX_FILE}"
 
STDOUT=$(/opt/psql2xlsx/psql2xlsx.py --output "${XLSX_FILE}" 2>&1)
 
if [ ! -f "${XLSX_FILE}" ]; then
    echo "ERROR" > "${XLSX_FILE}"
fi
 
sendemail -f test@domain.com -u "AutoOt4et" \
          -m "${STDOUT}" -o message-charset=utf-8 -a "${XLSX_FILE}" \
          -s smtp.mail.com:587 -xu 'LoGiN' -xp 'PaSsWorD' \
          -t alexey@domain.ru

В этом примере я использую утилиту sendemail. К сожалению в текущей версии у нее нет правильного mime-type для вложений .xlsx
Это может доставить небольшие неприятности при открытии документа из почтового клиента, но легко поправимо:

/bin/sendemail

    elsif ($encoding =~ /mdb|mda|mde/i) {                          $content_type = 'application/vnd.ms-access';       }
+   elsif ($encoding =~ /xlsx/i)                            {      $content_type = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';}
    elsif ($encoding =~ /xls|xlt|xlm|xld|xla|xlc|xlw|xll/i) {      $content_type = 'application/vnd.ms-excel';        }






Обсуждение

Haowu Ge, 2019/08/13 10:42

Здравствуйте, если количество строк данных превышает 1048576 строк, как я могу автоматически добавить лист? Hello, if the number of data lines exceeds 1048576 lines, how can I automatically add a sheet? 您好,如果数据行数超过1048576行,如何让他自动新增一个sheet?

Aleksey, 2019/08/13 11:27, 2019/08/13 11:27

Такого функционала не предусмотрено. Можете заранее сделать несколько страниц с указав параметры LIMIT и OFFSET в запросе, однако необходимо отсортировать данные по ключу.
Sorry, such functionality is not provided. You can make several pages in advance with specifying the LIMIT and OFFSET parameters in the SQL query, however, you need to sort the data by key.

Haowu Ge, 2019/08/13 11:48

Хорошо спасибо

Haowu Ge, 2019/08/13 12:09

Здравствуйте, я хочу установить числовой формат, но, похоже, он не вступил в силу Workbook_format_global.set_num_format ('0.00') не вступает в силу workbook_format_global.set_num_format (0.00) частично действителен, неправильный формат

Hello, I want to set the number format but it doesn't seem to take effect Workbook_format_global.set_num_format('0.00') does not take effect workbook_format_global.set_num_format(0.00) partially valid, format is incorrect

您好,我想设置数字格式但是似乎并不生效 workbook_format_global.set_num_format('0.00') 不生效 workbook_format_global.set_num_format(0.00) 部分生效,结果不正确

Haowu Ge, 2019/08/13 12:11

Не удается отправить фотографии, они должны были содержать 2 десятичных знака, последние два не отображаются

Can't send pictures, it should have kept 2 decimals, the last two don't show

Показать результаты: 111.66 → 112

Haowu Ge, 2019/08/14 12:11

Спасибо за вашу работу, Я решил проблему с format.num_format_index

Евгений, 2021/07/22 12:27

Здравствуйте, мне выдает ошибку "Missing configuration section :: default" Хотя она есть и настроена. Попытался разобраться в вашем коде, не нашёл, как это исправить

Aleksey, 2021/07/23 16:46

Если говорит, что не нашел секцию default в конфиге - значит не нашел. Используйте конфиг из гита как шаблон.

Ваш комментарий. Вики-синтаксис разрешён:
124​ +10 =
 
handmade/postgres/psql2xlsx.txt · Последнее изменение: 2022/08/31 14:53 — 127.0.0.1
Gentoo Linux Gentoo Linux Driven by DokuWiki