2017年12月4日 星期一

[ Python 文章收集 ] SQLAlchemy - SQL Expression Language Tutorial - Part1

Source From Here 
SQL Expression Language Tutorial 
The SQLAlchemy Expression Language presents a system of representing relational database structures and expressions using Python constructs. These constructs are modeled to resemble those of the underlying database as closely as possible, while providing a modicum of abstraction of the various implementation differences between database backends. While the constructs attempt to represent equivalent concepts between backends with consistent structures, they do not conceal useful concepts that are unique to particular subsets of backends. The Expression Language therefore presents a method of writing backend-neutral SQL expressions, but does not attempt to enforce that expressions are backend-neutral. 

The Expression Language is in contrast to the Object Relational Mapper, which is a distinct API that builds on top of the Expression Language. Whereas the ORM, introduced in Object Relational Tutorial, presents a high level and abstracted pattern of usage, which itself is an example of applied usage of the Expression Language, the Expression Language presents a system of representing the primitive constructs of the relational database directly without opinion.

While there is overlap among the usage patterns of the ORM and the Expression Language, the similarities are more superficial than they may at first appear. One approaches the structure and content of data from the perspective of a user-defined domain model which is transparently persisted and refreshed from its underlying storage model. The other approaches it from the perspective of literal schema and SQL expression representations which are explicitly composed into messages consumed individually by the database. 

A successful application may be constructed using the Expression Language exclusively, though the application will need to define its own system of translating application concepts into individual database messages and from individual database result sets. Alternatively, an application constructed with the ORM may, in advanced scenarios, make occasional usage of the Expression Language directly in certain areas where specific database interactions are required. 

Version Check 
A quick check to verify the version of SQLAlchemy: 
>>> import sqlalchemy
>>> sqlalchemy.__version__
'1.1.15'

Connecting 
For this tutorial we will use an in-memory-only SQLite database. This is an easy way to test things without needing to have an actual database defined anywhere. To connect we use create_engine()
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///:memory:', echo=True)

The echo flag is a shortcut to setting up SQLAlchemy logging, which is accomplished via Python’s standard logging moduleWith it enabled, we’ll see all the generated SQL produced. If you are working through this tutorial and want less output generated, set it to False. This tutorial will format the SQL behind a popup window so it doesn’t get in our way; just click the “SQL” links to see what’s being generated. 

The first time a method like Engine.execute() or Engine.connect() is called, the Engine establishes a real DBAPI connection to the database, which is then used to emit the SQL. 
Lazy Connecting 
The Engine, when first returned by create_engine(), has not actually tried to connect to the database yet; that happens only the first time it is asked to perform a task against the database.

Define and Create Tables 
The SQL Expression Language constructs its expressions in most cases against table columns. In SQLAlchemy, a column is most often represented by an object called Column, and in all cases a Column is associated with a Table. A collection of Table objects and their associated child objects is referred to as database metadata. In this tutorial we will explicitly lay out several Table objects, but note that SA can also “import” whole sets of Table objects automatically from an existing database (this process is called table reflection). 

We define our tables all within a catalog called MetaData, using the Table construct, which resembles regular SQL CREATE TABLE statements. We’ll make two tables, one of which represents “users” in an application, and another which represents zero or more “email addresses” for each row in the “users” table: 
>>> from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
>>> metadata = MetaData()
>>> users = Table('users', metadata,
... Column('id', Integer, primary_key=True),
... Column('name', String),
... Column('fullname', String),
... )
>>> addresses = Table('addresses', metadata,
... Column('id', Integer, primary_key=True),
... Column('user_id', None, ForeignKey('users.id')),
... Column('email_address', String, nullable=False)
... )
>>> metadata.tables.keys() // Show the created tables
['users', 'addresses']

All about how to define Table objects, as well as how to create them from an existing database automatically, is described in Describing Databases with MetaData

