python sanic orm_sanic中使用tortoise-orm

#models.py

from tortoise.models import Model

from tortoise import fields

class User(Model):

id = fields.IntField(pk=True, , source_field="userID")

name = fields.CharField(max_length = 100)

date_field = fields.DateTimeField()

# 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': {

'models': {

'models': ['__main__'],

#设置key值“default”的数据库连接

'default_connection': 'default',

}

}

}

#main.py

from sanic import Sanic, response

from models import User

from tortoise.contrib.sanic import register_tortoise

from config import db_config

app = Sanic(__name__)

#generate_schemas, 启动app时，是否创建数据表。

register_tortoise(

app, config=db_config, modules={"models": ["models"]}, generate_schemas=False

)

@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)

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

使用原生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", ["name", "dt_format"])

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;"