Как заменить полные имена на их псевдонимы «первые буквы» в стандартном выводе SQL-запроса MS Access, который не имеет псевдонимов?

Код Python для получения двух новых столбцов (New SQL Codes и Mapping) в конце входного файла Excel и вывода их в качестве нового выходного файла:

import pandas as pd
import re

# Define SQL keywords
SQL_KEYWORDS = {
    'SELECT', 'FROM', 'WHERE', 'JOIN', 'INNER', 'OUTER', 'LEFT', 'RIGHT', 
    'ON', 'AND', 'OR', 'GROUP', 'BY', 'ORDER', 'DESC', 'ASC', 'HAVING', 
    'INSERT', 'UPDATE', 'DELETE', 'VALUES', 'SET', 'CREATE', 'DROP', 
    'ALTER', 'TABLE', 'VIEW', 'AS', 'DISTINCT', 'LIKE', 'IN', 'BETWEEN', 
    'EXISTS', 'NULL', 'IS', 'CASE', 'WHEN', 'THEN', 'ELSE', 'END', 
    'UNION', 'ALL', 'LIMIT', 'OFFSET', 'SELECTED', 'EXPLAIN', 'WITH', 
    'INTO'
}

def is_valid_object_name(full_name):
    """Checks if the object name is a table or view."""
    return full_name.upper() not in SQL_KEYWORDS

def generate_short_name(full_name, existing_aliases):
    """Generates a short name from the full table name and ensures it is unique."""
    parts = full_name.split('_')
    short_name = ''.join(part[0] for part in parts if part)  # Generate abbreviation
    short_name = short_name.lower()

    # Ensure uniqueness
    if short_name in existing_aliases:
        count = 2
        new_short_name = f"{short_name}{count}"
        while new_short_name in existing_aliases:
            count += 1
            new_short_name = f"{short_name}{count}"
        short_name = new_short_name

    existing_aliases.add(short_name)
    return short_name

def process_queries(queries, from_clauses):
    """Processes the SQL queries and replaces the table names with aliases."""
    all_output_queries = []
    all_short_names = []

    first_query_processed = False  # Flag to track the first query

    for query, from_clause in zip(queries, from_clauses):
        if query and not first_query_processed:  # Debugging only for the first filled query
            print("\n=== Debugging Output for the First Query == = ")
            print("Original Query:", query)
            print("FROM Clause:", from_clause)

        short_names = {}
        existing_aliases = set()  # Collection for already used aliases
        output_query = query.strip()

        # Regex to identify table names and views (refined)
        found_tables = re.findall(r'\b(?!(INNER|JOIN|ON|AND|OR|WHERE|GROUP|ORDER|ASC|DESC)\b)(\w+(?:_\w+)?)\b', from_clause)
        found_tables = [name[1] for name in found_tables]  # Only extract actual table names
        
        if not first_query_processed:  # Debug information only for the first query
            print("Found Tables:", found_tables)  # Output of found tables

        for full_name in found_tables:
            if is_valid_object_name(full_name) and full_name not in short_names:
                short_name = generate_short_name(full_name, existing_aliases)
                short_names[full_name] = short_name

        if not first_query_processed:  # Debug information only for the first query
            print("Generated Aliases:", short_names)  # Output of generated aliases

        # Inserting aliases in the FROM block
        for full_name, short_name in short_names.items():
            if re.search(r'\b' + re.escape(full_name) + r'\s+' + re.escape(short_name) + r'\b', from_clause) is None:
                from_clause = re.sub(
                    r'\b' + re.escape(full_name) + r'\b',
                    f'{full_name} {short_name}',  # Full name and alias
                    from_clause, count=1
                )
                if not first_query_processed:  # Debug information only for the first query
                    print(f"Inserted: {full_name} with Alias {short_name}")

        if query:
            # Replacing table names with aliases in the SELECT query
            for full_name, short_name in short_names.items():
                output_query = re.sub(r'\b' + re.escape(full_name) + r'\b', short_name, output_query)

            if not first_query_processed:  # Debug information only for the first query
                print("Query after Alias Replacements:", output_query)

            # Inserting the FROM block into the complete query
            output_query = re.sub(r'\bFROM\b.*', f'FROM {from_clause}', output_query, count=1)

            if not first_query_processed:  # Debug information only for the first query
                print("Query after Adding the FROM Clause:", output_query)

            # Column names prefixed with table names should not be changed
            for full_name, short_name in short_names.items():
                output_query = re.sub(r'\b' + re.escape(full_name) + r'\.', f'{short_name}.', output_query)

            if not first_query_processed:
                print("Processed Query:", output_query)
                first_query_processed = True  # Set the flag after processing the first query

            all_output_queries.append(output_query)  # Add the processed query
            all_short_names.append(short_names)       # Add the abbreviations

    return all_output_queries, all_short_names

# Read the Excel file
input_file = 'input_file.xlsx'  # Path to the input file
output_file = 'output_file.xlsx'  # Path to the output file

# Load the Excel file and read relevant data
df = pd.read_excel(input_file)

# Extract SQL queries from column H (index 7) and the FROM clause from column L (index 11)
raw_queries = df.iloc[:, 7].dropna().tolist()  # Column H
from_clauses = df.iloc[:, 11].dropna().tolist()  # Column L

# Process queries
processed_queries, abbreviations = process_queries(raw_queries, from_clauses)

# Add new SQL codes and mappings to DataFrame
df['New SQL Codes'] = ''
df['Mapping'] = ''

processed_index = 0  # Index for processed queries
for i in range(len(df)):  # Loop through original queries
    query = df.at[i, 'Abfrage_SQL']
    if isinstance(query, str) and query:  # Check
        df.at[i, 'New SQL Codes'] = processed_queries[processed_index]

        # Create mapping only for table names, excluding column names
        mapping_str = ', '.join(f"{k}: {v}" for k, v in abbreviations[processed_index].items()
                                 if is_valid_object_name(k) and not '.' in k)  # Only use valid table names

        df.at[i, 'Mapping'] = mapping_str.strip()  # Prepare mapping

        processed_index += 1  # Only increment for processed queries

# Save the new data to an Excel file
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
    df.to_excel(writer, index=False, sheet_name='SQL_Queries')

print(f'Data has been saved in {output_file}.')

В столбце New SQL Codes перечислены все новые «псевдонимы» SQL-запросы. В столбце Mapping перечислены все таблицы и представления с их псевдонимами, поэтому:

SELECT my_random_table.*, my_raw_tbl.* 
FROM my_random_table 
INNER JOIN my_raw_tbl 
ON my_random_table.id = my_raw_tbl.random_tbl_id

..становится:

SELECT mrt.*, mrt2.* 
FROM my_random_table mrt 
INNER JOIN my_raw_tbl mrt2 
ON mrt.id = mrt2.random_tbl_id