import psycopg2
# 連接參數(shù)
dbname = "your_database_name"  # 數(shù)據(jù)庫名
user = "your_username"        # 用戶名
password = "your_password"    # 密碼
host = "your_host_address"    # 數(shù)據(jù)庫服務(wù)器地址，如 'localhost' 或 IP
port = "your_port"            # 端口號，默認(rèn)通常是 54321 或 5432 (取決于KingbaseES配置)
try:
    # 建立連接
    connection = psycopg2.connect(
        dbname=dbname,
        user=user,
        password=password,
        host=host,
        port=port
    )
    print("成功連接到 KingbaseES 數(shù)據(jù)庫！")
    # 創(chuàng)建一個(gè)游標(biāo)對象來執(zhí)行 SQL 語句
    cursor = connection.cursor()
    # ... 后續(xù)操作 (查詢、插入等) ...
except (Exception, psycopg2.Error) as error:
    print("連接或操作過程中發(fā)生錯(cuò)誤:", error)
finally:
    # 確保在結(jié)束時(shí)關(guān)閉連接和游標(biāo)
    if connection:
        cursor.close()
        connection.close()
        print("數(shù)據(jù)庫連接已關(guān)閉。")

# 假設(shè)游標(biāo) cursor 已經(jīng)創(chuàng)建
try:
    # 執(zhí)行 SQL 查詢語句
    cursor.execute("SELECT version();")  # 查詢數(shù)據(jù)庫版本信息
    # 獲取結(jié)果
    db_version = cursor.fetchone()  # 獲取單行結(jié)果
    print("KingbaseES 版本信息:", db_version)
    # 查詢多行數(shù)據(jù)示例
    cursor.execute("SELECT id, name FROM your_table WHERE status = %s;", ('active',))
    records = cursor.fetchall()  # 獲取所有結(jié)果行
    print("查詢結(jié)果:")
    for row in records:
        print(f"ID: {row[0]}, Name: {row[1]}")
except (Exception, psycopg2.Error) as error:
    print("查詢執(zhí)行錯(cuò)誤:", error)

try:
    # 插入數(shù)據(jù)
    insert_query = """INSERT INTO your_table (id, name, email)
                      VALUES (%s, %s, %s);"""
    record_to_insert = (101, '張三', 'zhangsan@example.com')
    cursor.execute(insert_query, record_to_insert)
    # 更新數(shù)據(jù)
    update_query = """UPDATE your_table SET name = %s WHERE id = %s;"""
    cursor.execute(update_query, ("李四", 101))
    # 刪除數(shù)據(jù)
    delete_query = """DELETE FROM your_table WHERE id = %s;"""
    cursor.execute(delete_query, (102,))
    # 提交事務(wù)！非常重要，否則更改不會(huì)持久化到數(shù)據(jù)庫
    connection.commit()
    print("事務(wù)已提交。")
except (Exception, psycopg2.Error) as error:
    # 如果發(fā)生錯(cuò)誤，回滾事務(wù)
    if connection:
        connection.rollback()
    print("操作執(zhí)行錯(cuò)誤:", error)

from psycopg2 import pool
# 創(chuàng)建連接池 (這里使用簡單的 ThreadedConnectionPool)
min_conn = 1
max_conn = 5
connection_pool = pool.ThreadedConnectionPool(min_conn, max_conn,
                                             dbname=dbname,
                                             user=user,
                                             password=password,
                                             host=host,
                                             port=port)
try:
    # 從連接池獲取一個(gè)連接
    connection = connection_pool.getconn()
    cursor = connection.cursor()
    cursor.execute("SELECT ...")
    # ... 執(zhí)行操作 ...
    connection.commit()
except (Exception, psycopg2.Error) as error:
    print("錯(cuò)誤:", error)
finally:
    # 將連接放回連接池
    if connection:
        cursor.close()
        connection_pool.putconn(connection)

# 寫入大對象 (例如圖片)
with open('image.jpg', 'rb') as f:
    data = f.read()
    # 創(chuàng)建大對象并獲取其 OID
    cursor.execute("INSERT INTO your_blob_table (file_name) VALUES (%s) RETURNING file_oid;", ('image.jpg',))
    file_oid = cursor.fetchone()[0]
    # 打開大對象進(jìn)行寫入
    lobject = connection.lobject(file_oid, 'wb')  # 'wb' 表示寫入二進(jìn)制模式
    lobject.write(data)
    lobject.close()
    connection.commit()
# 讀取大對象
cursor.execute("SELECT file_oid FROM your_blob_table WHERE file_name = %s;", ('image.jpg',))
file_oid = cursor.fetchone()[0]
lobject = connection.lobject(file_oid, 'rb')  # 'rb' 表示讀取二進(jìn)制模式
data = lobject.read()
lobject.close()
with open('retrieved_image.jpg', 'wb') as f:
    f.write(data)