• 使用Psycopg2连接openGauss

    文章目录

    1.简介

    Psycopg是一种用于执行SQL语句的PythonAPI，可以为PostgreSQL、openGauss数据库提供统一访问接口，应用程序可基于它进行数据操作。Psycopg2是对libpq的封装，主要使用C语言实现，既高效又安全。它具有客户端游标和服务器端游标、异步通信和通知、支持“COPY TO/COPY FROM”功能。支持多种类型Python开箱即用，适配PostgreSQL数据类型；通过灵活的对象适配系统，可以扩展和定制适配。Psycopg2兼容Unicode和Python 3。
    openGauss数据库提供了对Psycopg2特性的支持，并且支持Psycopg2通过SSL模式链接。

    2.接口介绍

    开发流程

    在这里插入图片描述

    接口说明

    openGauss提供了如下接口供开发者使用：

    • psycopg2.connect()
      此方法创建新的数据库会话并返回新的connection对象（连接openGauss数据库实例的对象）。

    conn=psycopg2.connect(dbname=“test”,user=“postgres”,password=“secret”,host=“127.0.0.1”,port=“5432”)

    或者

    conn = psycopg2.connect(“dbname=test user=postgres password=secret
    host=127.0.0.1 port=5432”)

    创建连接对象（SSl连接）

    conn = psycopg2.connect(dbname=“postgres”, user=“user”, password=“password”, host=“localhost”, port=port, sslmode=“verify-ca”, sslcert=“client.crt”, sslkey=“client.key”, sslrootcert=“cacert.pem”)

    注意： 如果sslcert, sslkey,sslrootcert没有填写，默认取当前用户.postgresql目录下对应的client.crt， client.key， root.crt

    • connection.cursor()
      此方法用于返回新的cursor对象（用于整个数据库使用Python编程的cursor）。

    cursor(name=None, cursor_factory=None, scrollable=None,
    withhold=False)

    • cursor.execute(query,vars_list)
      此方法执行被参数化的SQL语句（即占位符，而不是SQL文字）。psycopg2模块支持用％s标志的占位符。

    curosr.execute(query,vars_list)

    • curosr.executemany(query,vars_list)
      此方法执行SQL命令所有参数序列或序列中的SQL映射。

    curosr.executemany(query,vars_list)

    • connection.commit()
      此方法将当前挂起的事务提交到数据库。默认情况下，Psycopg在执行第一个命令之前打开一个事务：如果不调用commit()，任何数据操作的效果都将丢失。

    connection.commit()

    • connection.rollback()
      此方法回滚当前挂起事务。执行关闭连接“close()”而不先提交更改“commit()”将导致执行隐式回滚。

    connection.rollback()

    • cursor.fetchone()
      此方法提取查询结果集的下一行，并返回一个元组。返回单个元组，为结果集的第一条结果，当没有更多数据可用时，返回为“None”。

    cursor.fetchone()

    • cursor.fetchall()
      此方法获取查询结果的所有（剩余）行，并将它们作为元组列表返回。返回元组列表，为结果集的所有结果。空行时则返回空列表。

    cursor.fetchall()

    • cursor.close()
      此方法关闭当前连接的游标。

    cursor.close()

    • connection.close()
      此方法关闭数据库连接。此方法关闭数据库连接，并不自动调用commit()。如果只是关闭数据库连接而不调用commit()方法，那么所有更改将会丢失。

    connection.close()

    3.使用

    3.1环境准备

    本篇使用环境信息：

    • 华为云HECS 2核4G
    • CentOS Linux release 7.6
    • Python 3.6.8
    • openGauss 3.1.0 极简版

    3.2下载并加载python驱动

    1、可以在openGauss官网下载后用FTP工具比如winscp等上传到服务器，也可以直接在服务器上用wget方式获取，根据操作系统版本下载对应的驱动。
    在这里插入图片描述

    root用户下新建存放目录，执行wget和解压命令。

    mkdir psycopg2
wget https://opengauss.obs.cn-south-1.myhuaweicloud.com/3.1.0/x86/openGauss-3.1.0-CentOS-x86_64-Python.tar.gz
tar -zxvf openGauss-3.1.0-CentOS-x86_64-Python.tar.gz

    • 1
    • 2
    • 3

    在这里插入图片描述

    解压后有两个文件夹 lib和psycopg2，分别放置对应的库文件。
    2、在解压后的路径下执行拷贝命令，将驱动拷贝到python3下的site-packages目录

    cp -r psycopg2/ /usr/lib/python3.6/site-packages/


    • 1
    • 2

    3、修改psycopg2/目录权限为755

    cd /usr/lib/python3.6/site-packages/    
