How to extract column names from SQL query using Python

Question

I would like to extract the column names of a resulting table directly from the SQL statement:


query = """

select 
    sales.order_id as id, 
    p.product_name, 
    sum(p.price) as sales_volume 
from sales
right join products as p 
    on sales.product_id=p.product_id
group by id, p.product_name;

"""

column_names = parse_sql(query)
# column_names:
# ['id', 'product_name', 'sales_volume']

Any idea what to do in parse_sql()? The resulting function should be able to recognize aliases and remove the table aliases/identifiers (e.g. "sales." or "p.").

Thanks in advance!

You could try the python-sqlparse library.

Barmar
– Barmar

2022-01-17 17:28:47 +00:00
Commented Jan 17, 2022 at 17:28 — Barmar
– Barmar, Commented Jan 17, 2022 at 17:28

Simon Hawe · Accepted Answer · 2022-01-17 17:35:12Z

5

I've done something like this using the library sqlparse. Basically, this library takes your SQL query and tokenizes it. Once that is done, you can search for the select query token and parse the underlying tokens. In code, that reads like

import sqlparse
def find_selected_columns(query) -> list[str]:
    tokens = sqlparse.parse(query)[0].tokens
    found_select = False
    for token in tokens:
        if found_select:
            if isinstance(token, sqlparse.sql.IdentifierList):
                return [
                    col.value.split(" ")[-1].strip("`").rpartition('.')[-1]
                    for col in token.tokens
                    if isinstance(col, sqlparse.sql.Identifier)
                ]
        else:
            found_select = token.match(sqlparse.tokens.Keyword.DML, ["select", "SELECT"])
    raise Exception("Could not find a select statement. Weired query :)")

This code should also work for queries with Common table expressions, i.e. it only return the final select columns. Depending on the SQL dialect and the quote chars you are using, you might to have to adapt the line col.value.split(" ")[-1].strip("`").rpartition('.')[-1]

answered Jan 17, 2022 at 17:35

Simon Hawe

4,62913 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hein Schnell Over a year ago

Nice, this does exactly what I was looking for. Thanks a lot! I was aware of sqlparse, but didn't know how to utilize it properly.

Martim Jan 31 at 14:45

this does not work as intended, sqlparse apparently splits columns into batches, so this function only returns a subset of columns.

Toby Mao · Accepted Answer · 2022-04-30 05:58:03Z

5

Try out SQLGlot

It's much easier and less error prone than sqlparse.

import sqlglot
import sqlglot.expressions as exp

query = """
select
    sales.order_id as id,
    p.product_name,
    sum(p.price) as sales_volume
from sales
right join products as p
    on sales.product_id=p.product_id
group by id, p.product_name;

"""

column_names = []

for expression in sqlglot.parse_one(query).find(exp.Select).args["expressions"]:
    if isinstance(expression, exp.Alias):
        column_names.append(expression.text("alias"))
    elif isinstance(expression, exp.Column):
        column_names.append(expression.text("this"))

print(column_names)

answered Apr 30, 2022 at 5:58

Toby Mao

5944 silver badges11 bronze badges

4 Comments

justanothertekguy Over a year ago

is there any way to get both the column name and alias?

Zoltan Over a year ago

yup, check the github readme, "metadata" section github.com/tobymao/sqlglot

Lilu Over a year ago

so, when the alias is set up as "select [AliasColumnName] = ColumnName from tble" the sqlglot does not see it. But it does work if the alias is set up as "select ColumnName AS AliasColumnName from tble". Is there a workaround it?

Max Lyman Aug 28 at 19:36

you can do it even cleaner with some list comp [col_obj.name or col_obj.alias for col_obj in parse_one(query).expressions]

Martim · Accepted Answer · 2025-01-31 14:53:15Z

0

Use sql_metadata, which leverages sqlparse:

from sql_metadata import Parser


def get_query_columns(query) -> dict[str, str]:
    return Parser(query).columns_aliases

edited Jan 31 at 14:53

answered Jan 31 at 14:47

Martim

1,16611 silver badges14 bronze badges

Collectives™ on Stack Overflow

How to extract column names from SQL query using Python

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related