sqlexpression.txt

SQLAlchemy. 经典的Python ORM框架。学习必看。

    {sql}>>> for row in conn.execute(s):
    ...     print "name:", row[users.c.name], "; fullname:", row[users.c.fullname]
    SELECT users.id, users.name, users.fullname 
    FROM users
    []
    {stop}name: jack ; fullname: Jack Jones
    name: wendy ; fullname: Wendy Williams
    name: fred ; fullname: Fred Flintstone
    name: mary ; fullname: Mary Contrary

Result sets which have pending rows remaining should be explicitly closed before discarding.  While the resources referenced by the `ResultProxy` will be closed when the object is garbage collected, it's better to make it explicit as some database APIs are very picky about such things:

    {python}
    >>> result.close()

If we'd like to more carefully control the columns which are placed in the COLUMNS clause of the select, we reference individual `Column` objects from our `Table`.  These are available as named attributes off the `c` attribute of the `Table` object:

    {python}
    >>> s = select([users.c.name, users.c.fullname])
    {sql}>>> result = conn.execute(s)
    SELECT users.name, users.fullname 
    FROM users
    []
    {stop}>>> for row in result:  #doctest: +NORMALIZE_WHITESPACE
    ...     print row
    (u'jack', u'Jack Jones')
    (u'wendy', u'Wendy Williams')
    (u'fred', u'Fred Flintstone')
    (u'mary', u'Mary Contrary')
    
Lets observe something interesting about the FROM clause.  Whereas the generated statement contains two distinct sections, a "SELECT columns" part and a "FROM table" part, our `select()` construct only has a list containing columns.  How does this work ?  Let's try putting *two* tables into our `select()` statement:

    {python}
    {sql}>>> for row in conn.execute(select([users, addresses])):
    ...     print row
    SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address 
    FROM users, addresses
    []
    {stop}(1, u'jack', u'Jack Jones', 1, 1, u'[e-mail]')
    (1, u'jack', u'Jack Jones', 2, 1, u'[e-mail]')
    (1, u'jack', u'Jack Jones', 3, 2, u'[e-mail]')
    (1, u'jack', u'Jack Jones', 4, 2, u'[e-mail]')
    (2, u'wendy', u'Wendy Williams', 1, 1, u'[e-mail]')
    (2, u'wendy', u'Wendy Williams', 2, 1, u'[e-mail]')
    (2, u'wendy', u'Wendy Williams', 3, 2, u'[e-mail]')
    (2, u'wendy', u'Wendy Williams', 4, 2, u'[e-mail]')
    (3, u'fred', u'Fred Flintstone', 1, 1, u'[e-mail]')
    (3, u'fred', u'Fred Flintstone', 2, 1, u'[e-mail]')
    (3, u'fred', u'Fred Flintstone', 3, 2, u'[e-mail]')
    (3, u'fred', u'Fred Flintstone', 4, 2, u'[e-mail]')
    (4, u'mary', u'Mary Contrary', 1, 1, u'[e-mail]')
    (4, u'mary', u'Mary Contrary', 2, 1, u'[e-mail]')
    (4, u'mary', u'Mary Contrary', 3, 2, u'[e-mail]')
    (4, u'mary', u'Mary Contrary', 4, 2, u'[e-mail]')

It placed **both** tables into the FROM clause.  But also, it made a real mess.  Those who are familiar with SQL joins know that this is a **Cartesian product**; each row from the `users` table is produced against each row from the `addresses` table.  So to put some sanity into this statement, we need a WHERE clause.  Which brings us to the second argument of `select()`:

    {python}
    >>> s = select([users, addresses], users.c.id==addresses.c.user_id)
    {sql}>>> for row in conn.execute(s):
    ...     print row
    SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address 
    FROM users, addresses 
    WHERE users.id = addresses.user_id
    []
    {stop}(1, u'jack', u'Jack Jones', 1, 1, u'[e-mail]')
    (1, u'jack', u'Jack Jones', 2, 1, u'[e-mail]')
    (2, u'wendy', u'Wendy Williams', 3, 2, u'[e-mail]')
    (2, u'wendy', u'Wendy Williams', 4, 2, u'[e-mail]')

So that looks a lot better, we added an expression to our `select()` which had the effect of adding `WHERE users.id = addresses.user_id` to our statement, and our results were managed down so that the join of `users` and `addresses` rows made sense.  But let's look at that expression?  It's using just a Python equality operator between two different `Column` objects.  It should be clear that something is up.  Saying `1==1` produces `True`, and `1==2` produces `False`, not a WHERE clause.  So lets see exactly what that expression is doing:

    {python}
    >>> users.c.id==addresses.c.user_id #doctest: +ELLIPSIS
    <sqlalchemy.sql.expression._BinaryExpression object at 0x...>

Wow, surprise !  This is neither a `True` nor a `False`.  Well what is it ?

    {python}
    >>> str(users.c.id==addresses.c.user_id)
    'users.id = addresses.user_id'

