跳到主要内容

PostgreSQL PL/Python

PostgreSQL 是一个功能强大的开源关系型数据库管理系统，支持多种编程语言作为其存储过程和函数的编程接口。其中，PL/Python 允许开发者使用 Python 语言编写数据库函数和存储过程。本文将详细介绍如何在 PostgreSQL 中使用 PL/Python，并通过示例展示其实际应用。

什么是 PL/Python？​

PL/Python 是 PostgreSQL 的一个过程语言扩展，允许开发者使用 Python 编写数据库函数。通过 PL/Python，你可以在数据库中执行 Python 代码，从而利用 Python 的强大功能和丰富的库生态系统。

备注

PL/Python 支持 Python 2 和 Python 3。在本文中，我们将使用 Python 3。

安装 PL/Python​

在开始使用 PL/Python 之前，你需要确保它已经安装在你的 PostgreSQL 数据库中。你可以通过以下命令检查是否已安装 PL/Python：

sql
SELECT * FROM pg_language WHERE lanname = 'plpython3u';

如果查询结果为空，你需要安装 PL/Python 扩展。在大多数 Linux 发行版中，你可以通过以下命令安装：

bash
sudo apt-get install postgresql-plpython3-<version>

其中 <version> 是你的 PostgreSQL 版本号。

安装完成后，你可以通过以下命令在数据库中启用 PL/Python：

sql
CREATE EXTENSION plpython3u;

编写第一个 PL/Python 函数​

让我们从一个简单的例子开始。假设我们想要创建一个函数，该函数接受两个整数作为输入，并返回它们的和。

sql
CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
    return a + b
$$ LANGUAGE plpython3u;

在这个例子中，我们定义了一个名为 add_numbers 的函数，它接受两个整数参数 a 和 b，并返回它们的和。函数体中的 Python 代码非常简单，直接返回 a + b 的结果。

调用函数​

你可以像调用普通 SQL 函数一样调用这个 PL/Python 函数：

sql
SELECT add_numbers(10, 20);

输出结果将是：

30

处理复杂数据类型​

PL/Python 不仅支持简单的数据类型，还可以处理复杂的数据类型，如数组、JSON 等。让我们看一个处理数组的例子。

示例：计算数组的平均值​

假设我们有一个整数数组，我们想要计算这个数组的平均值。

sql
CREATE OR REPLACE FUNCTION calculate_average(arr INTEGER[])
RETURNS FLOAT AS $$
    return sum(arr) / len(arr)
$$ LANGUAGE plpython3u;

在这个例子中，我们定义了一个名为 calculate_average 的函数，它接受一个整数数组 arr 作为输入，并返回数组的平均值。

调用函数​

你可以通过以下方式调用这个函数：

sql
SELECT calculate_average(ARRAY[10, 20, 30, 40, 50]);

输出结果将是：

30.0

实际应用场景​

PL/Python 在实际应用中有很多用途，特别是在需要复杂计算或与外部系统交互时。以下是一些常见的应用场景：

  1. 数据清洗和转换：在数据仓库中，PL/Python 可以用于清洗和转换数据。
  2. 机器学习：通过 PL/Python，你可以在数据库中直接调用 Python 的机器学习库，如 scikit-learn。
  3. 与外部 API 交互：你可以使用 Python 的 requests 库在数据库中调用外部 API。

示例：调用外部 API​

假设我们想要在数据库中调用一个外部 API 来获取天气数据。

sql
CREATE OR REPLACE FUNCTION get_weather(city TEXT)
RETURNS TEXT AS $$
    import requests
    response = requests.get(f"https://api.weatherapi.com/v1/current.json?key=YOUR_API_KEY&q={city}")
    return response.json()
$$ LANGUAGE plpython3u;

在这个例子中，我们定义了一个名为 get_weather 的函数，它接受一个城市名称作为输入，并返回该城市的天气数据。

警告

在实际使用中，请确保将 YOUR_API_KEY 替换为你的实际 API 密钥。

总结​

PL/Python 是一个强大的工具，允许你在 PostgreSQL 中使用 Python 编写存储过程和函数。通过 PL/Python，你可以利用 Python 的丰富库生态系统，扩展数据库的功能。本文介绍了如何安装 PL/Python、编写简单的函数、处理复杂数据类型以及在实际应用中使用 PL/Python。

附加资源​

练习​

  1. 编写一个 PL/Python 函数，接受一个字符串数组并返回其中最长的字符串。
  2. 修改 get_weather 函数，使其返回特定城市的温度而不是整个 JSON 响应。

通过完成这些练习，你将更深入地理解 PL/Python 的使用方法。