sqlexpression.txt

来自「SQLAlchemy. 经典的Python ORM框架。学习必看。」· 文本 代码 · 共 936 行 · 第 1/4 页

    
Functions are most typically used in the columns clause of a select statement, and can also be labeled as well as given a type.  Labeling a function is recommended so that the result can be targeted in a result row based on a string name, and assigning it a type is required when you need result-set processing to occur, such as for Unicode conversion and date conversions.  Below, we use the result function `scalar()` to just read the first column of the first row and then close the result; the label, even though present, is not important in this case:

    {python}
    >>> print conn.execute(
    ...     select([func.max(addresses.c.email_address, type_=String).label('maxemail')])
    ... ).scalar()
    {opensql}SELECT max(addresses.email_address) AS maxemail 
    FROM addresses
    []
    {stop}[e-mail]
    
Databases such as Postgres and Oracle which support functions that return whole result sets can be assembled into selectable units, which can be used in statements.   Such as, a database function `calculate()` which takes the parameters `x` and `y`, and returns three columns which we'd like to name `q`, `z` and `r`, we can construct using "lexical" column objects as well as bind parameters:

    {python}
    >>> from sqlalchemy.sql import column
    >>> calculate = select([column('q'), column('z'), column('r')], 
    ...     from_obj=[func.calculate(bindparam('x'), bindparam('y'))])
    
    >>> print select([users], users.c.id > calculate.c.z)
    SELECT users.id, users.name, users.fullname 
    FROM users, (SELECT q, z, r 
    FROM calculate(:x, :y)) 
    WHERE users.id > z
    
If we wanted to use our `calculate` statement twice with different bind parameters, the `unique_params()` function will create copies for us, and mark the bind parameters as "unique" so that conflicting names are isolated.  Note we also make two separate aliases of our selectable:

    {python}
    >>> s = select([users], users.c.id.between(
    ...    calculate.alias('c1').unique_params(x=17, y=45).c.z, 
    ...    calculate.alias('c2').unique_params(x=5, y=12).c.z))
    
    >>> print s
    SELECT users.id, users.name, users.fullname 
    FROM users, (SELECT q, z, r 
    FROM calculate(:x_1, :y_1)) AS c1, (SELECT q, z, r 
    FROM calculate(:x_2, :y_2)) AS c2 
    WHERE users.id BETWEEN c1.z AND c2.z

    >>> s.compile().params
    {'x_2': 5, 'y_2': 12, 'y_1': 45, 'x_1': 17}
    
### Unions and Other Set Operations {@name=unions}

Unions come in two flavors, UNION and UNION ALL, which are available via module level functions:

    {python}
    >>> from sqlalchemy.sql import union
    >>> u = union(
    ...     addresses.select(addresses.c.email_address=='[e-mail]'),
    ...    addresses.select(addresses.c.email_address.like('[e-mail]')),
    ... ).order_by(addresses.c.email_address)

    {sql}>>> print conn.execute(u).fetchall()
    SELECT addresses.id, addresses.user_id, addresses.email_address 
    FROM addresses 
    WHERE addresses.email_address = ? UNION SELECT addresses.id, addresses.user_id, addresses.email_address 
    FROM addresses 
    WHERE addresses.email_address LIKE ? ORDER BY addresses.email_address
    ['[e-mail]', '[e-mail]']
    {stop}[(1, 1, u'[e-mail]')]
    
Also available, though not supported on all databases, are `intersect()`, `intersect_all()`, `except_()`, and `except_all()`:

    {python}
    >>> from sqlalchemy.sql import except_
    >>> u = except_(
    ...    addresses.select(addresses.c.email_address.like('%@%.com')),
    ...    addresses.select(addresses.c.email_address.like('[e-mail]'))
    ... )

    {sql}>>> print conn.execute(u).fetchall()
    SELECT addresses.id, addresses.user_id, addresses.email_address 
    FROM addresses 
    WHERE addresses.email_address LIKE ? EXCEPT SELECT addresses.id, addresses.user_id, addresses.email_address 
    FROM addresses 
    WHERE addresses.email_address LIKE ?
    ['%@%.com', '[e-mail]']
    {stop}[(1, 1, u'[e-mail]'), (4, 2, u'[e-mail]')]

### Scalar Selects {@name=scalar}

To embed a SELECT in a column expression, use `as_scalar()`:

    {python}
    {sql}>>> print conn.execute(select([
    ...       users.c.name, 
    ...       select([func.count(addresses.c.id)], users.c.id==addresses.c.user_id).as_scalar()
    ...    ])).fetchall()
    SELECT users.name, (SELECT count(addresses.id) 
    FROM addresses 
    WHERE users.id = addresses.user_id) AS anon_1 
    FROM users
    []
    {stop}[(u'jack', 2), (u'wendy', 2), (u'fred', 0), (u'mary', 0)]

Alternatively, applying a `label()` to a select evaluates it as a scalar as well:

    {python}
    {sql}>>> print conn.execute(select([
    ...       users.c.name, 
    ...       select([func.count(addresses.c.id)], users.c.id==addresses.c.user_id).label('address_count')
    ...    ])).fetchall()
    SELECT users.name, (SELECT count(addresses.id) 
    FROM addresses 
    WHERE users.id = addresses.user_id) AS address_count 
    FROM users
    []
    {stop}[(u'jack', 2), (u'wendy', 2), (u'fred', 0), (u'mary', 0)]

### Correlated Subqueries {@name=correlated}

