python3通过驱动包连接oceanbase并将数据导出为excel

1.创建文件夹driver并将驱动包放到项目中

2.编写db_config配置

# db_config.py
# 驱动包路径
driver_path = '../driver/oceanbase-client-2.2.9.jar'
# 连接参数
url = 'jdbc:oceanbase://osidaoobdit01.cn-shanghai-finance-1.oceanbase.aliyuncs.com:1526/***?pool=false'
user = '自己的用户名'
password = '自己的密码'
driver = 'com.oceanbase.jdbc.Driver'

3.pip安装需要的依赖包

pip install jaydebeapi

  pip install pandas

4. 代码部分

import jaydebeapi
import pandas as pd
import os
import sys
sys.path.append(os.path.abspath('../db_config'))
import db_config
# 建立连接
conn = jaydebeapi.connect(db_config.driver,db_config. url, [db_config.user, db_config.password], db_config.driver_path)


try:
    with conn.cursor() as cursor:
        # 执行 SQL 查询
        sql = """
        SELECT 
            t2.NAME AS 小程序名称,
            t1.APPLICATION_LONG_ID,
            t1.NAME AS 应用,
            t4.LAPP_ID,
            t4.MOBILE_SUPPORT,
            t4.PAD_SUPPORT,
            t4.PC_SUPPORT,
            t4.TV_SUPPORT,
            t4.UI_MOBILE_SUPPORT,
            t4.UI_PAD_SUPPORT,
            t4.UI_PC_SUPPORT,
            t4.UI_TV_SUPPORT,
            t4.LINK_URL
        FROM 
            IBOSMUCCOMPDATA.T_ISV_APPLICATION t1
        LEFT JOIN 
            IBOSMUCCOMPDATA.T_ISV_APPLICATION_LAPP t2 ON t1.APPLICATION_LONG_ID = t2.APPLICATION_LONG_ID
        LEFT JOIN 
            IBOSMUCCOMPDATA.T_ISV_APPLICATION_VERSION t3 ON t1.APPLICATION_LONG_ID = t3.APPLICATION_LONG_ID AND t3.STATUS = 2
        LEFT JOIN 
            IBOSMUCCOMPDATA.T_ISV_APPLICATION_VERSION_LAPP t4 ON t1.APPLICATION_LONG_ID = t4.APPLICATION_LONG_ID AND t3.ID = t4.APPLICATION_VERSION_ID AND t2.LAPP_ID = t4.LAPP_ID
        WHERE 
            t1.APPLICATION_LONG_ID IS NOT NULL AND t1.STATUS = 0 AND t2.DELETED = 'N' AND t3.DELETED = 'N'
        ORDER BY 
            t1.NAME, t2.NAME DESC
        """
        cursor.execute(sql)
        result = cursor.fetchall()
        columns = [desc[0] for desc in cursor.description]

        # 将查询结果转换为 DataFrame
        df = pd.DataFrame(result, columns=columns)

        # 导出为 Excel 文件
        output_file = '../data/query_result.xlsx'
        df.to_excel(output_file, index=False)
        print(f'查询结果已导出到 {output_file}')
finally:
    # 关闭连接
    conn.close()