工具包使用2

• pycharm的\OneMake30\auto_create_hive_table\cn\itcast\utils目录中编辑OracleMetaUtil.py

#!/usr/bin/env python
# @desc : todo 查询oracle一张表的元数据信息，封装为TableMeta对象
__coding__ = "utf-8"
__author__ = "itcast"
 
# 报错解决
import sys
sys.path.append("/tmp/pycharm_project_178/")
sys.path.append("/tmp/pycharm_project_178/auto_create_hive_table/")
 
import logging
import cx_Oracle
# 列的信息对象
from auto_create_hive_table.cn.itcast.entity.ColumnMeta import ColumnMeta
# 表的信息对象
from auto_create_hive_table.cn.itcast.entity.TableMeta import TableMeta
 
def getTableMeta(oracleConn, tableName) -> TableMeta:
    """
    用于读取Oracle中表的信息【表名、列的信息、表的注释】封装成TableMeta
    :param oracleConn: Oracle连接对象
    :param tableName: 表的名称
    :return:
    """
    # 从连接中获取一个游标【SQL对象】
    cursor = oracleConn.cursor()
    try:
        # 定义Oracle查询表信息的SQL语句
        oracleSql = f"""select columnName, dataType, dataScale, dataPercision, columnComment, tableComment from
(select column_name columnName,data_type dataType, DATA_SCALE dataScale,DATA_PRECISION dataPercision, TABLE_NAME
from all_tab_cols where '{tableName}' = table_name) t1
left join (select comments tableComment,TABLE_NAME from all_tab_comments WHERE '{tableName}' = TABLE_NAME) t2 on t1.TABLE_NAME = t2.TABLE_NAME
left join (select comments columnComment, COLUMN_NAME from all_col_comments WHERE TABLE_NAME='{tableName}') t3 on t1.columnName = t3.COLUMN_NAME
"""
        # 记录运行的SQL语句
        logging.warning(f'query oracle table {tableName} metadata sql:\n{oracleSql}')
        # 执行SQL语句
        cursor.execute(oracleSql)
        # 获取执行的结果
        resultSet = cursor.fetchall()
        # 构建返回的表的信息对象：表名 + 列的信息 + 表的注释
        tableMeta = TableMeta(f'{tableName}', '')
        # 表信息 = 表名 + 表的注释 + list[列的信息]
        for line in resultSet:
            # 获取每一列的信息
            columnName = line[0]        # 获取列的名称
            dataType = line[1]          # 获取列的类型
            dataScale = line[2]         # 获取列值长度
            dataScope = line[3]         # 获取列值精度
            columnComment = line[4]     # 获取列的注释
            tableComment = line[5]      # 获取表的注释
            if dataScale is None:       # 如果列值的长度为空，则设置为0
                dataScale = 0
            if dataScope is None:       # 如果列值的精度为空，则设置为0
                dataScope = 0
            # 将每条数据封装成一个列的信息对象【列名 + 类型 + 长度 + 精度 + 注释】
            columnMeta = ColumnMeta(columnName, dataType, columnComment, dataScope, dataScale)
            # 将列的信息添加到表的对象中
            tableMeta.addColumnMeta(columnMeta)
            # 将表的注释添加到表的对象中
            tableMeta.tableComment = tableComment
        # 返回当前表的所有信息【表名 + 所有列的信息 + 表的注释】
        return tableMeta
    # 异常处理
    except cx_Oracle.Error as error:
        print(error)
    # 关闭游标
    finally:
        if cursor:
            cursor.close()
# 测试
if __name__ == '__main__':
    orclConn = cx_Oracle.connect('ciss', '123456', cx_Oracle.makedsn('192.168.128.100', '1521', None, 'helowin'))
 
    tableMeta = getTableMeta(orclConn,"CAP_USER")
    print( tableMeta)

• 上传到服务器，Tools -> Deployment -> Upload

