用python连接opengauss数据库 - l1t1/note GitHub Wiki
- 使用docker安装opengauss数据库
因为我用的是Windows的WSL，enmotech-docker-opengauss github官网指出5.0以后版本enmotech/opengauss镜像无法在windows运行, 所以用如下命令拉取并运行enmotech/opengauss-lite:latest镜像, 指定容器名opengauss。
root@DESKTOP-59T6U68:/mnt/c/Users/lt# docker run --cgroup-manager=cgroupfs --events-backend=file --net=host -it --name opengauss -v/mnt/c/d:/par --privileged=true -d -e GS_PASSWORD=Enmo@123 -u root docker.1ms.run/enmotech/opengauss-lite:latest
检查后台进程正在运行
root@DESKTOP-59T6U68:/mnt/c/Users/lt# docker ps -a|grep opengauss
2f301e838076 docker.1ms.run/enmotech/opengauss-lite:latest gaussdb 29 seconds ago Up 29 seconds ago opengauss
登录到容器, 切换为omm用户访问数据库
root@DESKTOP-59T6U68:/mnt/c/Users/lt# docker exec -it opengauss /bin/bash
root@DESKTOP-59T6U68:/# su - omm
omm@DESKTOP-59T6U68:~$ gsql
gsql ((openGauss-lite 5.0.3 build 89d144c2) compiled at 2024-07-31 21:37:42 commit 0 last mr release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=# create table t as select 1 a;
INSERT 0 1
omm=# COPY t to '/tmp/t2022.bin' WITH binary;
COPY 1
omm=# \q
以上默认安装的数据库无法用通常的用户名口令方式连接，比如用duckdb的postgresql库连接报如下错误
D load postgres;
D ATTACH 'dbname=omm user=omm host=127.0.0.1 password=Enmo@123' AS db (TYPE postgres, READ_ONLY);
IO Error:
Unable to connect to Postgres at dbname=omm user=omm host=127.0.0.1 password=Enmo@123: connection to server at "127.0.0.1", port 5432 failed: none of the server's SASL authentication mechanisms are supported
修改认证方式
修改配置文件/var/lib/opengauss/data/postgresql.conf，将password_encryption_type修改为1，同时支持sha256和md5。
重启数据库服务，我采取重新启动容器的方式
docker stop opengauss
docker start opengauss
在gsql中更新用户口令, 验证用新口令可以登录
openGauss=# ALTER USER omm WITH PASSWORD 'Abcd@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
openGauss=# \q
omm@DESKTOP-59T6U68:~$ gsql -h 127.0.0.1 -U omm -W Enmo@123 -d omm
gsql: (connect to V5 server) FATAL: Invalid username/password,login denied.
(connect to V1 server) FATAL: Invalid username/password,login denied.
omm@DESKTOP-59T6U68:~$ gsql -h 127.0.0.1 -U omm -W Abcd@123 -d omm
2.安装并导入postgresql库
设置镜像地址
pip config set global.index-url https://mirrors.aliyun.com/pypi/simple
pip config set install.trusted-host mirrors.aliyun.com
pip install py-postgresql
在python命令行中输入
import postgresql
db = postgresql.open('pq://omm:Abcd@[email protected]:5432/omm')
报错，
postgresql.exceptions.ClientCannotConnectError: could not establish connection to server
CODE: 08001
LOCATION: CLIENT
CONNECTION: [failed]
CONNECTOR: [Host] pq://omm:***@[email protected]:5432/omm
category: None
DRIVER: postgresql.driver.pq3.Driver
这是因为密码中包含特殊字符@, 需要转义为%40，其他特殊字符的转义值参考如下：
! : %21
@ : %40
# : %23
$ : %24
% : %25
^ : %5e
& : %26
* : %2a
( : %28
) : %29
_ : %5f
+ : %2b
= : %3d
修改连接字符串后，就可以连接了，测试访问表、函数正常：
>>> db = postgresql.open('pq://omm:Abcd%[email protected]:5432/omm')
>>> get_table = db.prepare("SELECT * from t")
>>> print(get_table())
[(1,)]
>>> type(get_table())
<class 'list'>
>>> get_table = db.prepare("SELECT * from generate_series(1,10,2)")
>>> print(get_table())
[(1,), (3,), (5,), (7,), (9,)]
使用clickhouse连接open gaussdb很简单：
omm@DESKTOP-59T6U68:/par$ ch/clickhouse
ClickHouse local version 25.1.1.1731 (official build).
:) SELECT * FROM postgresql('127.0.0.1', 'omm', 't', 'omm', 'Abcd@123');
SELECT *
FROM postgresql('127.0.0.1', 'omm', 't', 'omm', 'Abcd@123')
Query id: d7d11df2-58e7-4a1a-941e-8a43829ca7a9
┌─a─┬─b─┐
1. │ 1 │ a │
2. │ 2 │ b │
└───┴───┘
2 rows in set. Elapsed: 0.116 sec.