sqlexpression.txt

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

    {python}
    >>> a1 = addresses.alias('a1')
    >>> a2 = addresses.alias('a2')
    >>> s = select([users], and_(
    ...        users.c.id==a1.c.user_id, 
    ...        users.c.id==a2.c.user_id, 
    ...        a1.c.email_address=='[e-mail]', 
    ...        a2.c.email_address=='[e-mail]'
    ...   ))
    {sql}>>> print conn.execute(s).fetchall()
    SELECT users.id, users.name, users.fullname 
    FROM users, addresses AS a1, addresses AS a2 
    WHERE users.id = a1.user_id AND users.id = a2.user_id AND a1.email_address = ? AND a2.email_address = ?
    ['[e-mail]', '[e-mail]']
    {stop}[(1, u'jack', u'Jack Jones')]

Easy enough.  One thing that we're going for with the SQL Expression Language is the melding of programmatic behavior with SQL generation.  Coming up with names like `a1` and `a2` is messy; we really didn't need to use those names anywhere, it's just the database that needed them.  Plus, we might write some code that uses alias objects that came from several different places, and it's difficult to ensure that they all have unique names.  So instead, we just let SQLAlchemy make the names for us, using "anonymous" aliases:

    {python}
    >>> a1 = addresses.alias()
    >>> a2 = addresses.alias()
    >>> s = select([users], and_(
    ...        users.c.id==a1.c.user_id, 
    ...        users.c.id==a2.c.user_id, 
    ...        a1.c.email_address=='[e-mail]', 
    ...        a2.c.email_address=='[e-mail]'
    ...   ))
    {sql}>>> print conn.execute(s).fetchall()
    SELECT users.id, users.name, users.fullname 
    FROM users, addresses AS addresses_1, addresses AS addresses_2 
    WHERE users.id = addresses_1.user_id AND users.id = addresses_2.user_id AND addresses_1.email_address = ? AND addresses_2.email_address = ?
    ['[e-mail]', '[e-mail]']
    {stop}[(1, u'jack', u'Jack Jones')]

One super-huge advantage of anonymous aliases is that not only did we not have to guess up a random name, but we can also be guaranteed that the above SQL string is **deterministically** generated to be the same every time.  This is important for databases such as Oracle which cache compiled "query plans" for their statements, and need to see the same SQL string in order to make use of it.

Aliases can of course be used for anything which you can SELECT from, including SELECT statements themselves.  We can self-join the `users` table back to the `select()` we've created by making an alias of the entire statement.  The `correlate(None)` directive is to avoid SQLAlchemy's attempt to "correlate" the inner `users` table with the outer one:

    {python}
    >>> a1 = s.correlate(None).alias()
    >>> s = select([users.c.name], users.c.id==a1.c.id)
    {sql}>>> print conn.execute(s).fetchall()
    SELECT users.name 
    FROM users, (SELECT users.id AS id, users.name AS name, users.fullname AS fullname 
    FROM users, addresses AS addresses_1, addresses AS addresses_2 
    WHERE users.id = addresses_1.user_id AND users.id = addresses_2.user_id AND addresses_1.email_address = ? AND addresses_2.email_address = ?) AS anon_1 
    WHERE users.id = anon_1.id
    ['[e-mail]', '[e-mail]']
    {stop}[(u'jack',)]
    
## Using Joins {@name=joins}

We're halfway along to being able to construct any SELECT expression.  The next cornerstone of the SELECT is the JOIN expression.  We've already been doing joins in our examples, by just placing two tables in either the columns clause or the where clause of the `select()` construct.  But if we want to make a real "JOIN" or "OUTERJOIN" construct, we use the `join()` and `outerjoin()` methods, most commonly accessed from the left table in the join:

    {python}
    >>> print users.join(addresses)
    users JOIN addresses ON users.id = addresses.user_id
    
The alert reader will see more surprises; SQLAlchemy figured out how to JOIN the two tables !  The ON condition of the join, as it's called, was automatically generated based on the `ForeignKey` object which we placed on the `addresses` table way at the beginning of this tutorial.  Already the `join()` construct is looking like a much better way to join tables.

Of course you can join on whatever expression you want, such as if we want to join on all users who use the same name in their email address as their username:

    {python}
    >>> print users.join(addresses, addresses.c.email_address.like(users.c.name + '%'))
    users JOIN addresses ON addresses.email_address LIKE users.name || :users_name_1

