CODEWITHSUNDEEP
python-3.xsql-parser
Prasad
Prasad

Reputation: 89

How to parse any SQL get columns names and table name using SQL parser in python3

I am able to get the column names and table name from using sql parse for only simple select SQL's.

Can somebody help how can get the column names and table name from any complex SQL's.

Upvotes: 4

Views: 12864

Answers (2)

dlink
dlink

Reputation: 1595

Here is a solution for extracting column names from complex sql select statements. Python 3.9

import sqlparse

def get_query_columns(sql):
    stmt = sqlparse.parse(sql)[0]
    columns = []
    column_identifiers = []

    # get column_identifieres
    in_select = False
    for token in stmt.tokens:
        if isinstance(token, sqlparse.sql.Comment):
            continue
        if str(token).lower() == 'select':
            in_select = True
        elif in_select and token.ttype is None:
            for identifier in token.get_identifiers():
                column_identifiers.append(identifier)
            break

    # get column names
    for column_identifier in column_identifiers:
        columns.append(column_identifier.get_name())

    return columns

def test():
    sql = '''
select
   a.a,
   replace(coalesce(a.b, 'x'), 'x', 'y') as jim,
   a.bla as sally  -- some comment
from
   table_a as a
where
   c > 20
'''
    print(get_query_columns(sql))

test()
# outputs: ['a', 'jim', 'sally']

Upvotes: 3

root user
root user

Reputation: 61

This is how you print the table name in sqlparse

1) Using SELECT statement

>>> import sqlparse
>>> print([str(t) for t in parse[0].tokens if t.ttype is None][0])
'dbo.table'

(OR)

2) Using INSERT statement:

def extract_tables(sql):
    """Extract the table names from an SQL statment.
    Returns a list of (schema, table, alias) tuples
    """
    parsed = sqlparse.parse(sql)
    if not parsed:
        return []

    # INSERT statements must stop looking for tables at the sign of first
    # Punctuation. eg: INSERT INTO abc (col1, col2) VALUES (1, 2)
    # abc is the table name, but if we don't stop at the first lparen, then
    # we'll identify abc, col1 and col2 as table names.
    insert_stmt = parsed[0].token_first().value.lower() == "insert"
    stream = extract_from_part(parsed[0], stop_at_punctuation=insert_stmt)
    return list(extract_table_identifiers(stream))

The column names may be tricky because column names can be ambiguous or even derived. However, you can get the column names, sequence and type from virtually any query or stored procedure.

Until FROM keyword is encountered, all the column names are fetched.

def parse_sql_columns(sql):
    columns = []
    parsed = sqlparse.parse(sql)
    stmt = parsed[0]
    for token in stmt.tokens:
        if isinstance(token, IdentifierList):
            for identifier in token.get_identifiers():
                columns.append(str(identifier))
        if isinstance(token, Identifier):
            columns.append(str(token))
        if token.ttype is Keyword:  # from
            break
    return columns

Upvotes: 0

Related Questions