Python development framework based on SqlAlchemy+Pydantic+FastApi

With the cross-platform needs of the larger environment more and more, for and development environment and the actual operating environment have cross-platform needs, Python development and deployment on the cross-platform , this essay accompanying the introduction of the technical details of the Python development framework based on SqlAlchemy + Pydantic + FastApi, as well as a number of technical summaries.

In the last few months, we have been busy working on the integration of the Python development framework, adding many important features from the previous development framework, and making it compatible with our .SqlSugar Development FrameworkThe interface standards of the Convention, and therefore for theSqlSugar Development FrameworkWinform front-end, Vue3+Typescript+ElementPlus front-end, WPF front-end, etc. can be seamlessly accessed, avoiding the need to complete the development of these access points from scratch, and quickly integrating them to realize the relevant management functions.

1、Development tools and development environment

Python development using the common VSCode development tools for development can be very convenient, and by using the "Fitten Code" an AI-assisted plugin for very efficient coding. Previously published in the accompanying article,Preparation of Python development environment and installation of some common library modules.There are some related modules in the "Introduction", you can refer to learn about it if you are interested.

1) About the development language

Python development language is very easy to understand, between strongly-typed language C#, Java and weakly-typed language Javascript, more similar to TypeScript, corresponding to the definition and processing of some types, is more like a replica of TypeScript. When we learn, a comprehensive understanding of his data types, control statements, variables and function definitions, as well as the handling of the differences between the class can be, but most of the concepts and implementations are similar, and some of the differences may be the characteristics of the language, such as the flexibility of the Python language leads to.

2）About common class libraries

Python development has many aspects of commonly used libraries, such as string processing, file processing, database processing, graphics processing, audio and video processing, scientific computing, network processing, artificial intelligence and other areas, some are basically standard solution libraries, at first we can generalize the understanding of the general direction. As we refine the specific solution, we gradually explore the differences between various similar libraries, such as for the processing of the back-end Web API, there may beFastAPI 、Django 、Flasketc. For database access, there are specific class libraries such as pymysql , pymssql, psycopg2, pymongo,

For the development environment of cross-platform is also a good experience, because VScode also look at the installation of MacOS, so whether you use the Window development environment, or Apple's MacOS development environment, are the same, can be said to be silky smooth, all the development habits are the same.

Some people worry that Python's compiled environment is a parsing type of processing, may be processing efficiency than the compiled language efficiency is much worse, I actually develop the back end of the same project, compared to our .netcore SqlSugar development framework back-end, Python's startup is faster, and there is no significant difference in processing.

2. Characteristics of the framework

1) Layered processing and base class abstraction

The layering of the framework follows some generalizations, as follows:

Even according to the division of layered logic, we should try to minimize the duplication of coding for the objects in each layer, so use Python's inheritance relationship to abstract some common properties or interfaces and implementations, etc., in order to achieve more efficient development and reduce redundant code.

For example, for my C# development framework, the back-end WebAPI we use the following inheritance to realize some logic stripping and abstraction.

For Python development frameworks, we are also able to use this inheritance idea, but there are some language differences in the implementation.

Relatively speaking, due to the nature of Python, we are a bit more flat in the implementation, but the main logic CRUD and other processing in the BaseController controller class to deal with.

As in the case of routers, we make the interface of the base class a bit more personalized by handling the generic parameters, as shown in the following code.

In this way, our abstract base class interfaces have more customizable features, and for subclasses, you only need to pass in the corresponding type to construct a different implementation of the interface.

With the definition of the base class controller, we just accept the information passed in by the subclass controller.

class BaseController(Generic[ModelType, PrimaryKeyType, PageDtoType, DtoType]):

Similar to BaseController's base class definition, our counterpart BaseCrud is also for the regular processing of database access, which we have done an abstract encapsulation.

Through the base class BaseCrud definition, we accept a number of subclass objects with different parameters to achieve a personalized implementation.

class BaseCrud(Generic[ModelType, PrimaryKeyType, PageDtoType, DtoType]):

For different business tables, we inherit BaseCrud, and pass the parameters of different objects, you can have a very powerful and rich function processing capabilities.

Which we implemented in the base class Crud class in the processing logic of the regular query conditions, as well as sorting rules, can be sorted by default, or according to the query object of the sorting conditions of the sorting process.

class CRUDUser(BaseCrud[User, int, UserPageDto, UserDto]):
    """Implementing custom interfaces"""

    def apply_default_sorting(self, query: Query[User]) -> Query[User]:
        """Default Sort-Modify to reverse order by name"""
        return super().apply_default_sorting(query).order_by(())

