# !/usr/bin/env python3
# @Version : 2019-02-22 16:10:17
# @Author  : Jiang Hui (jianghui@zigui.me)
# @Link    :
# @Description : python读取Oracle数据库数据到Excel

import os
import cx_Oracle
import xlsxwriter
import datetime
from ora_settings import ORA_CONNECTION, ORA_USERNAME, ORA_PASSWORD, SQL

# 防止读取结果出现中文乱码（问号）情况
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'


def get_oracle(connection, username, password):
    """
    connection: 'IP:PORT/SID'
        type: str
    username:
        type: str
    password:
        type: str
    rtype: cx_Oracle.connection object
    """
    try:
        print(f"username: {username}\npassword: {password}\nconnection: {connection}")
        conn = cx_Oracle.connect(username, password, connection)
        return conn
    except cx_Oracle.Error as e:
        exit(e)


def write_excel(filename, fields, contents):
    """
    filename: '20190101.xlsx'
        type: str
    fields:
        type: list
    contents:
        type: list
    """
    with xlsxwriter.Workbook(filename) as workbook:
        # 标题栏格式定义
        format_title = workbook.add_format({
            'bold': True,
            'align': 'center',
            'valign': 'vcenter'
        })
        # 内容区格式定义
        format_content = workbook.add_format({
            'align': 'center',
            'valign': 'vcenter'
        })

        # sheet1名称定义
        sheet = workbook.add_worksheet('sheet1')

        # 标题栏冻结
        sheet.freeze_panes(1, 0)

        # 标题栏
        for field in range(len(fields)):
            sheet.write(0, field, fields[field][0], format_title)

        # 内容区
        for row in range(len(contents)):
            for col in range(len(fields)):
                ceil = contents[row][col]
                if ceil is not None:
                    sheet.write(row + 1, col, str(ceil), format_content)
    print(f"已保存至{filename}")


def main():
    print('-----------------start------------------')
    # 设置文件名称
    m_file = datetime.date.today().strftime('%Y%m%d') + '.xlsx'

    # 获取数据库连接
    conn = get_oracle(ORA_CONNECTION, ORA_USERNAME, ORA_PASSWORD)

    # 查询数据库
    with conn.cursor() as cursor:
        try:
            result = cursor.execute(SQL)
            print('---------------config-end---------------')
            print(result.statement)
            print('-----------------SQL-end----------------')
            xlsx_fields = cursor.description
            xlsx_contents = cursor.fetchall()
        except cx_Oracle.DatabaseError as e:
            conn.close()
            exit(e.args[0])

        # 往Excel写数据
        write_excel(m_file, xlsx_fields, xlsx_contents)

        print(f'共有{str(len(xlsx_contents))}行，{str(len(result.description))}列')
        print('-------------------end------------------')
    conn.close()


if __name__ == '__main__':
    main()

# ora_settings.py
# ----------------------------------------
# ORA_CONNECTION = '127.0.0.1:1521/orcl'
# ORA_USERNAME = 'user'
# ORA_PASSWORD = 'password'
#
# SQL = 'select * from user_tables'