教你使用Psycopg2連線openGauss的方法

2022-12-01 14:02:46

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

解壓後有兩個資料夾 lib和psycopg2，分別放置對應的庫檔案。
2、在解壓後的路徑下執行拷貝命令，將驅動拷貝到python3下的site-packages目錄

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

3、修改psycopg2/目錄許可權為755

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

4、對於非資料庫使用者，需要將解壓後的lib目錄，設定在LD_LIBRARY_PATH中。

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

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;

如果在修改加密方式前之前已經建立過使用者了，需要在組態檔修改及資料庫重啟完成後，重新建使用者或者設定使用者密碼。

3.4範例

編寫python檔案

vi ogconn.py

將如下測試語句拷貝進去，根據實際情況修改對應的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()

連線測試

python3 ogconn.py

報錯了,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

根據提示輸入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

根據提示輸入y,等提示Complete!

2.提示如下錯誤：
psycopg2.OperationalError: none of the server’s SASL authentication mechanisms are supported
這個就是加密方式的問題，參考文章中“建立資料庫連線使用者”部分解決。

到此這篇關於教你使用Psycopg2連線openGauss的方法的文章就介紹到這了,更多相關Psycopg2連線openGauss內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！