When specifying a Crud subclass definition, we can pass in the corresponding definition of the model class, the primary key type, paging query objects, regular DTO interaction objects and other information to be processed, if we need to change the sorting rules, rewrite the apply_default_sorting function can be.

Since the access to the database in our framework using SqlAlchemy to achieve , that is, based on the ORM way to achieve a variety of asynchronous operations , so the processing logic are common . We can also add relevant specific processing function interfaces for business classes, as shown in the following screenshot.

Other business Crud class is also the same way to deal with the inheritance of BaseCrud can be, only in the special processing only need to add their own interface function.

We see that the Web API's controllers and data access objects are base class abstractions to implement the main logic.

We deal with it on the model class (which interacts with the database) in the same way, also by defining a base class for some basic definitions such as id, table name, id primary key default value handling etc.

A class similar to the following, such as the Base class, is generally defined as the base class for all database model classes.

@as_declarative()
class Base:
    id: Any

    __name__: str
    __abstract__ = True

    # Generate __tablename__ automatically
    @declared_attr
    def __tablename__(cls) -> str:
        return cls.__name__.lower()

For some character id primary keys, we sometimes, want it to automatically initialize a Guid-like string (uuid in Python's case), so let's extend the definition of the model base class again to beBaseModel。

class BaseModel(Base):
    """Define id field, provide default constructor, generate UUID as default value of ID."""

    __abstract__ = True

    @declared_attr
    def id(cls):
        # Assuming that UUID is used by default, if you need to use an incremental integer, define the id directly in the subclass.
        return Column(String(36), primary_key=True, default=lambda: str(uuid.uuid1()))

    def __init__(self, **kwargs):
        super().__init__(**kwargs)
        # Get the column object for the id field
        id_column = self.__class__.__table__.("id")

        # Initialized according to the type of the id column object
        if id_column is not None:
            if isinstance(id_column.type, String):
                if  is None:
                     = str(uuid.uuid1())
            elif isinstance(id_column.type, Integer):
                # Integer types do not require special handling and are usually self-incremented by the database
                pass

For the generic model class, we define the following.

For the model class of character uuid type values, we define the following as shown below.

This way when it writes to the database, the default null value of the Id primary key will be replaced by the ordered GUID value, and you don't need to manually assign the id each time, or you will be prompted to fail to write the record if you forget it.

General for the DTO object, as the UI interface on the exchange of objects, we also do the definition of the base class, the default BaseModel is the pydantic object, pydantic generally as the back-end data access to the processing of class libraries, you can check the data format and mapping and other processing.

We can do some customization in the SchemaBase, so that he can meet our actual needs, in addition to the ConfigDict processing, we let it and the Model object between the attribute mapping processing, similar to the C# AutoMapper processing it.

Synthesizing the previous inheritance relationship definitions, the following interface is shown.

The structure of the final project is shown below.

Previously in the accompanying article, "TheUsing FastAPI to develop projects, some references on how to plan the directory structure of the project and some handling of base class encapsulation.The catalog structure is also described in some detail in the book.

Once we have completed the project, run the FastAPI project as shown below.

After starting the project, you can see the detailed Swagger documentation on the WebAPI homepage, which is very convenient for reference.

In each business module, there are common interfaces due to the inheritance of standard base classes, which can also be removed in the corresponding EndPoint entry routes if we don't need them.

2) Multiple database support

Although we are developing a particular system, generally use a database can be, but support for multiple databases is the ability to choose as needed. I previously developed a variety of frameworks that support multiple database access, such as MySQL, SqlServer, Postgresql, SQLite, Oracle, MongoDB, etc., Python's support for these databases have a corresponding driver libraries to achieve access to our SqlAlchemy's ORM capabilities, to them. integration, we can specify different driver connection strings when configuring.

The database configuration information we use Pydantic and Pydantic-setting to realize that the contents of the .env file are automatically loaded into the Pydantic object can be. For example, the .env environment configuration file for our project is as follows.

We then introduce pydantic-settings, and we can make it work by defining aSetting class and have it automatically load the .env configuration information into it.

class Settings(BaseSettings):
    model_config = SettingsConfigDict(
        env_file=f"{BasePath}/.env",  # Loading env files
        extra="ignore",  # Load the env file and don't throw an exception if the properties are not defined in Settings
        env_file_encoding="utf-8",
        env_prefix="",
        case_sensitive=False,
    )
    # Env Database
    DB_NAME: str 
    DB_USER: str 
    DB_PASSWORD: str 
    DB_HOST: str 
    DB_PORT: int 

