sripathikrishnan / jinjasql Goto Github PK

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.

There is a circular dependency on Jinja2 while importing version from jinjasql/init.py in setup.py.
We need to externalize the version somewhere else.

After run a test suite in my project, I got the following warning:

DeprecationWarning: The 'autoescape' extension is deprecated and will be removed in Jinja 3.1. This is built in now.

Looking in the Jinja documentation (changelog), I confirmed the message. So, I believe the self.env.add_extension('jinja2.ext.autoescape') in the _prepare_environment method (on core.py file) isn't necessary and should be removed.

Hello!
After I've upgraded to version 0.1.8, now my ETL job is failing with the traceback below:

Traceback (most recent call last):
  File "C:\Users\<some_user>\apps\python38\envs\prefect_dev\lib\site-packages\pandas\io\sql.py", line 1697, in execute
    cur.execute(*args, **kwargs)
  File "C:\Users\<some_user>\apps\python38\envs\prefect_dev\lib\site-packages\jaydebeapi\__init__.py", line 532, in execute
    self._set_stmt_parms(self._prep, parameters)
  File "C:\Users\<some_user>\apps\python38\envs\prefect_dev\lib\site-packages\jaydebeapi\__init__.py", line 523, in _set_stmt_parms
    prep_stmt.setObject(i + 1, parameters[i])
com.ibm.db2.jcc.am.com.ibm.db2.jcc.am.SqlSyntaxErrorException: com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][10145][10844][4.15.146] Invalid parameter 1: Parameter index is out of range. ERRORCODE=-4461, SQLSTATE=42815

This is a codebase that is at least a few years old and nothing was changed, so I have no idea what the root cause could be. So out of desperation, I just removed the comments from the SQL query, and now it no longer errors. I am not sure if it is due to jinjasql or perhaps jinja2 or the other libraries I'm using. I am just connecting to a mainframe DB2 server using jaydebeapi library (JDBC).

Below is my heavily modified, redacted SQL with the comments roughly in the same location as they are in the original SQL. I am using just the single line comment style (-- my comments):

SELECT DISTINCT
    -- Renaming columns to match query with other query to help facilitate
    -- column to column comparisons between this database and the other database 
    'BLAH' AS SOURCE,
    RTRIM(BLAH.BLAH_BLAH) AS "PLANT-CD",
    BLAH.BLAH_BLAH AS "SUPP-CD",
    BLAH.BLAH_BLAH AS "SUPP-LOC",
    RTRIM(BLAH.PART_NO) AS "PART-NO",
    -- our part # does not have space in the middle
    REPLACE(RTRIM(BLAH.BLAH_BLAH), ' ', '') AS "PART-NO-NOSPACE",
    BLAH.BLAH_BLAH AS "BLAH_BLAH",
    
    -- Create ROW_NUM column to identify duplicate records at the
    -- plant, supplier code, supplier location, container type, and part number level
    row_number() OVER(PARTITION BY RTRIM(BLAH.PLANT_CD), RTRIM(BLAH.BLAH_BLAH), BLAH.BLAH_BLAH, BLAH.BLAH_BLAH, RTRIM(BLAH.BLAH_BLAH)
        ORDER BY BLAH.BLAH_BLAH ASC, BLAH.BLAH_BLAH DESC) AS ROW_NUM

FROM
    {{ plant_code3 | sqlsafe }}32.BLAH AS BLAH_BLAH
    
    -- To obtain supplier location code
    LEFT JOIN {{ plant_code3 | sqlsafe }}32.BLAH AS BLAH_BLAH ON
        AND BLAH.EXPIRE_DT > INT(REPLACE(CHAR(CURRENT DATE - 1 DAY, ISO),'-',''))
        
WHERE
    -- Ensure effective date <= today's date and expire date > today's date
    -- Can alternatively use HEX(CURRENT DATE) to obtain current date in YYYYMMDD format
    BLAH_BLAH.EFCT_DT <= (SELECT VARCHAR_FORMAT(CURRENT TIMESTAMP, 'YYYYMMDD') FROM SYSIBM.SYSDUMMY1)
    PLANT_CD = {{ plant_code }}
