PostgreSQL的客戶端接口

在PostgreSQL發(fā)行版中只包含兩個(gè)客戶端接口: 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.

其他語(yǔ)言客戶端接口：

Python 語(yǔ)言連接PostgreSQL數(shù)據(jù)庫(kù)

??之前研究 PostgreSQL 高可用 Patroni + etcd，其中 Patroni 就是基于Python語(yǔ)言編寫的，同時(shí)用到了 Psycopg 連接 PostgreSQL 數(shù)據(jù)庫(kù)，關(guān)于 Patroni 和高可用請(qǐng)參考：
??PostgreSQL高可用測(cè)試系列之Patroni + etcd + HAProxy + Keepalived 離線部署（四）

??Python 官網(wǎng)： https://www.python.org/ Python 文檔和源碼都可以在這里找到，當(dāng)前最新版本 Python 3.10.0。
??Psycopg 官網(wǎng)： https://www.psycopg.org/ Psycopg 文檔和源碼都可以在這里找到。

Python3 和 Psycopg 的安裝

??Python3 的安裝，這里不重述了，請(qǐng)參考上文 高可用之Patroni，建議聯(lián)網(wǎng)安裝(yum install python3)或下載我分享的安裝包，但是版本不是最新的(3.6.8)，如果需要使用新版本的 Python，還得下載源碼編譯安裝。但是使用源碼編譯安裝過(guò)程很漫長(zhǎng)。
Psycopg 現(xiàn)在有兩個(gè)大版本，Psycopg2 和 Psycopg3，Psycopg2 還有兩種類型的包(psycopg2 和 psycopg2-binary)，其中 psycopg2-binary 相當(dāng)于綠色版，不需要編譯，是為了初學(xué)者用 Python 快速和 PostgreSQL 進(jìn)行連接而用的，對(duì)于生產(chǎn)環(huán)境，建議編譯安裝使用psycopg2。

psycopg2，常用

編譯安裝psycopg2有幾個(gè)先決條件：

• 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
# 導(dǎo)入 psycopg2 包
import psycopg2
# 連接到一個(gè)給定的數(shù)據(jù)庫(kù)
conn = psycopg2.connect(host="192.168.58.10", port="5432", database="postgres", user="postgres", password="postgres", )
# 建立游標(biāo)，用來(lái)執(zhí)行數(shù)據(jù)庫(kù)操作
cur = conn.cursor()
# 執(zhí)行 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])
# 關(guān)閉游標(biāo)
cur.close()
# 關(guān)閉數(shù)據(jù)庫(kù)連接
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，新的，可能還不成熟，暫時(shí)不好用，沒(méi)測(cè)通

官方文檔： https://www.psycopg.org/psycopg3/docs/
Psycopg 3 是為Python 編程語(yǔ)言新設(shè)計(jì)的PostgreSQL數(shù)據(jù)庫(kù)適配器。

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 這種方式能裝上，但是運(yùn)行腳本報(bào)錯(cuò)

# 安裝
[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

# 運(yùn)行腳本
[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