As you can see, the `==` operator is producing an object that is very much like the `Insert` and `select()` objects we've made so far, thanks to Python's `__eq__()` builtin; you call `str()` on it and it produces SQL.  By now, one can that everything we are working with is ultimately the same type of object.  SQLAlchemy terms the base class of all of these expressions as `sqlalchemy.sql.ClauseElement`.

## Operators {@name=operators}

Since we've stumbled upon SQLAlchemy's operator paradigm, let's go through some of its capabilities.  We've seen how to equate two columns to each other:

    {python}
    >>> print users.c.id==addresses.c.user_id
    users.id = addresses.user_id
    
If we use a literal value (a literal meaning, not a SQLAlchemy clause object), we get a bind parameter:

    {python}
    >>> print users.c.id==7
    users.id = :users_id_1
    
The `7` literal is embedded in `ClauseElement`; we can use the same trick we did with the `Insert` object to see it:

    {python}
    >>> (users.c.id==7).compile().params
    {'users_id_1': 7}
    
Most Python operators, as it turns out, produce a SQL expression here, like equals, not equals, etc.:

    {python}
    >>> print users.c.id != 7
    users.id != :users_id_1
    
    >>> # None converts to IS NULL
    >>> print users.c.name == None
    users.name IS NULL
     
    >>> # reverse works too 
    >>> print 'fred' > users.c.name
    users.name < :users_name_1
    
If we add two integer columns together, we get an addition expression:

    {python}
    >>> print users.c.id + addresses.c.id
    users.id + addresses.id
    
Interestingly, the type of the `Column` is important !  If we use `+` with two string based columns (recall we put types like `Integer` and `String` on our `Column` objects at the beginning), we get something different:

    {python}
    >>> print users.c.name + users.c.fullname
    users.name || users.fullname

Where `||` is the string concatenation operator used on most databases.  But not all of them.  MySQL users, fear not:

    {python}
    >>> from sqlalchemy.databases.mysql import MySQLDialect
    >>> print (users.c.name + users.c.fullname).compile(dialect=MySQLDialect())
    concat(users.name, users.fullname)

The above illustrates the SQL that's generated for an `Engine` that's connected to a MySQL database (note that the `Dialect` is normally created behind the scenes; we created one above just to illustrate without using an engine).

If you have come across an operator which really isn't available, you can always use the `op()` method; this generates whatever operator you need:

    {python}
    >>> print users.c.name.op('tiddlywinks')('foo')
    users.name tiddlywinks :users_name_1
    
## Conjunctions {@name=conjunctions}

We'd like to show off some of our operators inside of `select()` constructs.  But we need to lump them together a little more, so let's first introduce some conjunctions.  Conjunctions are those little words like AND and OR that put things together.  We'll also hit upon NOT.  AND, OR and NOT can work from the corresponding functions SQLAlchemy provides (notice we also throw in a LIKE):

    {python}
    >>> from sqlalchemy.sql import and_, or_, not_
    >>> print and_(users.c.name.like('j%'), users.c.id==addresses.c.user_id, #doctest: +NORMALIZE_WHITESPACE  
    ...     or_(addresses.c.email_address=='[e-mail]', addresses.c.email_address=='[e-mail]'),
    ...     not_(users.c.id>5))
    users.name LIKE :users_name_1 AND users.id = addresses.user_id AND 
    (addresses.email_address = :addresses_email_address_1 OR addresses.email_address = :addresses_email_address_2) 
    AND users.id <= :users_id_1

And you can also use the re-jiggered bitwise AND, OR and NOT operators, although because of Python operator precedence you have to watch your parenthesis:

    {python}
    >>> print users.c.name.like('j%') & (users.c.id==addresses.c.user_id) &  \
    ...     ((addresses.c.email_address=='[e-mail]') | (addresses.c.email_address=='[e-mail]')) \
    ...     & ~(users.c.id>5) # doctest: +NORMALIZE_WHITESPACE
    users.name LIKE :users_name_1 AND users.id = addresses.user_id AND 
    (addresses.email_address = :addresses_email_address_1 OR addresses.email_address = :addresses_email_address_2) 
    AND users.id <= :users_id_1

