python通过JayDeBeApi使用JDBC链接postgres

环境准备

获取JayDeBeApi
方法一：

pip install JayDeBeApi

方法二：

[admin@localhost ~]$ git clone https://github.com/baztian/jaydebeapi.git
Cloning into 'jaydebeapi'...
remote: Enumerating objects: 1710, done.
remote: Counting objects: 100% (123/123), done.
remote: Compressing objects: 100% (70/70), done.
remote: Total 1710 (delta 48), reused 90 (delta 27), pack-reused 1587
Receiving objects: 100% (1710/1710), 427.56 KiB | 452.00 KiB/s, done.
Resolving deltas: 100% (919/919), done.
[root@localhost jaydebeapi]# python3 setup.py install
running install
running bdist_egg
running egg_info
creating JayDeBeApi.egg-info
writing JayDeBeApi.egg-info/PKG-INFO
......

获取postgres JDBC驱动
https://jdbc.postgresql.org/download.html
我这里使用的是最新版：PostgreSQL JDBC 4.2 Driver, 42.3.3

测试数据

postgres=# \d
                  List of relations
 Schema |          Name           | Type  |  Owner
--------+-------------------------+-------+----------
 public | pg_stat_statements      | view  | postgres
 public | pg_stat_statements_info | view  | postgres
 public | student                 | table | postgres
 public | test_table              | table | postgres
(4 rows)

postgres=# select * from student;
 id |  name   | sex
----+---------+-----
  1 | Aspirin | M
  2 | Taxol   | F
(2 rows)

测试验证

[postgres@localhost ~]$ python3.6
Python 3.6.13 (default, Feb 18 2022, 03:06:10)
[GCC 7.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import jaydebeapi
>>> url = 'jdbc:postgresql://127.0.0.1:5432/postgres'
>>> user = 'postgres'
>>> password = 'xk.xmx190035'
>>> driver = 'org.postgresql.Driver'
>>> jarFile = './postgresql-42.3.3.jar'
>>> sqlStr = 'select * from student'
>>> conn = jaydebeapi.connect(driver, url, [user, password], jarFile)
>>> curs = conn.cursor()
>>> curs.execute(sqlStr)
>>> result = curs.fetchall()
>>> print(result)
[(1, 'Aspirin', 'M'), (2, 'Taxol', 'F')]