sqlplate


Namesqlplate JSON
Version 0.0.2 PyPI version JSON
download
home_pageNone
SummarySQL template generator
upload_time2025-02-12 05:43:37
maintainerNone
docs_urlNone
authorNone
requires_python>=3.9
licenseNone
keywords utility sql
VCS
bugtrack_url
requirements No requirements were recorded.
Travis-CI No Travis.
coveralls test coverage No coveralls.
            # SQL Template

A SQLPlate (SQL template) provide the generator object for SQL template statements
via Python API object.
All SQL template files are store in the [Jinja template](https://jinja.palletsprojects.com/en/stable/templates/)
format that is the powerful template tool package.

**The layer of SQL template files will be:**

```text
đź“‚templates/
   ├─ 📂databricks/
   │     ├─ 📜etl.delta.sql
   │     ├─ 📜etl.scd2.sql
   │     ╰─ 📜select.sql
   ├─ 📂synapse-dedicate/
   │     ╰─ 📜etl.delta.sql
   ╰─ 📂utils/
         ╰─ ⚙️etl_vars.jinja
```

## :package: Installation

```shell
pip install -U sqlplate
```

## :fork_and_knife: Usage

Start passing option parameters before generate Delta ETL SQL template that use
on the Databricks service.

```python
from datetime import datetime
from sqlplate import SQLPlate

statement: str = (
    SQLPlate.system('databricks')
    .template('etl.delta')
    .option('catalog', 'catalog-name')
    .option('schema', 'schema-name')
    .option('table', 'table-name')
    .option('pk', 'pk_col')
    .option('columns', ['col01', 'col02'])
    .option('query', 'SELECT * FROM catalog-name.schema-name.source-name')
    .option('load_src', 'SOURCE_FOO')
    .option('load_id', 1)
    .option('load_date', datetime(2025, 2, 1, 10))
    .load()
)
print(statement.strip().strip('\n'))
```

Result SQL statement that was generated from this package.

```text
MERGE INTO catalog-name.schema-name.table-name AS target
USING (
    WITH change_query AS (
        SELECT
            src.*,
        CASE WHEN tgt.pk_col IS NULL THEN 99
             WHEN hash(src.col01, src.col02) <> hash(tgt.col01, tgt.col02) THEN 1
             ELSE 0 END AS data_change
        FROM ( SELECT * FROM catalog-name.schema-name.source-name ) AS src
        LEFT JOIN catalog-name.schema-name.table-name AS tgt
            ON  tgt.col01 = src.col01
AND tgt.col02 = src.col02
    )
    SELECT * EXCEPT( data_change ) FROM change_query WHERE data_change IN (99, 1)
) AS source
    ON  target.pk_col = source.pk_col
WHEN MATCHED THEN UPDATE
    SET target.col01            = source.col01
    ,   target.col02            = source.col02
    ,   target.updt_load_src    = 'SOURCE_FOO'
    ,   target.updt_load_id     = 1
    ,   target.updt_load_date   = to_timestamp('20250201', 'yyyyMMdd')
WHEN NOT MATCHED THEN INSERT
    (
        col01, col02, pk_col, load_src, load_id, load_date, updt_load_src, updt_load_id, updt_load_date
    )
    VALUES (
        source.col01,
        source.col02,
        source.pk_col,
        'SOURCE_FOO',
        1,
        20250201,
        'SOURCE_FOO',
        1,
        to_timestamp('20250201', 'yyyyMMdd')
    )
```

## Systems

| System     | Progress |       Templates       | Remark  |
|:-----------|:--------:|:---------------------:|---------|
| databricks |    🟡    | etl.delta<br>etl.scd2 |         |
| sqlite     |    🟡    |                       |         |

> [!NOTE]
> - 🟢 Complete
> - 🟡 In progress
> - đź”´ Does not develop
> - 🟣 Does not plan to support

## :speech_balloon: Contribute

I do not think this project will go around the world because it has specific propose,
and you can create by your coding without this project dependency for long term
solution. So, on this time, you can open [the GitHub issue on this project :raised_hands:](https://github.com/korawica/sqlplate/issues)
for fix bug or request new feature if you want it.


            

Raw data

            {
    "_id": null,
    "home_page": null,
    "name": "sqlplate",
    "maintainer": null,
    "docs_url": null,
    "requires_python": ">=3.9",
    "maintainer_email": null,
    "keywords": "utility, sql",
    "author": null,
    "author_email": "korawica <korawich.anu@gmail.com>",
    "download_url": "https://files.pythonhosted.org/packages/25/67/d0a4dfce5e187bd97b206c1ea003db7c0ce342e90774b46fc3d964f1d752/sqlplate-0.0.2.tar.gz",
    "platform": null,
    "description": "# SQL Template\n\nA SQLPlate (SQL template) provide the generator object for SQL template statements\nvia Python API object.\nAll SQL template files are store in the [Jinja template](https://jinja.palletsprojects.com/en/stable/templates/)\nformat that is the powerful template tool package.\n\n**The layer of SQL template files will be:**\n\n```text\n\ud83d\udcc2templates/\n   \u251c\u2500 \ud83d\udcc2databricks/\n   \u2502     \u251c\u2500 \ud83d\udcdcetl.delta.sql\n   \u2502     \u251c\u2500 \ud83d\udcdcetl.scd2.sql\n   \u2502     \u2570\u2500 \ud83d\udcdcselect.sql\n   \u251c\u2500 \ud83d\udcc2synapse-dedicate/\n   \u2502     \u2570\u2500 \ud83d\udcdcetl.delta.sql\n   \u2570\u2500 \ud83d\udcc2utils/\n         \u2570\u2500 \u2699\ufe0fetl_vars.jinja\n```\n\n## :package: Installation\n\n```shell\npip install -U sqlplate\n```\n\n## :fork_and_knife: Usage\n\nStart passing option parameters before generate Delta ETL SQL template that use\non the Databricks service.\n\n```python\nfrom datetime import datetime\nfrom sqlplate import SQLPlate\n\nstatement: str = (\n    SQLPlate.system('databricks')\n    .template('etl.delta')\n    .option('catalog', 'catalog-name')\n    .option('schema', 'schema-name')\n    .option('table', 'table-name')\n    .option('pk', 'pk_col')\n    .option('columns', ['col01', 'col02'])\n    .option('query', 'SELECT * FROM catalog-name.schema-name.source-name')\n    .option('load_src', 'SOURCE_FOO')\n    .option('load_id', 1)\n    .option('load_date', datetime(2025, 2, 1, 10))\n    .load()\n)\nprint(statement.strip().strip('\\n'))\n```\n\nResult SQL statement that was generated from this package.\n\n```text\nMERGE INTO catalog-name.schema-name.table-name AS target\nUSING (\n    WITH change_query AS (\n        SELECT\n            src.*,\n        CASE WHEN tgt.pk_col IS NULL THEN 99\n             WHEN hash(src.col01, src.col02) <> hash(tgt.col01, tgt.col02) THEN 1\n             ELSE 0 END AS data_change\n        FROM ( SELECT * FROM catalog-name.schema-name.source-name ) AS src\n        LEFT JOIN catalog-name.schema-name.table-name AS tgt\n            ON  tgt.col01 = src.col01\nAND tgt.col02 = src.col02\n    )\n    SELECT * EXCEPT( data_change ) FROM change_query WHERE data_change IN (99, 1)\n) AS source\n    ON  target.pk_col = source.pk_col\nWHEN MATCHED THEN UPDATE\n    SET target.col01            = source.col01\n    ,   target.col02            = source.col02\n    ,   target.updt_load_src    = 'SOURCE_FOO'\n    ,   target.updt_load_id     = 1\n    ,   target.updt_load_date   = to_timestamp('20250201', 'yyyyMMdd')\nWHEN NOT MATCHED THEN INSERT\n    (\n        col01, col02, pk_col, load_src, load_id, load_date, updt_load_src, updt_load_id, updt_load_date\n    )\n    VALUES (\n        source.col01,\n        source.col02,\n        source.pk_col,\n        'SOURCE_FOO',\n        1,\n        20250201,\n        'SOURCE_FOO',\n        1,\n        to_timestamp('20250201', 'yyyyMMdd')\n    )\n```\n\n## Systems\n\n| System     | Progress |       Templates       | Remark  |\n|:-----------|:--------:|:---------------------:|---------|\n| databricks |    \ud83d\udfe1    | etl.delta<br>etl.scd2 |         |\n| sqlite     |    \ud83d\udfe1    |                       |         |\n\n> [!NOTE]\n> - \ud83d\udfe2 Complete\n> - \ud83d\udfe1 In progress\n> - \ud83d\udd34 Does not develop\n> - \ud83d\udfe3 Does not plan to support\n\n## :speech_balloon: Contribute\n\nI do not think this project will go around the world because it has specific propose,\nand you can create by your coding without this project dependency for long term\nsolution. So, on this time, you can open [the GitHub issue on this project :raised_hands:](https://github.com/korawica/sqlplate/issues)\nfor fix bug or request new feature if you want it.\n\n",
    "bugtrack_url": null,
    "license": null,
    "summary": "SQL template generator",
    "version": "0.0.2",
    "project_urls": {
        "Homepage": "https://github.com/korawica/sqlplate/",
        "Source": "https://github.com/korawica/sqlplate/"
    },
    "split_keywords": [
        "utility",
        " sql"
    ],
    "urls": [
        {
            "comment_text": null,
            "digests": {
                "blake2b_256": "2da711311ed5994367fc708d1b7cf65383f033c0d3dcf98fe8ba42ba62d21937",
                "md5": "43c0430d79abb80f9380c1117b16e196",
                "sha256": "eb2c7ccf1e10c5989ef5beaafce13dfb081bed7a2209c1dfa0e970bb1f122da0"
            },
            "downloads": -1,
            "filename": "sqlplate-0.0.2-py3-none-any.whl",
            "has_sig": false,
            "md5_digest": "43c0430d79abb80f9380c1117b16e196",
            "packagetype": "bdist_wheel",
            "python_version": "py3",
            "requires_python": ">=3.9",
            "size": 6507,
            "upload_time": "2025-02-12T05:43:35",
            "upload_time_iso_8601": "2025-02-12T05:43:35.710976Z",
            "url": "https://files.pythonhosted.org/packages/2d/a7/11311ed5994367fc708d1b7cf65383f033c0d3dcf98fe8ba42ba62d21937/sqlplate-0.0.2-py3-none-any.whl",
            "yanked": false,
            "yanked_reason": null
        },
        {
            "comment_text": null,
            "digests": {
                "blake2b_256": "2567d0a4dfce5e187bd97b206c1ea003db7c0ce342e90774b46fc3d964f1d752",
                "md5": "12e0fe50f4ef4aa40d1a3f660d939093",
                "sha256": "25934ce574974271c2b232488071efbdd1bd33018b812e1e806bec02b3bbe127"
            },
            "downloads": -1,
            "filename": "sqlplate-0.0.2.tar.gz",
            "has_sig": false,
            "md5_digest": "12e0fe50f4ef4aa40d1a3f660d939093",
            "packagetype": "sdist",
            "python_version": "source",
            "requires_python": ">=3.9",
            "size": 7929,
            "upload_time": "2025-02-12T05:43:37",
            "upload_time_iso_8601": "2025-02-12T05:43:37.336495Z",
            "url": "https://files.pythonhosted.org/packages/25/67/d0a4dfce5e187bd97b206c1ea003db7c0ce342e90774b46fc3d964f1d752/sqlplate-0.0.2.tar.gz",
            "yanked": false,
            "yanked_reason": null
        }
    ],
    "upload_time": "2025-02-12 05:43:37",
    "github": true,
    "gitlab": false,
    "bitbucket": false,
    "codeberg": false,
    "github_user": "korawica",
    "github_project": "sqlplate",
    "travis_ci": false,
    "coveralls": false,
    "github_actions": true,
    "lcname": "sqlplate"
}
        
Elapsed time: 1.19016s