From bcbcc391c57d73a890aaa047c2821e2e0c6b367f Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 6 Jul 2007 17:39:05 +0000 Subject: [PATCH] - rewrote dbengine doc - some changes to metadata doc, no Bound/Dynamic metadata mentioned - fixed --file flag in genhtml.py --- doc/build/content/dbengine.txt | 215 ++++++++++++++++++---------- doc/build/content/metadata.txt | 55 +------ doc/build/genhtml.py | 2 +- doc/build/templates/formatting.html | 1 + 4 files changed, 147 insertions(+), 126 deletions(-) diff --git a/doc/build/content/dbengine.txt b/doc/build/content/dbengine.txt index 5f6e78d10d..1038440ba6 100644 --- a/doc/build/content/dbengine.txt +++ b/doc/build/content/dbengine.txt @@ -1,25 +1,57 @@ Database Engines {@name=dbengine} ============================ -A database engine is a subclass of `sqlalchemy.sql.Engine`, and is the starting point for where SQLAlchemy provides a layer of abstraction on top of the various DBAPI2 database modules. For all databases supported by SA, there is a specific "implementation" module, found in the `sqlalchemy.databases` package, that provides all the objects an `Engine` needs in order to perform its job. A typical user of SQLAlchemy never needs to deal with these modules directly. For many purposes, the only knowledge that's needed is how to create an Engine for a particular connection URL. When dealing with direct execution of SQL statements, one would also be aware of Result, Connection, and Transaction objects. The primary public facing objects are: +The **Engine** is the starting point for any SQLAlchemy application. It's "home base" for the actual database and its DBAPI, delivered to the SQLAlchemy application through a connection pool and a **Dialect**, which describes how to talk to a specific kind of database and DBAPI combination. -* [URL](boldrel:docstrings_sqlalchemy.engine.url_URL) - represents the identifier for a particular database. URL objects are usually created automatically based on a given connect string passed to the `create_engine()` function. -* [Engine](boldrel:docstrings_sqlalchemy.engine_Engine) - Combines a connection-providing resource with implementation-provided objects that know how to generate, execute, and gather information about SQL statements. It also provides the primary interface by which Connections are obtained, as well as a context for constructed SQL objects and schema constructs to "implicitly execute" themselves, which is an optional feature of SQLAlchemy. -* [Connection](boldrel:docstrings_sqlalchemy.engine_Connection) - represents a connection to the database. The underlying connection object returned by a DBAPI's connect() method is referenced internally by the Connection object. Connection provides methods that handle the execution of SQLAlchemy's own SQL constructs, as well as literal string-based statements. -* [Transaction](boldrel:docstrings_sqlalchemy.engine_Transaction) - represents a transaction on a single Connection. Includes `begin()`, `commit()` and `rollback()` methods that support basic "nestable" behavior, meaning an outermost transaction is maintained against multiple nested calls to begin/commit. -* [ResultProxy](boldrel:docstrings_sqlalchemy.engine_ResultProxy) - Represents the results of an execution, and is most analgous to the cursor object in DBAPI. It primarily allows iteration over result sets, but also provides an interface to information about inserts/updates/deletes, such as the count of rows affected, last inserted IDs, etc. -* [RowProxy](boldrel:docstrings_sqlalchemy.engine_RowProxy) - Represents a single row returned by the fetchone() method on ResultProxy. +The general structure is this: -Underneath the public-facing API of `Engine`, several components are provided by database implementations to provide the full behavior, including: + +-----------+ __________ + /---| Pool |---\ (__________) + +-------------+ / +-----------+ \ +--------+ | | + connect() <--| Engine |---x x----| DBAPI |---| database | + +-------------+ \ +-----------+ / +--------+ | | + \---| Dialect |---/ |__________| + +-----------+ (__________) -* [Dialect](boldrel:docstrings_sqlalchemy.engine_Dialect) - this object is provided by database implementations to describe the behavior of a particular database. It acts as a repository for metadata about a database's characteristics, and provides factory methods for other objects that deal with generating SQL strings and objects that handle some of the details of statement execution. -* [ConnectionProvider](boldrel:docstrings_sqlalchemy.engine_ConnectionProvider) - this object knows how to return a DBAPI connection object. It typically talks to a connection pool which maintains one or more connections in memory for quick re-use. -* [ExecutionContext](boldrel:docstrings_sqlalchemy.engine_ExecutionContext) - this object is created for each execution of a single SQL statement, and tracks information about its execution such as primary keys inserted, the total count of rows affected, etc. It also may implement any special logic that various DBAPI implementations may require before or after a statement execution. -* [Compiled](boldrel:docstrings_sqlalchemy.sql_Compiled) - represents a "compiled" SQL expression object. Includes a `compile()` method which receives SQL expression objects and assembles them into strings that are suitable for direct execution. Also collects default bind parameters into a datastructure that will be converted at execution time into a dictionary or list, depending on the dialect's paramstyle. +Where above, a [sqlalchemy.engine.Engine](rel:docstrings_sqlalchemy.engine_Engine) references both a [sqlalchemy.engine.Dialect](rel:docstrings_sqlalchemy.engine_Dialect) and [sqlalchemy.pool](rel:docstrings_sqlalchemy.pool_Pool), which together interpret the DBAPI's module functions as well as the behavior of the database. + +Creating an engine is just a matter of issuing a single call, `create_engine()`: + + {python} + engine = create_engine('postgres://scott:tiger@localhost:5432/mydatabase') + +The above engine invokes the `postgres` dialect and a connection pool which references `localhost:5432`. + +The engine can be used directly to issue SQL to the database. The most generic way is to use connections, which you get via the `connect()` method: + + {python} + connection = engine.connect() + result = connection.execute("select username from users") + for row in result: + print "username": row['username'] + connection.close() + +The connection is an instance of [sqlalchemy.engine.Connection](rel:docstrings_sqlalchemy.engine_Connection), which is a **proxy** object for an actual DBAPI connection. The returned result is an instance of [sqlalchemy.engine.ResultProxy](rel:docstrings_sqlalchemy.engine_ResultProxy), which acts very much like a DBAPI cursor. + +When you say `engine.connect()`, a new `Connection` object is created, and a DBAPI connection is retrieved from the connection pool. Later, when you call `connection.close()`, the DBAPI connection is returned to the pool; nothing is actually "closed" from the perspective of the database. + +To execute some SQL more quickly, you can skip the `Connection` part and just say: + + {python} + result = engine.execute("select username from users") + for row in result: + print "username": row['username'] + result.close() + +Where above, the `execute()` method on the `Engine` does the `connect()` part for you, and returns the `ResultProxy` directly. The actual `Connection` is *inside* the `ResultProxy`, waiting for you to finish reading the result. In this case, when you `close()` the `ResultProxy`, the underlying `Connection` is closed, which returns the DBAPI connection to the pool. + +To summarize the above two examples, when you use a `Connection` object, its known as **explicit execution**. When you don't see the `Connection` object, its called **implicit execution**. These two terms are fairly important. + +The `Engine` and `Connection` can do a lot more than what we illustrated above; SQL strings are only its most rudimental function. Later chapters will describe how "constructed SQL" expressions can be used with engines; in many cases, you don't have to deal with the `Engine` at all after it's created. The Object Relational Mapper (ORM), an optional feature of SQLAlchemy, also uses the `Engine` in order to get at connections; that's also a case where you can often create the engine once, and then forget about it. ### Supported Databases {@name=supported} -Engines exist for SQLite, Postgres, MySQL, MS-SQL, Firebird, Informix, and Oracle. For each engine, the appropriate DBAPI drivers must be installed separately. A distinct Python module exists in the `sqlalchemy.databases` package for each type of database as well, which provides implementations of some of the objects mentioned in the previous section. +Recall that the `Dialect` is used to describe how to talk to a specific kind of database. Dialects are included with SQLAlchemy for SQLite, Postgres, MySQL, MS-SQL, Firebird, Informix, and Oracle. For each engine, the appropriate DBAPI drivers must be installed separately. A distinct Python module exists in the `sqlalchemy.databases` package for each type of database which implements the appropriate classes used to construct a `Dialect` and its dependencies. Downloads for each DBAPI at the time of this writing are as follows: @@ -59,11 +91,11 @@ Available drivernames are `sqlite`, `mysql`, `postgres`, `oracle`, `mssql`, and # oracle will feed host/port/SID into cx_oracle.makedsn oracle_db = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname') -The `Engine` will ask the connection pool for a connection when a SQL statement is executed. The default connection pool, `QueuePool` as well as the default SQLite connection pool `SingletonThreadPool`, will open connections to the database on an as-needed basis. As concurrent statements are executed, `QueuePool` will grow its pool of connections to a default size of five, and will allow a default "overflow" of ten. Since the `Engine` is essentially "home base" for the connection pool, it follows that you should keep a single `Engine` per database established within an application, rather than creating a new one for each connection. +The `Engine` will ask the connection pool for a connection when the `connect()` or `execute()` methods are called. The default connection pool, `QueuePool`, as well as the default connection pool used with SQLite, `SingletonThreadPool`, will open connections to the database on an as-needed basis. As concurrent statements are executed, `QueuePool` will grow its pool of connections to a default size of five, and will allow a default "overflow" of ten. Since the `Engine` is essentially "home base" for the connection pool, it follows that you should keep a single `Engine` per database established within an application, rather than creating a new one for each connection. -#### Custom DBAPI keyword arguments +#### Custom DBAPI connect() arguments -Custom arguments can be passed to the underlying DBAPI in three ways. String-based arguments can be passed directly from the URL string as query arguments: +Custom arguments used when issuing the `connect()` call to the underlying DBAPI may be issued in three distinct ways. String-based arguments can be passed directly from the URL string as query arguments: {python} db = create_engine('postgres://scott:tiger@localhost/test?argument1=foo&argument2=bar') @@ -138,11 +170,9 @@ By default, the log level is set to `logging.ERROR` within the entire `sqlalchem The `echo` flags present as keyword arguments to `create_engine()` and others as well as the `echo` property on `Engine`, when set to `True`, will first attempt to ensure that logging is enabled. Unfortunately, the `logging` module provides no way of determining if output has already been configured (note we are referring to if a logging configuration has been set up, not just that the logging level is set). For this reason, any `echo=True` flags will result in a call to `logging.basicConfig()` using sys.stdout as the destination. It also sets up a default format using the level name, timestamp, and logger name. Note that this configuration has the affect of being configured **in addition** to any existing logger configurations. Therefore, **when using Python logging, ensure all echo flags are set to False at all times**, to avoid getting duplicate log lines. -### Using Connections {@name=connections} - -In this section we describe the SQL execution interface available from an `Engine` instance. Note that when using the Object Relational Mapper (ORM) as well as when dealing with with "bound" metadata objects, SQLAlchemy deals with the Engine and Connections for you and you generally don't need to know much about it; in those cases, you can skip this section and go to [metadata](rel:metadata). "Bound" metadata is described in [metadata_tables_binding](rel:metadata_tables_binding). +### More On Connections {@name=connections} -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. This object provides methods by which literal SQL text as well as SQL clause constructs can be compiled and executed. +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. This object provides methods by which literal SQL text as well as SQL clause constructs can be compiled and executed. {python} engine = create_engine('sqlite:///:memory:') @@ -156,7 +186,7 @@ The `close` method on `Connection` does not actually remove the underlying conne In both execution styles above, the `Connection` object 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 method on `Engine` and `Connection` can also receive SQL clause constructs as well, which are described in [sql](rel:sql): +The `execute()` methods on both `Engine` and `Connection` can also receive SQL clause constructs as well: {python} connection = engine.connect() @@ -165,7 +195,9 @@ The execute method on `Engine` and `Connection` can also receive SQL clause cons print row['col1'], row['col2'] connection.close() -Both `Connection` and `Engine` fulfill an interface known as `Connectable` which specifies common functionality between the two objects, such as getting a `Connection` and executing queries. Therefore, most SQLAlchemy functions which take an `Engine` as a parameter with which to execute SQL will also accept a `Connection` (and the name of the argument is typically called `connectable`): +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, such as getting a `Connection` and executing queries. Therefore, most SQLAlchemy functions which take an `Engine` as a parameter with which to execute SQL will also accept a `Connection`. In SQLAlchemy 0.3, this argument frequently named `connectable` or `engine`. In the 0.4 series of SQLAlchemy, its consistently named `bind`. {python title="Specify Engine or Connection"} engine = create_engine('sqlite:///:memory:') @@ -186,7 +218,7 @@ 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. -### Transactions {@name=transactions} +### 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()`: @@ -229,17 +261,25 @@ The `Transaction` object also handles "nested" behavior by keeping track of the 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. +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**. - + +#### 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 + ### Implicit Execution {@name=implicit} -**Implicit execution** refers to the execution of SQL without the explicit usage of a `Connection` object. This occurs when you call the `execute()` method off of an `Engine` object or off of a SQL expression or table that is associated with "bound" metadata. +Recall from the first section the distinction between **explicit** and **implicit** execution. When we say "implicit", we mean that we are executing SQL *without* a visible `Connection` object being used. In this case, the `ResultProxy` returned by the `execute()` call references the `Connection` used to issue the SQL statement. Implicit execution can be invoked by calling the `execute()` method on the `Engine`, as below: {python title="Implicit Execution Using Engine"} engine = create_engine('sqlite:///:memory:') @@ -248,40 +288,28 @@ Transaction Facts: print row['col1'], row['col2'] result.close() -Using "bound" metadata: +Another way to implicitly execute, is to use constructed SQL (described in [sql](rel:sql)) which references "bound" `MetaData` (`MetaData` is an object that stores information about the tables in your database, and is described in the next section, [metadata](rel:metadata)): {python title="Implicit Execution Using Engine-Bound SQL Construct"} engine = create_engine('sqlite:///:memory:') - meta = MetaData(engine) + meta = MetaData() + meta.connect(engine) + table = Table('mytable', meta, Column('col1', Integer), Column('col2', String(20))) r = table.insert().execute(col1=5, col2='some record') Notice in the above two examples, no `connect()` method is ever called nor do we ever see a `Connection` anywhere; the `Connection` is created for you automatically via the `execute()` method, and a handle to the execution's cursor remains open in the returned result set. When the result set is closed via the `close()` method, or if the result set object falls out of scope and is garbage collected, the underlying cursor is closed, the `Connection` is discarded and the underlying DBAPI connection is returned to the connection pool. -The purpose of the "implicit" connection is strictly one of convenience; while in SQLAlchemy 0.1 it was the only style of operation, it is now optional. - -#### Implicit Execution Strategies {@name=strategies} - -The internal behavior of engine during implicit execution can be affected by the `strategy` keyword argument to `create_engine()`. Generally this setting can be left at its default value of `plain`. However, for the advanced user, the `threadlocal` option can provide the service of managing connections against the current thread in which they were pulled from the connection pool, where the same underlying DBAPI connection as well as a single database-level transaction can then be shared by many operations without explicitly passing a `Connection` or `Transaction` object around. It also may reduce the number of connections checked out from the connection pool at a given time. - -Note that this setting does **not** affect the fact that **Connection and Transaction objects are not threadsafe.** The "threadlocal" strategy affects the selection of DBAPI connections which are pulled from the connection pool when a `Connection` object is created, but does not synchronize method access to the `Connection` or `Transaction` instances themselves, which are only proxy objects. It is instead intended that many `Connection` instances would share access to a single "connection" object that is referenced in relation to the current thread. - -When `strategy` is set to `plain`, each implicit execution requests a unique connection from the connection pool, which is returned to the pool when the resulting `ResultProxy` falls out of scope (i.e. `__del__()` is called) or its `close()` method is called. If a second implicit execution occurs while the `ResultProxy` from the previous execution is still open, then a second connection is pulled from the pool. - -When `strategy` is set to `threadlocal`, the `Engine` still checks out a connection which is closeable in the same manner via the `ResultProxy`, except the connection it checks out will be the **same** connection as one which is already checked out, assuming the operation is in the same thread. When all `ResultProxy` objects are closed in a particular thread, the connection is returned to the pool normally. +#### Using the Threadlocal Execution Strategy {@name=strategies} -An additional feature of the `threadlocal` selection is that `Transaction` objects can be managed implicitly as well, by calling the `begin()`,`commit()` and `rollback()` methods off of the `Engine`, or by using `Transaction` objects from the thread-local connection. - -It is crucial to note that the `plain` and `threadlocal` contexts **do not impact the connect() method on the Engine.** `connect()` always returns a unique connection. Implicit connections use a different method off of `Engine` for their operations called `contextual_connect()`. - -By default, every call to `execute` pulls a dedicated DBAPI connection from the connection pool: +In the above examples, each time we call `execute()`, either on the `Engine` or on a constructed SQL statement, and therefore use implicit execution, a distinct `Connection` is created behind the scenes, which references a distinct DBAPI connection. Each `ResultProxy` returned therefore references its own connection which was returned from the connection pool; when the result is closed, the underlying DBAPI connection is returned to the pool. The example below illustrates this: {python title="Plain Strategy"} db = create_engine('mysql://localhost/test', strategy='plain') - + # execute one statement and receive results. r1 now references a DBAPI connection resource. r1 = db.execute("select * from table1") - + # execute a second statement and receive results. r2 now references a *second* DBAPI connection resource. r2 = db.execute("select * from table2") for row in r1: @@ -290,76 +318,109 @@ By default, every call to `execute` pulls a dedicated DBAPI connection from the ... # release connection 1 r1.close() - + # release connection 2 r2.close() -Using the "threadlocal" strategy, all calls to `execute` within the same thread will be guaranteed to use the same underlying DBAPI connection, which is only returned to the connection pool when all `ResultProxy` instances have been closed. +Where above, we have two result sets in scope at the same time, therefore we have two distinct DBAPI connections, both separately checked out from the connection pool, in scope at the same time. + +An option exists to `create_engine()` called `strategy="threadlocal"`, which changes this behavior. When this option is used, the `Engine` which is returned by `create_engine()` is a special subclass of engine called `TLEngine`. This engine, when it creates the `Connection` used by an implicit execution, checks a **threadlocal variable** for an existing DBAPI connection that was already checked out from the pool, within the current thread. If one exists, it uses that one. + +The usage of "threadlocal" modifies the underlying behavior of our example above, as follows: {python title="Threadlocal Strategy"} db = create_engine('mysql://localhost/test', strategy='threadlocal') - + # execute one statement and receive results. r1 now references a DBAPI connection resource. r1 = db.execute("select * from table1") - + # execute a second statement and receive results. r2 now references the *same* resource as r1 r2 = db.execute("select * from table2") - + for row in r1: ... for row in r2: ... - # dereference r1. the connection is still held by r2. - r1 = None - - # dereference r2. with no more references to the underlying connection resources, they + # close r1. the connection is still held by r2. + r1.close() + + # close r2. with no more references to the underlying connection resources, they # are returned to the pool. - r2 = None + r2.close() -To get at the actual `Connection` object which is used by implicit executions, call the `contextual_connect()` method on `Engine`: +Where above, we again have two result sets in scope at the same time, but because they are present in the same thread, there is only **one DBAPI connection in use**. - {python title="Contextual Connection"} - # threadlocal strategy - db = create_engine('mysql://localhost/test', strategy='threadlocal') - - conn1 = db.contextual_connect() - conn2 = db.contextual_connect() +While the above distinction may not seem like much, it has several potentially desireable effects. One is that you can in some cases reduce the number of concurrent connections checked out from the connection pool, in the case that a `ResultProxy` is still opened and a second statement is issued. A second advantage is that by limiting the number of checked out connections in a thread to just one, you eliminate the issue deadlocks within a single thread, such as when connection A locks a table, and connection B attempts to read from the table in the same thread, which would normally deadlock; the `threadlocal` strategy eliminates this possibility. - >>> conn1.connection is conn2.connection - True +A third advantage to the `threadlocal` strategy is that it allows `Transaction` to be used in combination with implicit execution. Recall from the section on transactions, that the `Transaction` is returned by the `begin()` method on a `Connection`; all statements which wish to participate in this transcation must be `execute()`ed by the same `Connection`, thereby forcing the usage of explicit execution. However, the `TLEngine` provides a `Transaction` that is local to the current thread; using it, one can issue many statements implicitly within a thread and they will all automatically partake in the current transaction, as in the example below: -When the `plain` strategy is used, the `contextual_connect()` method is synonymous with the `connect()` method; both return a distinct connection from the pool. + {python title="threadlocal connection sharing"} + # get a TLEngine + engine = create_engine('mysql://localhost/test', strategy='threadlocal') -One programming pattern that the `threadlocal` strategy supports is transparent connection and transaction sharing. + engine.begin() + try: + engine.execute("insert into users values (?, ?)", 1, "john") + users.update(users.c.user_id==5).execute(name='ed') + engine.commit() + except: + engine.rollback() + +Notice that no `Connection` needed to be used; the `begin()` method on `TLEngine` (which note is not available on the regular `Engine`) created a `Transaction` as well as a `Connection` which were both held in the scope of the current thread. + +Complex application flows can take advantage of the "threadlocal" strategy in order to allow many disparate parts of an application to take place in the same transaction automatically: {python title="threadlocal connection sharing"} - db = create_engine('mysql://localhost/test', strategy='threadlocal') + engine = create_engine('mysql://localhost/test', strategy='threadlocal') def dosomethingimplicit(): table1.execute("some sql") table1.execute("some other sql") - + def dosomethingelse(): table2.execute("some sql") - conn = db.contextual_connect() + conn = engine.contextual_connect() # do stuff with conn conn.execute("some other sql") conn.close() - + def dosomethingtransactional(): - conn = db.contextual_connect() + conn = engine.contextual_connect() trans = conn.begin() # do stuff trans.commit() - - db.begin() + + engine.begin() try: dosomethingimplicit() dosomethingelse() dosomethingtransactional() - db.commit() + engine.commit() except: - db.rollback() + engine.rollback() + +In the above example, the program calls three functions `dosomethingimplicit()`, `dosomethingelse()` and `dosomethingtransactional()`. All three functions use either implicit execution, or a special function `contextual_connect()` which we will describe in a moment. These two styles of execution both indicate that all executions will participate in the thread-local transaction. Recall that `Transaction` supports "nesting" behavior, whereby transactions opened in the context of an existing transaction automatically "nest" into the enclosing transaction. Therefore, in the above example, while there are several calls that return "new" `Transaction` or `Connection` objects, in reality only one "real" connection is ever used, and there is only one "real" transaction in use. So only the bottommost `begin()`/`commit()` pair determine the actual transaction scope. + +Notice above that some of the methods used a method called `engine.contextual_connect()`. This method is available on both `Engine` as well as `TLEngine`, and returns the `Connection` that applies to the current **connection context**. When using the `TLEngine`, this is just another term for the "thread local connection" that is being used for all implicit executions. When using just the regular `Engine` (i.e. the "default" strategy), `contextual_connect()` is synonymous with `connect()`. + + {python title="Contextual Connection"} + # threadlocal strategy + db = create_engine('mysql://localhost/test', strategy='threadlocal') + + conn1 = db.contextual_connect() + conn2 = db.contextual_connect() + + >>> conn1.connection is conn2.connection + True + +The `contextual_connect()` function implies that the regular `connect()` function behaves differently. This is the case with `TLEngine`, where the `connect()` method will always return a **distinct** connection from the connection pool, regardless of any thread local connection or transaction in progress. Using this method in combination with `TLEngine` allows one to "circumvent" the current thread local context, as in this example where a single statement issues data to the database externally to the current transaction: + + {python} + engine.begin() + engine.execute("insert into users values (?, ?)", 1, "john") + connection = engine.connect() + connection.execute(users.update(users.c.user_id==5).execute(name='ed')) + engine.rollback() -In the above example, the program calls three functions `dosomethingimplicit()`, `dosomethingelse()` and `dosomethingtransactional()`. In all three functions, either implicit execution is used, **or** an explicit `Connection` is used via the `contextual_connect()` method. This indicates that they all will share the same underlying dbapi connection as well as the same parent `Transaction` instance, which is created in the main body of the program via the call to `db.create_transaction()`. So while there are several calls that return "new" `Transaction` or `Connection` objects, in reality only one "real" connection is ever used, and there is only one transaction (i.e. one begin/commit pair) executed. +In the above example, a thread-local transaction is begun, but is later rolled back. The statement `insert into users values (?, ?)` is implicitly executed, therefore uses the thread-local transaction. So its data is rolled back when the transaction is rolled back. However, the `users.update()` statement is executed using a distinct `Connection` returned by the `engine.connect()` method, so it therefore is not part of the threadlocal transaction; it autocommits immediately. diff --git a/doc/build/content/metadata.txt b/doc/build/content/metadata.txt index 1090b0851c..fd1079632d 100644 --- a/doc/build/content/metadata.txt +++ b/doc/build/content/metadata.txt @@ -121,64 +121,21 @@ And `Table` provides an interface to the table's properties as well as that of i A MetaData object can be associated with one or more Engine instances. This allows the MetaData and the elements within it to perform operations automatically, using the connection resources of that Engine. This includes being able to "reflect" the columns of tables, as well as to perform create and drop operations without needing to pass an `Engine` or `Connection` around. It also allows SQL constructs to be created which know how to execute themselves (called "implicit execution"). -To bind `MetaData` to a single `Engine`, supply an Engine when creating the MetaData, or use the `.connect()` method: +To bind `MetaData` to a single `Engine`, use the `connect()` method: {python} engine = create_engine('sqlite://', **kwargs) - # create MetaData with an Engine - meta = MetaData(engine) - - # create the Engine and MetaData in one step - meta = MetaData('postgres://db/', **kwargs) - - # or bind the MetaData to an Engine later + # create MetaData meta = MetaData() - invoices = Table('invoices', meta, - Column('invoice_id', Integer, primary_key=True), - Column('ref_num', Integer, primary_key=True), - Column('description', String(60), nullable=False) - ) - meta.connect(engine) - -Another form of `MetaData` exists which can connect to an engine within the current thread (or "on a per-thread basis"), allowing other threads to be connected to different engines simultaneously: - - {python} - meta = ThreadLocalMetaData() - # In thread 1, connect to an existing Engine + # bind to an engine meta.connect(engine) - - # Meanwhile in thread 2, create a new Engine and connect - meta.connect('mysq://user@host/dsn') - -`ThreadLocalMetaData` is intended for applications that need to use the same set of `Tables` for many different database connections in the same process, such as a CherryPy web application which handles multiple application instances in one process. -#### Using the global Metadata object -Some users prefer to create `Table` objects without specifying a `MetaData` object, having Tables scoped on an application-wide basis. For them the `default_metadata` object and the `global_connect()` function is supplied. `default_metadata` is simply an instance of `ThreadLocalMetaData` that exists within the `sqlalchemy` namespace, and `global_connect()` is a synonym for `default_metadata.connect()`. Defining a `Table` that has no `MetaData` argument will automatically use this default metadata as follows: - - {python} - from sqlalchemy import * - - # a Table with just a name and its Columns - mytable = Table('mytable', - Column('col1', Integer, primary_key=True), - Column('col2', String(40)) - ) - - # connect all the "anonymous" tables to a postgres uri in the current thread - global_connect('postgres://foo:bar@lala/test') - - # create all tables in the default metadata - default_metadata.create_all() - - # the table is bound - mytable.insert().execute(col1=5, col2='some value') - #### Reflecting Tables -Once you have a connected `MetaData` or `ThreadLocalMetaData`, you can create `Table` objects without specifying their columns, just their names, using `autoload=True`: +Once you have a `MetaData` bound to an engine, you can create `Table` objects without specifying their columns, just their names, using `autoload=True`: {python} >>> messages = Table('messages', meta, autoload = True) @@ -300,7 +257,9 @@ To force quoting for an identifier, set the "quote=True" flag on `Column` or `Ta Creating and dropping individual tables can be done via the `create()` and `drop()` methods of `Table`; these methods take an optional `connectable` parameter which references an `Engine` or a `Connection`. If not supplied, the `Engine` bound to the `MetaData` will be used, else an error is raised: {python} - meta = MetaData('sqlite:///:memory:') + meta = MetaData() + meta.connect('sqlite:///:memory:') + employees = Table('employees', meta, Column('employee_id', Integer, primary_key=True), Column('employee_name', String(60), nullable=False, key='name'), diff --git a/doc/build/genhtml.py b/doc/build/genhtml.py index 263626745d..3cb1f7b2c5 100644 --- a/doc/build/genhtml.py +++ b/doc/build/genhtml.py @@ -34,7 +34,7 @@ parser.add_option("--version", action="store", dest="version", default=sqlalchem (options, args) = parser.parse_args() if options.file: - files = [file] + files = [options.file] title='SQLAlchemy 0.3 Documentation' version = options.version diff --git a/doc/build/templates/formatting.html b/doc/build/templates/formatting.html index 563732d8ed..bb34b173ee 100644 --- a/doc/build/templates/formatting.html +++ b/doc/build/templates/formatting.html @@ -81,6 +81,7 @@ def hlight(match): return "
" + highlight.highlight(fix_indent(match.group(1)), html_escape = html_escape, syntaxtype = syntaxtype) + "
" content = p.sub(hlight, "
" + capture(caller.body) + "
") + %>
-- 2.47.2