Oracle adatbázis táblák szerkezetének xlsx be mentése

Oracle adatbázis táblák szerkezetének xlsx be mentése

Az alábbiakban bemutatok egy Python programot, amely az Oracle adatbázis Scott sémájából lekéri az összes tábla és azok mezőinformációit, majd Excel munkalapokba menti egy XLSX fájlba. A program használja a cx_Oracle könyvtárat az Oracle adatbázishoz való kapcsolódáshoz és az openpyxl könyvtárat az Excel fájl kezelésére.

1. Verzió

Előfeltételek

1. Telepített csomagok:
   • cx_Oracle: Oracle adatbázishoz való kapcsolódáshoz.
   • openpyxl: Excel fájlok kezeléséhez. Telepítésük parancssori utasításokkal:
   bash

   pip install cx_Oracle openpyxl

2. Oracle adatbázis hozzáférés:
   • Scott séma hozzáférési adatok (felhasználónév, jelszó, DSN vagy kapcsolati sztring).
   • Telepített Oracle Instant Client, ha szükséges (a cx_Oracle működéséhez).

Python program

 import cx_Oracle

import openpyxl
from openpyxl.utils import get_column_letter
from datetime import datetime

def connect_to_oracle(username, password, dsn):
    """Kapcsolódás az Oracle adatbázishoz."""
    try:
        connection = cx_Oracle.connect(user=username, password=password, dsn=dsn)
        print("Sikeres kapcsolódás az Oracle adatbázishoz.")
        return connection
    except cx_Oracle.Error as error:
        print(f"Hiba a kapcsolódás során: {error}")
        raise

def get_table_and_column_info(connection):
    """Lekéri az összes tábla és azok oszlopinformációit a Scott sémából."""
    try:
        cursor = connection.cursor()
        # Lekérdezés a táblák és oszlopok metaadataira
        query = """
        SELECT 
            t.table_name,
            c.column_name,
            c.data_type,
            c.data_length,
            c.data_precision,
            c.data_scale,
            c.nullable
        FROM 
            user_tables t
        LEFT JOIN 
            user_tab_columns c
        ON 
            t.table_name = c.table_name
        ORDER BY 
            t.table_name, c.column_id
        """
        cursor.execute(query)
        # Táblák és oszlopok tárolása
        tables = {}
        for row in cursor:
            table_name = row[0]
            column_info = {
                'column_name': row[1],
                'data_type': row[2],
                'data_length': row[3],
                'data_precision': row[4],
                'data_scale': row[5],
                'nullable': row[6]
            }
            if table_name not in tables:
                tables[table_name] = []
            tables[table_name].append(column_info)
        return tables
    except cx_Oracle.Error as error:
        print(f"Hiba a lekérdezés során: {error}")
        raise
    finally:
        cursor.close()

def create_excel_file(tables, output_file):
    """Excel fájl létrehozása a tábla és oszlop információkkal."""
    try:
        # Új munkafüzet létrehozása
        workbook = openpyxl.Workbook()
        # Az alapértelmezett munkalapot eltávolítjuk
        workbook.remove(workbook.active)

        # Fejléc definiálása
        headers = ["Column Name", "Data Type", "Data Length", "Data Precision", "Data Scale", "Nullable"]

        # Minden táblához külön munkalap
        for table_name, columns in tables.items():
            # Munkalap létrehozása (érvényes munkalapnév biztosítása)
            sheet_name = table_name[:31]  # Excel munkalapnév max 31 karakter
            sheet = workbook.create_sheet(title=sheet_name)

            # Fejléc hozzáadása
            for col_idx, header in enumerate(headers, start=1):
                cell = sheet[f"{get_column_letter(col_idx)}1"]
                cell.value = header
                cell.font = openpyxl.styles.Font(bold=True)

            # Adatok hozzáadása
            for row_idx, column in enumerate(columns, start=2):
                sheet[f"A{row_idx}"] = column['column_name']
                sheet[f"B{row_idx}"] = column['data_type']
                sheet[f"C{row_idx}"] = column['data_length']
                sheet[f"D{row_idx}"] = column['data_precision']
                sheet[f"E{row_idx}"] = column['data_scale']
                sheet[f"F{row_idx}"] = column['nullable']

            # Oszlopszélesség automatikus igazítása
            for col in sheet.columns:
                max_length = 0
                column = col[0].column_letter
                for cell in col:
                    try:
                        if len(str(cell.value)) > max_length:
                            max_length = len(str(cell.value))
                    except:
                        pass
                adjusted_width = max_length + 2
                sheet.column_dimensions[column].width = adjusted_width

        # Fájl mentése
        workbook.save(output_file)
        print(f"Excel fájl sikeresen létrehozva: {output_file}")
    except Exception as error:
        print(f"Hiba az Excel fájl létrehozása során: {error}")
        raise

