# fastapi-efficient-sql
Installed as package by `pip install fastapi-efficient-sql`
Install developing requirements by `pyenv local 3.7.9`, `poetry env use 3.7.9`, `poetry shell` and `pip install -r requirements-dev.txt`
Run demo service by `python -m examples.service`
Run unittest by `pytest -sv`
## Some preparations before using efficient sql
```python
from fastapi_esql import AppMetaclass, BaseManager, BaseModel
class DemoMetaclass(AppMetaclass):
def get_ro_conn(self):
return Tortoise.get_connection("demo_ro")
def get_rw_conn(self):
return Tortoise.get_connection("demo_rw")
class Account(BaseModel):
id = fields.IntField(pk=True)
active = fields.BooleanField(null=False, default=True)
gender = fields.IntEnumField(GenderEnum, null=False, default=GenderEnum.unknown)
name = fields.CharField(max_length=32, null=False, default="")
locale = fields.CharEnumField(LocaleEnum, max_length=5, null=False)
class AccountMgr(BaseManager, metaclass=DemoMetaclass):
model = Account
```
## Some supported efficient sql
### **select_custom_fields**
**basic example**
```python
aids = [1, 2, 3]
await AccountMgr.select_custom_fields(
fields=[
"id", "extend ->> '$.last_login.ipv4' ipv4",
"extend ->> '$.last_login.start_datetime' start_datetime",
"CAST(extend ->> '$.last_login.online_sec' AS SIGNED) online_sec"
],
wheres=f"id IN ({','.join(map(str, aids))}) AND gender=1", # These 4 types of `wheres` are equal
# wheres=Q(Q(id__in=aids), Q(gender=1), join_type="AND"),
# wheres={"id__in": aids, "gender": 1},
# wheres=[Q(id__in=aids), Q(gender=1)],
index="PRIMARY",
)
```
Generate sql and execute
```sql
SELECT
id, extend ->> '$.last_login.ipv4' ipv4, extend ->> '$.last_login.start_datetime' start_datetime, CAST(extend ->> '$.last_login.online_sec' AS SIGNED) online_sec
FROM `account` FORCE INDEX (`PRIMARY`)
WHERE id IN (1,2,3) AND gender=1
```
**complex example**
```python
await AccountMgr.select_custom_fields(
fields=[
"locale", "gender", "COUNT(1) cnt"
],
wheres=Q(id__range=[1, 12]),
groups=["locale", "gender"],
having="cnt > 0",
orders=["locale", "-gender"],
offset=0,
limit=10,
)
```
Generate sql and execute
```sql
SELECT
locale, gender, COUNT(1) cnt
FROM `account`
WHERE `id` BETWEEN 1 AND 12
GROUP BY locale, gender
HAVING cnt > 0
ORDER BY locale ASC, gender DESC
LIMIT 0, 10
```
### **update_json_field**
```python
await AccountMgr.update_json_field(
json_field="extend",
wheres=Q(id=8),
merge_dict={
"updated_at": "2022-10-30 21:34:15",
"info": {
"online_sec": 636,
}
},
path_value_dict={
"$.last_login": {
"ipv4": "209.182.101.161",
},
"$.uuid": "fd04f7f2-24fc-4a73-a1d7-b6e99a464c5f",
},
remove_paths=["$.deprecated"],
json_type=dict,
assign_field_dict={
"active": True,
"name": "new_name",
},
)
```
Generate sql and execute
```sql
UPDATE `account` SET extend =
JSON_MERGE_PATCH(JSON_SET(JSON_REMOVE(COALESCE(extend, '{}'), '$.deprecated'), '$.last_login',CAST('{"ipv4": "209.182.101.161"}' AS JSON), '$.uuid','fd04f7f2-24fc-4a73-a1d7-b6e99a464c5f'), '{"updated_at": "2022-10-30 21:34:15", "info": {"online_sec": 636}}')
, active=1, name='new_name'
WHERE `id`=8
```
### **upsert_on_duplicate**
```python
await AccountMgr.upsert_on_duplicate(
[
{"id": 10, "gender": 1, "name": "田中 知実", "locale": "ja_JP", "extend": {"rdm": 1}},
{"id": 11, "gender": 2, "name": "Tara Chadha", "locale": "en_IN", "extend": {"rdm": 10}},
{"id": 12, "gender": 2, "name": "吴磊", "locale": "zh_CN", "extend": {"rdm": 9}},
],
insert_fields=["id", "gender", "name", "locale", "extend"],
upsert_fields=["gender", "name"],
merge_fields=["extend"],
)
```
Generate sql and execute
```sql
INSERT INTO `account`
(id, gender, name, locale, extend)
VALUES
(10, 1, '田中 知実', 'ja_JP', '{"rdm": 1}'),
(11, 2, 'Tara Chadha', 'en_IN', '{"rdm": 10}'),
(12, 2, '吴磊', 'zh_CN', '{"rdm": 9}')
AS `new_account` ON DUPLICATE KEY UPDATE gender=`new_account`.gender, name=`new_account`.name, extend=JSON_MERGE_PATCH(COALESCE(`account`.extend, '{}'), `new_account`.extend)
```
### **insert_into_select**
```python
await AccountMgr.insert_into_select(
wheres=Q(id__in=[4, 5, 6]),
remain_fields=["gender"],
assign_field_dict={
"locale": Cases("id", {3: LocaleEnum.zh_CN, 4: LocaleEnum.en_US, 5: LocaleEnum.fr_FR}, default=""),
"active": False,
"name": RawSQL("CONCAT(LEFT(name, 26), ' [NEW]')"),
"extend": {},
},
to_table="account_bak",
)
```
Generate sql and execute
```sql
INSERT INTO `account_bak`
(gender, locale, active, name, extend)
SELECT gender, CASE id WHEN 3 THEN 'zh_CN' WHEN 4 THEN 'en_US' WHEN 5 THEN 'fr_FR' ELSE '' END locale, 0 active, CONCAT(LEFT(name, 26), ' [NEW]') name, '{}' extend
FROM `account`
WHERE `id` IN (4,5,6)
```
### **bulk_update_from_dicts**
```python
await AccountMgr.bulk_update_from_dicts(
[
{"id": 7, "active": False, "deleted": False, "gender": GenderEnum.male, "extend": {"test": 1, "debug": 0}},
{"id": 15, "active": True, "deleted": False, "gender": GenderEnum.unknown, "extend": {"test": 1, "debug": 0}}
],
join_fields=["id", "deleted"],
update_fields=["active", "gender"],
merge_fields=["extend"],
)
```
Generate sql and execute
```sql
UPDATE `account`
JOIN (
SELECT * FROM (
VALUES
ROW(7, 0, 0, 1, '{"test": 1, "debug": 0}'),
ROW(15, 0, 1, 0, '{"test": 1, "debug": 0}')
) AS fly_table (id, deleted, active, gender, extend)
) tmp ON `account`.id=tmp.id AND `account`.deleted=tmp.deleted
SET `account`.active=tmp.active, `account`.gender=tmp.gender, `account`.extend=JSON_MERGE_PATCH(COALESCE(`account`.extend, '{}'), tmp.extend)
```
Raw data
{
"_id": null,
"home_page": "https://github.com/NightMarcher/fastapi-efficient-sql",
"name": "fastapi-efficient-sql",
"maintainer": null,
"docs_url": null,
"requires_python": "<4.0,>=3.7",
"maintainer_email": null,
"keywords": "sql, fastapi, tortoise-orm, mysql8, bulk-operation",
"author": "BryanLee",
"author_email": "bryanlee@126.com",
"download_url": "https://files.pythonhosted.org/packages/e1/ab/8f778bf5f6dc7becba6cc842991dac3da55319ac0764b3080265c93caf41/fastapi_efficient_sql-0.0.15.tar.gz",
"platform": null,
"description": "# fastapi-efficient-sql\n\nInstalled as package by `pip install fastapi-efficient-sql`\n\nInstall developing requirements by `pyenv local 3.7.9`, `poetry env use 3.7.9`, `poetry shell` and `pip install -r requirements-dev.txt`\n\nRun demo service by `python -m examples.service`\n\nRun unittest by `pytest -sv`\n\n## Some preparations before using efficient sql\n```python\nfrom fastapi_esql import AppMetaclass, BaseManager, BaseModel\n\n\nclass DemoMetaclass(AppMetaclass):\n\n def get_ro_conn(self):\n return Tortoise.get_connection(\"demo_ro\")\n\n def get_rw_conn(self):\n return Tortoise.get_connection(\"demo_rw\")\n\n\nclass Account(BaseModel):\n id = fields.IntField(pk=True)\n active = fields.BooleanField(null=False, default=True)\n gender = fields.IntEnumField(GenderEnum, null=False, default=GenderEnum.unknown)\n name = fields.CharField(max_length=32, null=False, default=\"\")\n locale = fields.CharEnumField(LocaleEnum, max_length=5, null=False)\n\n\nclass AccountMgr(BaseManager, metaclass=DemoMetaclass):\n model = Account\n```\n\n## Some supported efficient sql\n### **select_custom_fields**\n**basic example**\n```python\naids = [1, 2, 3]\n\nawait AccountMgr.select_custom_fields(\n fields=[\n \"id\", \"extend ->> '$.last_login.ipv4' ipv4\",\n \"extend ->> '$.last_login.start_datetime' start_datetime\",\n \"CAST(extend ->> '$.last_login.online_sec' AS SIGNED) online_sec\"\n ],\n wheres=f\"id IN ({','.join(map(str, aids))}) AND gender=1\", # These 4 types of `wheres` are equal\n # wheres=Q(Q(id__in=aids), Q(gender=1), join_type=\"AND\"),\n # wheres={\"id__in\": aids, \"gender\": 1},\n # wheres=[Q(id__in=aids), Q(gender=1)],\n index=\"PRIMARY\",\n)\n```\nGenerate sql and execute\n```sql\n SELECT\n id, extend ->> '$.last_login.ipv4' ipv4, extend ->> '$.last_login.start_datetime' start_datetime, CAST(extend ->> '$.last_login.online_sec' AS SIGNED) online_sec\n FROM `account` FORCE INDEX (`PRIMARY`)\n WHERE id IN (1,2,3) AND gender=1\n```\n\n**complex example**\n```python\nawait AccountMgr.select_custom_fields(\n fields=[\n \"locale\", \"gender\", \"COUNT(1) cnt\"\n ],\n wheres=Q(id__range=[1, 12]),\n groups=[\"locale\", \"gender\"],\n having=\"cnt > 0\",\n orders=[\"locale\", \"-gender\"],\n offset=0,\n limit=10,\n)\n```\nGenerate sql and execute\n```sql\n SELECT\n locale, gender, COUNT(1) cnt\n FROM `account`\n WHERE `id` BETWEEN 1 AND 12\n GROUP BY locale, gender\n HAVING cnt > 0\n ORDER BY locale ASC, gender DESC\n LIMIT 0, 10\n```\n\n### **update_json_field**\n```python\nawait AccountMgr.update_json_field(\n json_field=\"extend\",\n wheres=Q(id=8),\n merge_dict={\n \"updated_at\": \"2022-10-30 21:34:15\",\n \"info\": {\n \"online_sec\": 636,\n }\n },\n path_value_dict={\n \"$.last_login\": {\n \"ipv4\": \"209.182.101.161\",\n },\n \"$.uuid\": \"fd04f7f2-24fc-4a73-a1d7-b6e99a464c5f\",\n },\n remove_paths=[\"$.deprecated\"],\n json_type=dict,\n assign_field_dict={\n \"active\": True,\n \"name\": \"new_name\",\n },\n)\n```\nGenerate sql and execute\n```sql\n UPDATE `account` SET extend =\n JSON_MERGE_PATCH(JSON_SET(JSON_REMOVE(COALESCE(extend, '{}'), '$.deprecated'), '$.last_login',CAST('{\"ipv4\": \"209.182.101.161\"}' AS JSON), '$.uuid','fd04f7f2-24fc-4a73-a1d7-b6e99a464c5f'), '{\"updated_at\": \"2022-10-30 21:34:15\", \"info\": {\"online_sec\": 636}}')\n , active=1, name='new_name'\n WHERE `id`=8\n```\n\n### **upsert_on_duplicate**\n```python\nawait AccountMgr.upsert_on_duplicate(\n [\n {\"id\": 10, \"gender\": 1, \"name\": \"\u7530\u4e2d \u77e5\u5b9f\", \"locale\": \"ja_JP\", \"extend\": {\"rdm\": 1}},\n {\"id\": 11, \"gender\": 2, \"name\": \"Tara Chadha\", \"locale\": \"en_IN\", \"extend\": {\"rdm\": 10}},\n {\"id\": 12, \"gender\": 2, \"name\": \"\u5434\u78ca\", \"locale\": \"zh_CN\", \"extend\": {\"rdm\": 9}},\n ],\n insert_fields=[\"id\", \"gender\", \"name\", \"locale\", \"extend\"],\n upsert_fields=[\"gender\", \"name\"],\n merge_fields=[\"extend\"],\n)\n```\nGenerate sql and execute\n```sql\n INSERT INTO `account`\n (id, gender, name, locale, extend)\n VALUES\n (10, 1, '\u7530\u4e2d \u77e5\u5b9f', 'ja_JP', '{\"rdm\": 1}'),\n (11, 2, 'Tara Chadha', 'en_IN', '{\"rdm\": 10}'),\n (12, 2, '\u5434\u78ca', 'zh_CN', '{\"rdm\": 9}')\n AS `new_account` ON DUPLICATE KEY UPDATE gender=`new_account`.gender, name=`new_account`.name, extend=JSON_MERGE_PATCH(COALESCE(`account`.extend, '{}'), `new_account`.extend)\n```\n\n### **insert_into_select**\n```python\nawait AccountMgr.insert_into_select(\n wheres=Q(id__in=[4, 5, 6]),\n remain_fields=[\"gender\"],\n assign_field_dict={\n \"locale\": Cases(\"id\", {3: LocaleEnum.zh_CN, 4: LocaleEnum.en_US, 5: LocaleEnum.fr_FR}, default=\"\"),\n \"active\": False,\n \"name\": RawSQL(\"CONCAT(LEFT(name, 26), ' [NEW]')\"),\n \"extend\": {},\n },\n to_table=\"account_bak\",\n)\n```\nGenerate sql and execute\n```sql\n INSERT INTO `account_bak`\n (gender, locale, active, name, extend)\n SELECT gender, CASE id WHEN 3 THEN 'zh_CN' WHEN 4 THEN 'en_US' WHEN 5 THEN 'fr_FR' ELSE '' END locale, 0 active, CONCAT(LEFT(name, 26), ' [NEW]') name, '{}' extend\n FROM `account`\n WHERE `id` IN (4,5,6)\n```\n\n### **bulk_update_from_dicts**\n```python\nawait AccountMgr.bulk_update_from_dicts(\n [\n {\"id\": 7, \"active\": False, \"deleted\": False, \"gender\": GenderEnum.male, \"extend\": {\"test\": 1, \"debug\": 0}},\n {\"id\": 15, \"active\": True, \"deleted\": False, \"gender\": GenderEnum.unknown, \"extend\": {\"test\": 1, \"debug\": 0}}\n ],\n join_fields=[\"id\", \"deleted\"],\n update_fields=[\"active\", \"gender\"],\n merge_fields=[\"extend\"],\n)\n```\nGenerate sql and execute\n```sql\n UPDATE `account`\n JOIN (\n SELECT * FROM (\n VALUES\n ROW(7, 0, 0, 1, '{\"test\": 1, \"debug\": 0}'),\n ROW(15, 0, 1, 0, '{\"test\": 1, \"debug\": 0}')\n ) AS fly_table (id, deleted, active, gender, extend)\n ) tmp ON `account`.id=tmp.id AND `account`.deleted=tmp.deleted\n SET `account`.active=tmp.active, `account`.gender=tmp.gender, `account`.extend=JSON_MERGE_PATCH(COALESCE(`account`.extend, '{}'), tmp.extend)\n```\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "Generate bulk DML SQL and execute them based on Tortoise ORM and mysql8.0+, and integrated with FastAPI.",
"version": "0.0.15",
"project_urls": {
"Homepage": "https://github.com/NightMarcher/fastapi-efficient-sql"
},
"split_keywords": [
"sql",
" fastapi",
" tortoise-orm",
" mysql8",
" bulk-operation"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "1a13a7df8566cf228f8e77a415a34188f74477c1c17b0277c5e7cbc08cbd4a34",
"md5": "4e7ec31024a0c88e8b3b1b849518528c",
"sha256": "0ca5657abe2dcfd2c7ed39ddbf1af689a11835e885b9c96d8e8e038331ee30ce"
},
"downloads": -1,
"filename": "fastapi_efficient_sql-0.0.15-py3-none-any.whl",
"has_sig": false,
"md5_digest": "4e7ec31024a0c88e8b3b1b849518528c",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": "<4.0,>=3.7",
"size": 13925,
"upload_time": "2025-01-13T07:33:27",
"upload_time_iso_8601": "2025-01-13T07:33:27.661388Z",
"url": "https://files.pythonhosted.org/packages/1a/13/a7df8566cf228f8e77a415a34188f74477c1c17b0277c5e7cbc08cbd4a34/fastapi_efficient_sql-0.0.15-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "e1ab8f778bf5f6dc7becba6cc842991dac3da55319ac0764b3080265c93caf41",
"md5": "89699d6429e20e25f6ea0c196856f350",
"sha256": "0b6a2151722da985c618d0e4c794b679361dea63c4ce884ee22747f8e813ca98"
},
"downloads": -1,
"filename": "fastapi_efficient_sql-0.0.15.tar.gz",
"has_sig": false,
"md5_digest": "89699d6429e20e25f6ea0c196856f350",
"packagetype": "sdist",
"python_version": "source",
"requires_python": "<4.0,>=3.7",
"size": 12035,
"upload_time": "2025-01-13T07:33:30",
"upload_time_iso_8601": "2025-01-13T07:33:30.394240Z",
"url": "https://files.pythonhosted.org/packages/e1/ab/8f778bf5f6dc7becba6cc842991dac3da55319ac0764b3080265c93caf41/fastapi_efficient_sql-0.0.15.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-01-13 07:33:30",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "NightMarcher",
"github_project": "fastapi-efficient-sql",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"requirements": [
{
"name": "tortoise-orm",
"specs": [
[
">=",
"0.16.17"
]
]
}
],
"lcname": "fastapi-efficient-sql"
}
JSON
