SQL proxies

These proxies are only used when the source is a SQL database. They build SQL queries, which are then executed by the SQLRequestExecutor module. They must therefore be placed between the source and the generic (source-independent) proxies.

Abstract classes

pydantic model crudcreator.adaptator.sql.proxy.AbstractSQLRequestProxy.AbstractSQLRequestProxy

A type of proxy that manipulates SQL queries. These CRUD actions don’t return data, but SQLAlchemy queries.

field base: AbstractSQLEntityType [Required]

The type of entity to which the proxy plugs in and modifies behavior.

field interface: CRUDableEntityTypeInterface [Required]

The interface/schema of the entity type, as seen by the entity’s CRUD users.

field params: AbstractSQLRequestProxyParams [Required]

Parameters for customizing a proxy. It’s up to the AbstractProxyParams implementations to build as they should.

async create(

params: CreateParams,

) → Insert

Must return an “insert” SQLAlchemy request.

Parameters:

params (CreateParams) –

Return type:

Insert

async delete(

params: DeleteParams,

) → Delete

Must return a “delete” SQLAlchemy request.

Parameters:

params (DeleteParams) –

Return type:

Delete

async get_inspector() → SQLColumnInspector

Returns the inspector that will allow the various proxies to interact with SQLAlchemy tables and columns.

Return type:

SQLColumnInspector

async read(

params: ReadParams,

) → Select

Must return a “select” SQLAlchemy request.

Parameters:

params (ReadParams) –

Return type:

Select

async update(

params: UpdateParams,

) → Update | None

Must return an “update” SQLAlchemy request (if an update is required). Otherwise returns None.

Parameters:

params (UpdateParams) –

Return type:

Update | None

pydantic model crudcreator.adaptator.sql.proxy.AbstractSQLRequestProxy.AbstractSQLRequestProxyParams

Concrete classes

SQLRequestConstructor

pydantic model crudcreator.adaptator.sql.proxy.SQLRequestConstructor.SQLRequestConstructor

The first of the SQL proxies. This is the module that will create the query skeleton, from which subsequent modules will work.

Example of descriptor :

{
    "name": "SQLRequestConstructor",
    "params": {
        "read_distinct": false
    }
}

field params: SQLRequestConstructorParams [Required]

SQLRequestConstructor proxy parameters.

async create(

params: CreateParams,

) → Insert

Builds a simple insert, whose list of SQL columns corresponds to the list of “dict_creator_value” fields given as parameters to the CRUD request.

Parameters:

params (CreateParams) –

Return type:

Insert

async delete(

params: DeleteParams,

) → Delete

Builds a simple “delete”, with a where clause on the ids given as parameters to the CRUD request.

Parameters:

params (DeleteParams) –

Return type:

Delete

async read(

params: ReadParams,

) → Select

Builds a simple “select” (or “select distinct”), whose list of SQL columns corresponds to the list of “list_read_field” fields given as parameters to the CRUD request.

Parameters:

params (ReadParams) –

Return type:

Select

async update(

params: UpdateParams,

) → Update | None

Builds a simple “update”, whose list of SQL columns corresponds to the list of “dict_updator_value” fields given as parameters to the CRUD request. Adds the where clause to the ids given as CRUD query parameters. If all dict_updator_value values are set to None, i.e. if no column is to be to be updated, no query is returned (None is returned).

Parameters:

params (UpdateParams) –

Return type:

Update | None

pydantic model crudcreator.adaptator.sql.proxy.SQLRequestConstructor.SQLRequestConstructorParams

SQLRequestConstructor proxy parameters.

field read_distinct: bool [Required]

Should select indicate “distinct”?

SQLFilter

pydantic model crudcreator.adaptator.sql.proxy.SQLFilter.SQLFilter

Transforms the filter instances in the CRUD query into “where clauses” and adds them to the SQL query.

Example of descriptor :

{
    "name": "SQLFilter",
    "params": {}
}

field params: SQLFilterParams [Required]

SQLFilter proxy settings.

async read(

params: ReadParams,

) → Select

Retrieves the next proxy select, then adds all where clauses that correspond to the filter instances given in the CRUD request.

Parameters:

params (ReadParams) –

Return type:

Select

pydantic model crudcreator.adaptator.sql.proxy.SQLFilter.SQLFilterParams

SQLFilter proxy parameters. No parameters.

SQLSort

pydantic model crudcreator.adaptator.sql.proxy.SQLSort.SQLSort

Adds “order by” clauses to the SQL query.

Example of a descriptor, which will add “order by field_foo asc” and and “order by field_bar desc” to the SQL query:

{
    "name": "SQLSort",
    "params": {
        "list_field_to_sort": [
            {
                "field_name": "field_foo",
                "type": "asc"
            },
            {
                "field_name": "field_bar",
                "type": "desc"
            }
        ]
    }
}

field params: SQLSortParams [Required]

SQLSort proxy parameters.

async read(

params: ReadParams,

) → list[Any]

Retrieves the SQL query from the following proxy. Then adds the “order by” clauses before returning the result.

Parameters:

params (ReadParams) –

Return type:

list[Any]

pydantic model crudcreator.adaptator.sql.proxy.SQLSort.SQLSortParams

SQLSort proxy parameters.

field list_field_to_sort: list[FieldToSort] [Required]

The “order by” list to add to the SQL query

