使用python语言连接postgresql数据库 -m6米乐安卓版下载

在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.

其他语言客户端接口：

  之前研究 postgresql 高可用 patroni etcd，其中 patroni 就是基于python语言编写的，同时用到了 psycopg 连接 postgresql 数据库，关于 patroni 和高可用请参考：
  postgresql高可用测试系列之patroni etcd haproxy keepalived 离线部署（四）

  python m6米乐安卓版下载官网： python 文档和源码都可以在这里找到，当前最新版本 python 3.10.0。
  psycopg m6米乐安卓版下载官网： 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 
                    ^
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，新的，可能还不成熟，暂时不好用，没测通

官方文档：
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 
    import psycopg
  file "/usr/local/lib/python3.6/site-packages/psycopg/__init__.py", line 9, in 
    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 
    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