PostgreSQL的客户端接口

在PostgreSQL发行版中只包含两个客户端接口: libpq 和 ECPG

• libpq is included because it is the primary C language interface, and because many other client interfaces are built on top of it.
• ECPG is included because it depends on the server-side SQL grammar, and is therefore sensitive to changes in PostgreSQL itself.

其他语言客户端接口：

Python 语言连接PostgreSQL数据库

  之前研究 PostgreSQL 高可用 Patroni + etcd，其中 Patroni 就是基于Python语言编写的，同时用到了 Psycopg 连接 PostgreSQL 数据库，关于 Patroni 和高可用请参考：
  PostgreSQL高可用测试系列之Patroni + etcd + HAProxy + Keepalived 离线部署（四）

  Python 官网： https://www.python.org/ Python 文档和源码都可以在这里找到，当前最新版本 Python 3.10.0。
  Psycopg 官网： https://www.psycopg.org/ Psycopg 文档和源码都可以在这里找到。

Python3 和 Psycopg 的安装

  Python3 的安装，这里不重述了，请参考上文 高可用之Patroni，建议联网安装(yum install python3)或下载我分享的安装包，但是版本不是最新的(3.6.8)，如果需要使用新版本的 Python，还得下载源码编译安装。但是使用源码编译安装过程很漫长。
Psycopg 现在有两个大版本，Psycopg2 和 Psycopg3，Psycopg2 还有两种类型的包(psycopg2 和 psycopg2-binary)，其中 psycopg2-binary 相当于绿色版，不需要编译，是为了初学者用 Python 快速和 PostgreSQL 进行连接而用的，对于生产环境，建议编译安装使用psycopg2。

psycopg2，常用

编译安装psycopg2有几个先决条件：

• A C compiler.
• The Python header files. They are usually installed in a package such as python-dev or python3-dev. A message such as error: Python.h: No such file or directory is an indication that the Python headers are missing.
• The libpq header files. They are usually installed in a package such as libpq-dev. If you get an error: libpq-fe.h: No such file or directory you are missing them.
• The pg_config program: it is usually installed by the libpq-dev package but sometimes it is not in a PATH directory. Having it in the PATH greatly streamlines the installation, so try running pg_config --version: if it returns an error or an unexpected version number then locate the directory containing the pg_config shipped with the right libpq version (usually /usr/lib/postgresql/X.Y/bin/) and add it to the PATH:

$ export PATH=/usr/lib/postgresql/X.Y/bin/:$PATH
You only need pg_config to compile psycopg2, not for its regular usage.

[root@proxy ~]# yum -y install  gcc gcc-c++ make 
[root@proxy ~]# pip3 install psycopg2

Error: pg_config executable not found.

[root@proxy ~]# yum install -y postgresql-devel

./psycopg/psycopg.h:35:20: fatal error: Python.h: No such file or directory
 #include <Python.h>
                    ^
compilation terminated.

[root@proxy ~]# yum install -y python36-devel

[root@proxy ~]# pip3 install psycopg2
WARNING: Running pip install with root privileges is generally not a good idea. Try `pip3 install --user` instead.
Collecting psycopg2
  Using cached https://files.pythonhosted.org/packages/aa/8a/7c80e7e44fb1b4277e89bd9ca509aefdd4dd1b2c547c6f293afe9f7ffd04/psycopg2-2.9.1.tar.gz
Installing collected packages: psycopg2
  Running setup.py install for psycopg2 ... done
Successfully installed psycopg2-2.9.1
[root@proxy ~]# 

[root@proxy ~]# cat py_to_pg.py
# 导入 psycopg2 包
import psycopg2
# 连接到一个给定的数据库
conn = psycopg2.connect(host="192.168.58.10", port="5432", database="postgres", user="postgres", password="postgres", )
# 建立游标，用来执行数据库操作
cur = conn.cursor()
# 执行 SQL 命令
cur.execute("select inet_server_addr(),pg_is_in_recovery(),current_database(),current_user")
# 获取SELECT返回的元组
rows = cur.fetchall()
for row in rows:
    print('inet_server_addr: ' + str(row[0]))
    print('pg_is_in_recovery: ' + str(row[1]))
    print('current_database: ' + row[2])
    print('current_user: ' + row[3])
# 关闭游标
cur.close()
# 关闭数据库连接
conn.close()

[root@proxy ~]# /usr/bin/python py_to_pg.py
inet_server_addr: 192.168.58.10
pg_is_in_recovery: True
current_database: postgres
current_user: postgres

Psycopg3，新的，可能还不成熟，暂时不好用，没测通