Next, to tell the MetaData we’d actually like to create our selection of tables for real inside the SQLite database, we use create_all(), passing it the engine instance which points to our database. This will check for the presence of each table first before creating, so it’s safe to call multiple times: 
>>> metadata.create_all(engine)
  1. ...  
  2. 2017-12-04 08:30:41,899 INFO sqlalchemy.engine.base.Engine  
  3. CREATE TABLE users (  
  4.         id INTEGER NOT NULL,  
  5.         name VARCHAR,  
  6.         fullname VARCHAR,  
  7.         PRIMARY KEY (id)  
  8. )  
  9.   
  10.   
  11. 2017-12-04 08:30:41,899 INFO sqlalchemy.engine.base.Engine ()  
  12. 2017-12-04 08:30:41,900 INFO sqlalchemy.engine.base.Engine COMMIT  
  13. 2017-12-04 08:30:41,900 INFO sqlalchemy.engine.base.Engine  
  14. CREATE TABLE addresses (  
  15.         id INTEGER NOT NULL,  
  16.         user_id INTEGER,  
  17.         email_address VARCHAR NOT NULL,  
  18.         PRIMARY KEY (id),  
  19.         FOREIGN KEY(user_id) REFERENCES users (id)  
  20. )  
  21.   
  22.   
  23. 2017-12-04 08:30:41,901 INFO sqlalchemy.engine.base.Engine ()  
  24. 2017-12-04 08:30:41,901 INFO sqlalchemy.engine.base.Engine COMMIT  

Note. 
Users familiar with the syntax of CREATE TABLE may notice that the VARCHAR columns were generated without a length; on SQLite and PostgreSQL, this is a valid datatype, but on others, it’s not allowed. So if running this tutorial on one of those databases, and you wish to use SQLAlchemy to issue CREATE TABLE, a “length” may be provided to the String type as below:
  1. Column('name', String(50))  
The length field on String, as well as similar precision/scale fields available on IntegerNumeric, etc. are not referenced by SQLAlchemy other than when creating tables.

Additionally, Firebird and Oracle require sequences to generate new primary key identifiers, and SQLAlchemy doesn’t generate or assume these without being instructed. For that, you use the Sequence construct:
  1. from sqlalchemy import Sequence  
  2. Column('id', Integer, Sequence('user_id_seq'), primary_key=True)  
A full, foolproof Table is therefore:
  1. users = Table('users', metadata,  
  2.    Column('id', Integer, Sequence('user_id_seq'), primary_key=True),  
  3.    Column('name', String(50)),  
  4.    Column('fullname', String(50)),  
  5.    Column('password', String(12))  
  6. )  
We include this more verbose Table construct separately to highlight the difference between a minimal construct geared primarily towards in-Python usage only, versus one that will be used to emit CREATE TABLE statements on a particular set of backends with more stringent requirements

Insert Expressions 
The first SQL expression we’ll create is the Insert construct, which represents an INSERT statement. This is typically created relative to its target table: 
>>> users = metadata.tables['users']
>>> ins = users.insert()

To see a sample of the SQL this construct produces, use the str() function: 
>>> str(ins)
'INSERT INTO users (id, name, fullname) VALUES (:id, :name, :fullname)'

Notice above that the INSERT statement names every column in the users table. This can be limited by using the values() method, which establishes the VALUES clause of the INSERT explicitly: 
>>> ins = users.insert().values(name='john', fullname='john lee')
>>> str(ins)
'INSERT INTO users (name, fullname) VALUES (:name, :fullname)'

Above, while the values method limited the VALUES clause to just two columns, the actual data we placed in values didn’t get rendered into the string; instead we got named bind parameters. As it turns out, our data is stored within our Insert construct, but it typically only comes out when the statement is actually executed; since the data consists of literal values, SQLAlchemy automatically generates bind parameters for them. We can peek at this data for now by looking at the compiled form of the statement: 
>>> ins.compile().params
{'fullname': 'john lee', 'name': 'john'}

Executing 
The interesting part of an Insert is executing it. In this tutorial, we will generally focus on the most explicit method of executing a SQL construct, and later touch upon some “shortcut” ways to do it. The engine object we created is a repository for database connections capable of issuing SQL to the database. To acquire a connection, we use the connect() method: 
>>> conn = engine.connect()
>>> conn

The Connection object represents an actively checked out DBAPI connection resource. Lets feed it our Insert object and see what happens: 
>>> result = conn.execute(ins)
2017-12-04 10:13:50,368 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname) VALUES (?, ?)
2017-12-04 10:13:50,369 INFO sqlalchemy.engine.base.Engine ('john', 'john lee')
2017-12-04 10:13:50,369 INFO sqlalchemy.engine.base.Engine COMMIT

So the INSERT statement was now issued to the database. Although we got positional “qmark” bind parameters instead of “named” bind parameters in the output. How come ? Because when executed, the Connection used the SQLite dialect to help generate the statement; when we use the str() function, the statement isn’t aware of this dialect, and falls back onto a default which uses named parameters. We can view this manually as follows: 
>>> ins.bind = engine
>>> str(ins)
'INSERT INTO users (name, fullname) VALUES (?, ?)'

