sqlx
SQL Extension
一种扩展 sql 的语言,目标是打造 "易读易写 方便维护" 的 sql 脚本
使用方法
Windows 系统,直接下载 sqlx.exe 放置于 .sqlx 脚本文件 同目录下
双击 sqlx.exe 即可完成自动编译,在 dist 目录中生成 sql 文件
语法简介
1. 通过 define 定义变量,可在脚本中反复引用
示例:
define field_name age
SELECT {field_name} from students WHERE {field_name} > 10;
SELECT {field_name} from teachers WHERE {field_name} > 10;
编译生成 sql 为:
SELECT age from students WHERE age > 10;
SELECT age from teachers WHERE age > 10;
2. 通过 block 定义脚本片段,并反复引用
示例:
-- ! 定义片段
block good_students(score)
(
SELECT
*
FROM
students
WHERE
score > {score}
) AS good_students
endblock
SELECT name FROM {good_students(80)};
SELECT count(*) FROM {good_students(80)};
编译生成 sql 为:
SELECT name FROM
(
SELECT
*
FROM
students
WHERE
score > 80
) AS good_students
;
SELECT count(*) FROM
(
SELECT
*
FROM
students
WHERE
score > 80
) AS good_students
;
3. 循环
通过 for 批量循环生成脚本(暂不支持循环嵌套)
示例1:
{% for n in table1,table2,table3 %}
SELECT * FROM {n};
{% endfor %}
编译生成 sql 为:
SELECT * FROM table1;
SELECT * FROM table2;
SELECT * FROM table3;
示例2:
{% for n|m in table1|id,table2|name,table3|age %}
SELECT {m} FROM {n};
{% endfor %}
编译生成 sql 为:
SELECT id FROM table1;
SELECT name FROM table2;
SELECT age FROM table3;
4. 判断
通过 if 生成逻辑分支脚本(暂不支持 if 嵌套)
示例1:
define a 8
{% if {a} > 4 %}
SELECT * FROM table1;
{% endif %}
编译生成 sql 为:
SELECT * FROM table1;
示例2:
{% for n in table1,table2,table3 %}
{% if n == table1 %}
SELECT id, name FROM {n};
{% else%}
SELECT * FROM {n};
{% endif %}
{% endfor %}
编译生成 sql 为:
SELECT id, name FROM table1;
SELECT * FROM table2;
SELECT * FROM table3;
更多示例可参考 demo.sqlx
在 Python3 程序中使用 sqlx 模块
如果使用 Python,这里还特别为你提供了 sqlx 的 python 模块包
可以方便地跨平台安装,以及更加灵活地处理和编译脚本
安装
pip install sqlx
使用 sqlx.build 编译脚本
import sqlx
my_script = """
{% for n in table1,table2,table3 %}
{% if n == table1 %}
SELECT id, name FROM {n};
{% else%}
SELECT * FROM {n};
{% endif %}
{% endfor %}
"""
sql = sqlx.build(my_script, pretty=True)
print(sql)
使用 sqlx 命令行工具
- 直接执行
sqlx命令,可一键编译当前目录下的所有脚本
$ ls
test1.sqlx test2.sqlx
$ sqlx
dist/test1.sql built
dist/test2.sql built
Finish!
$ ls dist
test1.sql test2.sql
sqlx命令后跟随目录路径参数,可编译指定路径下的所有脚本
$ ls test
test3.sqlx test4.sqlx
$ sqlx ./test/
test/dist/test3.sql built
test/dist/test4.sql built
Finish!
$ ls test/dist
test3.sql test4.sql
sqlx命令后跟随文件路径参数,可编译指定的单个脚本
$ sqlx ./test/test3.sqlx
test/dist/test3.sql built
Finish!
$ ls test/dist
test3.sql
Metadata
Release files for sqlx 0.1.0
For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.
Source distribution (sdist)
| File | Size | Uploaded | Provenance | Actions |
|---|---|---|---|---|
| sqlx-0.1.0.tar.gz | 32.3 kB | Without provenance | Details for sqlx-0.1.0.tar.gz |
Release files / sqlx-0.1.0.tar.gz
| Download URL | sqlx-0.1.0.tar.gz |
|---|---|
| Size | 32.3 kB |
| Tags | Source |
|
SHA-256 checksum How to use checksums |
7b5979719a5d39896246a919d79709bb2276edc9b5180ae03fddc812c8f375d4
|
|
BLAKE2b-256 checksum How to use checksums |
2dc9620757b9335f5c436c65cbceb61fd3812b6442f299b5e705f60defc445d7
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
No |
| Uploaded via |
twine/1.13.0 pkginfo/1.5.0.1 requests/2.22.0 setuptools/40.8.0 requests-toolbelt/0.9.1 tqdm/4.32.1 CPython/3.7.3
|
Security for sqlx 0.1.0
Provenance
Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.
No provenance found
None of the files in this release were uploaded with provenance metadata.
A note on package security
PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.
To learn more about protecting yourself from malicious actors, visit our package security documentation.