Ejemplo n.º 1
0
def generate_mysql_group_wise_query(
        *, table_name: str,
        columns_names: List[str],
        target_column_name: str,
        filters: Optional[Tuple[str, Any]] = None,
        groupings: List[str],
        limit: Optional[int] = None,
        offset: Optional[int] = None,
        orderings: Optional[List[OrderingType]] = None,
        is_maximum: bool) -> str:
    # based on article
    # http://mysql.rjweb.org/doc.php/groupwise_max
    columns = join_str(columns_names)
    groupings_str = join_str(groupings)
    operator = 'MAX' if is_maximum else 'MIN'
    query = (f'SELECT {groupings_str}, '
             f'{operator}({target_column_name}) AS {target_column_name} '
             f'FROM {table_name} ')
    query = add_filters(query,
                        filters=filters)
    query = add_pagination(query,
                           limit=limit,
                           offset=offset)
    query = add_groupings(query,
                          groupings=groupings)
    query = (f'SELECT {columns} '
             f'FROM {table_name} '
             f'JOIN ({query}) as subquery '
             f'USING ({groupings_str}, {target_column_name}) ')
    query = add_orderings(query,
                          orderings=orderings)
    return query
Ejemplo n.º 2
0
def generate_postgres_group_wise_query(
        *, table_name: str,
        columns_names: List[str],
        target_column_name: str,
        filters: Optional[Tuple[str, Any]] = None,
        groupings: List[str],
        limit: Optional[int] = None,
        offset: Optional[int] = None,
        orderings: Optional[List[OrderingType]] = None,
        is_maximum: bool) -> str:
    # based on article
    # https://explainextended.com/2009/11/26/postgresql-selecting-records-holding-group-wise-maximum/
    columns = join_str(columns_names)
    group_wise_orderings = generate_group_wise_orderings(
        groupings=groupings,
        target_column_name=target_column_name,
        is_maximum=is_maximum)
    groupings_str = join_str(groupings)
    query = (f'SELECT DISTINCT ON ({groupings_str}) {ALL_COLUMNS_ALIAS} '
             f'FROM {table_name} ')
    query = add_filters(query,
                        filters=filters)
    query = add_orderings(query,
                          orderings=group_wise_orderings)
    query = (f'SELECT {columns} '
             f'FROM ({query}) AS subquery ')
    query = add_orderings(query,
                          orderings=orderings)
    query = add_pagination(query,
                           limit=limit,
                           offset=offset)
    return query
Ejemplo n.º 3
0
def generate_update_query(*,
                          table_name: str,
                          updates: UpdatesType,
                          filters: Optional[FiltersType] = None) -> str:
    query = f'UPDATE {table_name} '
    query = add_updates(query, updates=updates)
    query = add_filters(query, filters=filters)
    return query
Ejemplo n.º 4
0
def generate_select_query(
        *, table_name: str,
        columns_names: List[str],
        filters: Optional[FiltersType] = None,
        orderings: Optional[List[OrderingType]] = None,
        groupings: List[str] = None,
        limit: Optional[int] = None,
        offset: Optional[int] = None) -> str:
    check_query_parameters(columns_names=columns_names)

    columns = join_str(columns_names)
    query = (f'SELECT {columns} '
             f'FROM {table_name} ')
    query = add_filters(query,
                        filters=filters)
    query = add_orderings(query,
                          orderings=orderings)
    query = add_groupings(query,
                          groupings=groupings)
    query = add_pagination(query,
                           limit=limit,
                           offset=offset)
    return query
Ejemplo n.º 5
0
def generate_delete_query(*, table_name: str,
                          filters: Optional[FiltersType]) -> str:
    query = f'DELETE FROM {table_name} '
    query = add_filters(query, filters=filters)
    return query