What about the result variable we got when we called execute() ? As the SQLAlchemy Connection object references a DBAPI connection, the result, known as a ResultProxy object, is analogous to the DBAPI cursor object. In the case of an INSERT, we can get important information from it, such as the primary key values which were generated from our statement using ResultProxy.inserted_primary_key
>>> result.inserted_primary_key
[1]

The value of 1 was automatically generated by SQLite, but only because we did not specify the id column in our Insert statement; otherwise, our explicit value would have been used. In either case, SQLAlchemy always knows how to get at a newly generated primary key value, even though the method of generating them is different across different databases; each database’s Dialect knows the specific steps needed to determine the correct value (or values; note that ResultProxy.inserted_primary_key returns a list so that it supports composite primary keys). Methods here range from using cursor.lastrowid, to selecting from a database-specific function, to using INSERT..RETURNING syntax; this all occurs transparently. 

Executing Multiple Statements 
Our insert example above was intentionally a little drawn out to show some various behaviors of expression language constructs. In the usual case, an Insert statement is usually compiled against the parameters sent to the execute()method on Connection, so that there’s no need to use the values keyword with Insert. Lets create a generic Insert statement again and use it in the “normal” way: 
>>> ins = users.insert()
>>> conn.execute(ins, id=2, name='Wendy', fullname='Wendy Williams')
2017-12-04 10:26:18,692 INFO sqlalchemy.engine.base.Engine INSERT INTO users (id, name, fullname) VALUES (?, ?, ?)
2017-12-04 10:26:18,692 INFO sqlalchemy.engine.base.Engine (2, 'Wendy', 'Wendy Williams')
2017-12-04 10:26:18,693 INFO sqlalchemy.engine.base.Engine COMMIT

Above, because we specified all three columns in the execute() method, the compiled Insert included all three columns. The Insert statement is compiled at execution time based on the parameters we specified; if we specified fewer parameters, the Insert would have fewer entries in its VALUES clause. 

To issue many inserts using DBAPI’s executemany() method, we can send in a list of dictionaries each containing a distinct set of parameters to be inserted, as we do here to add some email addresses: 
>>> conn.execute(addresses.insert(), [
... {'user_id': 1, 'email_address' : 'jack@yahoo.com'},
... {'user_id': 1, 'email_address' : 'jack@msn.com'},
... {'user_id': 2, 'email_address' : 'www@www.org'},
... {'user_id': 2, 'email_address' : 'wendy@aol.com'},
... ])
2017-12-04 10:30:06,924 INFO sqlalchemy.engine.base.Engine INSERT INTO addresses (user_id, email_address) VALUES (?, ?)
2017-12-04 10:30:06,924 INFO sqlalchemy.engine.base.Engine ((1, 'jack@yahoo.com'), (1, 'jack@msn.com'), (2, 'www@www.org'), (2, 'wendy@aol.com'))
2017-12-04 10:30:06,925 INFO sqlalchemy.engine.base.Engine COMMIT

Above, we again relied upon SQLite’s automatic generation of primary key identifiers for each addresses row. 

When executing multiple sets of parameters, each dictionary must have the same set of keys; i.e. you cant have fewer keys in some dictionaries than others. This is because the Insert statement is compiled against the first dictionary in the list, and it’s assumed that all subsequent argument dictionaries are compatible with that statement. 

The “executemany” style of invocation is available for each of the insert()update() and delete() constructs. 

Selecting 
We began with inserts just so that our test database had some data in it. The more interesting part of the data is selecting it! We’ll cover UPDATE and DELETE statements later. The primary construct used to generate SELECT statements is the select() function: 
>>> from sqlalchemy.sql import select
>>> s = select([users])
>>> result = conn.execute(s)
2017-12-04 10:45:25,840 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname
FROM users
2017-12-04 10:45:25,841 INFO sqlalchemy.engine.base.Engine ()

Above, we issued a basic select() call, placing the users table within the COLUMNS clause of the select, and then executing. SQLAlchemy expanded the users table into the set of each of its columns, and also generated a FROM clause for us. The result returned is again a ResultProxy object, which acts much like a DBAPI cursor, including methods such as fetchone() and fetchall(). The easiest way to get rows from it is to just iterate: 
>>> for row in result:
... print(row)
...
(1, u'john', u'john lee')
(2, u'Wendy', u'Wendy Williams')

