使用Python的hdbcli遍历SAP HANA多表并导出为独立CSV文件

实现步骤与代码示例 #

以下是直接可用的实现方案，基于hdbcli和pandas（简化CSV导出逻辑）：

1. 安装依赖
   确保已安装所需库：

   pip install hdbcli pandas
   

2. 完整代码

   from hdbcli import dbapi
   import pandas as pd
   
   # 数据库连接参数，请替换为你的实际信息
   HANA_HOST = "your-hana-host"
   HANA_PORT = 30015  # 通常是3<实例号>15，比如实例00就是30015
   HANA_USER = "your-username"
   HANA_PASSWORD = "your-password"
   TARGET_SCHEMA = None  # 留空则使用当前用户默认schema，如需指定则填schema名称
   
   def export_tables_to_csv():
       conn = None
       try:
           # 建立数据库连接
           conn = dbapi.connect(
               address=HANA_HOST,
               port=HANA_PORT,
               user=HANA_USER,
               password=HANA_PASSWORD
           )
           cursor = conn.cursor()
   
           # 查询目标schema下的所有表
           if TARGET_SCHEMA:
               cursor.execute(f"SELECT TABLE_NAME FROM TABLES WHERE SCHEMA_NAME = '{TARGET_SCHEMA}'")
           else:
               cursor.execute("SELECT TABLE_NAME FROM TABLES WHERE SCHEMA_NAME = CURRENT_SCHEMA")
           
           tables = [row[0] for row in cursor.fetchall()]
           print(f"找到 {len(tables)} 张表: {tables}")
   
           # 遍历每张表导出CSV
           for table_name in tables:
               try:
                   # 查询表数据
                   query = f"SELECT * FROM {table_name}"
                   df = pd.read_sql(query, conn)
                   
                   # 导出为CSV，文件名用表名，编码用utf-8-sig避免中文乱码
                   csv_filename = f"{table_name}.csv"
                   df.to_csv(csv_filename, index=False, encoding="utf-8-sig")
                   print(f"成功导出表 {table_name} 到 {csv_filename}")
               except Exception as e:
                   print(f"导出表 {table_name} 失败: {str(e)}")
                   continue
   
       except Exception as e:
           print(f"数据库操作出错: {str(e)}")
       finally:
           # 关闭连接
           if conn:
               conn.close()
   
   if __name__ == "__main__":
       export_tables_to_csv()
   

关键说明 #

• 连接参数：务必替换HANA_HOST、HANA_PORT等参数为你的SAP HANA实例实际信息，端口号规则为3<实例编号>15（比如实例00对应30015）。
• Schema指定：如果需要导出特定schema下的表，设置TARGET_SCHEMA为目标schema名称即可，否则默认导出当前用户权限下的所有表。
• 异常处理：每个表的导出逻辑单独捕获异常，避免某一张表导出失败导致整个流程中断。
• CSV编码：使用utf-8-sig编码可以确保导出的CSV在Windows系统中文本工具（如Excel）中正常显示中文。

无pandas的替代方案（纯hdbcli+csv模块） #

如果不想依赖pandas，可以用Python内置的csv模块处理导出：

from hdbcli import dbapi
import csv

# ... 连接参数与前面一致 ...

def export_tables_to_csv_no_pandas():
    conn = None
    try:
        conn = dbapi.connect(address=HANA_HOST, port=HANA_PORT, user=HANA_USER, password=HANA_PASSWORD)
        cursor = conn.cursor()

        # 查询表列表逻辑同上...
        cursor.execute("SELECT TABLE_NAME FROM TABLES WHERE SCHEMA_NAME = CURRENT_SCHEMA" if not TARGET_SCHEMA else f"SELECT TABLE_NAME FROM TABLES WHERE SCHEMA_NAME = '{TARGET_SCHEMA}'")
        tables = [row[0] for row in cursor.fetchall()]

        for table_name in tables:
            try:
                cursor.execute(f"SELECT * FROM {table_name}")
                # 获取表头
                headers = [desc[0] for desc in cursor.description]
                rows = cursor.fetchall()

                # 写入CSV
                with open(f"{table_name}.csv", "w", newline="", encoding="utf-8-sig") as csvfile:
                    writer = csv.writer(csvfile)
                    writer.writerow(headers)
                    writer.writerows(rows)
                print(f"成功导出表 {table_name}")
            except Exception as e:
                print(f"导出表 {table_name} 失败: {str(e)}")
                continue
    except Exception as e:
        print(f"数据库操作出错: {str(e)}")
    finally:
        if conn:
            conn.close()

内容的提问来源于stack exchange，提问作者Sandeep Mohanty