Re: 문제95. (오늘의 마지막 문제) 어제 마지막 문제로 출력한 테이블 리스트로... 전부 테이블 정의서를 엑셀 파

[17기_이승휘]

def table_def(table_name):

    table_name = table_name.upper()

    import cx_Oracle

    import pandas as pd

    dsn = cx_Oracle.makedsn('localhost', 1521, 'xe')

    db = cx_Oracle.connect('c##scott', 'tiger', dsn)

    cursor = db.cursor()

    query = f"""

            SELECT A.COLUMN_ID AS NO

                , B.COMMENTS AS "논리명"

                , A.COLUMN_NAME AS "물리명"

                , A.DATA_TYPE AS "자료 형태"

                , A.DATA_LENGTH AS "길이"

                , DECODE(A.NULLABLE, 'N', 'No', 'Y', 'Yes') AS "Null 허용"

                , A.DATA_DEFAULT AS "기본값"

                , B.COMMENTS AS "코멘트"

            FROM  ALL_TAB_COLUMNS A

            LEFT JOIN ALL_COL_COMMENTS B

            ON A.OWNER = B.OWNER

            AND A.TABLE_NAME = B.TABLE_NAME

            AND A.COLUMN_NAME = B.COLUMN_NAME

            WHERE A.TABLE_NAME = '{table_name}'  AND A.OWNER='C##SCOTT'

            ORDER BY A.COLUMN_ID

    """

    cursor.execute(query)

    row = cursor.fetchall()

    table = pd.DataFrame(row)

    colnames = cursor.description

    col = []

    for x in colnames:

        col.append(x[0].lower())

    table = pd.DataFrame(row, columns=col)

    return table

table_list = []

import  cx_Oracle

dsn = cx_Oracle.makedsn( 'localhost' , 1521, 'xe')  

db = cx_Oracle.connect('c##scott','tiger', dsn)

cursor  = db.cursor()      

cursor.execute(""" select  table_name

                    from  dba_tables

                    where  owner  in  ('C##SCOTT', 'HR', 'SH', 'OE')

                """)  

row = cursor.fetchall()  

for i in row:

    table_list.append(i[0])

for x in table_list:

    print(x)

    table = table_def(x)

    table.to_excel(f"D:/saveData/{x}_df.xlsx")

print("Excel 파일 생성이 완료되었습니다.")

----------------------------------------------------------------------