def main():
    # Oracle kapcsolódási adatok
    username = "scott"
    password = "tiger"
    dsn = "localhost:1521/orcl"  # Példa: host:port/service_name
    output_file = f"scott_schema_tables_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"

    # Kapcsolódás
    connection = connect_to_oracle(username, password, dsn)
    try:
        # Tábla és oszlop információk lekérése
        tables = get_table_and_column_info(connection)
        # Excel fájl létrehozása
        create_excel_file(tables, output_file)
    finally:
        # Kapcsolat lezárása
        connection.close()
        print("Kapcsolat lezárva.")

if __name__ == "__main__":
    main()

Program magyarázata

1. Kapcsolódás az Oracle adatbázishoz:
   • A connect_to_oracle függvény a megadott felhasználónév, jelszó és DSN segítségével kapcsolódik az adatbázishoz.
   • A DSN-t az Oracle környezet alapján kell megadni (pl. host:port/service_name).
2. Tábla és oszlop információk lekérése:
   • A get_table_and_column_info függvény az user_tables és user_tab_columns nézeteket használja a Scott sémában lévő táblák és oszlopok metaadatainak lekérdezésére.
   • Az eredmény egy szótár, ahol a kulcsok a táblanevek, az értékek pedig az oszlopok adatai (név, típus, hossz, stb.).
3. Excel fájl létrehozása:
   • A create_excel_file függvény egy új Excel munkafüzetet hoz létre.
   • Minden táblához külön munkalapot készít, ahol az oszlopok információit táblázatos formában jeleníti meg.
   • A fejléc tartalmazza az oszlopnevet, adattípust, hosszúságot, precizitást, skálát és az "nullable" tulajdonságot.
   • Az oszlopszélességet automatikusan igazítja a tartalomhoz.
   • A fájlt a megadott névvel menti (időbélyeggel ellátva az egyediség érdekében).
4. Fő program:
   • A main függvény összefogja a lépéseket: kapcsolódás, lekérdezés, Excel fájl létrehozása és kapcsolat lezárása.
   • A kapcsolódási adatok és a kimeneti fájlnév testreszabható.

Használati utasítás

1. Kapcsolódási adatok módosítása:
   • A username, password és dsn változókat állítsd be a saját Oracle környezetnek megfelelően.
   • Példa DSN: "localhost:1521/orcl" vagy TNS-alias, ha TNSNAMES.ORA fájlt használsz.
2. Futtatás:
   • Futtasd a programot Python környezetben:

     python script.py

   • A program létrehozza az Excel fájlt a futási könyvtárban, pl. scott_schema_tables_20250411_123456.xlsx.
3. Kimenet:
   • Az Excel fájlban minden tábla külön munkalapon jelenik meg.
   • Az oszlopok tartalmazzák: oszlopnév, adattípus, adat hossz, precizitás, skála, és hogy az oszlop lehet-e NULL.

Hibakezelés

• A program kezeli az Oracle kapcsolódási és lekérdezési hibákat.
• Az Excel fájl létrehozása során fellépő hibákat is naplózza.
• A kapcsolatot mindig lezárja, még hiba esetén is.

