Пример #1
0
def operator_and() -> Tuple['ModelSelect', 'ModelSelect']:
    """AND.

    SELECT vend_id, prod_price
    FROM product
    WHERE vend_id = 'DLL01' AND prod_price <= 4;
    """
    q1 = (Product.select(
        Product.vend_id,
        Product.prod_price).where((Product.vend_id == 'DLL01')
                                  & (Product.prod_price <= 4)))
    q2 = (Product.select(Product.vend_id,
                         Product.prod_price).where(Product.vend_id == 'DLL01',
                                                   Product.prod_price <= 4))
    return q1, q2
Пример #2
0
def not_select() -> 'ModelSelect':
    """按非选择列排序.

    SELECT prod_name FROM product
    ORDER BY prod_price;
    """
    return (Product.select(Product.prod_name).order_by(Product.prod_price))
Пример #3
0
def single_order() -> 'ModelSelect':
    """单字段排序.

    SELECT prod_name FROM product
    ORDER BY prod_name;
    """
    return (Product.select(Product.prod_name).order_by(Product.prod_name))
Пример #4
0
def desc() -> 'ModelSelect':
    """降序排列.

    SELECT prod_name, prod_price FROM product
    ORDER BY prod_price DESC, prod_name;
    """
    return (Product.select(Product.prod_name, Product.prod_price).order_by(
        Product.prod_price.desc(), Product.prod_name))
Пример #5
0
def avg() -> 'ModelSelect':
    """平均数.

    SELECT AVG(prod_price) AS avg_price
    FROM product;
    """
    avg_price = fn.AVG(Product.prod_price).alias('avg_price')
    return (Product.select(avg_price))
Пример #6
0
def number_order() -> 'ModelSelect':
    """按列位置排序.

    SELECT prod_name, prod_price FROM product
    ORDER BY 2, 1;
    """
    return (Product.select(Product.prod_name,
                           Product.prod_price).order_by(SQL('2'), SQL('1')))
Пример #7
0
def multi_order() -> 'ModelSelect':
    """多列排序.

    SELECT prod_name, prod_price FROM product
    ORDER BY prod_price, prod_name;
    """
    return (Product.select(Product.prod_name, Product.prod_price).order_by(
        Product.prod_price, Product.prod_name))
Пример #8
0
def distinct_avg() -> 'ModelSelect':
    """聚集不同值.

    SELECT AVG(DISTINCT prod_price) AS avg_price
    FROM product
    WHERE vend_id = 'DLL01';
    """
    avg_price = fn.AVG(Product.prod_price.distinct()).alias('avg_price')
    return (Product.select(avg_price).where(Product.vend_id == 'DLL01'))
Пример #9
0
def between() -> 'ModelSelect':
    """范围值检查.

    SELECT prod_name, prod_price
    FROM product
    WHERE prod_price BETWEEN 5.99 AND 9.49;
    """
    return (Product.select(Product.prod_name, Product.prod_price).where(
        Product.prod_price.between(5.99, 9.49)))
Пример #10
0
def equal() -> 'ModelSelect':
    """相等检查.

    SELECT prod_name, prod_price FROM product
    WHERE prod_price = 3.49;
    """
    return (Product.select(
        Product.prod_name,
        Product.prod_price).where(Product.prod_price == 3.49))
Пример #11
0
def single_match() -> 'ModelSelect':
    """匹配单个字符.

    SELECT prod_name
    FROM product
    WHERE prod_name LIKE '_ inch teddy bear';
    """
    return (Product.select(Product.prod_name).where(
        Product.prod_name**'_ inch teddy bear'))
Пример #12
0
def any_match() -> 'ModelSelect':
    """匹配任意个字符 (包括 0 个).

    SELECT prod_name
    FROM product
    WHERE prod_name LIKE '%bean bag%';
    """
    return (Product.select(Product.prod_name).where(
        Product.prod_name**'%bean bag%'))
Пример #13
0
def operator_not() -> 'ModelSelect':
    """NOT.

    SELECT prod_name
    FROM product
    WHERE NOT vend_id = 'DLL01';
    """
    return (Product.select(
        Product.prod_name).where(~(Product.vend_id == 'DLL01')))
Пример #14
0
def operator_in() -> 'ModelSelect':
    """IN.

    SELECT prod_name, vend_id
    FROM product
    WHERE vend_id IN ('DLL01', 'BRS01');
    """
    return (Product.select(
        Product.prod_name,
        Product.vend_id).where(Product.vend_id << ['DLL01', 'BRS01']))
