postgres使用PL/Python导出表到csv

摘要

在PostgreSQL的标准发布中当前有四种过程语言可用： PL/pgSQL、 PL/Tcl、 PL/Perl以及 PL/Python。 还有其他过程语言可用，但是它们没有被包括在核心发布中。本文主要介绍使用PL/Python过程语言实现一个将表导出到csv文件的函数。

环境准备

1. 需要重源码编译postgres，目的是启用PL/Python服务端编程语言。要编译PL/Python服务器端编程语言， 你需要一个Python的安装，包括头文件和distutils模块。最低的版本要求是Python 2.4。如果是版本3.1或更高版本，则支持Python 3。
2. 安装python，本文使用python 3版本进行演示

$ sudo apt install python3 python3-dev

3. 编译postgres源码

$ ./configure --enable-shared --with-python
$ make -j 8
$ sudo make install

因为PL/Python将以共享库的方式编译，libpython库在大多数平台上也必须是一个共享库。在默认的从源码安装Python时不是这样的，而是在很多操作系统发布中有一个共享库可用。如果选择了编译 PL/Python但找不到一个共享的 libpython，configure将会失败。这可能意味着你不得不安装额外的包或者（部分）重编译Python安装以提供这个共享库。在从源码编译时，请用--enable-shared标志运行 Python的配置脚本。

PL/Python函数简介

举个例子

CREATE FUNCTION py_add (a integer, b integer)
  RETURNS integer
AS $$
  return a + b
$$ LANGUAGE plpython3u;

• 查看函数

• 运行函数

操作步骤

如何获取表字段属性

以pgbench中的pgbench_accounts为例。

select * from pg_attribute where attrelid = (select oid from pg_class where relname = 'pgbench_accounts') and attnum > 0 order by attnum;

plpy函数使用

PL/Python 语言模块会自动导入一个被称为plpy的 Python 模块。这个模块中的函数和常量在 Python 代码中可以用plpy.foo样的方式访问。

CREATE OR REPLACE FUNCTION count_odd_fetch(batch_size integer) RETURNS integer AS $$
odd = 0
cursor = plpy.cursor("select num from largetable")
while True:
    rows = cursor.fetch(batch_size)
    if not rows:
        break
    for row in rows:
        if row['num'] % 2:
            odd += 1
return odd
$$ LANGUAGE plpython3u;

demo

CREATE or replace  FUNCTION tab2file(file_name varchar, table_name varchar) RETURNS varchar AS $$
import csv
header_list =[]
plan = plpy.prepare("select * from pg_attribute where attrelid = (select oid from pg_class where relname = $1) and attnum > 0 order by attnum;", ["text"])
t_column = plpy.execute(plan, [table_name])
for c in t_column:
	header_list .append(c['attname'])
f = open(file_name, mode="w", encoding="utf-8", newline="")
writer = csv.DictWriter(f, header_list)
writer.writeheader()
sql = "select * from " + table_name + ";"
cursor = plpy.cursor(sql)
while True:
    rows = cursor.fetch(100)
    if not rows:
        break
    writer.writerows(rows)
f.close()
sql = "select count(*) from " + table_name + ";"
count = plpy.execute(sql)
return count[0]['count'] 
$$ LANGUAGE plpython3u;

写在后面

上面只是演示pl/python的用法，把表导出csv并不是一个很好的很好的应用场景。postgres的copy to是最佳选择。

让我们看一下性能，应该有一个数量级的差距，当然python代码还是可以进一步优化的。