# 进入如下目录
cd /tmp/pycharm_project_178/auto_create_hive_table/cn/itcast/utils
 
# 执行
[root@node1 utils]# python3 OracleMetaUtil.py 
WARNING:root:query oracle table CAP_USER metadata sql:
select columnName, dataType, dataScale, dataPercision, columnComment, tableComment from
(select column_name columnName,data_type dataType, DATA_SCALE dataScale,DATA_PRECISION dataPercision, TABLE_NAME
from all_tab_cols where 'CAP_USER' = table_name) t1
left join (select comments tableComment,TABLE_NAME from all_tab_comments WHERE 'CAP_USER' = TABLE_NAME) t2 on t1.TABLE_NAME = t2.TABLE_NAME
left join (select comments columnComment, COLUMN_NAME from all_col_comments WHERE TABLE_NAME='CAP_USER') t3 on t1.columnName = t3.COLUMN_NAME
 
TableMeta:
 tableName: CAP_USER, tableComment: None, 
columnMetaList:
 ['ColumnMeta: columnName: OPERATOR_ID, dataType: NUMBER, columnComment: None, dataScope: 18, dataScale: 0', 'ColumnMeta: columnName: TENANT_ID, dataType: VARCHAR2, columnComment: None, dataScope: 0, dataScale: 0', 'ColumnMeta: columnName: USER_ID, dataType: VARCHAR2, columnComment: None, dataScope: 0, dataScale: 0', 'ColumnMeta: columnName: PASSWORD, dataType: VARCHAR2, columnComment: None, dataScope: 0, dataScale: 0', 'ColumnMeta: columnName: INVALDATE, dataType: DATE, columnComment: None, dataScope: 0, dataScale: 0', 'ColumnMeta: columnName: USER_NAME, dataType: VARCHAR2, columnComment: None, dataScope: 0, dataScale: 0', 'ColumnMeta: columnName: AUTHMODE, dataType: VARCHAR2, columnComment: None, dataScope: 0, dataScale: 0', 'ColumnMeta: columnName: STATUS, dataType: VARCHAR2, columnComment: None, dataScope: 0, dataScale: 0', 'ColumnMeta: columnName: UNLOCKTIME, dataType: TIMESTAMP(6), columnComment: None, dataScope: 0, dataScale: 6', 'ColumnMeta: columnName: MENUTYPE, dataType: VARCHAR2, columnComment: None, dataScope: 0, dataScale: 0', 'ColumnMeta: columnName: LASTLOGIN, dataType: TIMESTAMP(6), columnComment: None, dataScope: 0, dataScale: 6', 'ColumnMeta: columnName: ERRCOUNT, dataType: NUMBER, columnComment: None, dataScope: 10, dataScale: 0', 'ColumnMeta: columnName: STARTDATE, dataType: DATE, columnComment: None, dataScope: 0, dataScale: 0', 'ColumnMeta: columnName: ENDDATE, dataType: DATE, columnComment: None, dataScope: 0, dataScale: 0', 'ColumnMeta: columnName: VALIDTIME, dataType: VARCHAR2, columnComment: None, dataScope: 0, dataScale: 0', 'ColumnMeta: columnName: MACCODE, dataType: VARCHAR2, columnComment: None, dataScope: 0, dataScale: 0', 'ColumnMeta: columnName: IPADDRESS, dataType: VARCHAR2, columnComment: None, dataScope: 0, dataScale: 0', 'ColumnMeta: columnName: EMAIL, dataType: VARCHAR2, columnComment: None, dataScope: 0, dataScale: 0', 'ColumnMeta: columnName: CREATEUSER, dataType: VARCHAR2, columnComment: None, dataScope: 0, dataScale: 0', 'ColumnMeta: columnName: CREATETIME, dataType: TIMESTAMP(6), columnComment: None, dataScope: 0, dataScale: 6']

• 复制生成的sql到datagrip，连接oracle测试