Testreszabási lehetőségek

• További oszlop metaadatok: Ha más oszlopinformációkra is szükséged van (pl. alapértelmezett értékek, megjegyzések), módosítsd a lekérdezést a get_table_and_column_info függvényben.
• Formázás: Az Excel munkalapok formázása testreszabható (pl. színek, szegélyek) az openpyxl könyvtár további funkcióival.
• Kimeneti fájlnév: Módosítsd az output_file változót, ha fix nevet szeretnél.

 

2. Verzió

Az alábbiakban módosítom a korábbi Python programot úgy, hogy a pandas csomagot is használja az Excel fájl létrehozásához az openpyxl helyett, miközben megtartom a cx_Oracle használatát az Oracle adatbázis eléréséhez. A pandas segítségével az adatok kezelése és az Excel fájlba írása egyszerűbbé válik, mivel a DataFrame struktúra jól illeszkedik a táblázatos adatokhoz.

Előfeltételek

1. Telepített csomagok:
   • cx_Oracle: Oracle adatbázishoz való kapcsolódáshoz.
   • pandas: Adatok kezelésére és Excel fájlba írására.
   • openpyxl vagy xlsxwriter: A pandas Excel fájlba írásához szükséges motor (a pandas automatikusan használja). Telepítésük parancssori utasításokkal:

   pip install cx_Oracle pandas openpyxl

2. Oracle adatbázis hozzáférés:
   • Scott séma hozzáférési adatok (felhasználónév, jelszó, DSN vagy kapcsolati sztring).
   • Telepített Oracle Instant Client, ha szükséges (a cx_Oracle működéséhez).

Python program

import cx_Oracle
import pandas as pd
from datetime import datetime

def connect_to_oracle(username, password, dsn):
    """Kapcsolódás az Oracle adatbázishoz."""
    try:
        connection = cx_Oracle.connect(user=username, password=password, dsn=dsn)
        print("Sikeres kapcsolódás az Oracle adatbázishoz.")
        return connection
    except cx_Oracle.Error as error:
        print(f"Hiba a kapcsolódás során: {error}")
        raise

def get_table_and_column_info(connection):
    """Lekéri az összes tábla és azok oszlopinformációit a Scott sémából."""
    try:
        cursor = connection.cursor()
        # Lekérdezés a táblák és oszlopok metaadataira
        query = """
        SELECT 
            t.table_name,
            c.column_name,
            c.data_type,
            c.data_length,
            c.data_precision,
            c.data_scale,
            c.nullable
        FROM 
            user_tables t
        LEFT JOIN 
            user_tab_columns c
        ON 
            t.table_name = c.table_name
        ORDER BY 
            t.table_name, c.column_id
        """
        cursor.execute(query)
        # Táblák és oszlopok tárolása
        tables = {}
        for row in cursor:
            table_name = row[0]
            column_info = {
                'Column Name': row[1],
                'Data Type': row[2],
                'Data Length': row[3],
                'Data Precision': row[4],
                'Data Scale': row[5],
                'Nullable': row[6]
            }
            if table_name not in tables:
                tables[table_name] = []
            tables[table_name].append(column_info)
        return tables
    except cx_Oracle.Error as error:
        print(f"Hiba a lekérdezés során: {error}")
        raise
    finally:
        cursor.close()

