bug for example extract table name

[shyandsy]

it doesnt work for the example code to extract table name

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName FROM (
(Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID) 
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
)

it get nothing https://github.com/andialbrecht/sqlparse/blob/master/examples/extract_table_names.py

[MarkBell920]

The example gets the table aliases not the table name. I don't know if it is a mistake with the example or a bug.

Mark

[turkishmaid]

Can be reproduced with this simple select:

extract_tables("select * from table1 as t")  # produces ['t'], not ['table1']

Any idea how to fix that?

[shyandsy]

no it get nothing @MarkBell920 @turkishmaid

import sqlparse
from sqlparse.sql import IdentifierList, Identifier
from sqlparse.tokens import Keyword, DML


def is_subselect(parsed):
    if not parsed.is_group:
        return False
    for item in parsed.tokens:
        if item.ttype is DML and item.value.upper() == 'SELECT':
            return True
    return False


def extract_from_part(parsed):
    from_seen = False
    for item in parsed.tokens:
        if from_seen:
            if is_subselect(item):
                yield from extract_from_part(item)
            elif item.ttype is Keyword:
                return
            else:
                yield item
        elif item.ttype is Keyword and item.value.upper() == 'FROM':
            from_seen = True


def extract_table_identifiers(token_stream):
    for item in token_stream:
        if isinstance(item, IdentifierList):
            for identifier in item.get_identifiers():
                yield identifier.get_name()
        elif isinstance(item, Identifier):
            yield item.get_name()
        # It's a bug to check for Keyword here, but in the example
        # above some tables names are identified as keywords...
        elif item.ttype is Keyword:
            yield item.value


def extract_tables(sql):
    stream = extract_from_part(sqlparse.parse(sql)[0])
    return list(extract_table_identifiers(stream))


if __name__ == '__main__':
    sql = """
    SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName FROM (
(Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID) 
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
)
    """

    tables = ', '.join(extract_tables(sql))
    print('Tables: {}'.format(tables))

[jason9797]

Can be reproduced with this simple select:

extract_tables("select * from table1 as t")  # produces ['t'], not ['table1']

Any idea how to fix that?

Change yield item.get_name() to item.normalized full example as below

import sqlparse
from sqlparse.sql import IdentifierList, Identifier
from sqlparse.tokens import Keyword, DML


def is_subselect(parsed):
    if not parsed.is_group:
        return False
    for item in parsed.tokens:
        if item.ttype is DML and item.value.upper() == 'SELECT':
            return True
    return False


def extract_from_part(parsed):
    from_seen = False
    for item in parsed.tokens:
        if from_seen:
            if is_subselect(item):
                yield from extract_from_part(item)
            elif item.ttype is Keyword:
                return
            else:
                yield item
        elif item.ttype is Keyword and item.value.upper() == 'FROM':
            from_seen = True


def extract_table_identifiers(token_stream):
    for item in token_stream:
        if isinstance(item, IdentifierList):
            for identifier in item.get_identifiers():
                yield identifier.get_name()
        elif isinstance(item, Identifier):
            # print(item.get_name(), item.normalized)
            # yield item.get_name() # get alias name
            yield item.normalized # get token normalized
        # It's a bug to check for Keyword here, but in the example
        # above some tables names are identified as keywords...
        elif item.ttype is Keyword:
            yield item.value


def extract_tables(sql):
    stream = extract_from_part(sqlparse.parse(sql)[0])
    return list(extract_table_identifiers(stream))


if __name__ == '__main__':
    sql = """
select * from table1 as t
    """

    tables = ', '.join(extract_tables(sql))
    print('Tables: {}'.format(tables))