When we create a `select()` construct, SQLAlchemy looks around at the tables we've mentioned and then places them in the FROM clause of the statement.  When we use JOINs however, we know what FROM clause we want, so here we make usage of the `from_obj` keyword argument:

    {python}
    >>> s = select([users.c.fullname], from_obj=[
    ...    users.join(addresses, addresses.c.email_address.like(users.c.name + '%'))
    ...    ])
    {sql}>>> print conn.execute(s).fetchall()
    SELECT users.fullname 
    FROM users JOIN addresses ON addresses.email_address LIKE users.name || ?
    ['%']
    {stop}[(u'Jack Jones',), (u'Jack Jones',), (u'Wendy Williams',)]

The `outerjoin()` function just creates `LEFT OUTER JOIN` constructs.  It's used just like `join()`:

    {python}
    >>> s = select([users.c.fullname], from_obj=[users.outerjoin(addresses)])
    >>> print s
    SELECT users.fullname 
    FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id

That's the output `outerjoin()` produces, unless, of course, you're stuck in a gig using Oracle prior to version 9, and you've set up your engine (which would be using `OracleDialect`) to use Oracle-specific SQL:

    {python}
    >>> from sqlalchemy.databases.oracle import OracleDialect
    >>> print s.compile(dialect=OracleDialect(use_ansi=False))
    SELECT users.fullname 
    FROM users, addresses 
    WHERE users.id = addresses.user_id(+)

If you don't know what that SQL means, don't worry !  The secret tribe of Oracle DBAs don't want their black magic being found out ;).

## Intro to Generative Selects and Transformations {@name=transform}

We've now gained the ability to construct very sophisticated statements.  We can use all kinds of operators, table constructs, text, joins, and aliases.  The point of all of this, as mentioned earlier, is not that it's an "easier" or "better" way to write SQL than just writing a SQL statement yourself; the point is that it's better for writing *programmatically generated* SQL which can be morphed and adapted as needed in automated scenarios.

To support this, the `select()` construct we've been working with supports piecemeal construction, in addition to the "all at once" method we've been doing.  Suppose you're writing a search function, which receives criterion and then must construct a select from it.  To accomplish this, upon each criterion encountered, you apply "generative" criterion to an existing `select()` construct with new elements, one at a time.  We start with a basic `select()` constructed with the shortcut method available on the `users` table:

    {python}
    >>> query = users.select()
    >>> print query
    SELECT users.id, users.name, users.fullname 
    FROM users
    
We encounter search criterion of "name='jack'".  So we apply WHERE criterion stating such:

    {python}
    >>> query = query.where(users.c.name=='jack')
    
Next, we encounter that they'd like the results in descending order by full name.  We apply ORDER BY, using an extra modifier `desc`:

    {python}
    >>> query = query.order_by(users.c.fullname.desc())
    
We also come across that they'd like only users who have an address at MSN.  A quick way to tack this on is by using an EXISTS clause, which we correlate to the `users` table in the enclosing SELECT:

    {python}
    >>> from sqlalchemy.sql import exists
    >>> query = query.where(
    ...    exists([addresses.c.id], 
    ...        and_(addresses.c.user_id==users.c.id, addresses.c.email_address.like('[e-mail]'))
    ...    ).correlate(users))
    
