# sqlvalidator
[](https://actions-badge.atrox.dev/David-Wobrock/sqlvalidator/goto?ref=main)
[](https://pypi.python.org/pypi/sqlvalidator/)
[](https://codecov.io/gh/David-Wobrock/sqlvalidator)
SQL queries formatting, syntactic and semantic validation
**Only supports SELECT statements**
## Command line usage
### SQL Formatting
_sql.py_
```
def fun():
return "select col1, column2 from table"
```
Command line:
```
$ sqlvalidator --format sql.py
reformatted sql.py (1 changed SQL)
1 file reformatted (1 changed SQL queries).
```
_sql.py_
```
def fun():
return """
SELECT
col1,
column2
FROM table
"""
```
A `nosqlformat` comment can be appended to indicate to `sqlvalidator` that this string should not be formatted.
### Check SQL format
One can verify also that the file would be reformatted or not:
```
$ sqlvalidator --check-format sql.py
would reformat sql.py (1 changed SQL)
1 file would be reformatted (1 changed SQL queries).
$ sqlvalidator --format sql.py
reformatted sql.py (1 changed SQL)
1 file reformatted (1 changed SQL queries).
$ sqlvalidator --check-format sql.py
No file would be reformatted.
$ sqlvalidator --format sql.py
No file reformatted.
```
`--check-format` won't write the file back and just return a status code:
* Status code 0 when nothing would change.
* Status code 1 when some files would be reformatted.
The option is meant to be used within the CI/CD pipeline and ensure that SQL statements are formatted.
### SQL Validation
One can verify that the files SQL is valid:
```
$ sqlvalidator --validate sql.py
invalid queries in sql.py (1 invalid SQL)
1 file detected with invalid SQL (1 invalid SQL queries).
# ... do some manual fixes to the SQL ...
$ sqlvalidator --validate sql.py
No invalid queries found.
```
To get more details about the found invalid elements, use `--verbose-validate`
## API / Python code usage
### SQL Formatting
```python
import sqlvalidator
formatted_sql = sqlvalidator.format_sql("SELECT * FROM table")
```
### SQL Validation
```python
import sqlvalidator
sql_query = sqlvalidator.parse("SELECT * from table")
if not sql_query.is_valid():
print(sql_query.errors)
```
**Warning**: only a limited set of validation are implemented.
## Details about SQL Validation
Validation contains:
* not using a missing column
* existing functions
* correct aggregations
* schemaless (not assume that table names and columns in those exist)
* types correctness in functions
(only on SELECT-statements)
## SQL Syntax
* https://www.postgresql.org/docs/9.6/sql-select.html
* https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax
## Use with [pre-commit](https://pre-commit.com)
Add this to your `.pre-commit-config.yaml`:
```yaml
- repo: https://github.com/David-Wobrock/sqlvalidator
rev: <sha1 of the latest sqlvalidator commit>
hooks:
- id: sqlvalidator
```
## Contributing
If you want to contribute to the sqlvalidator, first, thank you for the interest.
Don't hesitate to open an Issue with a snippet of the failing SQL query and what the expected output would be.
However, I don't guarantee that will accept any Pull Request made to the repository.
This is not because I don't value the work and energy put into contribution, but more because the project is
still early stage, and I want to keep full control of its direction for now.
## Internals
### Run tests
```
pytest
```
### Publishing
* `python3 setup.py sdist bdist_wheel`
* `twine upload dist/sqlvalidator-X.Y.Z-py3-none-any.whl dist/sqlvalidator-X.Y.Z.tar.gz`
Raw data
{
"_id": null,
"home_page": "https://github.com/David-Wobrock/sqlvalidator",
"name": "sqlvalidator",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "python sql format formatter formatting validation validator validate automation",
"author": "David Wobrock",
"author_email": "david.wobrock@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/21/7f/bd1ba351693e60b4dcddd3a84dad89ea75cbc627f9631da17809761a3eb4/sqlvalidator-0.0.20.tar.gz",
"platform": null,
"description": "# sqlvalidator\n\n[](https://actions-badge.atrox.dev/David-Wobrock/sqlvalidator/goto?ref=main)\n[](https://pypi.python.org/pypi/sqlvalidator/)\n[](https://codecov.io/gh/David-Wobrock/sqlvalidator)\n\nSQL queries formatting, syntactic and semantic validation\n\n**Only supports SELECT statements**\n\n## Command line usage\n\n### SQL Formatting\n\n_sql.py_\n```\ndef fun():\n return \"select col1, column2 from table\"\n```\n\nCommand line:\n```\n$ sqlvalidator --format sql.py\nreformatted sql.py (1 changed SQL)\n1 file reformatted (1 changed SQL queries).\n```\n\n_sql.py_\n```\ndef fun():\n return \"\"\"\nSELECT\n col1,\n column2\nFROM table\n\"\"\"\n```\n\nA `nosqlformat` comment can be appended to indicate to `sqlvalidator` that this string should not be formatted.\n\n\n### Check SQL format\nOne can verify also that the file would be reformatted or not:\n```\n$ sqlvalidator --check-format sql.py\nwould reformat sql.py (1 changed SQL)\n1 file would be reformatted (1 changed SQL queries).\n\n\n$ sqlvalidator --format sql.py\nreformatted sql.py (1 changed SQL)\n1 file reformatted (1 changed SQL queries).\n\n\n$ sqlvalidator --check-format sql.py\nNo file would be reformatted.\n\n\n$ sqlvalidator --format sql.py\nNo file reformatted.\n```\n\n`--check-format` won't write the file back and just return a status code:\n* Status code 0 when nothing would change.\n* Status code 1 when some files would be reformatted.\n\nThe option is meant to be used within the CI/CD pipeline and ensure that SQL statements are formatted.\n\n### SQL Validation\n\nOne can verify that the files SQL is valid:\n```\n$ sqlvalidator --validate sql.py\ninvalid queries in sql.py (1 invalid SQL)\n1 file detected with invalid SQL (1 invalid SQL queries).\n\n# ... do some manual fixes to the SQL ...\n\n$ sqlvalidator --validate sql.py\nNo invalid queries found.\n```\n\nTo get more details about the found invalid elements, use `--verbose-validate`\n\n## API / Python code usage\n\n### SQL Formatting\n\n```python\nimport sqlvalidator\n\nformatted_sql = sqlvalidator.format_sql(\"SELECT * FROM table\")\n```\n\n### SQL Validation\n\n```python\nimport sqlvalidator\n\nsql_query = sqlvalidator.parse(\"SELECT * from table\")\n\nif not sql_query.is_valid():\n print(sql_query.errors)\n```\n\n**Warning**: only a limited set of validation are implemented.\n\n## Details about SQL Validation\n\nValidation contains:\n* not using a missing column\n* existing functions\n* correct aggregations\n* schemaless (not assume that table names and columns in those exist)\n* types correctness in functions\n\n(only on SELECT-statements)\n\n## SQL Syntax\n\n* https://www.postgresql.org/docs/9.6/sql-select.html\n* https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax\n\n## Use with [pre-commit](https://pre-commit.com)\n\nAdd this to your `.pre-commit-config.yaml`:\n```yaml\n - repo: https://github.com/David-Wobrock/sqlvalidator\n rev: <sha1 of the latest sqlvalidator commit>\n hooks:\n - id: sqlvalidator\n```\n\n## Contributing\n\nIf you want to contribute to the sqlvalidator, first, thank you for the interest.\n\nDon't hesitate to open an Issue with a snippet of the failing SQL query and what the expected output would be.\n\nHowever, I don't guarantee that will accept any Pull Request made to the repository.\nThis is not because I don't value the work and energy put into contribution, but more because the project is\nstill early stage, and I want to keep full control of its direction for now.\n\n## Internals\n\n### Run tests\n\n```\npytest\n```\n\n### Publishing\n\n* `python3 setup.py sdist bdist_wheel`\n* `twine upload dist/sqlvalidator-X.Y.Z-py3-none-any.whl dist/sqlvalidator-X.Y.Z.tar.gz`\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "SQL queries formatting, syntactic and semantic validation",
"version": "0.0.20",
"split_keywords": [
"python",
"sql",
"format",
"formatter",
"formatting",
"validation",
"validator",
"validate",
"automation"
],
"urls": [
{
"comment_text": "",
"digests": {
"md5": "f5a4884e5d3b118c261f55694472d18e",
"sha256": "8820752d9ec5ccb9cc977099edf991f0090acf4f1e4beb0f2fb35a6e1cc03c89"
},
"downloads": -1,
"filename": "sqlvalidator-0.0.20-py3-none-any.whl",
"has_sig": false,
"md5_digest": "f5a4884e5d3b118c261f55694472d18e",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": null,
"size": 24182,
"upload_time": "2022-12-25T15:30:12",
"upload_time_iso_8601": "2022-12-25T15:30:12.132024Z",
"url": "https://files.pythonhosted.org/packages/5f/9d/5434c2b90dac2a8ab12d42027398e2012d1ce347a0bcc9500525d05ac1ee/sqlvalidator-0.0.20-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"md5": "4752fa334bae5c123532fbb9332ba455",
"sha256": "6f399be1bf0ba54a17ad16f6818836c169d17c16306f4cfa6fc883f13b1705fc"
},
"downloads": -1,
"filename": "sqlvalidator-0.0.20.tar.gz",
"has_sig": false,
"md5_digest": "4752fa334bae5c123532fbb9332ba455",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 24291,
"upload_time": "2022-12-25T15:30:14",
"upload_time_iso_8601": "2022-12-25T15:30:14.149167Z",
"url": "https://files.pythonhosted.org/packages/21/7f/bd1ba351693e60b4dcddd3a84dad89ea75cbc627f9631da17809761a3eb4/sqlvalidator-0.0.20.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2022-12-25 15:30:14",
"github": true,
"gitlab": false,
"bitbucket": false,
"github_user": "David-Wobrock",
"github_project": "sqlvalidator",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "sqlvalidator"
}
JSON
