| Name | pydal2sql JSON |
| Version |
1.1.5
JSON |
| download |
| home_page | None |
| Summary | Convert pydal define_tables to SQL using pydal's CREATE TABLE logic. |
| upload_time | 2024-07-04 14:47:01 |
| maintainer | None |
| docs_url | None |
| author | None |
| requires_python | >=3.10 |
| license | None |
| keywords |
databases
pydal
sql
|
| VCS |
 |
| bugtrack_url |
|
| requirements |
No requirements were recorded.
|
| Travis-CI |
No Travis.
|
| coveralls test coverage |
No coveralls.
|
# pydal2sql
[](https://pypi.org/project/pydal2sql)
[](https://pypi.org/project/pydal2sql)
[](https://github.com/psf/black)
[](https://opensource.org/licenses/MIT)
[](https://github.com/robinvandernoord/pydal2sql/actions)
-----
`pydal2sql` is a command line interface (CLI) tool that translates pydal `define_table` Table definitions into SQL
statements. It supports different SQL dialects including SQLite, Postgres and MySQL. The tool generates
both `CREATE TABLE` and `ALTER TABLE` SQL statements. It does this using pydal's own logic.
Note: this package is only a Typer-based CLI front-end. The actual logic lives at [`robinvandernoord/pydal2sql-core`](https://github.com/robinvandernoord/pydal2sql-core)!
## Table of Contents
- [Installation](#installation)
- [Basic Usage](#basic-usage)
- [Create](#create)
- [Git Integration](#git-integration)
- [Options](#options)
- [Alter](#alter)
- [Global Options](#global-options)
- [Configuration](#configuration)
- [Magic](#-experimental-magic)
- [As a Python Library](#as-a-python-library)
- [License](#license)
## Installation
```bash
pip install pydal2sql
# or
pipx install pydal2sql
```
## Basic Usage
### `CREATE`
The following commands are supported:
- `pydal2sql create [file-name]`: Translate the file into SQL statements.
- `pydal2sql create [file-name]@[git-branch-or-commit-hash]`: Translate a specific version of the file into SQL
statements.
- `pydal2sql create [file-name]@latest`: Translate the latest version of the file in git into SQL statements.
- `pydal2sql create [file-name]@current`: Translate the current version of the file on disk into SQL statements.
- `pydal2sql create` or `pydal2sql create -`: Prompts the user to input the table definitions via stdin.
Bash pipe or redirect is also supported:
- `cat [file-name] | pydal2sql create`
- `pydal2sql create < [file-name]`
#### Git Integration
The tool allows you to specify a git branch or commit hash when translating a file. Using the 'latest' keyword will
use the latest commit, and 'current' will use the file as it currently is on disk.
#### Options
- `--table`, `--tables`, `-t`: Specify which database tables to generate CREATE statements for (default is all).
- `--db-type`, `--dialect`: Specify the SQL dialect to use (SQLite, Postgres, MySQL). The default is guessed from the
code or else the user is queried.
- `--magic`: If variables are missing, this flag will insert variables with that name so the code does (probably) not
crash.
- `--noop`: Doesn't create the migration code but only shows the Python code that would run to create it.
### `ALTER`
- `pydal2sql alter [file1] [file2]`: Generates the ALTER migration from the state in file1 to the state in file2.
- `pydal2sql alter [file1]@[branch-or-commit-hash] [file2]@[branch-or-commit-hash]`: Compares the files at those
specific versions and generates the ALTER migration.
Using `-` instead of a file name will prompt the user via stdin to paste the define tables code.
### Global Options
Global options that go before the subcommand:
- `--verbosity`: Sets how verbose the program should be, with a number between 1 and 4 (default is 2).
- `--config`: Path to a specific config toml file. Default is pyproject.toml at the key [tool.pydal2sql].
- `--version`: Prints the CLI tool version and exits.
- `--show-config`: Prints the currently used config and exits.
Example:
```bash
pydal2sql --verbosity 3 create
pydal2sql --version
```
### Configuration
A configuration file (in toml) can be selected with `--config`. By default, `pyproject.toml` is used.
In the configuration file, the following keys are supported under the [tool.pydal2sql] section:
- `dialect`/`db-type`: Default database dialect to use.
- `magic`: A boolean flag to use the `--magic` option (default is False).
- `noop`: A boolean flag to use the `--noop` option (default is False).
- `tables`: A list of table names to generate CREATE/ALTER statements for.
The CLI command options can overwrite the config values. For example, `--no-magic` will still set magic to False even if
it's set to True in the config file.
Example of the toml configuration:
```toml
[tool.pydal2sql]
dialect = "postgres" # postgres, mysql or sqlite
magic = true
noop = false
tables = ["table1", "table2"]
```
All keys are optional.
### ⚠️ Experimental 🪄✨Magic🌟💻
If you're copy-pasting some `define_table` statements which have validators or defaults that are defined elsewhere,
the SQL generation could crash due to msising variables. However, if these variables are irrelevant to the samentics of
the table definition (i.e. only used at runtime, not for the schema definition), you can now try the `--magic` flag.
This flag will replace all missing variables with a special `Empty` class, which does nothing but
prevent `NameError`, `AttributeError` and `TypeError`s.
`Magic` will also remove local imports and imports that could not be found.
This is of course not production-safe, so it shouldn't be used anywhere else.
#### TODO:
The following patterns are currently not supported:
- `def define_tables(db): ...`
## As a Python Library
`pydal2sql` also exposes a `generate_sql` method that can perform the same actions on one (for CREATE) or two (for
ALTER) `pydal.Table` objects when used within Python.
```python
from pydal import DAL, Field
from pydal2sql import generate_sql
db = DAL(None, migrate=False) # <- without running database or with a different type of database
person_initial = db.define_table(
"person",
Field(
"name",
"string",
notnull=True,
),
Field("age", "integer", default=18),
Field("float", "decimal(2,3)"),
Field("nicknames", "list:string"),
Field("obj", "json"),
)
print(
generate_sql(
db.person, db_type="psql" # or sqlite, or mysql; Optional with fallback to currently using database type.
)
)
```
```sql
CREATE TABLE person
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(512),
age INTEGER,
float NUMERIC(2, 3),
nicknames TEXT,
obj TEXT
);
```
```python
person_new = db.define_table(
"person",
Field(
"name",
"text",
),
Field("birthday", "datetime"),
redefine=True
)
generate_sql(
person_initial,
person_new,
db_type="psql"
)
```
```sql
ALTER TABLE person ADD "name__tmp" TEXT;
UPDATE person SET "name__tmp"=name;
ALTER TABLE person DROP COLUMN name;
ALTER TABLE person ADD name TEXT;
UPDATE person SET name="name__tmp";
ALTER TABLE person DROP COLUMN "name__tmp";
ALTER TABLE person ADD birthday TIMESTAMP;
ALTER TABLE person DROP COLUMN age;
ALTER TABLE person DROP COLUMN float;
ALTER TABLE person DROP COLUMN nicknames;
ALTER TABLE person DROP COLUMN obj;
```
## License
`pydal2sql` is distributed under the terms of the [MIT](https://spdx.org/licenses/MIT.html) license.
Raw data
{
"_id": null,
"home_page": null,
"name": "pydal2sql",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.10",
"maintainer_email": null,
"keywords": "databases, pydal, sql",
"author": null,
"author_email": "Robin van der Noord <robinvandernoord@gmail.com>",
"download_url": "https://files.pythonhosted.org/packages/87/e5/84e2a45df4c0a903efead1e9921d1c2399298a80d1e159bac326e82e77a8/pydal2sql-1.1.5.tar.gz",
"platform": null,
"description": "# pydal2sql\n\n\n[](https://pypi.org/project/pydal2sql)\n[](https://pypi.org/project/pydal2sql) \n[](https://github.com/psf/black)\n[](https://opensource.org/licenses/MIT) \n[](https://github.com/robinvandernoord/pydal2sql/actions)\n\n\n-----\n\n\n`pydal2sql` is a command line interface (CLI) tool that translates pydal `define_table` Table definitions into SQL\nstatements. It supports different SQL dialects including SQLite, Postgres and MySQL. The tool generates\nboth `CREATE TABLE` and `ALTER TABLE` SQL statements. It does this using pydal's own logic.\n\nNote: this package is only a Typer-based CLI front-end. The actual logic lives at [`robinvandernoord/pydal2sql-core`](https://github.com/robinvandernoord/pydal2sql-core)!\n\n## Table of Contents\n\n- [Installation](#installation)\n- [Basic Usage](#basic-usage)\n - [Create](#create)\n - [Git Integration](#git-integration)\n - [Options](#options)\n - [Alter](#alter)\n - [Global Options](#global-options)\n - [Configuration](#configuration)\n - [Magic](#-experimental-magic)\n- [As a Python Library](#as-a-python-library)\n- [License](#license)\n\n## Installation\n\n```bash\npip install pydal2sql\n# or\npipx install pydal2sql\n```\n\n## Basic Usage\n\n### `CREATE`\n\nThe following commands are supported:\n\n- `pydal2sql create [file-name]`: Translate the file into SQL statements.\n- `pydal2sql create [file-name]@[git-branch-or-commit-hash]`: Translate a specific version of the file into SQL\n statements.\n- `pydal2sql create [file-name]@latest`: Translate the latest version of the file in git into SQL statements.\n- `pydal2sql create [file-name]@current`: Translate the current version of the file on disk into SQL statements.\n- `pydal2sql create` or `pydal2sql create -`: Prompts the user to input the table definitions via stdin.\n\nBash pipe or redirect is also supported:\n\n- `cat [file-name] | pydal2sql create`\n- `pydal2sql create < [file-name]`\n\n#### Git Integration\n\nThe tool allows you to specify a git branch or commit hash when translating a file. Using the 'latest' keyword will\nuse the latest commit, and 'current' will use the file as it currently is on disk.\n\n#### Options\n\n- `--table`, `--tables`, `-t`: Specify which database tables to generate CREATE statements for (default is all).\n- `--db-type`, `--dialect`: Specify the SQL dialect to use (SQLite, Postgres, MySQL). The default is guessed from the\n code or else the user is queried.\n- `--magic`: If variables are missing, this flag will insert variables with that name so the code does (probably) not\n crash.\n- `--noop`: Doesn't create the migration code but only shows the Python code that would run to create it.\n\n### `ALTER`\n\n- `pydal2sql alter [file1] [file2]`: Generates the ALTER migration from the state in file1 to the state in file2.\n- `pydal2sql alter [file1]@[branch-or-commit-hash] [file2]@[branch-or-commit-hash]`: Compares the files at those\n specific versions and generates the ALTER migration.\n\nUsing `-` instead of a file name will prompt the user via stdin to paste the define tables code.\n\n### Global Options\n\nGlobal options that go before the subcommand:\n\n- `--verbosity`: Sets how verbose the program should be, with a number between 1 and 4 (default is 2).\n- `--config`: Path to a specific config toml file. Default is pyproject.toml at the key [tool.pydal2sql].\n- `--version`: Prints the CLI tool version and exits.\n- `--show-config`: Prints the currently used config and exits.\n\nExample:\n\n```bash\npydal2sql --verbosity 3 create\npydal2sql --version\n```\n\n### Configuration\n\nA configuration file (in toml) can be selected with `--config`. By default, `pyproject.toml` is used.\nIn the configuration file, the following keys are supported under the [tool.pydal2sql] section:\n\n- `dialect`/`db-type`: Default database dialect to use.\n- `magic`: A boolean flag to use the `--magic` option (default is False).\n- `noop`: A boolean flag to use the `--noop` option (default is False).\n- `tables`: A list of table names to generate CREATE/ALTER statements for.\n\nThe CLI command options can overwrite the config values. For example, `--no-magic` will still set magic to False even if\nit's set to True in the config file.\n\nExample of the toml configuration:\n\n```toml\n[tool.pydal2sql]\ndialect = \"postgres\" # postgres, mysql or sqlite\nmagic = true\nnoop = false\ntables = [\"table1\", \"table2\"]\n```\n\nAll keys are optional.\n\n### \u26a0\ufe0f Experimental \ud83e\ude84\u2728Magic\ud83c\udf1f\ud83d\udcbb\n\nIf you're copy-pasting some `define_table` statements which have validators or defaults that are defined elsewhere,\nthe SQL generation could crash due to msising variables. However, if these variables are irrelevant to the samentics of\nthe table definition (i.e. only used at runtime, not for the schema definition), you can now try the `--magic` flag.\n\nThis flag will replace all missing variables with a special `Empty` class, which does nothing but\nprevent `NameError`, `AttributeError` and `TypeError`s. \n\n`Magic` will also remove local imports and imports that could not be found.\n\nThis is of course not production-safe, so it shouldn't be used anywhere else.\n\n#### TODO:\nThe following patterns are currently not supported:\n- `def define_tables(db): ...`\n\n## As a Python Library\n\n`pydal2sql` also exposes a `generate_sql` method that can perform the same actions on one (for CREATE) or two (for\nALTER) `pydal.Table` objects when used within Python.\n\n```python\nfrom pydal import DAL, Field\nfrom pydal2sql import generate_sql\n\ndb = DAL(None, migrate=False) # <- without running database or with a different type of database\n\nperson_initial = db.define_table(\n \"person\",\n Field(\n \"name\",\n \"string\",\n notnull=True,\n ),\n Field(\"age\", \"integer\", default=18),\n Field(\"float\", \"decimal(2,3)\"),\n Field(\"nicknames\", \"list:string\"),\n Field(\"obj\", \"json\"),\n)\n\nprint(\n generate_sql(\n db.person, db_type=\"psql\" # or sqlite, or mysql; Optional with fallback to currently using database type.\n )\n)\n```\n\n```sql\nCREATE TABLE person\n(\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name VARCHAR(512),\n age INTEGER,\n float NUMERIC(2, 3),\n nicknames TEXT,\n obj TEXT\n);\n```\n\n```python\nperson_new = db.define_table(\n \"person\",\n Field(\n \"name\",\n \"text\",\n ),\n Field(\"birthday\", \"datetime\"),\n redefine=True\n)\n\ngenerate_sql(\n person_initial,\n person_new,\n db_type=\"psql\"\n)\n```\n\n```sql\nALTER TABLE person ADD \"name__tmp\" TEXT;\nUPDATE person SET \"name__tmp\"=name;\nALTER TABLE person DROP COLUMN name;\nALTER TABLE person ADD name TEXT;\nUPDATE person SET name=\"name__tmp\";\nALTER TABLE person DROP COLUMN \"name__tmp\";\nALTER TABLE person ADD birthday TIMESTAMP;\nALTER TABLE person DROP COLUMN age;\nALTER TABLE person DROP COLUMN float;\nALTER TABLE person DROP COLUMN nicknames;\nALTER TABLE person DROP COLUMN obj;\n```\n\n## License\n\n`pydal2sql` is distributed under the terms of the [MIT](https://spdx.org/licenses/MIT.html) license.\n",
"bugtrack_url": null,
"license": null,
"summary": "Convert pydal define_tables to SQL using pydal's CREATE TABLE logic.",
"version": "1.1.5",
"project_urls": {
"Documentation": "https://github.com/robinvandernoord/pydal2sql#readme",
"Issues": "https://github.com/robinvandernoord/pydal2sql/issues",
"Source": "https://github.com/robinvandernoord/pydal2sql"
},
"split_keywords": [
"databases",
" pydal",
" sql"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "49525125e4bd1245c69f142be1be1c9e82aec36895b35085c84721cc54f47a66",
"md5": "aa65cbed347c0a2734d99c906863ea22",
"sha256": "c454f0d3ae03e9e771d7b808132ff13fafe5da65c81e9000ae0f3f4d13b89616"
},
"downloads": -1,
"filename": "pydal2sql-1.1.5-py3-none-any.whl",
"has_sig": false,
"md5_digest": "aa65cbed347c0a2734d99c906863ea22",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.10",
"size": 12554,
"upload_time": "2024-07-04T14:47:03",
"upload_time_iso_8601": "2024-07-04T14:47:03.355072Z",
"url": "https://files.pythonhosted.org/packages/49/52/5125e4bd1245c69f142be1be1c9e82aec36895b35085c84721cc54f47a66/pydal2sql-1.1.5-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "87e584e2a45df4c0a903efead1e9921d1c2399298a80d1e159bac326e82e77a8",
"md5": "d6b7bb862f05c13edb00943155ef4ccc",
"sha256": "44a286df1960e5b28b9d495459bc7299d91f1e5b14dafe48bd27e7d182e31e85"
},
"downloads": -1,
"filename": "pydal2sql-1.1.5.tar.gz",
"has_sig": false,
"md5_digest": "d6b7bb862f05c13edb00943155ef4ccc",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.10",
"size": 90306,
"upload_time": "2024-07-04T14:47:01",
"upload_time_iso_8601": "2024-07-04T14:47:01.880567Z",
"url": "https://files.pythonhosted.org/packages/87/e5/84e2a45df4c0a903efead1e9921d1c2399298a80d1e159bac326e82e77a8/pydal2sql-1.1.5.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-07-04 14:47:01",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "robinvandernoord",
"github_project": "pydal2sql#readme",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "pydal2sql"
}