pydantic model crudcreator.adaptator.sql.proxy.SQLSort.FieldToSort

An “order by” to add to an SQL query.

field field_name: str [Required]

The name of the field on which to order.

field type: SortType [Required]

How to order?

class crudcreator.adaptator.sql.proxy.SQLSort.SortType

How to order?

asc: str = 'asc'

Order in ascending order.

desc: str = 'desc'

Order in descending order.

SQLPagination

pydantic model crudcreator.adaptator.sql.proxy.SQLPagination.SQLPagination

Adds a “limit” and an “offset” to the SQL query, on option.

Example of descriptor :

{
    "name": "SQLPagination",
    "params": {
        "limit_option_name": "my_limit",
        "offset_option_name": "my_offset"
    }
}

field params: SQLPaginationParams [Required]

SQLPagination proxy parameters.

async read(

params: ReadParams,

) → list[Any]

Retrieves the next proxy select, then adds the limit and offset clauses (if the options are set).

Parameters:

params (ReadParams) –

Return type:

list[Any]

pydantic model crudcreator.adaptator.sql.proxy.SQLPagination.SQLPaginationParams

SQLPagination proxy settings.

field limit_option_name: str [Required]

The option that specifies the “limit” value to be added to the SQL query.

field offset_option_name: str [Required]

The option that sets the offset value to be added to the SQL query.

SQLCreateLink

pydantic model crudcreator.adaptator.sql.proxy.SQLCreateLink.SQLCreateLink

Adds a link to the interface. Does not actually transform it into a “join” in the SQL query. You’ll have to use the SQLReadFromLink module for that.

Example of descriptor :

{
    "name": "SQLCreateLink",
    "params": {
        "interface_merge": true,
        "list_linked_field": [
            {
                "field_name": "column_to_join",
                "link": {
                    "entity_type_linked_to": "$entity3_sql$",
                    "field_name_linked_to": "column_to_be_joined",
                    "type": {
                        "source": {
                            "min": "one",
                            "max": "one"
                        },
                        "dest": {
                            "min": "one",
                            "max": "one"
                        }
                    }
                }
            }
        ]
    }
}

field sql_inspector: SQLColumnInspector | None = None

The new sql_inspection, which takes into account the new links between SQLAlchemy tables. Is built by the “get_inspector” overloaded by this class.

async get_inspector() → SQLColumnInspector

Creates a new sql_inspector containing the merged SQLALchemy tables and columns.

Return type:

SQLColumnInspector

SQLReadFromLink

pydantic model crudcreator.adaptator.sql.proxy.SQLReadFromLink.SQLReadFromLink

Transforms links present in the interface into “joins” in the SQL query.

Example of descriptor :

{
    "name": "SQLReadFromLink",
    "params": {}
}

field params: SQLReadFromLinkParams [Required]

SQLReadFromLink proxy parameters.

async read(

params: ReadParams,

) → Select

Retrieves the select from the next proxy, then adds the necessary “join”.

Parameters:

params (ReadParams) –

Return type:

Select

pydantic model crudcreator.adaptator.sql.proxy.SQLReadFromLink.SQLReadFromLinkParams

SQLReadFromLink proxy parameters.

field list_activate_entity_only_on_option: list[SQLActivateEntityOnlyOnOption] = []

It is possible to activate the “join” option only.

property index_activate_entity_only_on_option: dict[str, bool]

Index entity_name->option_name built automatically from list_activate_entity_only_on_option.

pydantic model crudcreator.adaptator.sql.proxy.SQLReadFromLink.SQLActivateEntityOnlyOnOption

Represents a “join” activated only on option.

field entity_name: str [Required]

Entity type to “join” only on option.

field option_name: str [Required]

The option that activates the “join” function

SQLRequestExecutor

pydantic model crudcreator.adaptator.sql.proxy.SQLRequestExecutor.SQLRequestExecutor

Is the last of the SQL proxies. Is the proxy that will execute the query and return the result. The following proxies are generic proxies.

Example of descriptor :

{
    "name": "SQLRequestExecutor",
    "params": {}
}

field params: SQLRequestExecutorParams [Required]

“SQLRequestExecutor” module parameters.

async create(

params: CreateParams,

)

Retrieves the SQL query from the next proxy, then executes it.

Raises:

EntityAlreadyExist – If SQLAlchemy raises an IntegrityError exception.

Parameters:

params (CreateParams) –

TODO: return the ids of the created entity.

async delete(

params: DeleteParams,

)

Retrieves the SQL query from the next proxy, then executes it.

Raises:

EntityNotExist – If no entities have been deleted.

Parameters:

params (DeleteParams) –

async read(

params: ReadParams,

) → list[Any]

Retrieves the SQL query from the next proxy, then executes it. Transforms the result into a dictionary list, then returns it.

Parameters:

params (ReadParams) –

Return type:

list[Any]

async update(

params: UpdateParams,

)

Retrieves the SQL query from the next proxy. If the query is set to None, there are no fields to update, so stop the CRUD here. Otherwise, execute the query.

Raises:

• EntityNotExist – If no entity has been updated.

• EntityAlreadyExist – If SQLAlchemy raises an IntegrityError exception.

Parameters:

params (UpdateParams) –

pydantic model crudcreator.adaptator.sql.proxy.SQLRequestExecutor.SQLRequestExecutorParams

“SQLRequestExecutor” module parameters. No parameters.