PostgreSQL python(plpython3u extension) abilities

posgresql-python-tricks.sql

-- 1. Install
--      apt-get update && apt-get install -f postgresql-plpython3-16 pip


-- 2. Python extension
CREATE EXTENSION plpython3u;

-- 3. Install API python packages
--      pip install http.client urllib3 --break-system-packages


-- Get html response
-- Create url_quote function
CREATE OR REPLACE FUNCTION request2(url text)
    RETURNS TEXT
AS
$$
    # Python stuff
    import ssl
    import http.client
    from urllib.parse import urlparse
    o = urlparse(url)
    # connect to server
    conn = http.client.HTTPSConnection(
        o.hostname,
        context=ssl._create_unverified_context()
    )

    # return response as string
    request = conn.request("GET", o.path)
    return conn.getresponse().read().decode('utf-8')
$$
    LANGUAGE 'plpython3u';

-- Get htmk
SELECT request2('https://jsonplaceholder.typicode.com/todos/1');

-- Get json
SELECT request2('https://jsonplaceholder.typicode.com/todos/1')::json ->> 'id'        as id,
       request2('https://jsonplaceholder.typicode.com/todos/1')::json ->> 'title'     as title,
       request2('https://jsonplaceholder.typicode.com/todos/1')::json ->> 'completed' as completed;


--- JSON response
SELECT request2('https://google.com');
SELECT request2('https://jsonplaceholder.typicode.com/todos/1')::json ->> 'userId' as userId;

-- check function
--- HTML response
SELECT request2('https://docs.python.org/3/') as html;

-- create function
DROP FUNCTION IF EXISTS calculate_statistics(numbers FLOAT[]);
CREATE OR REPLACE FUNCTION calculate_statistics(numbers FLOAT[]) RETURNS json AS
$$
    import statistics
    import json

    mean = statistics.mean(numbers)
    median = statistics.median(numbers)

    return json.dumps({
        'mean': mean,
        'median': median
    })
$$ LANGUAGE plpython3u;

-- test function
SELECT calculate_statistics(ARRAY [1, 2, 3, 4, 5, 6, 7111, 8, 9, 10]),
       calculate_statistics(ARRAY [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]) ->> 'mean'   as mean,
       calculate_statistics(ARRAY [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]) ->> 'median' as median;