Above, we see that printing each row produces a simple tuple-like result. We have more options at accessing the data in each row. One very common way is through dictionary access, using the string names of columns: 
>>> result = conn.execute(s)
>>> row = result.fetchone()
>>> print("name: {}; fullname: {}".format(row['name'], row['fullname']))
name: john; fullname: john lee

Integer indexes work as well: 
>>> row = result.fetchone()
>>> print("name: {}; fullname: {}".format(row[1], row[2]))
name: Wendy; fullname: Wendy Williams

But another way, whose usefulness will become apparent later on, is to use the Column objects directly as keys: 
>>> for row in conn.execute(s):
... print("name:", row[users.c.name], "; fullname:", row[users.c.fullname])
...
2017-12-04 12:07:29,962 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname FROM users
2017-12-04 12:07:29,962 INFO sqlalchemy.engine.base.Engine ()
('name:', u'john', '; fullname:', u'john lee')
('name:', u'Wendy', '; fullname:', u'Wendy Williams')

Result sets which have pending rows remaining should be explicitly closed before discarding. While the cursor and connection resources referenced by the ResultProxy will be respectively closed and returned to the connection pool when the object is garbage collected, it’s better to make it explicit as some database APIs are very picky about such things: 
>>> 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:
>>> s = select([users.c.name, users.c.fullname])
>>> result = conn.execute(s)
2017-12-04 12:12:55,330 INFO sqlalchemy.engine.base.Engine SELECT users.name, users.fullname
FROM users
2017-12-04 12:12:55,331 INFO sqlalchemy.engine.base.Engine ()

>>> for row in result:
... print(row)
...
(u'john', u'john lee')
(u'Wendy', u'Wendy Williams')

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: 
>>> addresses = metadata.tables['addresses']
>>> for row in conn.execute(select([users, addresses])):
... print(row)
...
2017-12-04 12:17:10,009 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address
FROM users, addresses

2017-12-04 12:17:10,009 INFO sqlalchemy.engine.base.Engine ()
(1, u'john', u'john lee', 1, 1, u'jack@yahoo.com')
(1, u'john', u'john lee', 2, 1, u'jack@msn.com')
(1, u'john', u'john lee', 3, 2, u'www@www.org')
(1, u'john', u'john lee', 4, 2, u'wendy@aol.com')
(2, u'Wendy', u'Wendy Williams', 1, 1, u'jack@yahoo.com')
(2, u'Wendy', u'Wendy Williams', 2, 1, u'jack@msn.com')
(2, u'Wendy', u'Wendy Williams', 3, 2, u'www@www.org')
(2, u'Wendy', u'Wendy Williams', 4, 2, u'wendy@aol.com')

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. We do that using Select.where()
>>> s = select([users, addresses]).where(users.c.id == addresses.c.user_id)
>>> for row in conn.execute(s):
... print(row)
...
2017-12-04 12:39:13,164 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address
FROM users, addresses

WHERE users.id = addresses.user_id
2017-12-04 12:39:13,164 INFO sqlalchemy.engine.base.Engine ()

(1, u'john', u'john lee', 1, 1, u'jack@yahoo.com')
(1, u'john', u'john lee', 2, 1, u'jack@msn.com')
(2, u'Wendy', u'Wendy Williams', 3, 2, u'www@www.org')
(2, u'Wendy', u'Wendy Williams', 4, 2, u'wendy@aol.com')

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 WHEREclause. So lets see exactly what that expression is doing: 
>>> users.c.id == addresses.c.user_id

Wow, surprise ! This is neither a True nor a False. Well what is it ? 
>>> 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 see that everything we are working with is ultimately the same type of object. SQLAlchemy terms the base class of all of these expressions as ColumnElement. 

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: 
>>> 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: 
>>> print(users.c.id == 7)
users.id = :id_1

The 7 literal is embedded the resulting ColumnElement; we can use the same trick we did with the Insert object to see it: 
>>> (users.c.id == 7).compile().params
{u'id_1': 7}

Most Python operators, as it turns out, produce a SQL expression here, like equals, not equals, etc.: 
>>> print(users.c.id != 7)
users.id != :id_1
>>> print(users.c.name == None) # None converts to IS NULL
users.name IS NULL
>>> print('fred' > users.c.name) # reverse works too
users.name < :name_1