FOR READ ONLY WITH UR

OS: Windows 10
Python version: 3.8.10
jinja2 version: 3.0.1
jinjasql version: 0.1.8
jaydebeapi version: 1.2.3
database: IBM DB2 z/OS v10.5

I'm trying to insert some JSON data into my database through JinjaSQL and Psycopg2. Though I'm getting stopped by JinjaSQL as it has the following check: https://github.com/hashedin/jinjasql/blob/93c9bbceedd45c2439a9b3698e9bddfc5df61958/jinjasql/core.py#L104

If we bypass this check and register an adaptor for dictionaries Psycopg2 will gladly eat the dictionary.

from psycopg2.extras import Json
register_adapter(dict, Json)

createAreaTemplate="""
  insert into  area_revisions (                                                                 
    area, modified_by, geo_json )                                                               
  values (                                                                                      
    {{ uuid }}, {{ modifiedBy }}, {{ geoJson }} )
"""

area = {
    "uuid":            uuid4(),
    "modifiedBy":      author['uuid'],
    "project":         parentProject['uuid'],
    "geoJson":         ""
}

query, params = prepareQuery(createAreaTemplate, area)
a = list(params)
a[2] = {}
cursor.execute(query, parameters)

Would it be possible to remove the is_dictionary check?

Usecase

Using python i am writing an aws lambda which interacts with redshift data api especially the BatchExecuteStatement operation. If you observe this api takes in list of SQL queries without any params.. meaning that the query should also have parameters within it as a single string

• https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/redshift-data.html#RedshiftDataAPIService.Client.batch_execute_statement
• https://docs.aws.amazon.com/redshift-data/latest/APIReference/API_BatchExecuteStatement.html

Hello,
I was just trying to use jinjasql when binding the same parameter twice, which breaks the named prepare query function.
Would you mind making a release v0.1.7 and then pushing that to pypi?

It would be nice to have a mode for rendering SQL arguments with %(name)s placeholders, with the names coming from the template and the bind parameters return value as dictionary instead of list.

http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

select username, sum(spend)
from transactions
where start_date > {{request.start_date}}
and end_date < {{request.end_date}}
{% if request.organization %}
and organization = {{request.organization}}
{% endif %}

select username, sum(spend)
from transaction
where start_date > %(start_date)s
and end_date < %(end_date)s
and organization = %(organization)s

It may be that only some database drivers can use named placeholders. It should be possible to configure this mode as default when instantiating JinjaSQL so templates and subsequent API calls don't need to pass that detail around:

JinjaSql(named_placeholders=True)

Given the example query

drop table if exists {{population_table}};
create table {{population_table}} as....;

If I call:
query, bind_params = j.prepare_query(templ, params_dict)
and then
engine.execute(query, bind_params)
I get the error that there aren't enough bind_params to substitute into query. I believe this is because I've included the {{population_table}} variable twice in the template.

