python

#!/usr/bin/env python
# -*- coding: utf-8 -*-
from dataclasses import replace

import pandas as pd
import oracledb
import configparser
import pathlib
import os


class ConnDB():
    def __init__(self ,table_name , sql_file_name , edate , config_path='../config/DB_config.ini', section='oracle' ):
        self.config_path = config_path
        self.section = section
        self.table_name = table_name
        self.sql_file_name = sql_file_name
        self.edate = edate
        self.base_dir = "../Result_File/"
        self.date_dir = edate.replace('-', '')
        self.target_folder_path = f"{self.base_dir}{self.date_dir}"
        self.excel_filename = f"{self.base_dir}{self.date_dir}/{self.table_name}_{self.date_dir}.xlsx"
        self.chinese_table_name = self.get_chinese_table_name(table_name)
        self.chinese_table_name = f"{self.base_dir}{self.date_dir}/{self.chinese_table_name}"

    def get_chinese_table_name(self, table_name):
        """
        根据英文表名获取对应的中文名称
        """
        table_name_mapping = {
            "DM_GZW_BANK_ACCOUNT": "银行账户",
            "DM_GZW_BANK_RECORD": "资金结算",
            "DM_GZW_BANK_CONTRACT": "银行贷款",
            "DM_GZW_COMPANY": "应付债券",
            "DM_GZW_BILLPAY": "应付票据",
            "DM_GZW_BILLREC": "应收票据",
            "DM_GZW_WARRANT": "担保",
            "DM_GZW_CREDIT": "信用证",
            "DM_GZW_GUARANTEE": "保函",
            "DM_GZW_PRODUCT": "金融投资业务",
            "DM_GZW_SCM": "供应链金融",
            "DM_GZW_CWGS": "财务公司附",
            "DM_GZW_PPP": "ppp附表",
            "DM_GZW_ACCOUNT_PAY": "应付账款",
            "DM_GZW_ACCOUNT_REC": "应收账款",
            "DM_GZW_CONTRACT": "合同",
            "DM_GZW_KSXX": "客商信息",
            "DM_GZW_VAT": "增值税发票",
            "DM_GZW_JRYS": "金融衍生品"
        }

        chinese_file_name =  table_name_mapping.get(table_name)
        chinese_file_name = f"057_中国中煤能源集团有限公司_{chinese_file_name}_01_{self.date_dir}.csv"
        return  chinese_file_name

    def get_connect(self):
        """读取数据库配置信息"""
        parser = configparser.ConfigParser()
        config_file = pathlib.Path(self.config_path)

        if not config_file.is_file():
            raise FileNotFoundError(f"配置文件 {self.config_path} 不存在")

        parser.read(self.config_path, encoding='utf-8')

        if not parser.has_section(self.section):
            raise ValueError(f"在配置文件中未找到 section: {self.section}")

        host = parser.get(self.section, 'host', fallback=None)
        port = parser.getint(self.section, 'port', fallback=1521)
        service_name = parser.get(self.section, 'service_name', fallback=None)
        user = parser.get(self.section, 'user', fallback=None)
        password = parser.get(self.section, 'password', fallback=None)

        print(f"连接模式:Thin , 连接IP:{host} , 连接端口:{port} , 连接用户名:{user} , 开始测试连接...")

        DSN = f"{host}:{port}/{service_name}"

        conn = oracledb.connect(
            user=user,
            password=password,
            dsn=DSN
        )

        if not conn:
            raise ConnectionError("数据库连接失败，请检查配置和网络连接")
        else:
            print('Oracle数据库连接成功!')

        return conn


    def query_date(self):
        conn = self.get_connect()



        try:
            cursor_bjg = conn.cursor()
            cursor = conn.cursor()
            #column_names = [desc[0] for desc in cursor.description]

            if  self.table_name != '':

                table_sql = f"SELECT COLMN_NAME , COLMN_CHINESE FROM GZW_TABLE_TEMPLATE WHERE TABLE_NAME =  '{self.table_name}'  ORDER BY ORDER_NUM ASC  "
                print(table_sql)
                cursor_bjg.execute(table_sql)
                rows = cursor_bjg.fetchall()
                sql_aliases = [f"{row[0]} AS \"{row[1]}\"" for row in rows]
                query_sql = ", ".join(sql_aliases)
                query_sql = f"SELECT  {query_sql} FROM {self.table_name}  WHERE DAYIDX = DATE '{self.edate}'"
                print(query_sql)
                result = cursor.execute(query_sql)
                return result


            elif   self.file_name != '':
                self.excel_filename =  f"../Result_File/{self.file_name}_{self.edate}.xlsx"
                with open(self.file_name, 'r') as file:
                    sql_str = file.read()
                    sql_str = sql_str.replace('${edate}', self.edate)
                    sql_str = sql_str.replace(';', '')
                    print(sql_str)
                    cursor.execute(sql_str)

            else:
                raise '表名和文件名都不存在'

            # df.to_excel(excel_filename, index=False)
            # print(f"数据已导出到: {excel_filename}")



        except Exception as e:
            print(f"执行 SQL 时出错: {e}")
            # 如果有更详细的错误信息，可以打印出来
            if hasattr(e, 'args') and e.args:
                print(f"错误详情: {e.args}")

    def save_file(self):
        result = self.query_date()
        if not result:
            print("没有查询结果！")



        else:
            if not os.path.exists(self.target_folder_path):
                try:
                    os.makedirs(self.target_folder_path)  # 使用makedirs可以同时创建不存在的父目录
                    print(f"已成功创建文件夹: {self.target_folder_path}")
                except OSError as e:
                    print(f"创建文件夹时出错: {e}")
            else:
                print(f"找到文件夹: {self.target_folder_path}")


        column_names = [desc[0] for desc in result.description]
        rows = result.fetchall()
        df = pd.DataFrame(rows, columns=column_names)

        df.to_excel(self.excel_filename, index=False)
        df.to_csv(self.chinese_table_name, index=False, encoding="utf-8")

        print(f"结果已写入 {self.excel_filename}文件 , {self.chinese_table_name}")

 

from OracleODBC import ConnDB

table_name = 'DM_GZW_BANK_ACCOUNT'
sql_file_name = ''
edate = '2025-08-27'


conn1 =  ConnDB(table_name = table_name , sql_file_name='',  edate = edate)

reuslt = conn1.save_file()