如何从SQL查询中提取表名和列名？

如何从SQL查询中提取表名和列名？

确实，这不是一件容易的事。您可以使用词法分析器（在此示例中为ply）并定义一些规则以从字符串中获取多个标记。以下代码为SQL字符串的不同部分定义了这些规则，并将它们放在一起，因为输入字符串中可能会有别名。结果，您将获得一个

result

以不同表名作为键的字典（）。

import ply.lex as lex, retokens = (    "TABLE",    "JOIN",    "COLUMN",    "TRASH")tables = {"tables": {}, "alias": {}}columns = []t_TRASH = r"Select|on|=|;|s+|,|t|r"def t_TABLE(t):    r"froms(w+)sass(w+)"    regex = re.compile(t_TABLE.__doc__)    m = regex.search(t.value)    if m is not None:        tbl = m.group(1)        alias = m.group(2)        tables["tables"][tbl] = ""        tables["alias"][alias] = tbl    return tdef t_JOIN(t):    r"inners+joins+(w+)s+ass+(w+)"    regex = re.compile(t_JOIN.__doc__)    m = regex.search(t.value)    if m is not None:        tbl = m.group(1)        alias = m.group(2)        tables["tables"][tbl] = ""        tables["alias"][alias] = tbl    return tdef t_COLUMN(t):    r"(w+.w+)"    regex = re.compile(t_COLUMN.__doc__)    m = regex.search(t.value)    if m is not None:        t.value = m.group(1)        columns.append(t.value)    return tdef t_error(t):    raise TypeError("Unknown text '%s'" % (t.value,))    t.lexer.skip(len(t.value))# here is where the magic startsdef mylex(inp):    lexer = lex.lex()    lexer.input(inp)    for token in lexer:        pass    result = {}    for col in columns:        tbl, c = col.split('.')        if tbl in tables["alias"].keys(): key = tables["alias"][tbl]        else: key = tbl        if key in result: result[key].append(c)        else: result[key] = list() result[key].append(c)    print result    # {'tb1': ['col1', 'col7'], 'tb2': ['col2', 'col8']}string = "Select a.col1, b.col2 from tb1 as a inner join tb2 as b on tb1.col7 = tb2.col8;"mylex(string)