python解析sql_解析SQL以使用python提取列和表名

importitertoolsimportsqlparsefromsqlparse.sqlimportIdentifierList,Identifierfromsqlparse.tokensimportKeyword,DMLdefis_subselect(parsed):ifnotparsed.is_group():returnFalseforiteminparsed.tokens:ifitem.ttypeisDMLanditem.value.upper()=='SELECT':returnTruereturnFalsedefextract_from_part(parsed):from_seen=Falseprint'hi'foriteminparsed.tokens:ifitem.is_group():print'group'forxinextract_from_part(item):yieldxiffrom_seen:print'from'ifis_subselect(item):forxinextract_from_part(item):yieldxelifitem.ttypeisKeywordanditem.value.upper()in['ORDER','GROUP','BY','HAVING']:from_seen=FalseStopIterationelse:yielditemifitem.ttypeisKeywordanditem.value.upper()=='FROM':from_seen=Truedefextract_table_identifiers(token_stream):foritemintoken_stream:ifisinstance(item,IdentifierList):foridentifierinitem.get_identifiers():value=identifier.value.replace('"','').lower()yieldvalueelifisinstance(item,Identifier):value=item.value.replace('"','').lower()yieldvaluedefextract_tables(sql):# let's handle multiple statements in one sql stringextracted_tables=[]statements=(sqlparse.parse(sql))forstatementinstatements:# print statement.get_type()ifstatement.get_type()!='UNKNOWN':stream=extract_from_part(statement)printstream

extracted_tables.append(set(list(extract_table_identifiers(stream))))returnlist(itertools.chain(*extracted_tables))# strsql = """# SELECT p.product_name, inventory.quantity# FROM products p join inventory# ON p.product_id = inventory.product_id;# """strsql="""SELECT suppliers.supplier_name, subquery1.total_amt

FROM suppliers

,

(SELECT supplier_id, SUM(orders.amount) AS total_amt

FROM orders

GROUP BY supplier_id) subquery1

WHERE subquery1.supplier_id = suppliers.supplier_id;"""extract_tables(strsql)