Oracle导出所有的表数据

import os
import cx_Oracle
import csv

# 从环境变量中读取数据库连接信息
db_host = os.environ.get('DB_HOST', 'localhost')
db_port = os.environ.get('DB_PORT', '1521')
db_name = os.environ.get('DB_NAME', 'ORCL')
db_user = os.environ.get('DB_USER', 'user')
db_password = os.environ.get('DB_PASSWORD', 'password')

# 数据库连接参数
dsn = cx_Oracle.makedsn(db_host, db_port, service_name=db_name)

# 创建保存文件的目录
save_dir = '你的路径'

if not os.path.exists(save_dir):
    os.makedirs(save_dir)

# 连接数据库
conn = cx_Oracle.connect(user=db_user, password=db_password, dsn=dsn)

# 获取所有表名
tables = []
cursor = conn.cursor()
cursor.execute('SELECT table_name FROM all_tables WHERE owner = \'SYSTEM\'')
for row in cursor:
    tables.append(row[0])

# 导出数据到 CSV 文件
for table in tables:
    file_path = os.path.join(save_dir, table + '.csv')

    if os.path.exists(file_path):
        print(f'{file_path} 已经存在，跳过...')
        continue

    with open(file_path, 'w', newline='', encoding='gbk') as csvfile:
        writer = csv.writer(csvfile)

        # 导出表头信息
        header = []
        cursor.execute(f"SELECT * FROM {table} WHERE rownum = 1")
        for col in cursor.description:
            header.append(col[0])
        writer.writerow(header)

        # 导出数据
        cursor.execute(f"SELECT * FROM {table}")
        rows = cursor.fetchmany(1000)
        while rows:
            for row in rows:
                row = [str(cell).encode('gbk', 'ignore').decode('gbk') for cell in row]
                writer.writerow(row)
            rows = cursor.fetchmany(1000)

    print(f'已导出表 {table} 至文件：{file_path}')

# 关闭连接
conn.close()