Support for custom filters

[zdk123]

First, thanks for developing this excellent package. It does nearly everything I need.

However, I would like to create a custom filter. It found it easy enough to write my own bind method and extend the JinjaSql class to add the filter in a custom _prepare_environment method.

The remaining issue is that in the provided SqlExtension class, the built-in filters are hard coded as a tuple of strings within the filter_stream method. One can extend or re-write SqlExtension's filter_stream function anyway ... but this is an ugly hack and not future-proof.

My proposal is to provide the filter names as a class attribute and keep only 'bind' hardcoded. I realize that this may create undesired security risk, but clearly this is possible anyway with minimal effort.

I can provide a pull request and an example use-case if there is any interest in this.

[sripathikrishnan]

Hey, thanks for writing a detailed issue! And my apologies for responding so late - missed the notification!

Just to be clear (and I think you already understand it), you can create custom filters, add them to an environment and then pass it to JinjaSQL constructor without having to create a new class. You don't need to override filter_stream unless you want to auto-apply the filters. You can, for example, invoke the filter directly when you write your SQL query template.

I am assuming you want to auto-apply the filter without having to invoke it in the SQL query template. If that's true, can you give me some use cases where that would be useful? When I wrote this tool, I didn't think of any use case that would require parsing the stream - but perhaps I was short sighted :)

I'd be happy to generalize this, but before I do that - it'd be nice to understand your use case.

Thanks!

[zdk123]

My use-case is for a CREATE TABLE template, where the data context is a python dictionary with the schema, such as: schema = {'header1': 'INT', 'header2':'STRING'}.

the template

tmpl="""CREATE TABLE {{tablename}} {{ schema | schemadict }}"""

The raw SQL should result in:

CREATE TABLE table1 (
  header1 INT,
  header2 STRING
)

An example bind function

## Custom bind and filter method for printing dicts as schemas
def bind_schema_dict(value):
  from jinjasql.core import _bind_param
  assert isinstance(value, dict), "input is not a dictionary"
  results = []
  for item in value.items():
    for i in item:
      results.append(_bind_param(_thread_local.bind_params, "schemadict", i))

  r1, r2 = results[::2], results[1::2]
  sep = "\n  "
  sep2 = "," + sep
  clause = sep2.join(["%s %s" %d for d in zip(r1, r2)])
  return "(" + sep + clause + "\n)"

My typical use case is something like:

from jinjasql import JinjaSql
from jinjasql.core import  _thread_local
j = JinjaSql()
j.env.filters['schemadict'] = bind_schema_dict
query, bind_params = j.prepare_query(tmpl, {'tablename':'table1', 'schema':schema})

Unfortunately, the clauses returned from bind_schema_dict get caught up in the bind_params values rather than making it into the template.

I understand one can pass an entirely new jina2 environment to the JinjaSQL constructor, but I wouldn't want to lose the inclause and sqlsafe filters.

I would be very interested in an alternative to parsing the stream, but I am not imaginative enough to see how this works.

thanks!

[sripathikrishnan]

For create table use case, I don't think you need to write any extra code. Here is how I would do it -

from jinjasql import JinjaSql
j = JinjaSql()

template = """
CREATE TABLE {{ table_name | sqlsafe }} (
{% for col in cols %}
    {{ col.name | sqlsafe }} {{ col.data_type | sqlsafe }} {% if not loop.last %},{% endif %}
{%- endfor %}
)
"""

data = {
    "table_name": "person",
    "cols": [
        {"name": "id", "data_type": "int"},
        {"name": "first_name", "data_type": "varchar"},
        {"name": "age", "data_type": "int"},
    ]
}

query, bind_params = j.prepare_query(template, data)
print(query)

If you think about it, I've just moved the for loop from python code into the jinja template. This makes it easier to work with the query.

Also, you can create a new environment and pass it to JinjaSQL. JinjaSQL will then add the inclause and sqlsafe filters automatically to the environment you provide.

Does this help?

[zdk123]

Great... these are both good alternatives. Thanks!