寻求获取SQL查询中列与关联表的字典/键值对的实现方法

Great question! Since you’ve already got a working solution to extract tables from SQL queries, extending that to map each column to its associated table is totally feasible. Let’s break down how to do this effectively:

The key here is to link each column in your SELECT clause to either its explicit table alias (or full table name) and then map that alias to the actual table name you’ve already extracted. Here’s a step-by-step breakdown:

1. Build an Alias-to-Table Mapping #

First, leverage your existing table-extraction logic to create a dictionary that maps table aliases to their full table names. For your example query:

SELECT P.FirstName, P.LastName, P.BirthDate, P.DeathDate, VP.FullName FROM Presidents P INNER JOIN VicePresidents VP ON P.VicePresidentID = VP.VicePresidentID

Your mapping would look like this:

alias_to_table = {
    "P": "Presidents",
    "VP": "VicePresidents"
}

If a table doesn’t use an alias (e.g., SELECT FirstName FROM Presidents), the mapping uses the table name as both key and value.

2. Parse the SELECT Clause to Extract Columns #

Avoid writing your own regex for this—SQL syntax can get messy (think nested functions, quoted identifiers, aliased columns). Instead, use a battle-tested SQL parsing library. Below is a Python example using sqlparse, a lightweight but reliable parser:

import sqlparse
from sqlparse.sql import IdentifierList, Identifier
from sqlparse.tokens import Token

def map_columns_to_tables(sql_query, alias_table_map):
    # Parse the SQL query into a syntax tree
    parsed_sql = sqlparse.parse(sql_query)[0]
    
    # Locate the SELECT clause
    select_clause = None
    for token in parsed_sql.tokens:
        if token.ttype is Token.Keyword and token.value.upper() == "SELECT":
            # Skip past the "SELECT" keyword and any whitespace
            select_clause = token.next_token.next_token
            break
    
    column_table_mapping = {}
    
    # Handle multiple columns in the SELECT clause
    if isinstance(select_clause, IdentifierList):
        column_identifiers = select_clause.get_identifiers()
    else:
        column_identifiers = [select_clause]
    
    for identifier in column_identifiers:
        # Check if the column has a table alias prefix (e.g., P.FirstName)
        if hasattr(identifier, "parent") and identifier.parent is not None:
            alias = identifier.parent.value
            column_name = identifier.value
            # Map alias to full table name; fall back to alias if not found
            table_name = alias_table_map.get(alias, alias)
            column_table_mapping[column_name] = table_name
        else:
            # Handle columns without a prefix (ambiguous in multi-table queries)
            if len(alias_table_map) == 1:
                # Single table query: assume the only table is the source
                table_name = next(iter(alias_table_map.values()))
                column_table_mapping[identifier.value] = table_name
            else:
                # Multi-table query without prefix: mark as ambiguous
                column_table_mapping[identifier.value] = "Ambiguous (no table prefix specified)"
    
    return column_table_mapping

# Test with your example query
sample_sql = "SELECT P.FirstName, P.LastName, P.BirthDate, P.DeathDate, VP.FullName FROM Presidents P INNER JOIN VicePresidents VP ON P.VicePresidentID = VP.VicePresidentID"
sample_alias_map = {"P": "Presidents", "VP": "VicePresidents"}
result = map_columns_to_tables(sample_sql, sample_alias_map)
print(result)

Running this code will output exactly the mapping you want:

{
    'FirstName': 'Presidents',
    'LastName': 'Presidents',
    'BirthDate': 'Presidents',
    'DeathDate': 'Presidents',
    'FullName': 'VicePresidents'
}

3. Handle Edge Cases #

Real-world SQL can throw curveballs, so here’s how to tackle common edge cases:

• Aliased columns with functions: For something like SELECT CONCAT(P.FirstName, ' ', P.LastName) AS FullName FROM Presidents P, use a parser that can traverse nested expressions (like sqlglot for Python) to extract the underlying column and its table alias, then map the alias FullName to Presidents.
• Wildcards (*): If you encounter P.*, you’ll need to pull column names from your database’s metadata (e.g., query INFORMATION_SCHEMA.COLUMNS for the Presidents table) and map each of those columns to Presidents.
• Subqueries: Recursively apply your table-extraction and column-mapping logic to subqueries, as they may introduce additional tables and columns.

Final Notes #

Using a dedicated SQL parser is non-negotiable here—rolling your own regex will fail on complex queries. Libraries like sqlglot (Python) or JSqlParser (Java) offer full AST traversal, making it easier to handle even the most convoluted SQL.

内容的提问来源于stack exchange，提问作者Tim Southard