Пример #15
0
def group_with_count() -> 'ModelSelect':
    """分组计算行数.

    SELECT vend_id, COUNT(*) AS num_prods
    FROM product
    GROUP BY vend_id;
    """
    return (Product.select(Product.vend_id,
                           count().alias('num_prods')).group_by(
                               Product.vend_id))
Пример #16
0
def operator_or() -> Tuple['ModelSelect', 'ModelSelect']:
    """OR.

    SELECT vend_id, prod_price
    FROM product
    WHERE vend_id = 'DLL01' OR prod_price <= 4;
    """
    return (Product.select(
        Product.vend_id,
        Product.prod_price).where((Product.vend_id == 'DLL01')
                                  | (Product.prod_price <= 4)))
Пример #17
0
def filter_group() -> 'ModelSelect':
    """过滤分组.

    SELECT vend_id, COUNT(*) AS num_prods
    FROM product
    GROUP BY vend_id
    HAVING COUNT(*) > 2;
    """
    return (Product.select(Product.vend_id,
                           count().alias('num_prods')).group_by(
                               Product.vend_id).having(count() > 2))
Пример #18
0
def parenthese_logical() -> 'ModelSelect':
    """使用括号控制逻辑操作符顺序.

    SELECT prod_name, prod_price
    FROM product
    WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')
          AND prod_price >= 10;
    """
    return (Product.select(
        Product.prod_name,
        Product.prod_price).where((
            (Product.vend_id == 'DLL01') | (Product.vend_id == 'BRS01'))
                                  & (Product.prod_price >= 10)))
Пример #19
0
def filter_row_and_group() -> 'ModelSelect':
    """过滤行和分组.

    SELECT vend_id, COUNT(*) AS num_prods
    FROM product
    WHERE prod_price >= 4
    GROUP BY vend_id
    HAVING COUNT(*) >= 2;
    """
    return (Product.select(
        Product.vend_id,
        count().alias('num_prods')).where(Product.prod_price >= 4).group_by(
            Product.vend_id).having(count() >= 2))
Пример #20
0
def logical_order() -> 'ModelSelect':
    """逻辑操作符顺序.

    SELECT prod_name, prod_price
    FROM product
    WHERE vend_id = 'DLL01'
          OR vend_id = 'BRS01'
          AND prod_price >= 10;
    """
    return (Product.select(
        Product.prod_name,
        Product.prod_price).where((Product.vend_id == 'DLL01')
                                  | (Product.vend_id == 'BRS01')
                                  & (Product.prod_price >= 10)))
Пример #21
0
def multi_reduce() -> 'ModelSelect':
    """多个聚集函数.

    SELECT COUNT(*) AS count,
           MIN(prod_price) AS min,
           MAX(prod_price) AS max,
           AVG(prod_price) AS avg,
           SUM(prod_price) AS sum
    FROM product;
    """
    return (Product.select(count().alias('count'),
                           fn.MIN(Product.prod_price).alias('min'),
                           fn.MAX(Product.prod_price).alias('max'),
                           fn.AVG(Product.prod_price).alias('avg'),
                           fn.SUM(Product.prod_price).alias('sum')))
Пример #22
0
def distinct_multi() -> 'ModelSelect':
    """检索不同的多列.

    SELECT DISTINCT vend_id, prod_price FROM product;
    """
    return (Product.select(Product.vend_id, Product.prod_price).distinct())
Пример #23
0
def single() -> 'ModelSelect':
    """检索单列数据.

    SELECT prod_name FROM product;
    """
    return (Product.select(Product.prod_name))
Пример #24
0
def multi() -> 'ModelSelect':
    """检索多列数据.

    SELECT prod_name, prod_price FROM product;
    """
    return (Product.select(Product.prod_name, Product.prod_price))
Пример #25
0
def all_select() -> 'ModelSelect':
    """检索所有列数据.

    SELECT * FROM product;
    """
    return (Product.select())
Пример #26
0
def distinct_single() -> 'ModelSelect':
    """检索不同的单列.

    SELECT DISTINCT vend_id FROM product;
    """
    return (Product.select(Product.vend_id).distinct())
Пример #27
0
def limit() -> 'ModelSelect':
    """limit.

    SELECT prod_name FROM product LIMIT 5;
    """
    return (Product.select(Product.prod_name).limit(5))
Пример #28
0
def offset() -> 'ModelSelect':
    """offset.

    SELECT prod_name FROM product LIMIT 5 OFFSET 5;
    """
    return (Product.select(Product.prod_name).limit(5).offset(5))