官方文档： https://www.psycopg.org/psycopg3/docs/
Psycopg 3 是为Python 编程语言新设计的PostgreSQL数据库适配器。

1. pip3 install psycopg[binary] 这种方式装不上

[root@proxy ~]# pip3 --version
pip 9.0.3 from /usr/lib/python3.6/site-packages (python 3.6)

[root@proxy ~]# pip3 install --upgrade pip    # upgrade pip to at least 20.3
Successfully installed pip-21.3.1

[root@proxy ~]# pip3 install psycopg[binary]
WARNING: pip is being invoked by an old script wrapper. This will fail in a future version of pip.
Please see https://github.com/pypa/pip/issues/5599 for advice on fixing the underlying issue.
To avoid this problem you can invoke Python with '-m pip' instead of running pip directly.
Collecting psycopg[binary]
  Using cached psycopg-3.0.3-py3-none-any.whl (140 kB)
Collecting typing-extensions
  Using cached typing_extensions-3.10.0.2-py3-none-any.whl (26 kB)
Collecting backports.zoneinfo
  Using cached backports.zoneinfo-0.2.1-cp36-cp36m-manylinux1_x86_64.whl (70 kB)
Collecting psycopg[binary]
  Using cached psycopg-3.0.2-py3-none-any.whl (140 kB)
  Using cached psycopg-3.0.1-py3-none-any.whl (140 kB)
  Using cached psycopg-3.0-py3-none-any.whl (140 kB)
ERROR: Cannot install psycopg[binary]==3.0, psycopg[binary]==3.0.1, psycopg[binary]==3.0.2 and psycopg[binary]==3.0.3 because these package versions have conflicting dependencies.

The conflict is caused by:
    psycopg[binary] 3.0.3 depends on psycopg-binary==3.0.3; extra == "binary"
    psycopg[binary] 3.0.2 depends on psycopg-binary==3.0.2; extra == "binary"
    psycopg[binary] 3.0.1 depends on psycopg-binary==3.0.1; extra == "binary"
    psycopg[binary] 3.0 depends on psycopg-binary==3.0; extra == "binary"

To fix this you could try to:
1. loosen the range of package versions you've specified
2. remove package versions to allow pip attempt to solve the dependency conflict

ERROR: ResolutionImpossible: for help visit https://pip.pypa.io/en/latest/user_guide/#fixing-conflicting-dependencies
[root@proxy ~]# 

1. pip install psycopg[c] 这种方式也装不上

[root@proxy ~]# pip install psycopg[c]
Collecting psycopg[c]
  Using cached psycopg-3.0.3-py3-none-any.whl (140 kB)
Collecting backports.zoneinfo
  Using cached backports.zoneinfo-0.2.1-cp36-cp36m-manylinux1_x86_64.whl (70 kB)
Collecting typing-extensions
  Using cached typing_extensions-3.10.0.2-py3-none-any.whl (26 kB)
Collecting psycopg-c==3.0.3
  Downloading psycopg-c-3.0.3.tar.gz (594 kB)
     |████████████████████████████████| 594 kB 16 kB/s            
  Installing build dependencies ... done
  Getting requirements to build wheel ... done
  Preparing metadata (pyproject.toml) ... done
Collecting importlib-resources
  Using cached importlib_resources-5.4.0-py3-none-any.whl (28 kB)
Collecting zipp>=3.1.0
  Using cached zipp-3.6.0-py3-none-any.whl (5.3 kB)
