Author : Kevin Smeeks
PySQL is an extension built off of Jinja2. It adds a Python extension to execute code between {% python %}{% endpython %} tags. Variables set between these tags are made available to the template context. A SQL extension allows for results to be loaded into context. {% sql myvariable %}select * from mytable{% endsql %} will load the result of the query into myvariable as a list.
There are a few reasons behind the project. One, SQL does not allow variable table or column names. Two, when using Python to do work on the database I did not like that my SQL had to be contained in strings. I wanted to be able to write my SQL as I regularly would with the addition of Python when I needed it.
All Jinja template syntax is valid. Below will demonstrate the use of the Python and SQL extensions. SQL is generated by executing
python pysql.py input_file_name.pysql. By default this will print the resulting SQL. You may pass a file to output the rendered template by using python pysql.py input_file_name.pysql -o my_output_file.sql.
A basic example
{% python %}
import datetime
today = datetime.date.today()
yyyymm = str(today.year)+str(today.month).zfill(2)
table_name = f"report_{yyyymm}"
{% endpython %}
select * from {{ table_name }};Variables are accessible throughout the template and can be updated as you go.
{% python %}
table_name = "mytable1"
{% endpython %}
select * from {{ table_name }};
{% python %}
table_name = "mytable2"
{% endpython %}
select * from {{ table_name }};wil result in
select * from mytable1;
select * from mytable2;There are a few filters defined to help with building queries as well.
{% python %}
table_name = "my table"
mycolumns = ["mycol1", "mycol2", "mycol3"]
myvalues = ['hello', 5, None]
{% endpython %}
{% for value in myvalues %}
select {{ mycolumns|columns }} from {{ table_name|sql_escape }} where mycol1 {{ value|where }};
{% endfor %}Will result in
select `mycol1`,`mycol2`,`mycol3` from `my table` where mycol1 = 'hello';
select `mycol1`,`mycol2`,`mycol3` from `my table` where mycol1 = 5;
select `mycol1`,`mycol2`,`mycol3` from `my table` where mycol1 IS NULL;The sql_escape wraps table and column names in backticks. It is probably easier to use backticks yourself in most cases, but it used within the columns filter which takes a list of column names and wraps them in backticks, separating them by commas.
The where filter is used on the value within a condition. As seen in the example above, the '=' is added to the condition, strings are wrapped in quotes, numbers are left as is, and None is converted to IS NULL.
If you only want to escape a string with quotes, use the quote filter.
{% python %}
mystring = "My dog's house is red"
{% endpython %}
select * from mytable where mycol = {{ mystring|quote }};outputs
select * from mytable where mycol = 'My dog\'s house is red';Additionaly you can specify to use double quotes
{% python %}
mystring = "My dog's house is red"
{% endpython %}
select * from mytable where mycol = {{ mystring|quote("double") }};outputs
select * from mytable where mycol = "My dog's house is red";The where filter can be used for other operators as well.
{% python %}
from datetime import datetime
from decimal import Decimal
values = ["some string", 5, None, 5.2, datetime.now(), Decimal('4.7')]
me = {'firstname': 'kevin', 'dob': datetime.strptime('1975-04-16', '%Y-%m-%d').date()}
{% endpython %}
{% for value in values %}
select * from mytable where col1 {{ value|where('!=') }};
{% endfor %}
{% for value in values %}
select * from mytable where col1 {{ value|where('>') }};
{% endfor %}
select * from users where DOB {{ me.dob|where('>=') }};
{% python %}
from datetime import timedelta
me['dob'] += timedelta(days=365 * 5)
{% endpython %}
select * from users where DOB {{ me.dob|where('>=') }};output
select * from mytable where col1 != 'some string';
select * from mytable where col1 != 5;
select * from mytable where col1 IS NOT NULL;
select * from mytable where col1 != '5.2';
select * from mytable where col1 != '2023-05-03 22:36:52.277321';
select * from mytable where col1 != '4.7';
select * from mytable where col1 > 'some string';
select * from mytable where col1 > 5;
select * from mytable where col1 > 'NULL';
select * from mytable where col1 > '5.2';
select * from mytable where col1 > '2023-05-03 22:36:52.277321';
select * from mytable where col1 > '4.7';
select * from users where DOB >= '1975-04-16';
select * from users where DOB >= '1980-04-14';'!=' is converted to IS NOT NULL but other logical operators simply use the NULL value.
There is also a SQL extension that will execute queries immediatly and are not part of the output. To use the SQL extension, create a configs.json file and set the PYSQL_DB_CONN_STRING key to a SQLAlchemy connection string. Rows are returned as a list of dictionaries. This can be useful when you need to use values from one table to generate SQL. For example :
python3 pysql.py examples/example7.pysql -c configs.json{% sql rows %}select * from test.products{% endsql %}
{% python %}
tables = []
for row in rows:
tables.append('items_' + row['Product'])
{% endpython %}
{% for table in tables %}
create table test.{{ table|sql_escape }} (
ID int not null auto_increment,
MPN varchar(64) not null,
Brand varchar(128),
CurrentPrice decimal(8,2),
primary key(ID),
index (MPN),
index (Brand)
);
{% endfor %}example output
create table test.`items_Cooktops` (
ID int not null auto_increment,
MPN varchar(64) not null,
Brand varchar(128),
CurrentPrice decimal(8,2),
primary key(ID),
index (MPN),
index (Brand)
);
create table test.`items_Microwaves` (
ID int not null auto_increment,
MPN varchar(64) not null,
Brand varchar(128),
CurrentPrice decimal(8,2),
primary key(ID),
index (MPN),
index (Brand)
);
create table test.`items_Ranges` (
ID int not null auto_increment,
MPN varchar(64) not null,
Brand varchar(128),
CurrentPrice decimal(8,2),
primary key(ID),
index (MPN),
index (Brand)
);