Notice in the examples on "scalar selects", the FROM clause of each embedded select did not contain the `users` table in its FROM clause.  This is because SQLAlchemy automatically attempts to correlate embedded FROM objects to that of an enclosing query.  To disable this, or to specify explicit FROM clauses to be correlated, use `correlate()`:

    {python}
    >>> s = select([users.c.name], users.c.id==select([users.c.id]).correlate(None))
    >>> print s
    SELECT users.name 
    FROM users 
    WHERE users.id = (SELECT users.id 
    FROM users)

    {python}
    >>> s = select([users.c.name, addresses.c.email_address], users.c.id==
    ...        select([users.c.id], users.c.id==addresses.c.user_id).correlate(addresses)
    ...    )
    >>> print s
    SELECT users.name, addresses.email_address 
    FROM users, addresses 
    WHERE users.id = (SELECT users.id 
    FROM users 
    WHERE users.id = addresses.user_id)

### Ordering, Grouping, Limiting, Offset...ing... {@name=ordering}

The `select()` function can take keyword arguments `order_by`, `group_by` (as well as `having`), `limit`, and `offset`.  There's also `distinct=True`.  These are all also available as generative functions.  `order_by()` expressions can use the modifiers `asc()` or `desc()` to indicate ascending or descending.  

    {python}
    >>> s = select([addresses.c.user_id, func.count(addresses.c.id)]).\
    ...     group_by(addresses.c.user_id).having(func.count(addresses.c.id)>1)
    {opensql}>>> print conn.execute(s).fetchall()
    SELECT addresses.user_id, count(addresses.id) 
    FROM addresses GROUP BY addresses.user_id 
    HAVING count(addresses.id) > ?
    [1]
    {stop}[(1, 2), (2, 2)]
    
    >>> s = select([addresses.c.email_address, addresses.c.id]).distinct().\
    ...     order_by(addresses.c.email_address.desc(), addresses.c.id)
    {opensql}>>> conn.execute(s).fetchall()
    SELECT DISTINCT addresses.email_address, addresses.id 
    FROM addresses ORDER BY addresses.email_address DESC, addresses.id
    []
    {stop}[(u'[e-mail]', 3), (u'[e-mail]', 4), (u'[e-mail]', 1), (u'[e-mail]', 2)]
    
    >>> s = select([addresses]).offset(1).limit(1)
    {opensql}>>> print conn.execute(s).fetchall() # doctest: +NORMALIZE_WHITESPACE
    SELECT addresses.id, addresses.user_id, addresses.email_address 
    FROM addresses 
    LIMIT 1 OFFSET 1
    []
    {stop}[(2, 1, u'[e-mail]')]
    
## Updates {@name=update}

Finally, we're back to UPDATE.  Updates work a lot like INSERTS, except there is an additional WHERE clause that can be specified.

    {python}
    >>> # change 'jack' to 'ed'
    {sql}>>> conn.execute(users.update(users.c.name=='jack'), name='ed') #doctest: +ELLIPSIS
    UPDATE users SET name=? WHERE users.name = ?
    ['ed', 'jack']
    COMMIT
    {stop}<sqlalchemy.engine.base.ResultProxy object at 0x...>
    
    >>> # use bind parameters
    >>> u = users.update(users.c.name==bindparam('oldname'), values={'name':bindparam('newname')})
    {sql}>>> conn.execute(u, oldname='jack', newname='ed') #doctest: +ELLIPSIS
    UPDATE users SET name=? WHERE users.name = ?
    ['ed', 'jack']
    COMMIT
    {stop}<sqlalchemy.engine.base.ResultProxy object at 0x...>

    >>> # update a column to an expression
    {sql}>>> conn.execute(users.update(values={users.c.fullname:"Fullname: " + users.c.name})) #doctest: +ELLIPSIS
    UPDATE users SET fullname=(? || users.name)
    ['Fullname: ']
    COMMIT
    {stop}<sqlalchemy.engine.base.ResultProxy object at 0x...>

### Correlated Updates {@name=correlated}

A correlated update lets you update a table using selection from another table, or the same table:

    {python}
    >>> s = select([addresses.c.email_address], addresses.c.user_id==users.c.id).limit(1)
    {sql}>>> conn.execute(users.update(values={users.c.fullname:s})) #doctest: +ELLIPSIS,+NORMALIZE_WHITESPACE
    UPDATE users SET fullname=(SELECT addresses.email_address 
    FROM addresses 
    WHERE addresses.user_id = users.id 
    LIMIT 1 OFFSET 0)
    []
    COMMIT
    {stop}<sqlalchemy.engine.base.ResultProxy object at 0x...>

## Deletes {@name=delete}

Finally, a delete.  Easy enough:

    {python}
    {sql}>>> conn.execute(addresses.delete()) #doctest: +ELLIPSIS
    DELETE FROM addresses
    []
    COMMIT
    {stop}<sqlalchemy.engine.base.ResultProxy object at 0x...>
    
    {sql}>>> conn.execute(users.delete(users.c.name > 'm')) #doctest: +ELLIPSIS
    DELETE FROM users WHERE users.name > ?
    ['m']
    COMMIT
    {stop}<sqlalchemy.engine.base.ResultProxy object at 0x...>

## Further Reference {@name=reference}

The best place to get every possible name you can use in constructed SQL is the [Generated Documentation](rel:docstrings_sqlalchemy.sql.expression).

Table Metadata Reference: [metadata](rel:metadata)

Engine/Connection/Execution Reference: [dbengine](rel:dbengine)

SQL Types: [types](rel:types)