Building wheels for collected packages: psycopg-c
  Building wheel for psycopg-c (pyproject.toml) ... error
  ERROR: Command errored out with exit status 1:
   command: /usr/bin/python3 /usr/local/lib/python3.6/site-packages/pip/_vendor/pep517/in_process/_in_process.py build_wheel /tmp/tmps_t972bv
       cwd: /tmp/pip-install-wzv5wgzf/psycopg-c_6775fda09fbd41d79b6ab223897198ee
  Complete output (65 lines):
  running bdist_wheel
  running build
  running build_py
  creating build
  creating build/lib.linux-x86_64-3.6
  creating build/lib.linux-x86_64-3.6/psycopg_c
  copying psycopg_c/__init__.py -> build/lib.linux-x86_64-3.6/psycopg_c
  copying psycopg_c/version.py -> build/lib.linux-x86_64-3.6/psycopg_c
  copying psycopg_c/py.typed -> build/lib.linux-x86_64-3.6/psycopg_c
  copying psycopg_c/_psycopg.pyi -> build/lib.linux-x86_64-3.6/psycopg_c
  copying psycopg_c/pq.pxd -> build/lib.linux-x86_64-3.6/psycopg_c
  creating build/lib.linux-x86_64-3.6/psycopg_c/_psycopg
  copying psycopg_c/_psycopg/__init__.pxd -> build/lib.linux-x86_64-3.6/psycopg_c/_psycopg
  copying psycopg_c/_psycopg/endian.pxd -> build/lib.linux-x86_64-3.6/psycopg_c/_psycopg
  copying psycopg_c/_psycopg/oids.pxd -> build/lib.linux-x86_64-3.6/psycopg_c/_psycopg
  creating build/lib.linux-x86_64-3.6/psycopg_c/pq
  copying psycopg_c/pq/__init__.pxd -> build/lib.linux-x86_64-3.6/psycopg_c/pq
  copying psycopg_c/pq/libpq.pxd -> build/lib.linux-x86_64-3.6/psycopg_c/pq
  running build_ext
  building 'psycopg_c._psycopg' extension
  creating build/temp.linux-x86_64-3.6
  creating build/temp.linux-x86_64-3.6/psycopg_c
  creating build/temp.linux-x86_64-3.6/psycopg_c/types
  gcc -pthread -Wno-unused-result -Wsign-compare -DNDEBUG -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -fPIC -I/usr/include -I/usr/include/python3.6m -c psycopg_c/_psycopg.c -o build/temp.linux-x86_64-3.6/psycopg_c/_psycopg.o
  gcc -pthread -Wno-unused-result -Wsign-compare -DNDEBUG -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -fPIC -I/usr/include -I/usr/include/python3.6m -c psycopg_c/types/numutils.c -o build/temp.linux-x86_64-3.6/psycopg_c/types/numutils.o
  gcc -pthread -shared -Wl,-z,relro -g build/temp.linux-x86_64-3.6/psycopg_c/_psycopg.o build/temp.linux-x86_64-3.6/psycopg_c/types/numutils.o -L/usr/lib64 -L/usr/lib64 -lpq -lpython3.6m -o build/lib.linux-x86_64-3.6/psycopg_c/_psycopg.cpython-36m-x86_64-linux-gnu.so
  building 'psycopg_c.pq' extension
  gcc -pthread -Wno-unused-result -Wsign-compare -DNDEBUG -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -fPIC -I/usr/include -I/usr/include/python3.6m -c psycopg_c/pq.c -o build/temp.linux-x86_64-3.6/psycopg_c/pq.o
  psycopg_c/pq.c: In function ‘__pyx_pf_9psycopg_c_2pq_6PGconn_4info___get__’:
  psycopg_c/pq.c:6581:3: warning: implicit declaration of function ‘PQconninfo’ [-Wimplicit-function-declaration]
     __pyx_v_opts = PQconninfo(__pyx_v_self->_pgconn_ptr);
     ^
  psycopg_c/pq.c:6581:16: warning: assignment makes pointer from integer without a cast [enabled by default]
     __pyx_v_opts = PQconninfo(__pyx_v_self->_pgconn_ptr);
                  ^
  psycopg_c/pq.c: In function ‘__pyx_pf_9psycopg_c_2pq_6PGconn_10ssl_in_use___get__’:
  psycopg_c/pq.c:8508:98: error: ‘PQsslInUse’ undeclared (first use in this function)
     __pyx_t_1 = __pyx_f_9psycopg_c_2pq__call_int(__pyx_v_self, ((__pyx_t_9psycopg_c_2pq_conn_int_f)PQsslInUse)); if (unlikely(__pyx_t_1 == ((int)-2))) __PYX_ERR(0, 205, __pyx_L1_error)
                                                                                                    ^
  psycopg_c/pq.c:8508:98: note: each undeclared identifier is reported only once for each function it appears in
  psycopg_c/pq.c: In function ‘__pyx_pf_9psycopg_c_2pq_6PGconn_68encrypt_password’:
  psycopg_c/pq.c:13960:15: warning: variable ‘__pyx_v_calgo’ set but not used [-Wunused-but-set-variable]
     char const *__pyx_v_calgo;
                 ^
  psycopg_c/pq.c: In function ‘__pyx_pf_9psycopg_c_2pq_6PGconn_15pipeline_status___get__’:
  psycopg_c/pq.c:14473:18: warning: assignment makes integer from pointer without a cast [enabled by default]
     __pyx_v_status = PQpipelineStatus(__pyx_v_self->_pgconn_ptr);
                    ^
  psycopg_c/pq.c: In function ‘__pyx_pf_9psycopg_c_2pq_6PGconn_72enter_pipeline_mode’:
  psycopg_c/pq.c:14656:64: warning: comparison between pointer and integer [enabled by default]
     __pyx_t_1 = ((PQenterPipelineMode(__pyx_v_self->_pgconn_ptr) != 1) != 0);
                                                                  ^
  psycopg_c/pq.c: In function ‘__pyx_pf_9psycopg_c_2pq_6PGconn_74exit_pipeline_mode’:
  psycopg_c/pq.c:14877:63: warning: comparison between pointer and integer [enabled by default]
     __pyx_t_1 = ((PQexitPipelineMode(__pyx_v_self->_pgconn_ptr) != 1) != 0);
                                                                 ^
  psycopg_c/pq.c: In function ‘__pyx_pf_9psycopg_c_2pq_6PGconn_76pipeline_sync’:
  psycopg_c/pq.c:15122:14: warning: assignment makes integer from pointer without a cast [enabled by default]
     __pyx_v_rv = PQpipelineSync(__pyx_v_self->_pgconn_ptr);
                ^
  psycopg_c/pq.c: In function ‘__pyx_pf_9psycopg_c_2pq_6PGconn_78send_flush_request’:
  psycopg_c/pq.c:15408:14: warning: assignment makes integer from pointer without a cast [enabled by default]
     __pyx_v_rv = PQsendFlushRequest(__pyx_v_self->_pgconn_ptr);
                ^
  error: command 'gcc' failed with exit status 1
  ----------------------------------------
  ERROR: Failed building wheel for psycopg-c
