SQLParse-通配符LIKE条件的问题

Question

我专注于SQL语句的Where子句，并试图撤回Where子句中使用的所有列。下面是一个示例SQL:

sql_2 = """Select
PERS_ID
, STF_NO
, NAME 
FROM 
TEST T
WHERE T.JOIN_DT >= T.POSTING_DT'
AND T.PERS_ID LIKE '%123%'
AND T.LEAVE_DT BETWEEN CURRENT_DATE - 20 AND CURRENT_DATE - 1"""

我期望的列是T.JOIN_DT、T.POSTING_DT、T.PERS_ID和T.LEAVE_DT

from __future__ import print_function
import re
import sqlparse 
import numpy as np
from sqlparse.sql import IdentifierList, Identifier, Function, Where, Parenthesis, TokenList, Comparison, Operation
from sqlparse.tokens import Keyword, DML, Punctuation
sql_2 = """Select
PERS_ID
, STF_NO
, NAME 
FROM 
TEST T

WHERE T.JOIN_DT >= T.POSTING_DT'
AND T.PERS_ID LIKE '%123%'
AND T.LEAVE_DT BETWEEN CURRENT_DATE - 20 AND CURRENT_DATE - 1"""
parsed = sqlparse.parse(sql_2)[0]
where_columns = []
full_columns = []
for item in parsed.tokens:
if isinstance(item, Where):

for condition in item.tokens:

if isinstance(condition, Identifier):
where_columns.append(condition.get_parent_name())
where_columns.append(condition.get_real_name())
full_columns.append(where_columns)
where_columns = []


if isinstance(condition, Comparison):
for breakdown in condition.tokens:
if isinstance(breakdown, Identifier):
where_columns.append(breakdown.get_parent_name())
where_columns.append(breakdown.get_real_name())
full_columns.append(where_columns)
where_columns = []

print(full_columns)

它似乎将AND p.PERS_ID LIKE'%123'分组为一个令牌，我不确定如何进一步分解它以获得标识符p.PERS_ID。如果在for condition in item.tokens:之后添加print(condition)，你就会明白我的意思。

Answer 1

问题是在T.POSTING_DT 之后有一个尾随报价

然而，我的库SQLGlot能够轻松地提取出列。

import sqlglot
import sqlglot.expressions as exp
sql = """
Select
PERS_ID
, STF_NO
, NAME
FROM
TEST T
WHERE T.JOIN_DT >= T.POSTING_DT'
AND T.PERS_ID LIKE '%123%'
AND T.LEAVE_DT BETWEEN CURRENT_DATE - 20 AND CURRENT_DATE - 1
"""
for column in sqlglot.parse_one(sql).find(exp.Where).find_all(exp.Column):
print(column.text("this"))

CURRENT_DATE
CURRENT_DATE
LEAVE_DT
PERS_ID
POSTING_DT'
JOIN_DT