chmod 755 psycopg2/

    • 1
    • 2

    4、对于非数据库用户，需要将解压后的lib目录，配置在LD_LIBRARY_PATH中。

    export $LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/path/to/psycopg2/lib/
ldconfig


    • 1
    • 2
    • 3

    3.3创建数据库连接用户

    注意，由于psycopg2只能使用MD5方式连接，而openGauss默认安装时使用sha256加密，所以这里需要修改一下配置。
    修改 data/single_node/postgresql.conf 中password_encryption_type = 1 ，表示支持md5和sha256。
    修改pg_hba.conf 中加密算法
    在这里插入图片描述

    然后重启openGauss：gs_ctl restart -D /opt/software/openGauss/data/single_node
    连接到openGauss创建用户和数据库。

    create database mydb;
CREATE USER tuser WITH PASSWORD '自己定义';
GRANT ALL PRIVILEGES TO tuser;
alter database mydb owner to tuser;

    • 1
    • 2
    • 3
    • 4

    如果在修改加密方式前之前已经创建过用户了，需要在配置文件修改及数据库重启完成后，重新建用户或者设置用户密码。

    3.4示例

    编写python文件

    vi ogconn.py

    • 1

    将如下测试语句拷贝进去，根据实际情况修改对应的openGauss数据库连接信息。

    import psycopg2
conn=psycopg2.connect(database="mydb",user="tuser",password="XXXXXXX",host="127.0.0.1",port=5432)
#或者用下面的也可以
#conn = psycopg2.connect("dbname=mydb user=tuser password=password host=localhost port=port")
print("Conn openGauss successfully")
cur=conn.cursor()
cur.execute("DROP TABLE IF EXISTS student")
cur.execute("CREATE TABLE student(id integer,name varchar,sex varchar);")
cur.execute("INSERT INTO student(id,name,sex) VALUES(%s,%s,%s)",(1,'Bob','M'))
cur.execute("INSERT INTO student(id,name,sex) VALUES(%s,%s,%s)",(2,'Kevin','M'))
cur.execute('SELECT id,name,sex FROM student')
results=cur.fetchall()
print (results)
conn.commit()
cur.close()
conn.close()

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    连接测试

    python3 ogconn.py

    • 1

    报错了,psycopg2.OperationalError: SCRAM authentication requires libpq version 10 or above

    解决办法：
    根据提示升级版本

    rpm -Uvh https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

yum install postgresql10-devel

    • 1
    • 2
    • 3

    根据提示输入y,等提示Complete!
    重新测试连接
    在这里插入图片描述

    注意，如果使用本地工具连接远端云服务器的数据库时，需要修改云服务器安全组，将openGauss的端口放开，否则会连接不上。

    4.常见报错

    执行python3 ogconn.py报错
    1.提示如下错误
    File “/opt/software/psycopg2/psycopg2/init.py”, line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
    psycopg2.OperationalError: SCRAM authentication requires libpq version 10 or above

    这个错是说libpq版本应该在10以上，需要升级下版本。
    解决办法：

    rpm -Uvh https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

yum install postgresql10-devel

    • 1
    • 2
    • 3

    根据提示输入y,等提示Complete!

    2.提示如下错误：
    psycopg2.OperationalError: none of the server’s SASL authentication mechanisms are supported
    这个就是加密方式的问题，参考文章中“创建数据库连接用户”部分解决。

    欢迎大家测试、交流！

    🍒如果您觉得博主的文章还不错或者有帮助的话，请关注一下博主，如果三连点赞评论收藏就更好啦！谢谢各位大佬给予的支持！

  • 相关阅读:
    Azkaban
    php代码审计篇熊海cms代码审计
    十四天学会C++之第三天（数组和字符串）
    加入自定义函数共享output数组功能
    Day17_10 JavaWeb基础之IDEA、JavaWeb项目部署
    heic格式图片怎么转换jpg?
    vue实现搜索关键词高亮
    java计算机毕业设计政府资源交易中心业务流程管理系统设计与实现源码+数据库+系统+部署+lw文档
    第四十九周总结——初遇http
    Flink报错处理-1
  • 原文地址：https://blog.csdn.net/GaussDB/article/details/128091194