1

I am trying to parse all the queries executed by users (within a period of time) in PostgreSQL DB (by querying the pg_stat_statements table) and trying to create a report of which tables are used by users to run either a Select or an Insert or a Delete query. Basically running something like Select query, queryid, userid from pg_stat_state and then parsing each query to check if it was a Select or an Insert or a Delete query and also extract the table_Name from the query.

I am using sqlparse python module but very new to it so need help. I am able to get the table name by using something like:

import sqlparse
from sqlparse.sql import Where, Comparison, Parenthesis, Identifier

for tokens in sqlparse.parse(sql_statement)[0]:
    if isinstance(tokens, Identifier):
        print(str(tokens))

but not sure how to get the type of statement (Select/Insert/Delete) together with the name of the table. Also, need to incorporate COPY statements as Selects too.

I tried using psqlparse but I did not see much info/help online regarding this module.

Please suggest.

Thanks.

P_Ar
  • 377
  • 2
  • 9
  • 25

1 Answers1

0

This is not trivial, and I don't think sqlparse really helps very much. INSERT and DELETE are pretty easy, because they usually start out "INSERT INTO table" and "DELETE FROM table", but "SELECT" is the wild wild west. Clearly the tables will be mentioned in a FROM clause, but it could be "FROM table1 t1, table t2, table t3 WHERE" or "FROM table t1 LEFT INNER JOIN table t2 LEFT INNER JOIN table t3 WHERE". You might have nested queries, and a SELECT doesn't even have to have a table. Plus, there could be UNIONs that mention further tables. And, of course, "SELECT INTO" is just another way of doing "INSERT". I believe you should start out just doing text processing, looking for the key words. You might get far enough.

Tim Roberts
  • 48,973
  • 4
  • 21
  • 30