# 创建批量操作脚本
cat > /kingbase/batch_operation_platform/batch_operation.py << 'EOF'
#!/usr/bin/env python3
# batch_operation.py
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
import psycopg2
import subprocess
import config
# 连接数据库
def connect_db():
conn = psycopg2.connect(
host=config.DB_HOST,
port=config.DB_PORT,
database=config.DB_NAME,
user=config.DB_USER,
password=config.DB_PASSWORD
)
return conn
# 执行SQL命令
def execute_sql(host, port, database, user, password, sql):
cmd = f”psql -h {host} -p {port} -U {user} -d {database} -c ‘{sql}'”
env = {‘PGPASSWORD’: password}
result = subprocess.run(cmd, shell=True, env=env, capture_output=True, text=True)
return result.returncode, result.stdout, result.stderr
# 批量执行SQL命令
def batch_execute_sql(sql):
conn = connect_db()
cursor = conn.cursor()
sql = “SELECT host, port, database, user, password FROM instances WHERE status = ‘active'”
cursor.execute(sql)
instances = cursor.fetchall()
cursor.close()
conn.close()
results = []
for instance in instances:
host, port, database, user, password = instance
print(f”执行SQL命令到 {host}:{port}/{database}”)
returncode, stdout, stderr = execute_sql(host, port, database, user, password, sql)
results.append({
‘host’: host,
‘port’: port,
‘database’: database,
‘returncode’: returncode,
‘stdout’: stdout,
‘stderr’: stderr
})
# 打印执行结果
print(“\n执行结果：”)
print(“Host | Port | Database | Returncode | Status”)
print(“—————|——-|————|———–|——–“)
for result in results:
status = “成功” if result[‘returncode’] == 0 else “失败”
print(f”{result[‘host’]:13} | {result[‘port’]:5} | {result[‘database’]:10} | {result[‘returncode’]:9} | {status:6}”)
if result[‘stderr’]:
print(f”错误信息: {result[‘stderr’]}”)
# 批量备份数据库
def batch_backup(output_dir):
conn = connect_db()
cursor = conn.cursor()
sql = “SELECT host, port, database, user, password FROM instances WHERE status = ‘active'”
cursor.execute(sql)
instances = cursor.fetchall()
cursor.close()
conn.close()
results = []
for instance in instances:
host, port, database, user, password = instance
backup_file = f”{output_dir}/{host}_{port}_{database}_backup.sql”
print(f”备份数据库 {host}:{port}/{database} 到 {backup_file}”)
cmd = f”pg_dump -h {host} -p {port} -U {user} -d {database} -f {backup_file}”
env = {‘PGPASSWORD’: password}
result = subprocess.run(cmd, shell=True, env=env, capture_output=True, text=True)
results.append({
‘host’: host,
‘port’: port,
‘database’: database,
‘backup_file’: backup_file,
‘returncode’: result.returncode,
‘stderr’: result.stderr
})
# 打印执行结果
print(“\n执行结果：”)
print(“Host | Port | Database | Returncode | Status”)
print(“—————|——-|————|———–|——–“)
for result in results:
status = “成功” if result[‘returncode’] == 0 else “失败”
print(f”{result[‘host’]:13} | {result[‘port’]:5} | {result[‘database’]:10} | {result[‘returncode’]:9} | {status:6}”)
if result[‘stderr’]:
print(f”错误信息: {result[‘stderr’]}”)
if __name__ == “__main__”:
# 创建操作表
conn = connect_db()
cursor = conn.cursor()
sql = “””
CREATE TABLE IF NOT EXISTS operations (
id SERIAL PRIMARY KEY,
type VARCHAR(50) NOT NULL,
sql TEXT,
output_dir VARCHAR(255),
status VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
completed_at TIMESTAMP
)
“””
cursor.execute(sql)
conn.commit()
cursor.close()
conn.close()
print(“操作表创建成功”)
EOF