sanic+tortoise-orm的基本使用

官方文档：https://tortoise-orm.readthedocs.io/en/latest/

配置ORM

#models.py
from tortoise.models import Model
from tortoise import fields
class User(Model):
    user_id = fields.IntField(pk=True, source_field="userID")
    user_name = fields.CharField(max_length=100)
    date_field = fields.DateTimeField()
    group = fields.ForeignKeyField("auth.Group", on_delete=field.SET_NULL, null=True)
    # fields.ForeignKeyField("models.Books", on_delete=field.SET_NULL, null=True)
    # def __str__(self):
    #     return self.name
    class Meta:
        table = "user"  #数据表名字
        unique_together=("field_a", "field_b")#唯一字段
        indexes = ("field_a", "field_b")  #索引
        ordering = ["name", "-date_field"]   #默认排序

class Books(Model):
    id = fields.IntField(pk=True, , source_field="bookID")

#config.py
db_config = {
    'connections': {
        # Dict format for connection
        'default': {
            'engine': 'tortoise.backends.mysql',
            'credentials': {
                'host': '',
                'port': '3306',
                'user': '',
                'password': '',
                'database': 'testdb',
                "maxsize":"15",
                "minsize":"5"
            }
        },
        # 也可以设置 db_url
        # 'default': 'postgres://postgres:qwerty123@localhost:5432/events'
    },
    'apps': {
        # app的名字，类似于django中注册的app  ->  INSTALLED_APPS
        'auth': {
            # models的路径
            'models': ['apps.v1.user.models', 'apps.v1.group.models'],
            #设置key值“default”的数据库连接
            'default_connection': 'default',
        }
    }
}


class BaseConfig:
    DB_CONFIG = db_config
    JWT_CONFIG = {
        'jwt_secret': '12123JSAGHDJSA'
    }
    ALI_YUN_CONFIG = ()
    WE_CHAT_CONFIG = ()

#main.py
from sanic import Sanic, response
from sanic.blueprints import Blueprint
from models import User
from tortoise.contrib.sanic import register_tortoise
from config import BaseConfig
from apps.v2.user.urls import user_bp

app = Sanic(__name__)
# 导入配置文件   通过索引获取配置信息，如：app.config['DB_CONFIG'] 
app.config.update_config(BaseConfig)
#generate_schemas, 启动app时，是否创建数据表。
register_tortoise(
    app, config=app.config["DB_CONFIG"], generate_schemas=False
)

# 注册蓝图, 访问的路由为  http://127.0.0.1/v2/[user_bp]
api_v2 = Blueprint.group(
    user_bp,
    version='2',
)


# 中间件，登录状态
@app.middleware('request')
async def login(request):
    from utils.handle import decode_jwt
    token = request.token
    if isinstance(token, str):
        request.ctx.user = decode_jwt(token, app.config["JWT_CONFIG"]["jwt_secret"])
    else:
        request.ctx.user = None


@app.route("/")
async def list_all(request):
    user_obj = await User.get(pk=6032)
    return response.json({"name": user_obj.name})


if __name__ == '__main__':
    app.run(port=8080)
    import uvicorn
    uvicorn.run(app='main:app', host='0.0.0.0', port='8888', reload=True, debug=True, loop='auto')

视图中的方法

from sanic.response import json

async def index(request, pk):
    # 获取中间件中的user信息
    user = request.ctx.user
    # 异步任务
    request.app.add_task(func())
    # 协程任务
    await User.get(pk=pk),
    await Book.filter(user_id=pk),
    await notes.filter(user_id=pk)




from sanic import Blueprint
user_bp = user_bp = Blueprint('user', url_prefix='/user')
user_bp.add_route(index, '/<pk:int>', methods=["GET"])

ORM的查询方法，复杂查询参考官方文档

基本的查询方法与django-orm相似，如：all()、get()、filter()、first()、exclude()、values()、annotate()、create()、get_or_create() 、F和Q：

from models import User
instance = await User.get(pk=1) -> <User: 1>
queryset = await User.filter(user_name='张三')  -> List [<User:1>, <User:2>]
data = await User.get(pk=1).values('user_id', 'user_name')  -> Dict
data_list = await User.filter(user_name='张三').values()    -> List

使用sql查询：

from tortoise import Tortoise
db = Tortoise.get_connection("default")
result = await db.execute_query_dict("SELECT * FROM user WHERE id=%s", [1])
print(result)

使用sql方法

from pypika import CustomFunction
from tortoise.functions import Function
class DateFormat(Function):
    database_func = CustomFunction("DATE_FORMAT", ["user_name", "dt_format"])
class Left(Function):
    database_func = CustomFunction("LEFT", ["user_name", "length"])

User.get(pk = 1).annotate(date_joined=DateFormat("date_field", "%Y-%m-%d %H:%i:%s")).values("date_joined").sql()
"SELECT DATE_FORMAT(`date_field`, '%Y-%m-%d %H:%i:%s') AS date_joined FROM `user` WHERE id=1;"