dbengine.txt

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

### More On Connections {@name=connections}

Recall from the beginning of this section that the Engine provides a `connect()` method which returns a `Connection` object.  `Connection` is a *proxy* object which maintains a reference to a DBAPI connection instance.  The `close()` method on `Connection` does not actually close the DBAPI connection, but instead returns it to the connection pool referenced by the `Engine`.  `Connection` will also automatically return its resources to the connection pool when the object is garbage collected, i.e. its `__del__()` method is called.  When using the standard C implementation of Python, this method is usually called immediately as soon as the object is dereferenced.  With other Python implementations such as Jython, this is not so guaranteed.  
    
The `execute()` methods on both `Engine` and `Connection` can also receive SQL clause constructs as well:

    {python}
    connection = engine.connect()
    result = connection.execute(select([table1], table1.c.col1==5))
    for row in result:
        print row['col1'], row['col2']
    connection.close()

The above SQL construct is known as a `select()`.  The full range of SQL constructs available are described in [sql](rel:sql).

Both `Connection` and `Engine` fulfill an interface known as `Connectable` which specifies common functionality between the two objects, namely being able to call `connect()` to return a `Connection` object (`Connection` just returns itself), and being able to call `execute()` to get a result set.   Following this, most SQLAlchemy functions and objects which accept an `Engine` as a parameter or attribute with which to execute SQL will also accept a `Connection`.  As of SQLAlchemy 0.3.9, this argument is named `bind`.

    {python title="Specify Engine or Connection"}
    engine = create_engine('sqlite:///:memory:')
    
    # specify some Table metadata
    metadata = MetaData()
    table = Table('sometable', metadata, Column('col1', Integer))
    
    # create the table with the Engine
    table.create(bind=engine)
    
    # drop the table with a Connection off the Engine
    connection = engine.connect()
    table.drop(bind=connection)

Connection facts:

 * the Connection object is **not threadsafe**.  While a Connection can be shared among threads using properly synchronized access, this is also not recommended as many DBAPIs have issues with, if not outright disallow, sharing of connection state between threads.
 * The Connection object represents a single dbapi connection checked out from the connection pool.  In this state, the connection pool has no affect upon the connection, including its expiration or timeout state.  For the connection pool to properly manage connections, **connections should be returned to the connection pool (i.e. `connection.close()`) whenever the connection is not in use**.  If your application has a need for management of multiple connections or is otherwise long running (this includes all web applications, threaded or not), don't hold a single connection open at the module level.
 
### Using Transactions with Connection {@name=transactions}

The `Connection` object provides a `begin()` method which returns a `Transaction` object.  This object is usually used within a try/except clause so that it is guaranteed to `rollback()` or `commit()`:

    {python}
    trans = connection.begin()
    try:
        r1 = connection.execute(table1.select())
        connection.execute(table1.insert(), col1=7, col2='this is some data')
        trans.commit()
    except:
        trans.rollback()
        raise

The `Transaction` object also handles "nested" behavior by keeping track of the outermost begin/commit pair.  In this example, two functions both issue a transaction on a Connection, but only the outermost Transaction object actually takes effect when it is committed.

    {python}
    # method_a starts a transaction and calls method_b
    def method_a(connection):
        trans = connection.begin() # open a transaction
        try:
            method_b(connection)
            trans.commit()  # transaction is committed here
        except:
            trans.rollback() # this rolls back the transaction unconditionally
            raise

    # method_b also starts a transaction
    def method_b(connection):
        trans = connection.begin() # open a transaction - this runs in the context of method_a's transaction
        try:
            connection.execute("insert into mytable values ('bat', 'lala')")
            connection.execute(mytable.insert(), col1='bat', col2='lala')
            trans.commit()  # transaction is not committed yet
        except:
            trans.rollback() # this rolls back the transaction unconditionally
            raise

    # open a Connection and call method_a
    conn = engine.connect()                
    method_a(conn)
    conn.close()

