Python驱动连接GBase8c

python3驱动包：openGauss-5.0.2-CentOS-x86_64-Python.tar.gz
python2驱动源码包：psycopg2-2.7.7.tar.gz
python3版本：python3.6
python2版本：python2.7.5

1、使用 python3 连接数据库

1.1.上传驱动包到服务器并解压

驱动包openGauss-5.0.2-CentOS-x86_64-Python.tar.gz

tar -zxvf openGauss-5.0.2-CentOS-x86_64-Python.tar.gz

1.2.配置 psycopg2

将 psycopg2 拷贝到/usr/lib/python3.6/site-packages 目录下

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

配置在 LD_LIBRARY_PATH 环境变量中

# Python psycopg2
export LD_LIBRARY_PATH=/home/gbase/python-psycopg2/lib:$LD_LIBRARY_PATH

配置完成。

1.3.使用 python3脚本连接数据库

编写一个Connect_test.py 测试脚本，写入以下内容，连接信息根据实际情况修改

import psycopg2
from psycopg2 import OperationalError

def create_connection(db_name, db_user, db_password, db_host, db_port):
    connection = None
    try:
        connection = psycopg2.connect(
            database=db_name,
            user=db_user,
            password=db_password,
            host=db_host,
            port=db_port,
        )
        print("Connection to PostgreSQL DB successful")
    except OperationalError as e:
        print(f"The error '{e}' occurred")
    return connection

def execute_query(connection, query):
    connection.autocommit = True
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        print("Query executed successfully")
    except OperationalError as e:
        print(f"The error '{e}' occurred")

def execute_read_query(connection, query):
    cursor = connection.cursor()
    result = None
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except OperationalError as e:
        print(f"The error '{e}' occurred")

# Replace actual database credentials
connection = create_connection(
    "postgres",
    "gbase8c",
    "Database@123",
    "192.168.123.111",
    "15400"
)

# Drop table if exists
drop_table_query = "DROP TABLE IF EXISTS users;"
execute_query(connection, drop_table_query)

# Create table
create_table_query = """
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  age INT,
  gender TEXT,
  nationality TEXT
);
"""
execute_query(connection, create_table_query)

# Insert data
insert_user_query = """
INSERT INTO
  users (name, age, gender, nationality)
VALUES
  ('James', 25, 'male', 'USA'),
  ('Leila', 32, 'female', 'France');
"""
execute_query(connection, insert_user_query)

# Read data
select_users_query = "SELECT * from users"
users = execute_read_query(connection, select_users_query)
for user in users:
    print(user)

# Update data
update_user_query = """
UPDATE
  users
SET
  age = 26
WHERE
  name = 'James'
"""
execute_query(connection, update_user_query)

# Delete data
delete_user_query = "DELETE FROM users WHERE name = 'Leila'"
execute_query(connection, delete_user_query)

# Read data again
users = execute_read_query(connection, select_users_query)
for user in users:
    print(user)

运行python脚本

python3 Connect_test.py

2、使用 python2 连接数据库

2.1.安装编译源码包所需依赖包

yum install postgresql-devel -y

2.1.上传驱动源码包到服务器并解压

驱动包：psycopg2-2.7.7.tar.gz

tar -zxvf psycopg2-2.7.7.tar.gz

2.2. 编译并安装 psycopg2

python2 setup.py build
python2 setup.py install