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 Distribution
Built Distribution
Close
Hashes for fastapi-efficient-sql-0.0.2.tar.gz
Algorithm | Hash digest | |
---|---|---|
SHA256 | 4430443ef1e4c3ce44fca1145cefbcd68f94b090a459b04f4a40412df1a71ee9 |
|
MD5 | 451e6455e8553eb985337251ca917cf5 |
|
BLAKE2b-256 | 1f9e57c218d6b2e074ec41fe85d76b5be3d6d99796230f1517c7418ba4e99e10 |
Close
Hashes for fastapi_efficient_sql-0.0.2-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 01ca94c5052eb8711208ac30d5f1ae3109e1e4cdcb998ab9ac627c5885d15bd6 |
|
MD5 | 151d48c94f73bd12dc095a98c1eb4569 |
|
BLAKE2b-256 | c474e85d401bb736bbc6547d468adf369dc50b408fb2f93e10f1582f6822ee75 |