Performing regex search on a sql query string python 3.x

Question

I would like to find out if a select some_col_name, * , some_col_name from table_name occurs in a query string like example below:

select some_col_name, *, some_col_names
from table_name

or

select some_col_name, 
*, some_col_name
from 
table_name

So far, I am only able to match select some_col_names, * using

re.search("select(\s*.*?)*\*", query_string)

How do I match the desired expression? I would like to stop after matching the first occurrence.

This smacks of an XY problem. Why do you want to do this? It's not that it's impossible, but it's probably not the right way to do whatever it is you're trying to do. — Adam Smith
– Adam Smith, Commented Feb 22, 2019 at 21:30
Do you want to find all queries that select * as a column? — Bohemian
– Bohemian ♦, Commented Feb 22, 2019 at 21:30
Given a query, I want to know if a user tried to select all columns in a specific table_name (using select * as opposed to mentioning the column name). But note that the query string could have multiple select statements because of any use of WITH. — cidi30bg
– cidi30bg, Commented Feb 22, 2019 at 22:26

Bohemian · Accepted Answer · 2019-02-22 22:40:59Z

1

Try this regex:

select([^*](?!from))*\*([^*](?!from))* from[\s\r\n]*table_name

See live demo.

This will match over multiple lines, but due to the negative look aheads won’t skip from the select of one query to the from of a later query.

edited Feb 22, 2019 at 22:40

answered Feb 22, 2019 at 21:37

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

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

2 Comments

cidi30bg Over a year ago

Thanks @Bohemian, but I want to also add a specific table name to the match. You example matches only up to from. In addition, I want to only stop at the first occurrence.

Bohemian Over a year ago

@obiigbe91 I’ve added the table name to the regex. If you want to match only first, see this answer for how.

Collectives™ on Stack Overflow

Performing regex search on a sql query string python 3.x

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related