If we add two integer columns together, we get an addition expression: 
>>> 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: 
>>> 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: 
>>> print((users.c.name + users.c.fullname).compile(bind=create_engine('mysql://'))
concat(users.name, users.fullname)

The above illustrates the SQL that’s generated for an Engine that’s connected to a MySQL database; the || operator now compiles as MySQL’s concat() function. 

If you have come across an operator which really isn’t available, you can always use the Operators.op() method; this generates whatever operator you need: 
>>> print(users.c.name.op('tiddlywinks')('foo'))
users.name tiddlywinks :name_1

This function can also be used to make bitwise operators explicit. For example: 
  1. somecolumn.op('&')(0xff)  
is a bitwise AND of the value in somecolumn

When using Operators.op(), the return type of the expression may be important, especially when the operator is used in an expression that will be sent as a result column. For this case, be sure to make the type explicit, if not what’s normally expected, using type_coerce()
  1. from sqlalchemy import type_coerce  
  2. expr = type_coerce(somecolumn.op('-%>')('foo'), MySpecialType())  
  3. stmt = select([expr])  
For boolean operators, use the Operators.bool_op() method, which will ensure that the return type of the expression is handled as boolean: 
  1. somecolumn.bool_op('-->')('some value')  
Operator Customization 
While Operators.op() is handy to get at a custom operator in a hurry, the Core supports fundamental customization and extension of the operator system at the type level. The behavior of existing operators can be modified on a per-type basis, and new operations can be defined which become available for all column expressions that are part of that particular type. See the section Redefining and Creating New Operators for a description. 

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 NOTand_()or_(), and not_() can work from the corresponding functions SQLAlchemy provides (notice we also throw in a like()): 
>>> from sqlalchemy.sql import and_, or_, not_
>>> print(and_(
... users.c.name.like('j%'),
... users.c.id == addresses.c.user_id,
... or_(
... addresses.c.email_address == 'wendy@aol.com',
... addresses.c.email_address == 'jack@yahoo.com'
... ),
... not_(users.c.id > 5)
... )
... )
users.name LIKE :name_1 AND users.id = addresses.user_id AND (addresses.email_address = :email_address_1 OR addresses.email_address = :email_address_2) AND users.id <= :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: 
>>> print(users.c.name.like('j%') & (users.c.id == addresses.c.user_id) &
... (
... (addresses.c.email_address == 'wendy@aol.com') | \
... (addresses.c.email_address == 'jack@yahoo.com')
... ) \
... & ~(users.c.id>5)
... )
users.name LIKE :name_1 AND users.id = addresses.user_id AND (addresses.email_address = :email_address_1 OR addresses.email_address = :email_address_2) AND users.id <= :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: 
  1. >>> s = select([(users.c.fullname +  
  2. ...             ", " + addresses.c.email_address).label('title')]).\  
  3. ...                     where(  
  4. ...                             and_(  
  5. ...                                     users.c.id == addresses.c.user_id,  
  6. ...                                     users.c.name.between('M''Z'),  
  7. ...                                     or_(  
  8. ...                                             addresses.c.email_address.like('%@aol.com'),  
  9. ...                                             addresses.c.email_address.like('%@msn.com')  
  10. ...                                     )  
  11. ...                             )  
  12. ...                     )  
  13. >>> conn.execute(s).fetchall()  
  14. 2017-12-04 16:36:02,620 INFO sqlalchemy.engine.base.Engine SELECT users.fullname || ? || addresses.email_address AS title  
  15. FROM users, addresses  
  16. WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)  
  17. 2017-12-04 16:36:02,621 INFO sqlalchemy.engine.base.Engine (', ''M''Z''%@aol.com''%@msn.com')  
  18. [(u'Wendy Williams, wendy@aol.com',)]  
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 where clause, and also some other elements which we haven’t covered yet, which include ORDER BYGROUP BY, and HAVING

A shortcut to using and_() is to chain together multiple where() clauses. The above can also be written as: 
  1. >>> s = select([(users.c.fullname +  
  2. ...             ", " + addresses.c.email_address).label('title')]).\  
  3. ...             where(users.c.id == addresses.c.user_id).\  
  4. ...             where(users.c.name.between('M''Z')).\  
  5. ...             where(  
  6. ...                     or_(  
  7. ...                             addresses.c.email_address.like('%@aol.com'),  
  8. ...                             addresses.c.email_address.like('%@msn.com')  
  9. ...                     )  
  10. ...             )  
  11. >>>  
  12. >>> conn.execute(s).fetchall()  
  13. 2017-12-04 16:42:25,299 INFO sqlalchemy.engine.base.Engine SELECT users.fullname || ? || addresses.email_address AS title  
  14. FROM users, addresses  
  15. WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)  
  16. 2017-12-04 16:42:25,300 INFO sqlalchemy.engine.base.Engine (', ''M''Z''%@aol.com''%@msn.com')  
  17. [(u'Wendy Williams, wendy@aol.com',)]  