Above, `method_a` is called first, which calls `connection.begin()`.  Then it calls `method_b`. When `method_b` calls `connection.begin()`, it just increments a counter that is decremented when it calls `commit()`.  If either `method_a` or `method_b` calls `rollback()`, the whole transaction is rolled back.  The transaction is not committed until `method_a` calls the `commit()` method.  This "nesting" behavior allows the creation of functions which "guarantee" that a transaction will be used if one was not already available, but will automatically participate in an enclosing transaction if one exists.

Note that SQLAlchemy's Object Relational Mapper also provides a way to control transaction scope at a higher level; this is described in [unitofwork_transaction](rel:unitofwork_transaction).

Transaction Facts:

 * the Transaction object, just like its parent Connection, is **not threadsafe**.
 * SQLAlchemy 0.4 will feature transactions with two-phase commit capability as well as SAVEPOINT capability.

#### Understanding Autocommit

The above transaction example illustrates how to use `Transaction` so that several executions can take part in the same transaction.  What happens when we issue an INSERT, UPDATE or DELETE call without using `Transaction`?  The answer is **autocommit**.  While many DBAPIs  implement a flag called `autocommit`, the current SQLAlchemy behavior is such that it implements its own autocommit.  This is achieved by searching the statement for strings like INSERT, UPDATE, DELETE, etc. and then issuing a COMMIT automatically if no transaction is in progress.

    {python}
    conn = engine.connect()
    conn.execute("INSERT INTO users VALUES (1, 'john')")  # autocommits

### Connectionless Execution, Implicit Execution {@name=implicit}

Recall from the first section we mentioned executing with and without a `Connection`.  `Connectionless` execution refers to calling the `execute()` method on an object which is not a `Connection`, which could be on the `Engine` itself, or could be a constructed SQL object.  When we say "implicit", we mean that we are calling the `execute()` method on an object which is neither a `Connection` nor an `Engine` object; this can only be used with constructed SQL objects which have their own `execute()` method, and can be "bound" to an `Engine`.  A description of "constructed SQL objects" may be found in [sql](rel:sql).

A summary of all three methods follows below.  First, assume the usage of the following `MetaData` and `Table` objects; while we haven't yet introduced these concepts, for now you only need to know that we are representing a database table, and are creating an "executable" SQL construct which issues a statement to the database.  These objects are described in [metadata](rel:metadata).

    {python}
    meta = MetaData()
    users_table = Table('users', meta, 
        Column('id', Integer, primary_key=True), 
        Column('name', String(50))
    )
    
Explicit execution delivers the SQL text or constructed SQL expression to the `execute()` method of `Connection`:

    {python}
    engine = create_engine('sqlite:///file.db')
    connection = engine.connect()
    result = connection.execute(users_table.select())
    for row in result:
        # ....
    connection.close()

Explicit, connectionless execution delivers the expression to the `execute()` method of `Engine`:

    {python}
    engine = create_engine('sqlite:///file.db')
    result = engine.execute(users_table.select())
    for row in result:
        # ....
    result.close()

Implicit execution is also connectionless, and calls the `execute()` method on the expression itself, utilizing the fact that either an `Engine` or `Connection` has been *bound* to the expression object (binding is discussed further in the next section, [metadata](rel:metadata)):

    {python}
    engine = create_engine('sqlite:///file.db')
    meta.bind = engine
    result = users_table.select().execute()
    for row in result:
        # ....
    result.close()
    
In both "connectionless" examples, the `Connection` is created behind the scenes; the `ResultProxy` returned by the `execute()` call references the `Connection` used to issue the SQL statement.   When we issue `close()` on the `ResultProxy`, or if the result set object falls out of scope and is garbage collected, the underlying `Connection` is closed for us, resulting in the DBAPI connection being returned to the pool.

#### Using the Threadlocal Execution Strategy {@name=strategies}

With connectionless execution, each returned `ResultProxy` object references its own distinct DBAPI connection object.  This means that multiple executions will result in multiple DBAPI connections being used at the same time; the example below illustrates this:

    {python}
    db = create_engine('mysql://localhost/test')