How to parse SQL syntax, tokenize SQL in Python

Question

I need to parsing SQL syntax to get tables name in FROM and WHERE keyword if exists (root level, include sub query). For example:

Select * 
---
From a
Inner join b on a.c = b.c
---
Where a.o in (
  select o 
  from z
  where z.l in (
      select l 
      from a
  )
)

Output: From = [a,b], Where = [z,a]

I have to process all of type: select, insert, update, delete, select into, create

I have some idea such as:

tokenlize sql statement
find index of root FROM, root WHERE if exists (include subquery)
split query to 2 part: from FROM to WHERE and from WHERE to end
for each, find table name with regrex or some libraries

I try some libraries: sqlparse, sql_metadata, moz_sql_parser. But it is failed when query is complex. With sql_metadata, i will missing table name when comment -- exists, if remove comment with sqlparse, i will miss ')' somewhere... etc.

How to split part of FROM and part of WHERE How to find table name in each

How to resolve it? Thank you

This blog should help you in a way, grisha.org/blog/2016/11/14/table-names-from-sql — Nishant Patel
– Nishant Patel, Commented Dec 9, 2020 at 5:26

rfii · Accepted Answer · 2020-12-20 03:58:47Z

Harvard's CS50 web class has the following code which I think helps you in it's last two lines. Basically call upon the required result with result.field

  import os

  from sqlalchemy import create_engine
  from sqlalchemy.orm import scoped_session, sessionmaker

  engine = create_engine(os.getenv("DATABASE_URL")) # database engine object from SQLAlchemy that manages connections to the database
                                                    # DATABASE_URL is an environment variable that indicates where the database lives
  db = scoped_session(sessionmaker(bind=engine))    # create a 'scoped session' that ensures different users' interactions with the
                                                    # database are kept separate

  flights = db.execute("SELECT origin, destination, duration FROM flights").fetchall() # execute this SQL command and return all of the results
  
  for flight in flights
          print(f"{flight.origin} to {flight.destination}, {flight.duration} minutes.") # for every flight, print out the flight info

Collectives™ on Stack Overflow

How to parse SQL syntax, tokenize SQL in Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related