The way that we can build up a select() construct through successive method calls is called method chaining

Using Textual SQL 
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, for those cases when the SQL is already known and there isn’t a strong need for the statement to support dynamic features. The text() construct is used to compose a textual statement that is passed to the database mostly unchanged. Below, we create a text() object and execute it: 
  1. >>> from sqlalchemy.sql import text  
  2. >>> s = text(  
  3. ...     "SELECT users.fullname || ', ' || addresses.email_address AS title "  
  4. ...             "FROM users, addresses "  
  5. ...             "WHERE users.id = addresses.user_id "  
  6. ...             "AND users.name BETWEEN :x AND :y "  
  7. ...             "AND (addresses.email_address LIKE :e1 "  
  8. ...                     "OR addresses.email_address LIKE :e2)")  
  9. >>> conn.execute(s, x='M', y='Z', e1='%@aol.com', e2='%@msn.com').fetchall()  
  10. 2017-12-04 16:48:32,275 INFO sqlalchemy.engine.base.Engine 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 ?)  
  11. 2017-12-04 16:48:32,275 INFO sqlalchemy.engine.base.Engine ('M''Z''%@aol.com''%@msn.com')  
  12. [(u'Wendy Williams, wendy@aol.com',)]  
Above, we can see that bound parameters are specified in text() using the named colon format; this format is consistent regardless of database backend. To send values in for the parameters, we passed them into the execute() method as additional arguments. 

Specifying Bound Parameter Behaviors 
The text() construct supports pre-established bound values using the TextClause.bindparams() method: 
  1. stmt = text("SELECT * FROM users WHERE users.name BETWEEN :x AND :y")  
  2. stmt = stmt.bindparams(x="M", y="Z")  
The parameters can also be explicitly typed: 
  1. stmt = stmt.bindparams(bindparam("x", String), bindparam("y", String))  
  2. result = conn.execute(stmt, {"x""m""y""z"})  
Typing for bound parameters is necessary when the type requires Python-side or special SQL-side processing provided by the datatype. 

Specifying Result-Column Behaviors 
We may also specify information about the result columns using the TextClause.columns() method; this method can be used to specify the return types, based on name: 
  1. stmt = stmt.columns(id=Integer, name=String)  
or it can be passed full column expressions positionally, either typed or untyped. In this case it’s a good idea to list out the columns explicitly within our textual SQL, since the correlation of our column expressions to the SQL will be done positionally: 
  1. stmt = text("SELECT id, name FROM users")  
  2. stmt = stmt.columns(users.c.id, users.c.name)  
When we call the TextClause.columns() method, we get back a TextAsFrom object that supports the full suite of TextAsFrom.c and other “selectable” operations: 
  1. j = stmt.join(addresses, stmt.c.id == addresses.c.user_id)  
  2.   
  3. new_stmt = select([stmt.c.id, addresses.c.id]).\  
  4.     select_from(j).where(stmt.c.name == 'x')  
The positional form of TextClause.columns() is particularly useful when relating textual SQL to existing Core or ORM models, because we can use column expressions directly without worrying about name conflicts or other issues with the result column names in the textual SQL: 
  1. >>> stmt = text("SELECT users.id, addresses.id, users.id, "  
  2. ...             "users.name, addresses.email_address AS email "  
  3. ...             "FROM users JOIN addresses ON users.id=addresses.user_id "  
  4. ...             "WHERE users.id = 1").columns(  
  5. ...                     users.c.id,  
  6. ...                     addresses.c.id,  
  7. ...                     addresses.c.user_id,  
  8. ...                     users.c.name,  
  9. ...                     addresses.c.email_address  
  10. ...             )  
  11. >>> result = conn.execute(stmt)  
Above, there’s three columns in the result that are named “id”, but since we’ve associated these with column expressions positionally, the names aren’t an issue when the result-columns are fetched using the actual column object as a key. Fetching the email_address column would be: 
>>> row = result.fetchone()
>>> row[addresses.c.email_address]
u'jack@yahoo.com'

If on the other hand we used a string column key, the usual rules of name- based matching still apply, and we’d get an ambiguous column error for the id value: 
>>> row["id"]
Traceback (most recent call last):
File "", line 1, in
sqlalchemy.exc.InvalidRequestError: Ambiguous column name 'id' in result set column descriptions