Failed to build psycopg-c
ERROR: Could not build wheels for psycopg-c, which is required to install pyproject.toml-based projects

1. pip install psycopg 这种方式能装上，但是运行脚本报错

# 安装
[root@proxy ~]# pip install psycopg
Collecting psycopg
  Using cached psycopg-3.0.3-py3-none-any.whl (140 kB)
Collecting typing-extensions
  Using cached typing_extensions-3.10.0.2-py3-none-any.whl (26 kB)
Collecting backports.zoneinfo
  Using cached backports.zoneinfo-0.2.1-cp36-cp36m-manylinux1_x86_64.whl (70 kB)
Collecting importlib-resources
  Using cached importlib_resources-5.4.0-py3-none-any.whl (28 kB)
Collecting zipp>=3.1.0
  Using cached zipp-3.6.0-py3-none-any.whl (5.3 kB)
Installing collected packages: zipp, importlib-resources, typing-extensions, backports.zoneinfo, psycopg
Successfully installed backports.zoneinfo-0.2.1 importlib-resources-5.4.0 psycopg-3.0.3 typing-extensions-3.10.0.2 zipp-3.6.0
WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv

# 运行脚本
[root@proxy ~]# cat py_to_pg.py 
# Note: the module name is psycopg, not psycopg3
import psycopg

# Connect to an existing database
with psycopg.connect("host=192.168.58.10 port=5432 database=postgres user=postgres password=postgres") as conn:

    # Open a cursor to perform database operations
    with conn.cursor() as cur:

        # Query the database and obtain data as Python objects.
        cur.execute("select inet_server_addr(),pg_is_in_recovery(),current_database(),current_user")
        rows = cur.fetchone()
        
        # You can use `cur.fetchmany()`, `cur.fetchall()` to return a list of several records, or even iterate on the cursor
        for row in rows:
            print('inet_server_addr: ' + str(row[0]))
            print('pg_is_in_recovery: ' + str(row[1]))
            print('current_database: ' + row[2])
            print('current_user: ' + row[3])

[root@proxy ~]# /usr/bin/python3 py_to_pg.py
Traceback (most recent call last):
  File "py_to_pg.py", line 2, in <module>
    import psycopg
  File "/usr/local/lib/python3.6/site-packages/psycopg/__init__.py", line 9, in <module>
    from . import pq  # noqa: F401 import early to stabilize side effects
  File "/usr/local/lib/python3.6/site-packages/psycopg/pq/__init__.py", line 114, in <module>
    import_from_libpq()
  File "/usr/local/lib/python3.6/site-packages/psycopg/pq/__init__.py", line 110, in import_from_libpq
    {sattempts}"""
ImportError: no pq wrapper available.
Attempts made:
- couldn't import psycopg 'c' implementation: No module named 'psycopg_c'
- couldn't import psycopg 'binary' implementation: No module named 'psycopg_binary'
- couldn't import psycopg 'python' implementation: /lib64/libpq.so.5: undefined symbol: PQconninfo