def create_excel_file(tables, output_file):
    """Excel fájl létrehozása a tábla és oszlop információkkal pandas segítségével."""
    try:
        # ExcelWriter objektum létrehozása
        with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
            for table_name, columns in tables.items():
                # DataFrame létrehozása az oszlopokból
                df = pd.DataFrame(columns)
                # Munkalapra írás (érvényes munkalapnév: max 31 karakter)
                sheet_name = table_name[:31]
                df.to_excel(writer, sheet_name=sheet_name, index=False)
                
                # Oszlopszélesség igazítása (openpyxl munkalap elérése)
                worksheet = writer.sheets[sheet_name]
                for idx, col in enumerate(df.columns):
                    max_length = max(
                        df[col].astype(str).map(len).max(),  # Adatok maximális hossza
                        len(str(col))  # Fejléc hossza
                    )
                    worksheet.column_dimensions[chr(65 + idx)].width = max_length + 2

        print(f"Excel fájl sikeresen létrehozva: {output_file}")
    except Exception as error:
        print(f"Hiba az Excel fájl létrehozása során: {error}")
        raise

def main():
    # Oracle kapcsolódási adatok
    username = "scott"
    password = "tiger"
    dsn = "localhost:1521/orcl"  # Példa: host:port/service_name
    output_file = f"scott_schema_tables_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"

    # Kapcsolódás
    connection = connect_to_oracle(username, password, dsn)
    try:
        # Tábla és oszlop információk lekérése
        tables = get_table_and_column_info(connection)
        # Excel fájl létrehozása
        create_excel_file(tables, output_file)
    finally:
        # Kapcsolat lezárása
        connection.close()
        print("Kapcsolat lezárva.")

if __name__ == "__main__":
    main()

Program magyarázata

1. Kapcsolódás az Oracle adatbázishoz:
   • A connect_to_oracle függvény változatlan, a cx_Oracle segítségével kapcsolódik az adatbázishoz a megadott hitelesítő adatokkal.
2. Tábla és oszlop információk lekérése:
   • A get_table_and_column_info függvény szintén változatlan, az Oracle user_tables és user_tab_columns nézetekből gyűjti össze a metaadatokat.
   • Az oszlopinformációkat szótárak listájaként tárolja, ahol a kulcsok olvashatóbb neveket kapnak (pl. "Column Name" helyett "COLUMN_NAME").
3. Excel fájl létrehozása pandas segítségével:
   • A create_excel_file függvény most a pandas csomagot használja:
     • Minden tábla adatait egy pandas.DataFrame-be konvertálja.
     • Az ExcelWriter objektum segítségével minden táblát külön munkalapra ír.
     • A munkalap neve a táblanév (max. 31 karakterre rövidítve, az Excel korlátozása miatt).
     • Az openpyxl motort használja az Excel fájl írásához, ami lehetővé teszi az oszlopszélesség finomhangolását.
   • Az oszlopszélességet automatikusan igazítja az adatok és a fejlécek maximális hosszához.
4. Fő program:
   • A main függvény koordinálja a lépéseket: kapcsolódás, adatok lekérése, Excel fájl létrehozása és kapcsolat lezárása.
   • A kimeneti fájlnév időbélyeggel egyedi.

A pandas előnyei ebben a programban

• Egyszerűbb adatkezelés: A pandas.DataFrame megkönnyíti az adatok táblázatos formában való kezelését és Excelbe írását.
• Kód rövidebbé válik: Az Excel fájlba írás egyetlen to_excel hívással megoldható munkalaponként.
• Beépített formázási lehetőségek: A pandas és az openpyxl kombinációja rugalmas oszlopszélesség-beállítást tesz lehetővé.

Használati utasítás

1. Kapcsolódási adatok módosítása:
   • Módosítsd a username, password és dsn változókat a saját Oracle környezetnek megfelelően.
   • Példa DSN: "localhost:1521/orcl" vagy TNS-alias, ha TNSNAMES.ORA fájlt használsz.
2. Futtatás:
   • Futtasd a programot Python környezetben:

     python script.py

   • A program létrehozza az Excel fájlt a futási könyvtárban, pl. scott_schema_tables_20250411_123456.xlsx.
3. Kimenet:
   • Az Excel fájl minden táblát külön munkalapon tartalmaz.
   • Az oszlopok: "Column Name", "Data Type", "Data Length", "Data Precision", "Data Scale", "Nullable".
   • Az oszlopszélesség automatikusan igazodik a tartalomhoz.