It’s important to note that while accessing columns from a result set using Column objects may seem unusual, it is in fact the only system used by the ORM, which occurs transparently beneath the facade of the Query object; in this way, the TextClause.columns() method is typically very applicable to textual statements to be used in an ORM context. The example at Using Textual SQL illustrates a simple usage. 

Using text() fragments inside bigger statements 
text() can also be used to produce fragments of SQL that can be freely within a select() object, which accepts text() objects as an argument for most of its builder functions. Below, we combine the usage of text() within a select() object. The select() construct provides the “geometry” of the statement, and the text() construct provides the textual content within this form. We can build a statement without the need to refer to any pre-established Table metadata: 
  1. >>> s = select([  
  2. ...        text("users.fullname || ', ' || addresses.email_address AS title")  
  3. ...     ]).\  
  4. ...         where(  
  5. ...             and_(  
  6. ...                 text("users.id = addresses.user_id"),  
  7. ...                 text("users.name BETWEEN 'm' AND 'z'"),  
  8. ...                 text(  
  9. ...                     "(addresses.email_address LIKE :x "  
  10. ...                     "OR addresses.email_address LIKE :y)")  
  11. ...             )  
  12. ...         ).select_from(text('users, addresses'))  
  13. >>> conn.execute(s, x='%@aol.com', y='%@msn.com').fetchall()  
  14. [(u'Wendy Williams, wendy@aol.com',)]  

Using More Specific Text with table(), literal_column(), and column() 
We can move our level of structure back in the other direction too, by using column()literal_column(), and table() for some of the key elements of our statement. Using these constructs, we can get some more expression capabilities than if we used text() directly, as they provide to the Core more information about how the strings they store are to be used, but still without the need to get into full Table based metadata. Below, we also specify the String datatype for two of the key literal_column() objects, so that the string-specific concatenation operator becomes available. We also use literal_column() in order to use table-qualified expressions, e.g. users.fullname, that will be rendered as is; using column()implies an individual column name that may be quoted: 
  1. >>> from sqlalchemy import select, and_, text, String  
  2. >>> from sqlalchemy.sql import table, literal_column  
  3. >>> s = select([  
  4. ...    literal_column("users.fullname", String) +  
  5. ...    ', ' +  
  6. ...    literal_column("addresses.email_address").label("title")  
  7. ... ]).\  
  8. ...    where(  
  9. ...        and_(  
  10. ...            literal_column("users.id") == literal_column("addresses.user_id"),  
  11. ...            text("users.name BETWEEN 'm' AND 'z'"),  
  12. ...            text(  
  13. ...                "(addresses.email_address LIKE :x OR "  
  14. ...                "addresses.email_address LIKE :y)")  
  15. ...        )  
  16. ...    ).select_from(table('users')).select_from(table('addresses'))  
  17. >>> conn.execute(s, x='%@aol.com', y='%@msn.com').fetchall()  
  18. [(u'Wendy Williams, wendy@aol.com',)]  
Ordering or Grouping by a Label 
One place where we sometimes want to use a string as a shortcut is when our statement has some labeled column element that we want to refer to in a place such as the “ORDER BY” or “GROUP BY” clause; other candidates include fields within an “OVER” or “DISTINCT” clause. If we have such a label in our select() construct, we can refer to it directly by passing the string straight into select.order_by() or select.group_by(), among others. This will refer to the named label and also prevent the expression from being rendered twice: 
  1. >>> from sqlalchemy import func  
  2. >>> stmt = select([  
  3. ...         addresses.c.user_id,  
  4. ...         func.count(addresses.c.id).label('num_addresses')]).\  
  5. ...         order_by("num_addresses")  
  6.   
  7. SQL>>> conn.execute(stmt).fetchall()  
  8. [(24)]  
We can use modifiers like asc() or desc() by passing the string name: 
  1. >>> from sqlalchemy import func, desc  
  2. >>> stmt = select([  
  3. ...         addresses.c.user_id,  
  4. ...         func.count(addresses.c.id).label('num_addresses')]).\  
  5. ...         order_by(desc("num_addresses"))  
  6.   
  7. SQL>>> conn.execute(stmt).fetchall()  
  8. [(24)]  
