暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片

使用Python语言连接PostgreSQL数据库

原创 张玉龙 2021-11-11
5327

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.

其他语言客户端接口:

Name Language Comments Website
DBD::Pg Perl Perl DBI driver https://metacpan.org/release/DBD-Pg
JDBC Java Type 4 JDBC driver https://jdbc.postgresql.org/
libpqxx C++ C++ interface https://pqxx.org/
node-postgres JavaScript Node.js driver https://node-postgres.com/
Npgsql .NET .NET data provider https://www.npgsql.org/
pgtcl Tcl - https://github.com/flightaware/Pgtcl
pgtclng Tcl - https://sourceforge.net/projects/pgtclng/
pq Go Pure Go driver for Go’s database/sql https://github.com/lib/pq
psqlODBC ODBC ODBC driver https://odbc.postgresql.org/
psycopg Python DB API 2.0-compliant https://www.psycopg.org/

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
最后修改时间:2021-11-25 15:19:27
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论