Tortoise ORM: A Powerful Object Relational Mapper Based on asyncio

Tortoise ORM is an easy-to-use asyncio ORM (Object Relational Mapper) for Python, inspired by Django ORM. It borrows the design concept of Django ORM. It not only supports the processing of traditional tabular data but also can efficiently manage relational data. In terms of performance, it is not inferior to other Python ORMs.

Supported Databases

Tortoise ORM currently supports multiple mainstream databases:

SQLite: Driven by aiosqlite, suitable for lightweight application scenarios.
PostgreSQL: The version should be >= 9.4, supporting asyncpg (asynchronous mode) or psycopg (synchronous mode) drivers.
MySQL/MariaDB: Achieves efficient connection with the help of the asyncmy driver.
Microsoft SQL Server: Completes data interaction through the asyncodbc driver.

Environment Configuration and Installation

Install Tortoise ORM

pip install tortoise-orm

Install the corresponding database driver

PostgreSQL (Asynchronous):

pip install tortoise-orm[asyncpg]

MySQL/MariaDB:

pip install tortoise-orm[asyncmy]

SQLite: Supported by default, no additional driver installation is required

Database Connection Configuration

SQLite

The connection string format is sqlite://DB_FILE. For example, if the database file is /data/DB.sqlite3, the complete connection string is sqlite:///data/db.sqlite3 (note the three slashes).

MySQL

The connection string format: mysql://user:password@host:3306/somedb, parameter description:

user: Database username
password: User password
host: Database host address
port: Database port (default is 3306)
database: The name of the database to connect to

PostgreSQL

Asynchronous mode: asyncpg://postgres:pass@db.host:5432/somedb
Synchronous mode: psycopg://postgres:pass@db.host:5432/somedb

Microsoft SQL Server

The connection string format: mssql://user:pass@host:1433/db?driver=theodbcdriver, parameter description:

user: Username
password: Password
host: Host address
port: Port (default is 1433)
database: Database name
driver: ODBC driver name, which needs to be configured in the odbcinst.ini file

Database Creation and Initialization

from tortoise import Tortoise, run_async

async def init():
    # Use the SQLite database, and the file name is db.sqlite3
    # And specify the application name containing the model as "models"
    await Tortoise.init(
        db_url='sqlite://db.sqlite3',
        modules={'models': ['models']}
    )
    # Generate the database table structure
    await Tortoise.generate_schemas()  # safe parameter: When set to True, the table will only be created if it does not exist

run_async(init())  # Automatically handle the context and close the database connection after the operation ends

If using the MySQL database, you need to install the tortoise-orm[aiomysql] dependency first.

Model Definition

from tortoise.models import Model
from tortoise import fields
from tortoise.manager import Manager

class Team(Model):
    id = fields.IntField(pk=True)
    name = fields.TextField()

    class Meta:
        abstract = False  # Whether it is an abstract class, if True, no data table will be generated
        table = "team"  # Table name, if not set, the class name will be used by default
        table_description = ""  # Table comment
        unique_together = ()  # Composite unique index
        indexes = ()  # Composite non-unique index
        ordering = []  # Default sorting
        manager = Manager  # Custom manager

Field Types

Data Fields

from tortoise import fields

fields.Field(
    source_field=None,  # Custom database column name
    generated=False,  # Whether it is automatically generated by the database
    pk=False,  # Whether it is the primary key
    null=False,  # Whether the field can be empty
    default=None,  # Default value
    unique=False,  # Whether the value is unique
    index=False,  # Whether to create an index
    description=None,  # Field description
    validators=None  # List of validators
)

Relationship Fields

Foreign Key Field

fields.ForeignKeyField(
    model_name,  # Associated model name, in the format of {app}.{models}
    related_name=None,  # Reverse resolution attribute name
    on_delete='CASCADE',  # Deletion strategy, optional values: CASCADE, RESTRICT, SET_NULL, SET_DEFAULT
    db_constraint=True,  # Whether to create a foreign key constraint in the database
)

One-to-One Field

fields.OneToOneField(
    model_name,
    related_name=None,
    on_delete='CASCADE',
    db_constraint=True
)

Many-to-Many Field

fields.ManyToManyField(
    model_name,
    through=None,  # Intermediate table
    forward_key=None,  # Forward lookup key
    backward_key='',  # Reverse lookup key
    related_name='',
    on_delete='CASCADE',
    db_constraint=True
)

Query Operations

The model provides multiple query methods:

filter(*args, **kwargs): Filter data according to conditions
exclude(*args, **kwargs): Exclude data that meets the conditions
all(): Get all data
first(): Get the first piece of data
annotate(): Aggregate query

The query conditions supported by the filter method:

Range query: in, not_in, gte, gt, lte, lt, range
Null value query: isnull, not_isnull
String query: contains, icontains, startswith, istartswith, endswith, iendswith, iexact
Full-text search: search

Here are some specific query examples:

Simple Query Example

Suppose the Team model has been defined:

from tortoise import run_async
from models import Team  # Assume the model is defined in the models.py file

async def simple_query():
    # Get all Team records
    all_teams = await Team.all()
    print("All teams:", all_teams)

    # Get the first Team record
    first_team = await Team.first()
    print("The first team:", first_team)

    # Filter according to the condition and get the team whose name is "Team A"
    filtered_teams = await Team.filter(name="Team A")
    print("Teams named Team A:", filtered_teams)

run_async(simple_query())

Range Query Example

from tortoise import run_async
from models import Team

async def range_query():
    # Query teams with id greater than 5
    greater_than_5 = await Team.filter(id__gt=5)
    print("Teams with id greater than 5:", greater_than_5)

    # Query teams with id between 2 and 8 (including 2 and 8)
    in_range = await Team.filter(id__range=(2, 8))
    print("Teams with id between 2 and 8:", in_range)

    # Query teams whose id is not in [1, 3, 5]
    not_in_list = await Team.filter(id__not_in=[1, 3, 5])
    print("Teams whose id is not in [1, 3, 5]:", not_in_list)

run_async(range_query())

String Query Example

from tortoise import run_async
from models import Team

async def string_query():
    # Query teams whose name contains the string "team" (case-insensitive)
    contains_team = await Team.filter(name__icontains="team")
    print("Teams whose name contains team (case-insensitive):", contains_team)

    # Query teams whose name starts with "A" (case-sensitive)
    startswith_A = await Team.filter(name__startswith="A")
    print("Teams whose name starts with A:", startswith_A)

    # Query teams whose name ends with "B" (case-insensitive)
    endswith_B = await Team.filter(name__iendswith="B")
    print("Teams whose name ends with B (case-insensitive):", endswith_B)

run_async(string_query())