And finally, the application also wants to see the listing of email addresses at once; so to save queries, we outerjoin the `addresses` table (using an outer join so that users with no addresses come back as well; since we're programmatic, we might not have kept track that we used an EXISTS clause against the `addresses` table too...).  Additionally, since the `users` and `addresses` table both have a column named `id`, let's isolate their names from each other in the COLUMNS clause by using labels:

    {python}
    >>> query = query.column(addresses).select_from(users.outerjoin(addresses)).apply_labels()
    
Let's bake for .0001 seconds and see what rises:

    {python}
    {opensql}>>> conn.execute(query).fetchall()
    SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, addresses.id AS addresses_id, addresses.user_id AS addresses_user_id, addresses.email_address AS addresses_email_address 
    FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id 
    WHERE users.name = ? AND (EXISTS (SELECT addresses.id 
    FROM addresses 
    WHERE addresses.user_id = users.id AND addresses.email_address LIKE ?)) ORDER BY users.fullname DESC
    ['jack', '[e-mail]']
    {stop}[(1, u'jack', u'Jack Jones', 1, 1, u'[e-mail]'), (1, u'jack', u'Jack Jones', 2, 1, u'[e-mail]')]

So we started small, added one little thing at a time, and at the end we have a huge statement..which actually works.  Now let's do one more thing; the searching function wants to add another `email_address` criterion on, however it doesn't want to construct an alias of the `addresses` table; suppose many parts of the application are written to deal specifically with the `addresses` table, and to change all those functions to support receiving an arbitrary alias of the address would be cumbersome.  We can actually *convert* the `addresses` table within the *existing* statement to be an alias of itself, using `replace_selectable()`:

    {python}
    >>> a1 = addresses.alias()
    >>> query = query.replace_selectable(addresses, a1)
    >>> print query
    {opensql}SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, addresses_1.id AS addresses_1_id, addresses_1.user_id AS addresses_1_user_id, addresses_1.email_address AS addresses_1_email_address 
    FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id 
    WHERE users.name = :users_name_1 AND (EXISTS (SELECT addresses_1.id 
    FROM addresses AS addresses_1 
    WHERE addresses_1.user_id = users.id AND addresses_1.email_address LIKE :addresses_email_address_1)) ORDER BY users.fullname DESC

One more thing though, with automatic labeling applied as well as anonymous aliasing, how do we retrieve the columns from the rows for this thing ?  The label for the `email_addresses` column is now the generated name `addresses_1_email_address`; and in another statement might be something different !  This is where accessing by result columns by `Column` object becomes very useful:

    {python}    
    {sql}>>> for row in conn.execute(query):
    ...     print "Name:", row[users.c.name], "; Email Address", row[a1.c.email_address]
    SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, addresses_1.id AS addresses_1_id, addresses_1.user_id AS addresses_1_user_id, addresses_1.email_address AS addresses_1_email_address 
    FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id 
    WHERE users.name = ? AND (EXISTS (SELECT addresses_1.id 
    FROM addresses AS addresses_1 
    WHERE addresses_1.user_id = users.id AND addresses_1.email_address LIKE ?)) ORDER BY users.fullname DESC
    ['jack', '[e-mail]']
    {stop}Name: jack ; Email Address [e-mail]
    Name: jack ; Email Address [e-mail]

The above example, by its end, got significantly more intense than the typical end-user constructed SQL will usually be.  However when writing higher-level tools such as ORMs, they become more significant.  SQLAlchemy's ORM relies very heavily on techniques like this.

## Everything Else {@name=everythingelse}

The concepts of creating SQL expressions have been introduced.  What's left are more variants of the same themes.  So now we'll catalog the rest of the important things we'll need to know.

### Bind Parameter Objects {@name=bindparams}

Throughout all these examples, SQLAlchemy is busy creating bind parameters wherever literal expressions occur.  You can also specify your own bind parameters with your own names, and use the same statement repeatedly.  The database dialect converts to the appropriate named or positional style, as here where it converts to positional for SQLite:

    {python}
    >>> from sqlalchemy.sql import bindparam
    >>> s = users.select(users.c.name==bindparam('username'))
    {sql}>>> conn.execute(s, username='wendy').fetchall()
    SELECT users.id, users.name, users.fullname 
    FROM users 
    WHERE users.name = ?
    ['wendy']
    {stop}[(2, u'wendy', u'Wendy Williams')]

Another important aspect of bind parameters is that they may be assigned a type.  The type of the bind parameter will determine its behavior within expressions and also how the data bound to it is processed before being sent off to the database:

    {python}
    >>> s = users.select(users.c.name.like(bindparam('username', type_=String) + text("'%'")))
    {sql}>>> conn.execute(s, username='wendy').fetchall()
    SELECT users.id, users.name, users.fullname 
    FROM users 
    WHERE users.name LIKE ? || '%'
    ['wendy']
    {stop}[(2, u'wendy', u'Wendy Williams')]
    
    
Bind parameters of the same name can also be used multiple times, where only a single named value is needed in the execute parameters:

    {python}
    >>> s = select([users, addresses], 
    ...    users.c.name.like(bindparam('name', type_=String) + text("'%'")) | 
    ...    addresses.c.email_address.like(bindparam('name', type_=String) + text("'@%'")), 
    ...    from_obj=[users.outerjoin(addresses)])
    {sql}>>> conn.execute(s, name='jack').fetchall()
    SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address 
    FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id 
    WHERE users.name LIKE ? || '%' OR addresses.email_address LIKE ? || '@%'
    ['jack', 'jack']
    {stop}[(1, u'jack', u'Jack Jones', 1, 1, u'[e-mail]'), (1, u'jack', u'Jack Jones', 2, 1, u'[e-mail]')]

### Functions {@name=functions}

SQL functions are created using the `func` keyword, which generates functions using attribute access:

    {python}
    >>> from sqlalchemy.sql import func
    >>> print func.now()
    now()

    >>> print func.concat('x', 'y')
    concat(:param_1, :param_2)
    
Certain functions are marked as "ANSI" functions, which mean they don't get the parenthesis added after them, such as CURRENT_TIMESTAMP:

    {python}
    >>> print func.current_timestamp()
    CURRENT_TIMESTAMP