Hibakezelés

• A program kezeli az Oracle kapcsolódási és lekérdezési hibákat.
• Az Excel fájl létrehozása során fellépő hibákat is naplózza.
• A kapcsolatot mindig lezárja, még hiba esetén is.

Testreszabási lehetőségek

• További oszlop metaadatok: Ha más oszlopinformációkra van szükség (pl. megjegyzések, alapértelmezett értékek), bővítsd a lekérdezést a get_table_and_column_info függvényben.
• Formázás: A pandas és openpyxl kombinációjával további formázások adhatók hozzá, pl. cellaszínek vagy szegélyek (ehhez az openpyxl stílusokat kell használni a munkalapokon).
• Kimeneti fájlnév: Módosítsd az output_file változót, ha fix nevet szeretnél.
• Excel motor: Ha más motort szeretnél használni (pl. xlsxwriter), módosítsd az engine paramétert az ExcelWriter-ben.

Különbségek az előző verzióhoz képest

• Pandas használata: Az openpyxl közvetlen munkalap-manipuláció helyett a pandas DataFrame-eket használ, ami egyszerűbb és olvashatóbb kódot eredményez.
• Oszlopszélesség kezelése: Az oszlopszélesség igazítása most a pandas DataFrame adataira és az openpyxl munkalapra épül.
• Kód tömörsége: A pandas miatt az Excel fájl létrehozása kevesebb sorból áll.

3. verzió

Lépések

1. Csatlakozik egy Oracle adatbázishoz.

2. Lekérdezi az SCOTT sémában található összes tábla és azok mezőinek nevét, adattípusát.

3. Minden táblát külön Excel munkalapra ír.

4. Ment egy .xlsx állományt.

A megoldáshoz a következő csomagokra lesz szükséged (telepíthetőek pip-pel):

--- bash

pip install oracledb openpyxl pandas

Most jön a teljes Python script:

-- python
-
import oracledb
import pandas as pd
from openpyxl import Workbook

# Oracle csatlakozási adatok
oracle_user = "SCOTT"
oracle_password = "tiger"
oracle_dsn = "localhost/orclpdb"  # módosítsd a saját DSN-ed szerint

# Excel fájl neve
excel_filename = "scott_tables.xlsx"

# Csatlakozás Oracle adatbázishoz
connection = oracledb.connect(user=oracle_user, password=oracle_password, dsn=oracle_dsn)
cursor = connection.cursor()

# SCOTT séma tábla nevek lekérdezése
cursor.execute("""
    SELECT table_name 
    FROM all_tables 
    WHERE owner = 'SCOTT'
""")

tables = [row[0] for row in cursor.fetchall()]

# Excel writer inicializálása
with pd.ExcelWriter(excel_filename, engine='openpyxl') as writer:
    for table in tables:
        # Oszlop információk lekérdezése
        cursor.execute(f"""
            SELECT column_name, data_type, data_length, nullable
            FROM all_tab_columns
            WHERE owner = 'SCOTT' AND table_name = :table_name
            ORDER BY column_id
        """, table_name=table)
        
        columns_info = cursor.fetchall()
        df = pd.DataFrame(columns_info, columns=["Column Name", "Data Type", "Data Length", "Nullable"])
        
        # Munkalap nevének beállítása (max 31 karakter)
        sheet_name = table[:31]
        
        # Írás Excel fájlba
        df.to_excel(writer, sheet_name=sheet_name, index=False)

# Erőforrások lezárása
cursor.close()
connection.close()

print(f"Excel fájl elkészült: {excel_filename}")

---

Megjegyzések:

• Ha cx_Oracle-t használsz, akkor azt is lehet használni az oracledb helyett, de az oracledb a modernebb változat.

• Az owner = 'SCOTT' rész feltételezi, hogy az adatbázisodban a SCOTT séma látható. Ha nem, használj dba_tables helyett all_tables vagy user_tables, vagy adj DBA jogot.