PostgresSQL数据库通过PL/Python3自定义函数

地球人是我哈

已于 2024-02-27 11:00:08 修改

阅读量379

点赞数 2

文章标签：数据库

于 2024-02-22 18:01:53 首次发布

本文链接：https://blog.csdn.net/weixin_44865574/article/details/136240468

版权

本文介绍了如何在PostgreSQL数据库中安装PL/Python3扩展，创建一个通过发送HTTP请求获取数据并解析XML的自定义函数，以及如何部署所需的Python模块进行测试。

摘要由CSDN通过智能技术生成

PostgresSQL数据库通过PL/Python实现自定义函数

1、安装PL/Python3

libtirpc-0.2.4-0.16.el7.x86_64.rpm
python3-libs-3.6.8-13.el7.x86_64.rpm
python3-3.6.8-13.el7.x86_64.rpm
python3-pip-9.0.3-5.el7.noarch.rpm
python3-setuptools-39.2.0-10.el7.noarch.rpm

下载上述rpm包,然后通过命令安装
rpm -ivh *.rpm

PostgresSQL数据库下执行

CREATE EXTENSION plpython3u;
CREATE Language plpython3u;

2、创建自定义函数

自定义函数功能：

通过http请求接口，获取接口返回值并返回。需要使用python的requests模块

CREATE OR REPLACE FUNCTION public.instance_decrypt(instanceid character varying)
 RETURNS text
 LANGUAGE plpython3u
AS $function$
# -*- coding: utf-8 -*-
import requests
import xml.etree.ElementTree as ET
from requests.packages.urllib3.exceptions import InsecureRequestWarning

def requesutUrl(instanceid):
    url = 'https://172.20.30.98:1443/csp/hsb/DHC.Published.ZLLISOrderService.BS.ZLLISOrderService.cls'
    template = '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:dhcc="http://www.dhcc.com.cn"><soapenv:Header/><soapenv:Body><dhcc:HIPMessageServer><dhcc:action>MES0168</dhcc:action><dhcc:message>INSTANCELD</dhcc:message></dhcc:HIPMessageServer></soapenv:Body></soapenv:Envelope>'
    data = template.replace("INSTANCELD",instanceid)
    headers = {'Content-type': 'text/xml; charset=utf-8','SOAPAction':'http://www.dhcc.com.cn/DHC.Published.ZLLISOrderService.BS.ZLLISOrderService.HIPMessageServer'}

    requests.packages.urllib3.disable_warnings(InsecureRequestWarning)
    response = requests.post(url, data=data, headers=headers,verify=False)
    return response.text

def parseXml(xml_string):
    root = ET.fromstring(xml_string)
    result_element = root.find('.//{http://www.dhcc.com.cn}HIPMessageServerResult')

    if result_element is not None:
        text_content = result_element.text.strip()
        return text_content
    else:
        print("Element not found")

	
xml_string=requesutUrl(instanceid)
res=parseXml(xml_string)
return res
$function$
;

3、部署模块

在python3中，默认是没有requests模块的，需要手动安装；

下载下面依赖

wget https://mirrors.cloud.tencent.com/pypi/packages/e2/f0/c81405acbf53d0412b984eb3fc578cdd10e347374e1aec074638a500c186/requests-2.18.0-py2.py3-none-any.whl
wget https://files.pythonhosted.org/packages/11/7d/9bbbd7bb35f34b0169542487d2a8859e44306bb2e6a4455d491800a5621f/idna-2.5-py2.py3-none-any.whl
wget https://files.pythonhosted.org/packages/ba/06/a07f096c664aeb9f01624f858c3add0a4e913d6c96257acb4fce61e7de14/certifi-2024.2.2-py3-none-any.whl
wget https://files.pythonhosted.org/packages/63/cb/6965947c13a94236f6d4b8223e21beb4d576dc72e8130bd7880f600839b8/urllib3-1.22-py2.py3-none-any.whl
wget https://files.pythonhosted.org/packages/bc/a9/01ffebfb562e4274b6487b4bb1ddec7ca55ec7510b22e4c51f14098443b8/chardet-3.0.4-py2.py3-none-any.whl

安装模块

pip3 install *.whl

WARNING: Running pip install with root privileges is generally not a good idea. Try `pip3 install --user` instead.
Processing ./certifi-2024.2.2-py3-none-any.whl
Processing ./chardet-3.0.4-py2.py3-none-any.whl
Processing ./idna-2.5-py2.py3-none-any.whl
Processing ./requests-2.18.0-py2.py3-none-any.whl
Processing ./urllib3-1.22-py2.py3-none-any.whl
Installing collected packages: certifi, chardet, idna, urllib3, requests
Successfully installed certifi-2024.2.2 chardet-3.0.4 idna-2.5 requests-2.18.0 urllib3-1.22

4、测试

select public.instance_decrypt('769431||1');