| Name | sql-blocks JSON |
| Version |
0.1.1
JSON |
| download |
| home_page | https://github.com/julio-cascalles/sql_blocks |
| Summary | Allows you to create objects for parts of SQL query commands. Also to combine these objects by joining them, adding or removing parts... |
| upload_time | 2024-09-10 16:19:19 |
| maintainer | None |
| docs_url | None |
| author | Júlio Cascalles |
| requires_python | >=3.8 |
| license | None |
| keywords |
|
| VCS |
 |
| bugtrack_url |
|
| requirements |
No requirements were recorded.
|
| Travis-CI |
No Travis.
|
| coveralls test coverage |
No coveralls.
|
# SQL_Blocks
### 1 - You can assemble a simple object that will then be converted into an SQL command:
> a = Select('Actor') # --> SELECT * FROM Actor act
_Note that an alias "act" has been added._
You can specify your own alias: `a = Select('Actor a')`
---
### 2 - You can also add a field, like this...
* a = Select('Actor a', **name=Field**)
* Here are another ways to add a field:
- Select('Actor a', name=Distinct )
- Select('Actor a', name=NamedField('actors_name'))
- Select(
'Actor a',
name=NamedField('actors_name', Distinct)
)
2.1 -- Using expression as a field:
```
Select(
'Product',
due_date=NamedField(
'YEAR_ref',
ExpressionField('extract(year from %)') # <<---
)
)
```
...should return:
**SELECT extract(year from due_date) as YEAR_ref...**
---
### 3 - To set conditions, use **Where**:
* For example, `a = Select(... age=Where.gt(45) )`
Some possible conditions:
* field=Where.eq(value) - ...the field is EQUAL to the value;
* field=Where.gt(value) - ...the field is GREATER than the value;
* field=Where.lt(value) - ...the field is LESS than the value;
3.1 -- If you want to filter the field on a range of values:
`a = Select( 'Actor a', age=Between(45, 69) )`
3.2 -- Sub-queries:
```
query = Select('Movie m', title=Field,
id=SelectIN(
'Review r',
rate=Where.gt(4.5),
movie_id=Distinct
)
)
```
**>> print(query)**
SELECT
m.title
FROM
Movie m
WHERE
m.id IN (
SELECT DISTINCT r.movie
FROM Review r WHERE r.rate > 4.5
)
3.3 -- Optional conditions:
```
OR=Options(
genre=Where.eq("Sci-Fi"),
awards=Where.like("Oscar")
)
```
> Could be AND=Options(...)
3.4 -- Negative conditions use the _Not_ class instead of _Where_
```
based_on_book=Not.is_null()
```
3.5 -- List of values
```
hash_tag=Where.list(['space', 'monster', 'gore'])
```
---
### 4 - A field can be two things at the same time:
* m = Select('Movie m' release_date=[Field, OrderBy])
- This means that the field will appear in the results and also that the query will be ordered by that field.
* Applying **GROUP BY** to item 3.2, it would look like this:
```
SelectIN(
'Review r', movie=[GroupBy, Distinct],
rate=Having.avg(Where.gt(4.5))
)
```
---
### 5 - Relationships:
```
query = Select('Actor a', name=Field,
cast=Select('Cast c', id=PrimaryKey)
)
```
**>> print(query)**
```
SELECT
a.name
FROM
Actor a
JOIN Cast c ON (a.cast = c.id)
```
---
### 6 - The reverse process (parse):
```
text = """
SELECT
cas.role,
m.title,
m.release_date,
a.name as actors_name
FROM
Actor a
LEFT JOIN Cast cas ON (a.cast = cas.id)
LEFT JOIN Movie m ON (cas.movie = m.id)
WHERE
(
m.genre = 'Sci-Fi'
OR
m.awards LIKE '%Oscar%'
)
AND a.age <= 69 AND a.age >= 45
ORDER BY
m.release_date DESC
"""
```
`a, c, m = Select.parse(text)`
**6.1 --- print(a)**
```
SELECT
a.name as actors_name
FROM
Actor a
WHERE
a.age <= 69
AND a.age >= 45
```
**6.2 --- print(c)**
SELECT
c.role
FROM
Cast c
**6.3 --- print(m)**
SELECT
m.title,
m.release_date
FROM
Movie m
WHERE
( m.genre = 'Sci-Fi' OR m.awards LIKE '%Oscar%' )
ORDER BY
m.release_date DESC
**6.4 --- print(a+c)**
SELECT
a.name as actors_name,
cas.role
FROM
Actor a
JOIN Cast cas ON (a.cast = cas.id)
WHERE
a.age >= 45
AND a.age <= 69
**6.5 --- print(c+m)**
> `... or print(m+c)`
SELECT
cas.role,
m.title,
m.release_date,
m.director
FROM
Cast cas
JOIN Movie m ON (cas.movie = m.id)
WHERE
( m.genre = 'Sci-Fi' OR m.awards LIKE '%Oscar%' )
AND m.director LIKE '%Coppola%'
ORDER BY
m.release_date,
m.director
---
### 7 - You can add or delete attributes directly in objects:
* a(gender=Field)
* m.delete('director')
---
### 8 - Defining relationship on separate objects:
```
a = Select...
c = Select...
m = Select...
```
`a + c => ERROR: "No relationship found between Actor and Cast"`
8.1 - But...
a( cast=ForeignKey('Cast') )
c(id=PrimaryKey)
**a + c => Ok!**
8.2
c( movie=ForeignKey('Movie') )
m(id=PrimaryKey)
> **c + m => Ok!**
>> **m + c => Ok!**
---
### 9 - Comparing objects
9.1
```
a1 = Select.parse('''
SELECT gender, Max(act.age) FROM Actor act
WHERE act.age <= 69 AND act.age >= 45
GROUP BY gender
''')[0]
a2 = Select('Actor',
age=[ Between(45, 69), Max ],
gender=[GroupBy, Field]
)
```
> **a1 == a2 # --- True!**
9.2
```
m1 = Select.parse("""
SELECT title, release_date FROM Movie m ORDER BY release_date
WHERE m.genre = 'Sci-Fi' AND m.awards LIKE '%Oscar%'
""")[0]
m2 = Select.parse("""
SELECT release_date, title
FROM Movie m
WHERE m.awards LIKE '%Oscar%' AND m.genre = 'Sci-Fi'
ORDER BY release_date
""")[0]
```
**m1 == m2 # --- True!**
9.3
```
best_movies = SelectIN(
Review=Table('role'),
rate=[GroupBy, Having.avg(Where.gt(4.5))]
)
m1 = Select(
Movie=Table('title,release_date),
id=best_movies
)
sql = "SELECT rev.role FROM Review rev GROUP BY rev.rate HAVING Avg(rev.rate) > 4.5"
m2 = Select(
'Movie', release_date=Field, title=Field,
id=Where(f"IN ({sql})")
)
```
**m1 == m2 # --- True!**
---
### 10 - CASE...WHEN...THEN
Select(
'Product',
label=Case('price').when(
lt(50), 'cheap'
).when(
gt(100), 'expensive'
).else_value(
'normal'
)
)
---
### 11 - optimize method
p1 = Select.parse("""
SELECT * FROM Product p
WHERE (p.category = 'Gizmo'
OR p.category = 'Gadget'
OR p.category = 'Doohickey')
AND NOT price <= 387.64
AND YEAR(last_sale) = 2024
ORDER BY
category
""")[0]
p1.optimize() # <<===============
p2 = Select.parse("""
SELECT category FROM Product p
WHERE category IN ('Gizmo','Gadget','Doohickey')
and p.price > 387.64
and p.last_sale >= '2024-01-01'
and p.last_sale <= '2024-12-31'
ORDER BY p.category LIMIT 100
""")[0]
p1 == p2 # --- True!
This will...
* Replace `OR` conditions to `SELECT IN ...`
* Put `LIMIT` if no fields or conditions defined;
* Normalizes inverted conditions;
* Auto includes fields present in `ORDER/GROUP BY`;
* Replace `YEAR` function with date range comparison.
> The method allows you to select which rules you want to apply in the optimization...Or define your own rules!
Raw data
{
"_id": null,
"home_page": "https://github.com/julio-cascalles/sql_blocks",
"name": "sql-blocks",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.8",
"maintainer_email": null,
"keywords": null,
"author": "J\u00falio Cascalles",
"author_email": "Julio Cascalles <julio.cascalles@outlook.com>",
"download_url": "https://files.pythonhosted.org/packages/26/bd/bf52b3470b183700c757b1cf580da5bb626252891f4446d7754d32fb6b81/sql_blocks-0.1.1.tar.gz",
"platform": null,
"description": "# SQL_Blocks\r\n\r\n### 1 - You can assemble a simple object that will then be converted into an SQL command:\r\n\r\n> a = Select('Actor') # --> SELECT * FROM Actor act\r\n\r\n_Note that an alias \"act\" has been added._\r\n\r\nYou can specify your own alias: `a = Select('Actor a')`\r\n\r\n---\r\n### 2 - You can also add a field, like this...\r\n\r\n* a = Select('Actor a', **name=Field**)\r\n\r\n* Here are another ways to add a field:\r\n - Select('Actor a', name=Distinct )\r\n\r\n - Select('Actor a', name=NamedField('actors_name'))\r\n\r\n - Select(\r\n 'Actor a', \r\n name=NamedField('actors_name', Distinct)\r\n )\r\n\r\n\r\n 2.1 -- Using expression as a field:\r\n```\r\n Select(\r\n 'Product',\r\n due_date=NamedField(\r\n 'YEAR_ref',\r\n ExpressionField('extract(year from %)') # <<---\r\n )\r\n )\r\n```\r\n...should return: \r\n**SELECT extract(year from due_date) as YEAR_ref...**\r\n\r\n---\r\n\r\n### 3 - To set conditions, use **Where**:\r\n* For example, `a = Select(... age=Where.gt(45) )`\r\n\r\n Some possible conditions:\r\n * field=Where.eq(value) - ...the field is EQUAL to the value;\r\n * field=Where.gt(value) - ...the field is GREATER than the value;\r\n * field=Where.lt(value) - ...the field is LESS than the value;\r\n\r\n 3.1 -- If you want to filter the field on a range of values:\r\n \r\n `a = Select( 'Actor a', age=Between(45, 69) )`\r\n\r\n 3.2 -- Sub-queries:\r\n```\r\nquery = Select('Movie m', title=Field,\r\n id=SelectIN(\r\n 'Review r',\r\n rate=Where.gt(4.5),\r\n movie_id=Distinct\r\n )\r\n)\r\n```\r\n\r\n**>> print(query)** \r\n\r\n SELECT\r\n m.title\r\n FROM\r\n Movie m\r\n WHERE\r\n m.id IN (\r\n SELECT DISTINCT r.movie\r\n FROM Review r WHERE r.rate > 4.5\r\n )\r\n \r\n3.3 -- Optional conditions:\r\n```\r\n OR=Options(\r\n genre=Where.eq(\"Sci-Fi\"),\r\n awards=Where.like(\"Oscar\")\r\n )\r\n```\r\n> Could be AND=Options(...)\r\n\r\n3.4 -- Negative conditions use the _Not_ class instead of _Where_\r\n```\r\nbased_on_book=Not.is_null()\r\n```\r\n\r\n3.5 -- List of values\r\n```\r\nhash_tag=Where.list(['space', 'monster', 'gore'])\r\n```\r\n\r\n---\r\n### 4 - A field can be two things at the same time:\r\n\r\n* m = Select('Movie m' release_date=[Field, OrderBy])\r\n - This means that the field will appear in the results and also that the query will be ordered by that field.\r\n* Applying **GROUP BY** to item 3.2, it would look like this:\r\n ``` \r\n SelectIN(\r\n 'Review r', movie=[GroupBy, Distinct],\r\n rate=Having.avg(Where.gt(4.5))\r\n )\r\n ```\r\n---\r\n### 5 - Relationships:\r\n```\r\n query = Select('Actor a', name=Field,\r\n cast=Select('Cast c', id=PrimaryKey)\r\n )\r\n```\r\n**>> print(query)** \r\n```\r\nSELECT\r\n a.name\r\nFROM\r\n Actor a\r\n JOIN Cast c ON (a.cast = c.id) \r\n```\r\n\r\n---\r\n### 6 - The reverse process (parse):\r\n```\r\ntext = \"\"\"\r\n SELECT\r\n cas.role,\r\n m.title,\r\n m.release_date,\r\n a.name as actors_name\r\n FROM\r\n Actor a\r\n LEFT JOIN Cast cas ON (a.cast = cas.id)\r\n LEFT JOIN Movie m ON (cas.movie = m.id)\r\n WHERE\r\n (\r\n m.genre = 'Sci-Fi'\r\n OR\r\n m.awards LIKE '%Oscar%'\r\n )\r\n AND a.age <= 69 AND a.age >= 45\r\n ORDER BY\r\n m.release_date DESC\r\n\"\"\"\r\n```\r\n\r\n`a, c, m = Select.parse(text)`\r\n\r\n**6.1 --- print(a)**\r\n```\r\n SELECT\r\n a.name as actors_name\r\n FROM\r\n Actor a\r\n WHERE\r\n a.age <= 69\r\n AND a.age >= 45\r\n```\r\n\r\n**6.2 --- print(c)**\r\n\r\n SELECT\r\n c.role\r\n FROM\r\n Cast c\r\n\r\n**6.3 --- print(m)**\r\n\r\n SELECT\r\n m.title,\r\n m.release_date\r\n FROM\r\n Movie m\r\n WHERE\r\n ( m.genre = 'Sci-Fi' OR m.awards LIKE '%Oscar%' )\r\n ORDER BY\r\n m.release_date DESC\r\n\r\n\r\n\r\n**6.4 --- print(a+c)**\r\n\r\n SELECT\r\n a.name as actors_name,\r\n cas.role\r\n FROM\r\n Actor a\r\n JOIN Cast cas ON (a.cast = cas.id)\r\n WHERE\r\n a.age >= 45\r\n AND a.age <= 69\r\n\r\n**6.5 --- print(c+m)** \r\n> `... or print(m+c)`\r\n\r\n SELECT\r\n cas.role,\r\n m.title,\r\n m.release_date,\r\n m.director\r\n FROM\r\n Cast cas\r\n JOIN Movie m ON (cas.movie = m.id)\r\n WHERE\r\n ( m.genre = 'Sci-Fi' OR m.awards LIKE '%Oscar%' )\r\n AND m.director LIKE '%Coppola%'\r\n ORDER BY\r\n m.release_date,\r\n m.director\r\n---\r\n\r\n### 7 - You can add or delete attributes directly in objects:\r\n* a(gender=Field)\r\n* m.delete('director')\r\n\r\n---\r\n\r\n### 8 - Defining relationship on separate objects:\r\n```\r\na = Select...\r\nc = Select...\r\nm = Select...\r\n```\r\n`a + c => ERROR: \"No relationship found between Actor and Cast\"`\r\n\r\n8.1 - But...\r\n\r\n a( cast=ForeignKey('Cast') )\r\n c(id=PrimaryKey)\r\n\r\n**a + c => Ok!**\r\n\r\n8.2\r\n\r\n c( movie=ForeignKey('Movie') )\r\n m(id=PrimaryKey)\r\n\r\n> **c + m => Ok!**\r\n>> **m + c => Ok!**\r\n\r\n\r\n---\r\n\r\n### 9 - Comparing objects\r\n\r\n9.1\r\n```\r\n a1 = Select.parse('''\r\n SELECT gender, Max(act.age) FROM Actor act\r\n WHERE act.age <= 69 AND act.age >= 45\r\n GROUP BY gender\r\n ''')[0]\r\n\r\n a2 = Select('Actor',\r\n age=[ Between(45, 69), Max ],\r\n gender=[GroupBy, Field]\r\n ) \r\n```\r\n> **a1 == a2 # --- True!**\r\n\r\n\r\n\r\n9.2\r\n```\r\n m1 = Select.parse(\"\"\"\r\n SELECT title, release_date FROM Movie m ORDER BY release_date \r\n WHERE m.genre = 'Sci-Fi' AND m.awards LIKE '%Oscar%'\r\n \"\"\")[0]\r\n\r\n m2 = Select.parse(\"\"\"\r\n SELECT release_date, title\r\n FROM Movie m\r\n WHERE m.awards LIKE '%Oscar%' AND m.genre = 'Sci-Fi'\r\n ORDER BY release_date \r\n \"\"\")[0]\r\n```\r\n\r\n**m1 == m2 # --- True!**\r\n\r\n\r\n9.3\r\n```\r\nbest_movies = SelectIN(\r\n Review=Table('role'),\r\n rate=[GroupBy, Having.avg(Where.gt(4.5))]\r\n)\r\nm1 = Select(\r\n Movie=Table('title,release_date),\r\n id=best_movies\r\n)\r\n\r\nsql = \"SELECT rev.role FROM Review rev GROUP BY rev.rate HAVING Avg(rev.rate) > 4.5\"\r\nm2 = Select(\r\n 'Movie', release_date=Field, title=Field,\r\n id=Where(f\"IN ({sql})\")\r\n)\r\n```\r\n**m1 == m2 # --- True!**\r\n\r\n---\r\n\r\n### 10 - CASE...WHEN...THEN\r\n Select(\r\n 'Product',\r\n label=Case('price').when(\r\n lt(50), 'cheap'\r\n ).when(\r\n gt(100), 'expensive'\r\n ).else_value(\r\n 'normal'\r\n )\r\n )\r\n\r\n---\r\n\r\n### 11 - optimize method\r\n p1 = Select.parse(\"\"\"\r\n SELECT * FROM Product p\r\n WHERE (p.category = 'Gizmo'\r\n OR p.category = 'Gadget'\r\n OR p.category = 'Doohickey')\r\n AND NOT price <= 387.64\r\n AND YEAR(last_sale) = 2024\r\n ORDER BY\r\n category\r\n \"\"\")[0]\r\n p1.optimize() # <<===============\r\n p2 = Select.parse(\"\"\"\r\n SELECT category FROM Product p\r\n WHERE category IN ('Gizmo','Gadget','Doohickey')\r\n and p.price > 387.64\r\n and p.last_sale >= '2024-01-01'\r\n and p.last_sale <= '2024-12-31'\r\n ORDER BY p.category LIMIT 100\r\n \"\"\")[0]\r\n p1 == p2 # --- True!\r\n\r\n This will...\r\n* Replace `OR` conditions to `SELECT IN ...`\r\n* Put `LIMIT` if no fields or conditions defined;\r\n* Normalizes inverted conditions;\r\n* Auto includes fields present in `ORDER/GROUP BY`;\r\n* Replace `YEAR` function with date range comparison.\r\n\r\n> The method allows you to select which rules you want to apply in the optimization...Or define your own rules!\r\n",
"bugtrack_url": null,
"license": null,
"summary": "Allows you to create objects for parts of SQL query commands. Also to combine these objects by joining them, adding or removing parts...",
"version": "0.1.1",
"project_urls": {
"Homepage": "https://github.com/julio-cascalles/sql_blocks"
},
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "6e52f1bc275c391e33b450c1c868e1c3a273686b381860236f5c9d4083ded98a",
"md5": "b0d0433f4b0890542b1b816d22584198",
"sha256": "55a0c31fbc832ad91a77ea32ddf57263f101f96c9ff0f3780b0ec25de287fe3e"
},
"downloads": -1,
"filename": "sql_blocks-0.1.1-py3-none-any.whl",
"has_sig": false,
"md5_digest": "b0d0433f4b0890542b1b816d22584198",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.8",
"size": 10364,
"upload_time": "2024-09-10T16:19:18",
"upload_time_iso_8601": "2024-09-10T16:19:18.203567Z",
"url": "https://files.pythonhosted.org/packages/6e/52/f1bc275c391e33b450c1c868e1c3a273686b381860236f5c9d4083ded98a/sql_blocks-0.1.1-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "26bdbf52b3470b183700c757b1cf580da5bb626252891f4446d7754d32fb6b81",
"md5": "bcf4d8aa06e9cd7df4f24bc001cc9bbd",
"sha256": "1044c9ea279bd9e4b9242cfa34f0ce1c66a1208690fc4958b394f0fc3926b4e2"
},
"downloads": -1,
"filename": "sql_blocks-0.1.1.tar.gz",
"has_sig": false,
"md5_digest": "bcf4d8aa06e9cd7df4f24bc001cc9bbd",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.8",
"size": 12867,
"upload_time": "2024-09-10T16:19:19",
"upload_time_iso_8601": "2024-09-10T16:19:19.209872Z",
"url": "https://files.pythonhosted.org/packages/26/bd/bf52b3470b183700c757b1cf580da5bb626252891f4446d7754d32fb6b81/sql_blocks-0.1.1.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-09-10 16:19:19",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "julio-cascalles",
"github_project": "sql_blocks",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"lcname": "sql-blocks"
}