Since we want to specify a different connection string by configuring the database type, we need to specify the connection string for the final build for asynchronous access to theDB_URI_ASYNC Perform assembly.

    # Conversion to method properties
    @property
    def DB_URI_ASYNC(self):
        connect_string: str = ""
        if self.DB_TYPE == "mysql":
            self.DB_PORT = self.DB_PORT if self.DB_PORT > 0 else 3306
            connect_string = f"mysql+aiomysql://{self.DB_USER}:{self.DB_PASSWORD}@{self.DB_HOST}:{self.DB_PORT}/{self.DB_NAME}"
        elif self.DB_TYPE == "mssql":
            self.DB_PORT = self.DB_PORT if self.DB_PORT > 0 else 1433
            # If port >0 and not 1433, add the port number
            portString = f":{self.DB_PORT}" if (self.DB_PORT != 1433) else ""
            connect_string = f"mssql+aioodbc://{self.DB_USER}:{self.DB_PASSWORD}@{self.DB_HOST}{portString}/{self.DB_NAME}?driver=ODBC+Driver+17+for+SQL+Server"

        elif self.DB_TYPE == "sqlite":
            # The files are placed in the sqlitedb directory
            connect_string = f"sqlite+aiosqlite:///app//sqlitedb//{self.DB_NAME}.db"
        elif self.DB_TYPE == "postgresql":
            self.DB_PORT = self.DB_PORT if self.DB_PORT > 0 else 5432
            connect_string = f"postgresql+asyncpg://{self.DB_USER}:{self.DB_PASSWORD}@{self.DB_HOST}:{self.DB_PORT}/{self.DB_NAME}"
        else:
            return ""
return connect_string

Ultimately, the code is as follows when we pass in the connection string constructed from the configuration information to create the database access object.

# asynchronous processing
async_engine, async_session = create_engine_and_session(settings.DB_URI_ASYNC)

We define an asynchronous connection object for get_db as a dependent function of the database access portal in the class as follows.

async def get_db() -> AsyncGenerator[AsyncSession, None]:
    """Creating a SQLAlchemy Database Session - Asynchronous Processing."""
    async with async_session() as session:
        yield session

This way we can just rely on this get_db asynchronous connection object function when handled by the API controller function.

    async def get(cls, id: int, db: AsyncSession = Depends(get_db)):
        item = await user_crud.get(db, id)
         ........................

The get function, which is rewritten in the final UserController, also calls a function of the BaseCrud base class inside user_crud, as shown below.

    async def get(self, db: AsyncSession, id: PrimaryKeyType) -> Optional[ModelType]:
        """Get an object based on the primary key"""
        query = select().filter( == id)
        result = await (query)
        item = ().first()
        return item

In this way, in addition to defining different database types and generating different connection strings in the configuration file, none of the other functions are specific to a particular database type, so this kind of database access is insensitive and can be implemented in a generic way to deal with a variety of databases to access the processing.

As in MySQL, using mysql+aiomysql://

In SqlServer, this uses mssql+aioodbc://

In Postgresql, the use of postgresql+asyncpg://

wait a minute!

3) Multiple access front-ends

Previously introduced our newly developed PythonWeb API to follow the "SqlSugar Development Framework" in the Web API standard naming rules, but also the use of Restful naming specification processing, for the business interface we use a unified naming approach. Therefore, we do not need to develop the front-end part from scratch, just appropriate processing can reuse the existing front-end part.

We modify the API path that specifies the configuration of the Winform front-end, so that it points to the Python Web API interface, you can dock the Winform front-end successfully.

And for the BS front-end interface of Vue3+ElementPlus, since the front-end and back-end are in a strict separation mode, just handle it the same way.

Just treat the other front-end parts similarly.

4) Code generation tool support

After completing the successful integration of the project, we put forward higher requirements for their own development, although most of the rules have been processed in the abstraction of the base class, for a new business table, we still need to add the corresponding subclasses in different hierarchical directories, such as controllers, CRUD data access classes, Model model classes, DTO object classes, etc., especially for the model classes and database tables. One by one corresponding code, hand-written must be more boring, so these problems, we use the code generation tool to solve it at once.

We added Python's back-end code generation to the code generation tool, which can generate class files for each layer with a single click, including the most tedious Model mapping class information.