Last active
June 24, 2022 02:11
-
-
Save firstDismay/526011ac1eaea28e70e8dea42e15c685 to your computer and use it in GitHub Desktop.
PostgreSQL python select export to excel
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION py_export_to_excel( | |
command_export text) | |
RETURNS bytea | |
LANGUAGE 'plpython3u' | |
COST 100 | |
VOLATILE PARALLEL UNSAFE | |
AS $BODY$ | |
'''Функция Python 3.7.4''' | |
'''Дата: 29.05.2020 / 26.11.2020''' | |
'''Версия: 1.2''' | |
'''Функция экспортирует запрос в файл Excel''' | |
import uuid, io | |
from os import getcwd, remove, makedirs | |
from os.path import isfile, exists | |
from openpyxl import Workbook | |
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font, Color | |
from openpyxl.utils import get_column_letter | |
from copy import copy | |
'''Защита от пустой строки''' | |
if len(command_export) < 10: | |
return None | |
'''Код безопасности''' | |
forbidden_commands = ['update', 'delete', 'insert', 'truncate', 'references', 'trigger','set', 'grant','revoke', 'null', 'none', 'drop', 'create', 'alter', 'set'] | |
for x in forbidden_commands: | |
if command_export.count(x): | |
return None | |
'''Создаем книгу Excel''' | |
wb = Workbook() | |
'''Подготавливаем область экспорта данных''' | |
wb.remove(wb.active) | |
ws_info = wb.create_sheet('Info',0) | |
ws_data = wb.create_sheet('Export_Import',1) | |
wb.active = ws_data | |
'''Заголовок таблицы''' | |
try: | |
tshema = plpy.execute(command_export, 1) | |
tshema_col = tshema.colnames() | |
except: | |
return None | |
thin = Side(border_style="thin", color="000000") | |
double = Side(border_style="double", color="000000") | |
r = 1 | |
c = 1 | |
for cn in tshema.colnames(): | |
ws_data.cell(row=r,column=c).number_format = 'General' | |
ws_data.cell(row=r,column=c).value = cn | |
ws_data.cell(row=r,column=c).fill = PatternFill("solid", fgColor="DDDDDD") | |
ws_data.cell(row=r,column=c).border = Border(top=thin, left=thin, right=thin, bottom=double) | |
ft = copy(ws_data.cell(row=r,column=c).font) | |
ft.bold = True | |
ws_data.cell(row=r,column=c).font = ft | |
column_letter = get_column_letter(c) | |
ws_data.column_dimensions[column_letter].width = len(str(cn)) + 2 | |
c += 1 | |
'''Заголовок таблицы закрепить''' | |
ws_data.freeze_panes = 'A2' | |
'''Данные таблицы''' | |
tcursor = plpy.cursor(command_export) | |
for trow in tcursor: | |
c = 1 | |
r += 1 | |
for cn in tshema.colnames(): | |
ws_data.cell(row=r,column=c).number_format = 'General' | |
tcn = trow[cn] | |
column_letter = get_column_letter(c) | |
if ws_data.column_dimensions[column_letter].width < len(str(tcn)) + 2: | |
ws_data.column_dimensions[column_letter].width = len(str(tcn)) + 2 | |
if type(tcn) is int: | |
ws_data.cell(row=r,column=c).value = str(tcn) | |
elif type(tcn) is list: | |
ws_data.cell(row=r,column=c).value = str(tcn) | |
else: | |
ws_data.cell(row=r,column=c).value = tcn | |
ws_data.cell(row=r,column=c).border = Border(left=thin, right=thin, bottom=thin) | |
c += 1 | |
'''***********************************************************''' | |
'''Преобразование книги в байтовый массив без временного файла''' | |
'''***********************************************************''' | |
MemoryWB = io.BytesIO() | |
wb.save(filename = MemoryWB) | |
MemoryWB.seek(0) | |
res = MemoryWB.read() | |
'''***********************************************************''' | |
'''Преобразование книги в байтовый массив без временного файла''' | |
'''***********************************************************''' | |
return res | |
$BODY$; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment