Programmatic creation / modification of SQL

[darugar]

I'm looking for a way to programmatically modify SQL statements: given a large number of existing SQL statements, I'd like to analyze the statements and conditionally modify them - for example, if the SQL includes a particular table, then add a filter to the WHERE clause to limit it to particular date ranges, to implement soft deletes, etc. So: analyze SQL, if certain conditions based on presence of tables and columns are met, modify the SQL to include more clauses.

Is this a reasonable use of sqpglot, and if so are there any relevant examples / code samples I could refer to?

[georgesittas]

Hey,

Yes, this is a reasonable use of SQLGlot. There's a dedicated section in the README for building/modifying SQL. In most cases, you can simply traverse the AST and apply any transformations you want, either manually or using some helpers (e.g. find, find_all, transform, walk etc).

For your example, I'd do something like:

import sqlglot

expression = sqlglot.parse_one(query)
tables = expression.find_all(sqlglot.exp.Table)

if any(table.alias_or_name == target_table_name for table in tables):
    expression.where(some_condition, copy=False)  # some_condition can be either an exp.Expression or a string.

Let me know if you have any question and I'll try to help.