So with all of this vocabulary, let's select all users who have an email address at AOL or MSN, whose name starts with a letter between "m" and "z", and we'll also generate a column containing their full name combined with their email address.  We will add two new constructs to this statement, `between()` and `label()`.  `between()` produces a BETWEEN clause, and `label()` is used in a column expression to produce labels using the `AS` keyword; it's recommended when selecting from expressions that otherwise would not have a name:

    {python}
    >>> s = select([(users.c.fullname + ", " + addresses.c.email_address).label('title')], 
    ...        and_( 
    ...            users.c.id==addresses.c.user_id, 
    ...            users.c.name.between('m', 'z'), 
    ...           or_(
    ...              addresses.c.email_address.like('[e-mail]'), 
    ...              addresses.c.email_address.like('[e-mail]')
    ...           )
    ...        )
    ...    ) 
    >>> print conn.execute(s).fetchall() #doctest: +NORMALIZE_WHITESPACE
    SELECT users.fullname || ? || addresses.email_address AS title 
    FROM users, addresses 
    WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND 
    (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
    [', ', 'm', 'z', '[e-mail]', '[e-mail]']
    [(u'Wendy Williams, [e-mail]',)]

Once again, SQLAlchemy figured out the FROM clause for our statement.  In fact it will determine the FROM clause based on all of its other bits; the columns clause, the whereclause, and also some other elements which we haven't covered yet, which include ORDER BY, GROUP BY, and HAVING. 

## Using Text {@name=text}

Our last example really became a handful to type.  Going from what one understands to be a textual SQL expression into a Python construct which groups components together in a programmatic style can be hard.  That's why SQLAlchemy lets you just use strings too.  The `text()` construct represents any textual statement.  To use bind parameters with `text()`, always use the named colon format.  Such as below, we create a `text()` and execute it, feeding in the bind parameters to the `execute()` method:

    {python}
    >>> from sqlalchemy.sql import text
    >>> s = text("""SELECT users.fullname || ', ' || addresses.email_address AS title 
    ...            FROM users, addresses 
    ...            WHERE users.id = addresses.user_id AND users.name BETWEEN :x AND :y AND 
    ...            (addresses.email_address LIKE :e1 OR addresses.email_address LIKE :e2)
    ...        """)
    {sql}>>> print conn.execute(s, x='m', y='z', e1='[e-mail]', e2='[e-mail]').fetchall() # doctest:+NORMALIZE_WHITESPACE
    SELECT users.fullname || ', ' || addresses.email_address AS title 
    FROM users, addresses 
    WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND 
    (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
    ['m', 'z', '[e-mail]', '[e-mail]']
    {stop}[(u'Wendy Williams, [e-mail]',)]

To gain a "hybrid" approach, any of SA's SQL constructs can have text freely intermingled wherever you like - the `text()` construct can be placed within any other `ClauseElement` construct, and when used in a non-operator context, a direct string may be placed which converts to `text()` automatically.  Below we combine the usage of `text()` and strings with our constructed `select()` object, by using the `select()` object to structure the statement, and the `text()`/strings to provide all the content within the structure.  For this example, SQLAlchemy is not given any `Column` or `Table` objects in any of its expressions, so it cannot generate a FROM clause.  So we also give it the `from_obj` keyword argument, which is a list of `ClauseElements` (or strings) to be placed within the FROM clause:

    {python}
    >>> s = select([text("users.fullname || ', ' || addresses.email_address AS title")], 
    ...        and_( 
    ...            "users.id = addresses.user_id", 
    ...             "users.name BETWEEN 'm' AND 'z'",
    ...             "(addresses.email_address LIKE :x OR addresses.email_address LIKE :y)"
    ...        ),
    ...         from_obj=['users', 'addresses']
    ...    )
    {sql}>>> print conn.execute(s, x='[e-mail]', y='[e-mail]').fetchall() #doctest: +NORMALIZE_WHITESPACE
    SELECT users.fullname || ', ' || addresses.email_address AS title 
    FROM users, addresses 
    WHERE users.id = addresses.user_id AND users.name BETWEEN 'm' AND 'z' AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
    ['[e-mail]', '[e-mail]']
    {stop}[(u'Wendy Williams, [e-mail]',)]

Going from constructed SQL to text, we lose some capabilities.  We lose the capability for SQLAlchemy to compile our expression to a specific target database; above, our expression won't work with MySQL since it has no `||` construct.  It also becomes more tedious for SQLAlchemy to be made aware of the datatypes in use; for example, if our bind parameters required UTF-8 encoding before going in, or conversion from a Python `datetime` into a string (as is required with SQLite), we would have to add extra information to our `text()` construct.  Similar issues arise on the result set side, where SQLAlchemy also performs type-specific data conversion in some cases; still more information can be added to `text()` to work around this.  But what we really lose from our statement is the ability to manipulate it, transform it, and analyze it.  These features are critical when using the ORM, which makes heavy usage of relational transformations.  To show off what we mean, we'll first introduce the ALIAS construct and the JOIN construct, just so we have some juicier bits to play with.

## Using Aliases {@name=aliases}

The alias corresponds to a "renamed" version of a table or arbitrary relation, which occurs anytime you say "SELECT  .. FROM sometable AS someothername".  The `AS` creates a new name for the table.  Aliases are super important in SQL as they allow you to reference the same table more than once.  Scenarios where you need to do this include when you self-join a table to itself, or more commonly when you need to join from a parent table to a child table multiple times.  For example, we know that our user `jack` has two email addresses.  How can we locate jack based on the combination of those two addresses?  We need to join twice to it.  Let's construct two distinct aliases for the `addresses` table and join: