1

使用python语言连接postgresql数据库 -利来国际app

张玉龙 2021-11-11
254

在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
jdbc java type 4 jdbc driver
libpqxx c c interface
node-postgres javascript node.js driver
npgsql .net .net data provider
pgtcl tcl -
pgtclng tcl -
pq go pure go driver for go’s database/sql
psqlodbc odbc odbc driver
psycopg python db api 2.0-compliant

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

  python 利来国际app官网: python 文档和源码都可以在这里找到,当前最新版本 python 3.10.0。
  psycopg 利来国际app官网: 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.

[[email protected] ~]# yum -y install  gcc gcc-c   make 
[[email protected] ~]# pip3 install psycopg2
error: pg_config executable not found.
[[email protected] ~]# yum install -y postgresql-devel
./psycopg/psycopg.h:35:20: fatal error: python.h: no such file or directory
 #include 
                    ^
compilation terminated.
[[email protected] ~]# yum install -y python36-devel
[[email protected] ~]# 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
[[email protected] ~]# 
[[email protected] ~]# 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()
[[email protected] ~]# /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] 这种方式装不上
[[email protected] ~]# pip3 --version
pip 9.0.3 from /usr/lib/python3.6/site-packages (python 3.6)
[[email protected] ~]# pip3 install --upgrade pip    # upgrade pip to at least 20.3
successfully installed pip-21.3.1
[[email protected] ~]# 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
[[email protected] ~]# 
  1. pip install psycopg[c] 这种方式也装不上
[[email protected] ~]# 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 这种方式能装上,但是运行脚本报错
# 安装
[[email protected] ~]# 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
# 运行脚本
[[email protected] ~]# 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])
[[email protected] ~]# /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
最后修改时间:2021-11-25 15:19:27
「喜欢文章,快来给作者赞赏墨值吧」
【利来手机国际的版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:[email protected]进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论