Note that the string feature here is very much tailored to when we have already used the label() method to create a specifically-named label. In other cases, we always want to refer to the ColumnElement object directly so that the expression system can make the most effective choices for rendering. Below, we illustrate how using the ColumnElement eliminates ambiguity when we want to order by a column name that appears more than once: 
  1. >>> u1a, u1b = users.alias(), users.alias()  
  2. >>> stmt = select([u1a, u1b]).\  
  3. ...             where(u1a.c.name > u1b.c.name).\  
  4. ...             order_by(u1a.c.name)  # using "name" here would be ambiguous  
  5.   
  6. SQL>>> conn.execute(stmt).fetchall()  
  7. [(2, u'wendy', u'Wendy Williams'1, u'jack', u'Jack Jones')]  
Using Aliases 
The alias in SQL corresponds to a “renamed” version of a table or SELECT statement, which occurs anytime you say “SELECT .. FROM sometable AS someothername”. The AS creates a new name for the table. Aliases are a key construct as they allow any table or subquery to be referenced by a unique name. In the case of a table, this allows the same table to be named in the FROM clause multiple times. In the case of a SELECT statement, it provides a parent name for the columns represented by the statement, allowing them to be referenced relative to this name. 
  1. >>> a1 = addresses.alias()  
  2. >>> a2 = addresses.alias()  
  3. >>> s = select([users]).\  
  4. ...     where(and_(  
  5. ...             users.c.id == a1.c.user_id,  
  6. ...             users.c.id == a2.c.user_id,  
  7. ...             a1.c.email_address == 'jack@msn.com',  
  8. ...             a2.c.email_address == 'jack@yahoo.com'  
  9. ...     ))  
  10. >>> str(s)  
  11. 'SELECT users.id, users.name, users.fullname \nFROM users, addresses AS addresses_1, addresses AS addresses_2 \nWHERE users.id = addresses_1.user_id AND users.id = addresses_2.user_id AND addresses_1.email_address = :email_address_1 AND addresses_2.email_address = :email_address_2'  
  12. >>> conn.execute(s).fetchall()  
  13. 2017-12-05 10:54:49,239 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname  
  14. FROM users, addresses AS addresses_1, addresses AS addresses_2  
  15. WHERE users.id = addresses_1.user_id AND users.id = addresses_2.user_id AND addresses_1.email_address = ? AND addresses_2.email_address = ?  
  16. 2017-12-05 10:54:49,240 INFO sqlalchemy.engine.base.Engine ('jack@msn.com''jack@yahoo.com')  
  17. [(1, u'john', u'john lee')]  
Note that the Alias construct generated the names addresses_1 and addresses_2 in the final SQL result. The generation of these names is determined by the position of the construct within the statement. If we created a query using only the second a2 alias, the name would come out as addresses_1. The generation of the names is also deterministic, meaning the same SQLAlchemy statement construct will produce the identical SQL string each time it is rendered for a particular dialect. 

Since on the outside, we refer to the alias using the Alias construct itself, we don’t need to be concerned about the generated name. However, for the purposes of debugging, it can be specified by passing a string name to the FromClause.alias() method: 
>>> a1 = addresses.alias('a1')

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: 
>>> a1 = s.correlate(None).alias()
>>> s = select([users.c.name]).where(users.c.id == a1.c.id)
>>> conn.execute(s).fetchall()
[(u'jack',)]

Using 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: 
>>> 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: 
  1. >>> print(users.join(addresses,  
  2. ...                 addresses.c.email_address.like(users.c.name + '%')  
  3. ...             )  
  4. ...  )  
  5. users JOIN addresses ON addresses.email_address LIKE users.name || :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 use of the select_from() method: 
  1. >>> s = select([users.c.fullname]).select_from(  
  2. ...    users.join(addresses,  
  3. ...             addresses.c.email_address.like(users.c.name + '%'))  
  4. ...    )  
  5. >>> conn.execute(s).fetchall()  
  6. [(u'Jack Jones',), (u'Jack Jones',), (u'Wendy Williams',)]  
The outerjoin() method creates LEFT OUTER JOIN constructs, and is used in the same way as join()
  1. >>> s = select([users.c.fullname]).select_from(users.outerjoin(addresses))  
  2. >>> print(s)  
  3. SELECT users.fullname  
  4.     FROM users  
  5.     LEFT OUTER JOIN addresses ON users.id = addresses.user_id  

Supplement 
SQLAlchemy 1.2 Documentation - Engine Configuration 
FAQ - How to delete a table in SQLAlchemy? 
FAQ - List database tables with SQLAlchemy

沒有留言:

張貼留言

[ Python 常見問題 ] Get all object attributes in Python?

Source From  Here   Question   Is there a way to  get all attributes/methods/fields/etc. of an object in Python ?  vars()  is close to what ...