Generate bulk DML SQL and execute them based on `tortoise-orm` and mysql8.0+, and integrated with `fastapi`.
Project description
fastapi-efficient-sql
Installed by pip install fastapi-efficient-sql
Some preparations before using efficient sql
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
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)],
)
Generate sql and execute
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
WHERE id IN (1,2,3) AND gender=1
complex example
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"],
limit=10,
)
Generate sql and execute
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 10
update_json_field
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,
)
Generate sql and execute
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}}')
WHERE `id`=8
upsert_on_duplicated
await AccountMgr.upsert_on_duplicated(
[
{'id': 7, 'gender': 1, 'name': '斉藤 修平', 'locale': 'ja_JP', 'extend': {}},
{'id': 8, 'gender': 1, 'name': 'Ojas Salvi', 'locale': 'en_IN', 'extend': {}},
{'id': 9, 'gender': 1, 'name': '羊淑兰', 'locale': 'zh_CN', 'extend': {}}
],
insert_fields=["id", "gender", "name", "locale", "extend"],
upsert_fields=["name", "locale"],
using_values=False,
)
Generate sql and execute
INSERT INTO account
(id, gender, name, locale, extend)
VALUES
(7, 1, '斉藤 修平', 'ja_JP', '{}'), (8, 1, 'Ojas Salvi', 'en_IN', '{}'), (9, 1, '羊淑兰', 'zh_CN', '{}')
AS `new_account` ON DUPLICATE KEY UPDATE name=`new_account`.name, locale=`new_account`.locale
insert_into_select
await AccountMgr.insert_into_select(
wheres=Q(id__in=[4, 5, 6]),
remain_fields=["gender", "locale"],
assign_field_dict={
"active": False,
"name": RawSQL("CONCAT(LEFT(name, 26), ' [NEW]')"),
"extend": {},
},
to_table="account_bak",
)
Generate sql and execute
INSERT INTO account_bak
(gender, locale, active, name, extend)
SELECT gender, locale, False active, CONCAT(LEFT(name, 26), ' [NEW]') name, '{}' extend
FROM account
WHERE `id` IN (4,5,6)
bulk_update_with_fly_table
await AccountMgr.bulk_update_with_fly_table(
[
{'id': 7, 'active': False, 'gender': <GenderEnum.male: 1>},
{'id': 15, 'active': True, 'gender': <GenderEnum.unknown: 0>}
],
join_fields=["id"],
update_fields=["active", "gender"],
using_values=True,
)
Generate sql and execute
UPDATE account
JOIN (
SELECT * FROM (
VALUES
ROW(7, False, 1), ROW(15, True, 0)
) AS fly_table (id, active, gender)
) tmp ON account.id=tmp.id
SET account.active=tmp.active, account.gender=tmp.gender
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distributions
No source distribution files available for this release.See tutorial on generating distribution archives.
Built Distribution
Close
Hashes for fastapi_efficient_sql-0.0.1-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 984240ebe3b2020150a2664b370eba8de48e2d99dffec783519db973d5dcb72f |
|
MD5 | 7453dbb62782833b3bc78e48b5ab06a8 |
|
BLAKE2b-256 | 87a18a6fd896b044a88ff0f833668e9d607a7b7333aac32d0587b187bfcf7b66 |