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