How could I go about fixing this? (It might be true that I should execute those queries separately, but it's also valid to have a case where there are several of the same substitution in one query).

Jinja2 moved deprecated objects to Markupsafe. This is causing 'import jinjasql' to break.

If possible, could you please

• update the dependencies
• update the code to use the relocated objects
• release the changes to PyPi

Thanks

Again, might just be an issue pertaining to sqlite3?

When preparing the query, the bind parameter is type ordered_dict. Executing the query and this bind parameter in sqlite3 raises a ValueError: parameters are of unsupported type exception. However, when we turn the bind parameter into a list (list(bind_parameter)), sqlite3 will execute the query just fine.

I wrote a gist to reproduce this bug.

named and pyformat create random variable names to avoid conflicts. As a result, navie test cases will fail. Need to mock Random() object or seed it so that tests are deterministic.

When I attempt to render a template with 500 to 1000 bind_params performance falls off a cliff. Any insights as to where I could look to start trying to find the slow down? Can profile, but figured you might have some insider knowledge

Hi, I have a template string, e.g.

SELECT *
FROM users
WHERE name in {{ names | inclause }}
AND age > {{ min_age }}

Is there a way to get the list of all variables present in the template? i.e. something that would return ["names", "min_age"].

Thanks!

Really like this package. However I've stumbled upon an issue. I'm trying to create queries for dynamic tables and have to use the sqlsafe filter to do so. However, sqlfluff is then complaing that it cannot find the filter. Perhaps there's a way around this?

Failure to parse jinja template: no filter named 'sqlsafe'

Sample bigquery query that generates this issue for me

SELECT *
FROM `{{ project | sqlsafe }}.{{ dataset | sqlsafe }}.{{ table | sqlsafe }}`

Just based on this section:

JinjaSQL is not meant to replace your ORM. ORMs like those provided by SQLAlchemy or Django are great for a variety of use cases, and should be the default in most cases. But there are a few use cases where you really need the power of SQL.

Use JinjaSQL for -

Reporting, business intelligence or dashboard like use cases
When you need aggregation/group by
Use cases that require data from multiple tables
Migration scripts & bulk updates that would benefit from macros
In all other use cases, you should reach to your ORM instead of writing SQL/JinjaSQL.

It would be good to know why this would be better than SQLAlchemy (I'm less familiar with Django's ORM) - some examples: SQLAlchemy has Group By support, you can get data from multiple tables using it, and you can dynamically build queries using it. Also it has the core API as a lower level SQL DSL.

Thanks!

Hello,

I'm having trouble understanding how I should use JinjaSQL to make a template to insert values. For example, the following doesn't work. I know the problem is with my VALUES section, but I'm not sure how I should change it. Please could you help? Thank you

def add_entry(self, entry):
        add_entry_template = '''INSERT INTO
                                    cards(title,
                                          name,
                                          address,
                                          date_added)
                                VALUES(title,
                                       name,
                                       address,
                                       date_added)'''
        params = { 'title': entry.title,
                   'name': entry.name,
                   'address': entry.address,
                   'date_added': entry.date_added }
        cursor = self.execute_sql(add_entry_template, params)

def execute_sql(self, template, params):

        j = JinjaSql(param_style='named')
        query, bind_params = j.prepare_query(template, params)

        ...

Hello,

After some investigation, I found a thread stuck here :

Thread 0x00007fec7f9e2700 (most recent call first):
  File "/usr/local/lib/python3.8/site-packages/jinjasql/core.py", line 125 in _bind_param
  File "/usr/local/lib/python3.8/site-packages/jinjasql/core.py", line 116 in bind_in_clause
  File "<template>", line 14 in root
  File "/usr/local/lib/python3.8/site-packages/jinja2/environment.py", line 1088 in render
  File "/usr/local/lib/python3.8/site-packages/jinjasql/core.py", line 179 in _prepare_query
  File "/usr/local/lib/python3.8/site-packages/jinjasql/core.py", line 172 in prepare_query

This happened when we passed more than 1000 values in an 'IN' query. We already fixed that call on our side but instead of a possible infinite loop we expected an error. We are using SQLServer. I also think that in postgresql, the IN query is not limited to 1000 values, so it should normally works.

Thanks for your help.

Tetra

from jinjasql import JinjaSql
j = JinjaSql()
query, params = j.prepare_query("SELECT {{ '%' ~ tag ~ '%' }}", {"tag": "sql"})
print(query)
print(params)

Should print
SELECT %s
['%sql%']

Instead, it prints
SELECT %sql%s
['%']

Analysis

JinjaSQL internally rewrites the query to SELECT {{ '%' ~ tag ~ '%' | bind('tag')}}. The bind filter attaches itself only to the last '%', and not to the entire expression.

To fix this, JinjaSQL should enclose the entire expression in a pair of parentheses, like this - SELECT {{ ('%' ~ tag ~ '%') | bind('tag') }}

I would like to use this with https://github.com/moban to convert a template containing a few custom queries with preset data into a db-specific rendered file, each enclosed in a method which receives extra params and returns the cursor and bound output cols where possible.

A little more voodoo is needed for this tool to process a string containing multiple queries, such as expanding the params in generated wrapping code. That could be done manually in jinja, but it would be a shame to do it that way as this tool is so close.

Moban allows plugins, so some extra init and per file steps can be done in the plugin to make this workable, but ideally most of the voodoo is in this project so it is reusable.

Hi,

I am getting variables from outer world (not under my influence) in the following format:

json = {
  "scopedVars": {
    "VARTEXT": {
      "text": "sel_val",
      "value": "sel_val"
    },
    "VARTEXT_MV": {
      "text": "sel_val",
      "value": [
        "sel_val"
      ]
    },
    "VARTEXT_MV_ALL": {
      "text": "All",
      "value": [
        "sel_val1",
        "sel_val2",
        "sel_val3"
      ]
    },
    "VARINT": {
      "text": "48",
      "value": "48"
    },
    "VARINT_MV": {
      "text": "62 + 55",
      "value": [
        "62",
        "55"
      ]
    },
    "VARINT_MV_ALL": {
      "text": "62 + 55 + 41",
      "value": [
        "62",
        "55",
        "41"
      ]
    }
  }
}

In the json above I have variation of received variables:
VARTEXT - single value string variable
VARTEXT_MV - multi value string variable (in this case with a single value selected, list of values from which I can choose doesn't have an "All" option)
VARTEXT_MV_ALL - multi value string variable with selected "All" option
VARINT - single value integer variable
VARINT_MV - multi value integer variable (several values) with no "All" option
VARINT_MV_ALL - multi value integer variable with selected "All" option

In all cases I have a list from which I can select single value (than I have that value in "text" and "value" keys), multiple values (than in "text" key I have plus sign separated values and in "value" i have list of selected values) or multiple values with "All" option (that behaves as multiple values except when "All" is selected, in that case "text" is "All" and "value" is a list of all options).

As I don't have any clue about data types, I have to maintain a separate dictionary like this one:

dtypes = {
    'VARTEXT' : 'str',
    'VARINT' : 'int'
}

Now with information above I should create sql queries. I have to use single quote for all string variables.

For example:

1. string variable - single value selected

select *
from table
where string_field = 'sel_val' (or string_variable in ('sel_val')

2. string variable - multiple values selected (in this case single value selected from multiple values)

select *
from table
where string_field in ('sel_val')

3. string variable - "All" selected

select *
from table (if "All" is selected I don't need the condition at all)

4. integer variable - single value selected

select *
from table
where integer_field = 48 (or integer_variable in (48), no single quotes here)

5. integer variable - multiple values selected

select *
from table
where integer_field in (64,55)

6. integer_variable - "All" selected

select *
from table (no condition)

I thought, maybe to put single value in the list as well so all "value" keys will contain a list.
Than I could use these lists always in sql in() function (and never use =).
I have to decide whether I need to single quote values (string) or not (integer), and finally, if "All" option is selected to drop the condition.

Can I use junjasql for such use case?
Is it feasible, or I should use something else?

Hi,

I am having an issue with template inheritance and sqlsafe: the argument doesn't seem to propagate in the inherited templates. Here is a toy example showing my problem:
.
├── templates
│   ├── tables.sql
│   └── test.sql
└── test.py

In tables.sql you have listed all your tables you want to include in other sql files. For instance here's the content of tables.sql
{% set test_table = 'test' | sqlsafe %}

In test.sql you include tables.sql

{% include 'templates/tables.sql' %}
SELECT * FROM {{ test_table }}

And in test.py I have the following code:

from pathlib import Path
from jinjasql import JinjaSql
from jinja2 import Environment, FileSystemLoader

rtemplate = Environment(loader=FileSystemLoader(str(Path(__file__).parent)))
j = JinjaSql(param_style='pyformat', env=rtemplate)

file_name = str(Path(__file__).parent / 'templates/test.sql')
with open(file_name, 'r') as f:
    template = f.read()
query_params = {}
query, bind_params = j.prepare_query(template, query_params)
print(query)
print(bind_params)

The answer is

SELECT * FROM %(test_table)s 
OrderedDict([('test_table', Undefined)])

The sqlsafe hasn't "survived" template inheritance. I also tried putting the sqlsafe parameter in the tables.sql and I got this answer:

SELECT * FROM  
OrderedDict()

Am I missing something?

Thank you very much for your help

Is there an example showing how to use macros with this library? I want to build a CLI that renders the SQL with template SQL + variable. Macros would be useful for common snippets.

Ref : python/cpython#28382

I was a little surprised to find django in requirements.txt. So I looked at how it was used. As far as I can tell, it's used only in tests.

I'd like to be able to use this package without pulling in all of django.

Specifically when developing dbt, can you write queries using this?
or is the only recommended way to wrap it in python?

I'm trying to exec this query in Superset-SQLab

SELECT {{gender_enum(users.gender)}} as gender
from users;

The gender_enum is defined like this

lambda x: ['Femenino', 'Masculino', 'Otro', 'Prefiere no decir'][x]

The gender field could be 0, 1, 2 or 3. It's an enum.

Of course, in jinja context, users.gender does not exist. I'm not finding a way to solve this, can you help me please?

Thanks!!

I'm currently trying to use this module in my Flask app, but I keep getting errors about the JinjaSql-specific helpers.

Example Error:
jinja2.exceptions.TemplateAssertionError: no filter named 'inclause'

The helper function I have in my code to load my templates looks like this:

from flask import current_app
from app import jsql  # jsql is referencing JinjaSQL() from my app_factory function
import jinja2

def _get_template(self, name):
    try:
        sql_template = current_app.jinja_env.get_template(f'{self.TEMPLATES_FOLDER}/{name}')
    except jinja2.exceptions.TemplateNotFound:
        raise
    return sql_template2

def run_report(self, extract_definition):
    sql_template = self._get_template('run_report.sql')
    data = {
        'dataset': self.client.dataset,
        'selected_table': extract_definition.selected_table.name,
        'time_period_column': extract_definition.time_period_type.upper(),
        'time_values': [time_value.name for time_value in extract_definition.time_values]
    }
    query, bind_params = jsql.prepare_query(sql_template, data)
    return self.engine.execute(query % bind_params).fetchall()

is this project EOL? don't see much activity, is there any alternative anyone recommends?

Would like to have an example which documents how to perform bulk insert

Desired output

INSERT INTO 
    links (url, name)
VALUES
    ('https://www.google.com','Google'),
    ('https://www.yahoo.com','Yahoo'),
    ('https://www.bing.com','Bing');

Just wondering if this is a necessary fix just pertaining to using Jinjasql with sqlite3?

According to Python 3's Sqlite3 documentation, sqlite3 only accepts two kinds of placeholders when parameterising queries: question mark and named style. Would it be necessary to set the default param_style to be qmark?

Hello, Thank you for a great library. I am looking to build a complex sql query which has multiple wild card statements, for example
Select * From table A Where (A.col1 like '%abc%' or A.col1 like '%123%' or A.col1 like '%345%') AND (B.col2 like '%xyz%' or B.col2 like '%qwe%')...
etc., where the values within the wildcards are in a list or dictionary. Is this possible?

Currently, table names and field names can be used with the sqlsafe filter. Would it be possible to introduce a new filter to handle escaping for table names and field names?

The motivation for this comes from a current bulk load job I'm working on which consists on a bunch of queries that all look the same except for table and schema that are fed from a configuration file. I'm currently using psycopg2.sql to handle escaping, but I'd prefer to remove the SQL code from the application. This would allow me to have access to Jinja features like template inheritance or tests to extend the bulk load job capabilities.

psycopg2.sql ends up using PQescapeIdentifier from libpq to handle escaping, but we can do what the folks at diesel did and implement it ourselves (forgetting about encodings for the example).

Adding something like this in jinjasql/jinjasql/core.py:

def identifier(*values: str) -> str:
    return Markup('.'.join(f'"{escape_quotes(value)}"' for value in values))

def escape_quotes(s: str) -> str:
    return s.replace('"', '""')

...

class JinjaSql(object):
        def _prepare_environment(self):
            self.env.filters["identifier"] = identifier

Would allow for templates like:

SELECT *
FROM {{ table | identifier }}

To be rendered as:

SELECT *
FROM "my_schema"."my_table"

Assuming a call to prepare_query like: j.prepare_query(template, {'table': ('my_schema', 'my_table')})

I understand this feature would be dependent on database system: this particular example would work with PostgreSQL but not with MySQL since double quotes for system identifiers are not a thing there. So the filter would need to be decorated with contextfilter so that a db_engine parameter can be read from it and new environments can be added over time; really bad example:

@contextfilter
def identifier(context, *values: str) -> str: 
    if context.eval_ctx.db_engine == 'postgres':
        return Markup('.'.join(f'"{escape_quotes(value)}"' for value in values))
    elif context.eval_ctx.db_engine == 'mysql':
        return do_mysql_escape(values)
    else:
        raise ValueError(f'{db_engine} not supported')

With the new attribute added to the environment:

class SqlExtension(Extension):
    def__init__(self, environment):
        super().__init__(environment)
        environment.extend(
            db_engine='postgres',
        )

edit: Forgot to add I'd be more than willing to implement something like this myself if it's deemed to be a good feature.

Hi there,

Firstly thank you for a really useful package!

I've tried a number of queries and they all worked until I used a query that uses LIKE statements in the SQL to check for phrases in text fields. The use of the % sign and 'S' is causing an error - perhaps its being resolved to something else in the code? Please see example below. When I change the %SNB% to something else (eg. %FNB%), I get a different error. Please assist :)

query being executed:
select count(1)
from
<transactions_table>
where
BeneficiaryReference like '%SNB%'
and TransactionDate between {{ TrxnStartDate }} and {{ TrxnEndDate }}

params:
params = {'TrxnStartDate': '2019-04-01', 'TrxnEndDate': '2020-03-31'}

error output when using like '%SNB%':

ValueError Traceback (most recent call last)
in
1 #add competitor home loans
2 #queries.sql_get_Competitor_HL
----> 3 apply_sql_template(queries.sql_get_Competitor_HL, params)
4 #competitor_home_loans_dataset = pd.read_sql(apply_sql_template(queries.sql_get_Competitor_HL, params), conn)
5 #clients_details = competitor_home_loans(clients_details)

in apply_sql_template(template, parameters)
24 j = JinjaSql(param_style='pyformat')
25 query, bind_params = j.prepare_query(template, parameters)
---> 26 return get_sql_from_template(query, bind_params)

in get_sql_from_template(query, bind_params)
19 for key, val in params.items():
20 params[key] = quote_sql_string(val)
---> 21 return query % params
22
23 def apply_sql_template(template, parameters):

ValueError: unsupported format character 'S' (0x53) at index 114

error output when using like '%FNB%':

TypeError Traceback (most recent call last)
in
1 #add competitor home loans
2 #queries.sql_get_Competitor_HL
----> 3 apply_sql_template(queries.sql_get_Competitor_HL, params)
4 #competitor_home_loans_dataset = pd.read_sql(apply_sql_template(queries.sql_get_Competitor_HL, params), conn)
5 #clients_details = competitor_home_loans(clients_details)

in apply_sql_template(template, parameters)
24 j = JinjaSql(param_style='pyformat')
25 query, bind_params = j.prepare_query(template, parameters)
---> 26 return get_sql_from_template(query, bind_params)

in get_sql_from_template(query, bind_params)
19 for key, val in params.items():
20 params[key] = quote_sql_string(val)
---> 21 return query % params
22
23 def apply_sql_template(template, parameters):

TypeError: must be real number, not collections.OrderedDict