def table_def(table_name): 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()
colnames = cursor.description
col = [ ] for i in colnames: col.append( i[0].lower() )
df = pd.DataFrame(row, columns = col) return(df)
emp = table_def('EMP') emp
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()
# 나의 오라클 db에 있는 테이블명 리스트를 cursor라는 메모리에 올림 cursor.execute(""" select table_name from dba_tables where owner in ('C##SCOTT', 'HR', 'SH', 'OE') and table_name not like 'DM%' """)
# cursor 에 있는 데이터를 불러와서 row 변수에 담아냄 row = cursor.fetchall()
table_list = [ ]
for i in row: table_list.append(i[0])
table_list.sort() print( table_list )
for table_name in table_list: df = table_def(table_name)
output_path = f"c:\\aaa2\\{table_name}_df.xlsx" df.to_excel(output_path)
print("Excel 파일 생성이 완료되었습니다.")
|