Untitled

# Script on python for connecting postgre database, after listing all the tables names, and for each table
# listing column names with its datatype and possible field length. All the data will be visualized in 
# tables in Word document.

import docx
import psycopg2
from docx.enum.table import WD_TABLE_ALIGNMENT

# Connect to the database
conn = psycopg2.connect(
    host="10.2.0.213",
    database="webApi",
    user="mvd",
    password="Mvd123",
    port="5432",
)

# Create a cursor
cur = conn.cursor()

# Get a list of all the tables in the database
cur.execute("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'")
tables = [table[0] for table in cur.fetchall()]

# Create a new Word document and styling font
doc = docx.Document()
style = doc.styles['Normal']
font = style.font
font.name = 'Times New Roman'

# Add data to the table
for table_name in tables:
    if table_name.startswith("_"):
        continue
    # Add a heading for the table name
    doc.add_heading(table_name, level=1)

    # Add a table to the document
    table = doc.add_table(rows=1, cols=4)
    table.alignment = WD_TABLE_ALIGNMENT.CENTER
    hdr_cells = table.rows[0].cells
    hdr_cells[0].text = 'Поле'
    hdr_cells[1].text = 'Тип данных'
    hdr_cells[2].text = 'Длина'
    hdr_cells[3].text = 'Описание'

    # Add data to the table
    cur.execute(
        f"SELECT column_name, data_type, character_maximum_length FROM information_schema.columns WHERE table_name = '{table_name}'")
    columns = cur.fetchall()

    for column in columns:
        row_cells = table.add_row().cells
        row_cells[0].text = column[0]
        row_cells[1].text = column[1]
        row_cells[2].text = str(column[2]) if column[2] is not None else ''

    # Add an empty paragraph to separate the tables
    doc.add_paragraph()
    # Add a page break
    doc.add_page_break()

# Save the document
doc.save('table_data111.docx')

# Close the cursor and connection
cur.close()
conn.close()