Hi everyone,
I’m currently facing an issue with a Python script that I’m using to automate the generation of a database documentation in Word format. I would greatly appreciate your assistance in resolving this problem as I’m running out of time and could really use the expertise of the community.
Here’s a brief overview of the problem:
I have a Python script that connects to an Oracle database and retrieves information about tables, views, columns, indexes, and constraints. It then generates a Word document with the documentation based on this data. However, I’m encountering an issue when trying to retrieve information about materialized views from the database. They are just not being exported.
I believe that the issue can be resolved by modifying the script to retrieve the description of materialized views from a different view or table. However, I’m not sure which specific changes are needed to fix the problem.
If anyone has experience with Python, Oracle databases, or generating database documentation, I would greatly appreciate your help. Please let me know if you have any insights, suggestions, or solutions to overcome this error.
I have shared the Python script on a code-sharing platform to provide more context:
import sys
sys.path.insert(0,'C:\AGIS\Python_Modules')
import sqlalchemy
from sqlalchemy.engine import create_engine
import pandas as pd
import docx
from datetime import date
from docx.shared import Pt, Cm
DIALECT = 'oracle'
SQL_DRIVER = 'cx_oracle'
USERNAME = 'your_username'
DB = 'your_database'
HOST = 'your_host'
ENGINE_PATH_WIN_AUTH = DIALECT + '+' + SQL_DRIVER + '://' + USERNAME + ':' + USERNAME + DB + '@' + HOST
engine = create_engine(ENGINE_PATH_WIN_AUTH)
print("Connected to the database")
def add_tab(df, doc):
t = doc.add_table(df.shape[0]+1, df.shape[1])
for j in range(df.shape[-1]):
t.cell(0,j).text = df.columns[j]
for i in range(df.shape[0]):
for j in range(df.shape[-1]):
t.cell(i+1,j).text = str(df.values[i,j])
t.style = 'Light List Accent 1'
t.cell(0,0).width = Cm(5.0)
t.cell(0,1).width = Cm(3.0)
t.cell(0,2).width = Cm(12.5)
aktuellesDatum = date.today()
Einleitungstext = """
This data model describes the tables of the ADZ schema from AGIS. The ADZ schema includes structure, payment, registration data, individual crop contributions, and milk data.
For detailed explanations about data management and historization, please refer to the document 160607_DB_FACH_arex_adz.docx.
"""
doc = docx.Document()
def chg_font(obj, fontname, size=None):
obj.font.name = fontname
if size and isinstance(size, Pt):
obj.font.size = size
chg_font(doc.styles['Normal'], fontname='Calibri')
distance = Cm(1.5)
sec = doc.sections[0]
sec.left_margin = distance
sec.right_margin = distance
sec.top_margin = distance
sec.bottom_margin = distance
print("Created document template")
Quelle = """
SELECT SYS_CONTEXT('USERENV', 'SERVICE_NAME') FROM DUAL
"""
Tabellennamen = """
SELECT table_name FROM ALL_TAB_COMMENTS
WHERE owner IN ('ADZ')
AND table_name NOT IN ('flyway_schema_history')
"""
Tab_Beschreibung_MIN = """
SELECT
table_name NAME,
table_type TYPE,
comments Beschreibung
FROM ALL_TAB_COMMENTS
WHERE owner IN ('ADZ')
AND table_name NOT IN ('flyway_schema_history')
ORDER BY 1, 2
"""
Tab_Beschreibung = """
SELECT
owner,
table_type TYPE,
comments Beschreibung
FROM ALL_TAB_COMMENTS
WHERE owner IN ('ADZ')
AND TABLE_NAME = '{0}'
"""
Tab_Inhalt = """
SELECT
a.column_name AS Spaltenname,
Decode(a.data_type, 'NUMBER', a.data_type||'('||a.data_precision||','||a.data_scale||')',
'DATE', a.data_type,
'VARCHAR2', a.data_type||'('||a.data_length||')',
'CHAR', a.data_type||'('||a.data_length||')'
) AS TYP,
comments AS Beschreibung
FROM all_tab_columns a, ALL_COL_COMMENTS b
WHERE a.owner IN ('ADZ')
AND b.owner IN ('ADZ')
AND a.TABLE_NAME = b.TABLE_NAME
AND a.TABLE_NAME = '{0}'
AND a.column_name = b
Thank you in advance for your support and expertise. Your assistance will be invaluable in helping me.
PS: I am also facing the issue that the second column ‘type’ is way more narrow the 3 cm I have set, but only in the first section with the overview (doc.add_heading(‘Übersicht über alle Tabellen und Views’, 1)).
Cheers, Dinigi