From 58662f502b2e34f01a026a88576ecc8f2c8ed3f1 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 12 Jul 2007 00:55:18 +0000 Subject: [PATCH] merged trunk r2826-2879 --- CHANGES | 21 ++ doc/build/content/adv_datamapping.txt | 2 +- doc/build/content/dbengine.txt | 332 ++++++++++++++++---------- doc/build/content/metadata.txt | 102 +++----- doc/build/templates/formatting.html | 1 + lib/sqlalchemy/databases/mssql.py | 54 ++++- lib/sqlalchemy/databases/postgres.py | 7 +- lib/sqlalchemy/orm/session.py | 16 +- lib/sqlalchemy/schema.py | 3 +- lib/sqlalchemy/sql.py | 18 +- test/dialect/postgres.py | 67 +++++- test/engine/reflection.py | 18 ++ test/orm/assorted_eager.py | 95 ++++++++ test/orm/mapper.py | 18 +- test/orm/session.py | 15 ++ test/sql/selectable.py | 40 +++- test/testbase.py | 4 +- 17 files changed, 562 insertions(+), 251 deletions(-) diff --git a/CHANGES b/CHANGES index ce93a6d6d9..b1d68dad64 100644 --- a/CHANGES +++ b/CHANGES @@ -125,6 +125,8 @@ - added synchronization to the mapper() construction step, to avoid thread collections when pre-existing mappers are compiling in a different thread [ticket:613] + - synonym() properties are fully supported by all Query joining/ + with_parent operations [ticket:598] - fixed very stupid bug when deleting items with many-to-many uselist=False relations - remember all that stuff about polymorphic_union ? for @@ -137,6 +139,17 @@ to polymorphic mappers that are using a straight "outerjoin" clause - sql + - ForeignKey to a table in a schema thats not the default schema + requires the schema to be explicit; i.e. ForeignKey('alt_schema.users.id') + - MetaData can now be constructed with an engine or url as the first + argument, just like BoundMetaData + - BoundMetaData is now deprecated, and MetaData is a direct substitute. + - DynamicMetaData has been renamed to ThreadLocalMetaData. the + DynamicMetaData name is deprecated and is an alias for ThreadLocalMetaData + or a regular MetaData if threadlocal=False + - some enhancements to "column targeting", the ability to match a column + to a "corresponding" column in another selectable. this affects mostly + ORM ability to map to complex joins - MetaData and all SchemaItems are safe to use with pickle. slow table reflections can be dumped into a pickled file to be reused later. Just reconnect the engine to the metadata after unpickling. [ticket:619] @@ -182,10 +195,18 @@ - added support for reflection of domains [ticket:570] - types which are missing during reflection resolve to Null type instead of raising an error + - the fix in "schema" above fixes reflection of foreign keys from an + alt-schema table to a public schema table - sqlite - sqlite better handles datetime/date/time objects mixed and matched with various Date/Time/DateTime columns - string PK column inserts dont get overwritten with OID [ticket:603] + +- mssql + - fix port option handling for pyodbc [ticket:634] + - now able to reflect start and increment values for identity columns + - preliminary support for using scope_identity() with pyodbc + - extensions - added selectone_by() to assignmapper diff --git a/doc/build/content/adv_datamapping.txt b/doc/build/content/adv_datamapping.txt index fa47291b96..3877152faa 100644 --- a/doc/build/content/adv_datamapping.txt +++ b/doc/build/content/adv_datamapping.txt @@ -818,7 +818,7 @@ Options which can be sent to the `mapper()` function. For arguments to `relatio * **allow_column_override** - if True, allows the usage of a `relation()` which has the same name as a column in the mapped table. The table column will no longer be mapped. * **allow_null_pks=False** - indicates that composite primary keys where one or more (but not all) columns contain NULL is a valid primary key. Primary keys which contain NULL values usually indicate that a result row does not contain an entity and should be skipped. -* **always_refresh=False** - if True, all query operations for this mapped class will overwrite all data within object instances that already exist within the session, erasing any in-memory changes with whatever information was loaded from the database. +* **always_refresh=False** - if True, all query operations for this mapped class will overwrite all data within object instances that already exist within the session, erasing any in-memory changes with whatever information was loaded from the database. Note that this option bypasses the usage patterns for which the Session is designed - negative side effects should be expected, and usage issues involving this flag are not supported. For a better way to refresh data, use `query.load()`, `session.refresh()`, `session.expunge()`, or `session.clear()`. * **batch=True** - when False, indicates that when a mapper is persisting a list of instances, each instance will be fully saved to the database before moving onto the next instance. Normally, inserts and updates are batched together per-table, such as for an inheriting mapping that spans multiple tables. This flag is for rare circumstances where custom `MapperExtension` objects are used to attach logic to `before_insert()`, `before_update()`, etc., and the user-defined logic requires that the full persistence of each instance must be completed before moving onto the next (such as logic which queries the tables for the most recent ID). Note that this flag has a significant impact on the efficiency of a large save operation. * **column_prefix** - a string which will be prepended to the "key" name of all Columns when creating column-based properties from the given Table. Does not affect explicitly specified column-based properties. Setting `column_prefix='_'` is equivalent to defining all column-based properties as `_columnname=table.c.columnname`. See [advdatamapping_properties_colname](rel:advdatamapping_properties_colname) for information on overriding column-based attribute names. * **concrete** - if True, indicates this mapper should use concrete table inheritance with its parent mapper. Requires `inherits` to be set. diff --git a/doc/build/content/dbengine.txt b/doc/build/content/dbengine.txt index 3ba23f402f..6785471667 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. -* [Pool](boldrel:docstrings_sqlalchemy.pool_Pool) - provides DBAPI connections from a managed pool of connections. All `Engine` objects use a connection pool, which can be provided by a variety of different backend strategies. The most common is [QueuePool](rel:docstrings_sqlalchemy.pool_QueuePool). -* [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.engine_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.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, but you still use the `execute()` method on the `Engine`, its called **explicit, connectionless execution**. A third variant of execution also exists called **implicit execution**; this will be described later. + +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; these can each be seen as a Python module present in the `sqlalchemy.databases` package. Each dialect requires the appropriate DBAPI drivers to be installed separately. Downloads for each DBAPI at the time of this writing are as follows: @@ -33,11 +65,11 @@ Downloads for each DBAPI at the time of this writing are as follows: The SQLAlchemy Wiki contains a page of database notes, describing whatever quirks and behaviors have been observed. Its a good place to check for issues with specific databases. [Database Notes](http://www.sqlalchemy.org/trac/wiki/DatabaseNotes) -### Establishing a Database Engine {@name=establishing} +### create_engine() URL Arguments {@name=establishing} SQLAlchemy indicates the source of an Engine strictly via [RFC-1738](http://rfc.net/rfc1738.html) style URLs, combined with optional keyword arguments to specify options for the Engine. The form of the URL is: - $ driver://username:password@host:port/database + driver://username:password@host:port/database Available drivernames are `sqlite`, `mysql`, `postgres`, `oracle`, `mssql`, and `firebird`. For sqlite, the database name is the filename to connect to, or the special name ":memory:" which indicates an in-memory database. The URL is typically sent as a string to the `create_engine()` function: @@ -49,6 +81,7 @@ Available drivernames are `sqlite`, `mysql`, `postgres`, `oracle`, `mssql`, and sqlite_db = create_engine('sqlite:////absolute/path/to/database.txt') sqlite_db = create_engine('sqlite:///relative/path/to/database.txt') sqlite_db = create_engine('sqlite://') # in-memory database + sqlite_db = create_engine('sqlite://:memory:') # the same # mysql mysql_db = create_engine('mysql://localhost/foo') @@ -59,11 +92,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') @@ -95,68 +128,26 @@ A list of all standard options, as well as several that are used by particular d * **connect_args** - a dictionary of options which will be passed directly to the DBAPI's `connect()` method as additional keyword arguments. * **convert_unicode=False** - if set to True, all String/character based types will convert Unicode values to raw byte values going into the database, and all raw byte values to Python Unicode coming out in result sets. This is an engine-wide method to provide unicode conversion across the board. For unicode conversion on a column-by-column level, use the `Unicode` column type instead, described in [types](rel:types). * **creator** - a callable which returns a DBAPI connection. This creation function will be passed to the underlying connection pool and will be used to create all new database connections. Usage of this function causes connection parameters specified in the URL argument to be bypassed. -* **echo=False** - if True, the Engine will log all statements as well as a repr() of their parameter lists to the engines logger, which defaults to sys.stdout. The `echo` attribute of `Engine` can be modified at any time to turn logging on and off. If set to the string `"debug"`, result rows will be printed to the standard output as well. This flag ultimately controls a Python logger; see [dbengine_logging](rel:dbengine_logging) for information on how to configure logging directly. +* **echo=False** - if True, the Engine will log all statements as well as a repr() of their parameter lists to the engines logger, which defaults to sys.stdout. The `echo` attribute of `Engine` can be modified at any time to turn logging on and off. If set to the string `"debug"`, result rows will be printed to the standard output as well. This flag ultimately controls a Python logger; see [dbengine_logging](rel:dbengine_logging) at the end of this chapter for information on how to configure logging directly. * **echo_pool=False** - if True, the connection pool will log all checkouts/checkins to the logging stream, which defaults to sys.stdout. This flag ultimately controls a Python logger; see [dbengine_logging](rel:dbengine_logging) for information on how to configure logging directly. * **encoding='utf-8'** - the encoding to use for all Unicode translations, both by engine-wide unicode conversion as well as the `Unicode` type object. -* **module=None** - used by database implementations which support multiple DBAPI modules, this is a reference to a DBAPI2 module to be used instead of the engine's default module. For Postgres, the default is psycopg2, or psycopg1 if 2 cannot be found. For Oracle, its cx_Oracle. +* **module=None** - used by database implementations which support multiple DBAPI modules, this is a reference to a DBAPI2 module to be used instead of the engine's default module. For Postgres, the default is psycopg2. For Oracle, its cx_Oracle. * **pool=None** - an already-constructed instance of `sqlalchemy.pool.Pool`, such as a `QueuePool` instance. If non-None, this pool will be used directly as the underlying connection pool for the engine, bypassing whatever connection parameters are present in the URL argument. For information on constructing connection pools manually, see [pooling](rel:pooling). * **poolclass=None** - a `sqlalchemy.pool.Pool` subclass, which will be used to create a connection pool instance using the connection parameters given in the URL. Note this differs from `pool` in that you don't actually instantiate the pool in this case, you just indicate what type of pool to be used. * **max_overflow=10** - the number of connections to allow in connection pool "overflow", that is connections that can be opened above and beyond the pool_size setting, which defaults to five. this is only used with `QueuePool`. * **pool_size=5** - the number of connections to keep open inside the connection pool. This used with `QueuePool` as well as `SingletonThreadPool`. -* **pool_recycle=-1** - this setting causes the pool to recycle connections after the given number of seconds has passed. It defaults to -1, or no timeout. For example, setting to 3600 means connections will be recycled after one hour. Note that MySQL in particular will disconnect automatically if no activity is detected on a connection for eight hours (although this is configurable with the MySQLDB connection itself and the server configuration as well). +* **pool_recycle=-1** - this setting causes the pool to recycle connections after the given number of seconds has passed. It defaults to -1, or no timeout. For example, setting to 3600 means connections will be recycled after one hour. Note that MySQL in particular will **disconnect automatically** if no activity is detected on a connection for eight hours (although this is configurable with the MySQLDB connection itself and the server configuration as well). * **pool_timeout=30** - number of seconds to wait before giving up on getting a connection from the pool. This is only used with `QueuePool`. -* **strategy='plain'** - the Strategy argument is used to select alternate implementations of the underlying Engine object, which coordinates operations between dialects, compilers, connections, and so on. Currently, the only alternate strategy besides the default value of "plain" is the "threadlocal" strategy, which selects the usage of the `TLEngine` class that provides a modified connection scope for implicit executions. Implicit execution as well as further detail on this setting are described in [dbengine_implicit](rel:dbengine_implicit). +* **strategy='plain'** - the Strategy argument is used to select alternate implementations of the underlying Engine object, which coordinates operations between dialects, compilers, connections, and so on. Currently, the only alternate strategy besides the default value of "plain" is the "threadlocal" strategy, which selects the usage of the `TLEngine` class that provides a modified connection scope for connectionless executions. Connectionless execution as well as further detail on this setting are described in [dbengine_implicit](rel:dbengine_implicit). * **threaded=True** - used by cx_Oracle; sets the `threaded` parameter of the connection indicating thread-safe usage. cx_Oracle docs indicate setting this flag to `False` will speed performance by 10-15%. While this defaults to `False` in cx_Oracle, SQLAlchemy defaults it to `True`, preferring stability over early optimization. * **use_ansi=True** - used only by Oracle; when False, the Oracle driver attempts to support a particular "quirk" of Oracle versions 8 and previous, that the LEFT OUTER JOIN SQL syntax is not supported, and the "Oracle join" syntax of using `<column1>(+)=<column2>` must be used in order to achieve a LEFT OUTER JOIN. * **use_oids=False** - used only by Postgres, will enable the column name "oid" as the object ID column, which is also used for the default sort order of tables. Postgres as of 8.1 has object IDs disabled by default. -### Configuring Logging {@name=logging} +### More On Connections {@name=connections} -As of the 0.3 series of SQLAlchemy, Python's standard [logging](http://www.python.org/doc/lib/module-logging.html) module is used to implement informational and debug log output. This allows SQLAlchemy's logging to integrate in a standard way with other applications and libraries. The `echo` and `echo_pool` flags that are present on `create_engine()`, as well as the `echo_uow` flag used on `Session`, all interact with regular loggers. - -This section assumes familiarity with the above linked logging module. All logging performed by SQLAlchemy exists underneath the `sqlalchemy` namespace, as used by `logging.getLogger('sqlalchemy')`. When logging has been configured (i.e. such as via `logging.basicConfig()`), the general namespace of SA loggers that can be turned on is as follows: - -* `sqlalchemy.engine` - controls SQL echoing. set to `logging.INFO` for SQL query output, `logging.DEBUG` for query + result set output. -* `sqlalchemy.pool` - controls connection pool logging. set to `logging.INFO` or lower to log connection pool checkouts/checkins. -* `sqlalchemy.orm` - controls logging of various ORM functions. set to `logging.INFO` for configurational logging as well as unit of work dumps, `logging.DEBUG` for extensive logging during query and flush() operations. Subcategories of `sqlalchemy.orm` include: - * `sqlalchemy.orm.attributes` - logs certain instrumented attribute operations, such as triggered callables - * `sqlalchemy.orm.mapper` - logs Mapper configuration and operations - * `sqlalchemy.orm.unitofwork` - logs flush() operations, including dependency sort graphs and other operations - * `sqlalchemy.orm.strategies` - logs relation loader operations (i.e. lazy and eager loads) - * `sqlalchemy.orm.sync` - logs synchronization of attributes from parent to child instances during a flush() - -For example, to log SQL queries as well as unit of work debugging: - - {python} - import logging +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. - logging.basicConfig() - logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO) - logging.getLogger('sqlalchemy.orm.unitofwork').setLevel(logging.DEBUG) - -By default, the log level is set to `logging.ERROR` within the entire `sqlalchemy` namespace so that no log operations occur, even within an application that has logging enabled otherwise. - -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). - -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:') - connection = engine.connect() - result = connection.execute("select * from mytable where col1=:col1", col1=5) - for row in result: - print row['col1'], row['col2'] - connection.close() - -The `close` method on `Connection` does not actually remove the underlying connection to the database, but rather indicates that the underlying resources can be returned to the connection pool. When using the `connect()` method, the DBAPI connection referenced by the `Connection` object is not referenced anywhere else. - -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 +156,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, 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`. In SQLAlchemy 0.3, this argument is 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:') @@ -184,9 +177,9 @@ Both `Connection` and `Engine` fulfill an interface known as `Connectable` which 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. + * 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,59 +222,76 @@ 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**. - -### 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. +#### Understanding Autocommit - {python title="Implicit Execution Using Engine"} - engine = create_engine('sqlite:///:memory:') - result = engine.execute("select * from mytable where col1=:col1", col1=5) - for row in result: - print row['col1'], row['col2'] - result.close() +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. -Using "bound" metadata: + {python} + conn = engine.connect() + conn.execute("INSERT INTO users VALUES (1, 'john')") # autocommits - {python title="Implicit Execution Using Engine-Bound SQL Construct"} - engine = create_engine('sqlite:///:memory:') - meta = MetaData(engine) - table = Table('mytable', meta, Column('col1', Integer), Column('col2', String(20))) - r = table.insert().execute(col1=5, col2='some record') +### Connectionless Execution, Implicit Execution {@name=implicit} -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. +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 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). -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. +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 "executeable" SQL construct which issues a statement to the database. These objects are described in [metadata](rel:metadata). -#### Implicit Execution Strategies {@name=strategies} + {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`: -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. + {python} + engine = create_engine('sqlite:///file.db') + connection = engine.connect() + result = connection.execute(users_table.select()) + for row in result: + # .... + connection.close() -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. +Explicit, connectionless execution delivers the expression to the `execute()` method of `Engine`: -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. + {python} + engine = create_engine('sqlite:///file.db') + result = engine.execute(users_table.select()) + for row in result: + # .... + result.close() -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. +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)): -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. + {python} + engine = create_engine('sqlite:///file.db') + meta.connect(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. -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()`. +#### Using the Threadlocal Execution Strategy {@name=strategies} -By default, every call to `execute` pulls a dedicated DBAPI connection from the connection pool: +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') - {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 +300,136 @@ 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 a connectionless 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 of deadlocks within a single thread, such as when connection A locks a table, and connection B attempts to read from the same table in the same thread, it will "deadlock" on waiting for connection A to release its lock; the `threadlocal` strategy eliminates this possibility. - >>> conn1.connection is conn2.connection - True +A third advantage to the `threadlocal` strategy is that it allows the `Transaction` object to be used in combination with connectionless 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 transaction must be executed by the same `Connection`, thereby forcing the usage of an explicit connection. However, the `TLEngine` provides a `Transaction` that is local to the current thread; using it, one can issue many "connectionless" statements within a thread and they will all automatically partake in the current transaction, as in the example below: + + {python title="threadlocal connection sharing"} + # get a TLEngine + engine = create_engine('mysql://localhost/test', strategy='threadlocal') + + 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() -When the `plain` strategy is used, the `contextual_connect()` method is synonymous with the `connect()` method; both return a distinct connection from the pool. +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`, and held onto both in a context corresponding to the current thread. Each `execute()` call made use of the same connection, allowing them all to participate in the same transaction. -One programming pattern that the `threadlocal` strategy supports is transparent connection and transaction sharing. +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. The example below demonstrates several forms of "connectionless execution" as well as some specialized explicit ones: {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.create_transaction() + + 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 connectionless 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 use the same connection object. Additionally, the method `dosomethingtransactional()` begins and commits its own `Transaction`. But only one transaction is used, too; it's controlled completely by the `engine.begin()`/`engine.commit()` calls at the bottom. Recall that `Transaction` supports "nesting" behavior, whereby transactions begun on a `Connection` which already has a tranasaction open, will "nest" into the enclosing transaction. Since the transaction opened in `dosomethingtransactional()` occurs using the same connection which already has a transaction begun, it "nests" into that transaction and therefore has no effect on the actual transaction scope (unless it calls `rollback()`). + +Some of the functions in the above example make use of 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 connectionless executions. When using just the regular `Engine` (i.e. the "default" strategy), `contextual_connect()` is synonymous with `connect()`. Below we illustrate that two connections opened via `contextual_connect()` at the same time, both reference the same underlying DBAPI connection: + + {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 basic idea of `contextual_connect()` is that its the "connection used by connectionless execution". It's different from the `connect()` method in that `connect()` is always used when handling an explicit `Connection`, which will always reference distinct DBAPI connection. Using `connect()` 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, a thread-local transaction is begun, but is later rolled back. The statement `insert into users values (?, ?)` is executed without using a connection, 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. + +### Configuring Logging {@name=logging} + +As of the 0.3 series of SQLAlchemy, Python's standard [logging](http://www.python.org/doc/lib/module-logging.html) module is used to implement informational and debug log output. This allows SQLAlchemy's logging to integrate in a standard way with other applications and libraries. The `echo` and `echo_pool` flags that are present on `create_engine()`, as well as the `echo_uow` flag used on `Session`, all interact with regular loggers. + +This section assumes familiarity with the above linked logging module. All logging performed by SQLAlchemy exists underneath the `sqlalchemy` namespace, as used by `logging.getLogger('sqlalchemy')`. When logging has been configured (i.e. such as via `logging.basicConfig()`), the general namespace of SA loggers that can be turned on is as follows: -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. +* `sqlalchemy.engine` - controls SQL echoing. set to `logging.INFO` for SQL query output, `logging.DEBUG` for query + result set output. +* `sqlalchemy.pool` - controls connection pool logging. set to `logging.INFO` or lower to log connection pool checkouts/checkins. +* `sqlalchemy.orm` - controls logging of various ORM functions. set to `logging.INFO` for configurational logging as well as unit of work dumps, `logging.DEBUG` for extensive logging during query and flush() operations. Subcategories of `sqlalchemy.orm` include: + * `sqlalchemy.orm.attributes` - logs certain instrumented attribute operations, such as triggered callables + * `sqlalchemy.orm.mapper` - logs Mapper configuration and operations + * `sqlalchemy.orm.unitofwork` - logs flush() operations, including dependency sort graphs and other operations + * `sqlalchemy.orm.strategies` - logs relation loader operations (i.e. lazy and eager loads) + * `sqlalchemy.orm.sync` - logs synchronization of attributes from parent to child instances during a flush() + +For example, to log SQL queries as well as unit of work debugging: + {python} + import logging + + logging.basicConfig() + logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO) + logging.getLogger('sqlalchemy.orm.unitofwork').setLevel(logging.DEBUG) + +By default, the log level is set to `logging.ERROR` within the entire `sqlalchemy` namespace so that no log operations occur, even within an application that has logging enabled otherwise. + +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. diff --git a/doc/build/content/metadata.txt b/doc/build/content/metadata.txt index 2ffb279f4d..7088a08aed 100644 --- a/doc/build/content/metadata.txt +++ b/doc/build/content/metadata.txt @@ -6,16 +6,16 @@ Database Meta Data {@name=metadata} ### Describing Databases with MetaData {@name=tables} -The core of SQLAlchemy's query and object mapping operations is database metadata, which are Python objects that describe tables and other schema-level objects. Metadata objects can be created by explicitly naming the various components and their properties, using the Table, Column, ForeignKey, Index, and Sequence objects imported from `sqlalchemy.schema`. There is also support for *reflection*, which means you only specify the *name* of the entities and they are recreated from the database automatically. +The core of SQLAlchemy's query and object mapping operations are supported by **database metadata**, which is comprised of Python objects that describe tables and other schema-level objects. These objects can be created by explicitly naming the various components and their properties, using the Table, Column, ForeignKey, Index, and Sequence objects imported from `sqlalchemy.schema`. There is also support for **reflection** of some entities, which means you only specify the *name* of the entities and they are recreated from the database automatically. -A collection of metadata entities is stored in an object aptly named `MetaData`. This object takes an optional `name` parameter: +A collection of metadata entities is stored in an object aptly named `MetaData`: {python} from sqlalchemy import * - metadata = MetaData(name='my metadata') + metadata = MetaData() -Then to construct a Table, use the `Table` class: +To represent a Table, use the `Table` class: {python} users = Table('users', metadata, @@ -119,95 +119,63 @@ And `Table` provides an interface to the table's properties as well as that of i #### Binding MetaData to an Engine {@name=binding} -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"). +A `MetaData` object can be associated with an `Engine` (or an individual `Connection`); this process is called **binding**. This allows the `MetaData` and the elements which it contains to perform operations against the database directly, using the connection resources to which it's bound. Common operations which are made more convenient through binding include being able to generate SQL constructs which know how to execute themselves, creating `Table` objects which query the database for their column and constraint information, and issuing CREATE or DROP statements. -To bind `MetaData` to a single `Engine`, supply an Engine when creating the Meta -Data, or use the `.connect()` method: +To bind `MetaData` to an `Engine`, use the `connect()` method: {python} engine = create_engine('sqlite://', **kwargs) - # create MetaData from an Engine - meta = MetaData(engine) - - # create the Engine and MetaData in one step - meta = MetaData('postgres://db/', **kwargs) - - # or bind the engine later + # create MetaData meta = MetaData() - # ... + + # bind to an engine meta.connect(engine) -Another form of `MetaData` exits 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: +Once this is done, the `MetaData` and its contained `Table` objects can access the database directly: {python} - meta = ThreadLocalMetaData() - - # In thread 1, connect to an existing Engine - meta.connect(engine) + meta.create_all() # issue CREATE statements for all tables - # 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 + # describe a table called 'users', query the database for its columns + users_table = Table('users', meta, autoload=True) + + # generate a SELECT statement and execute + result = users_table.select().execute() -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 `DynamicMetaData` 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: +Note that the feature of binding engines is **completely optional**. All of the operations which take advantage of "bound" `MetaData` also can be given an `Engine` or `Connection` explicitly with which to perform the operation. The equivalent "non-bound" of the above would be: {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') + meta.create_all(engine) # issue CREATE statements for all tables + + # describe a table called 'users', query the database for its columns + users_table = Table('users', meta, autoload=True, autoload_with=engine) + # generate a SELECT statement and execute + result = engine.execute(users_table.select()) + #### 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`: +A `Table` object can be created without specifying any of its contained attributes, using the argument `autoload=True` in conjunction with the table's name and possibly its schema (if not the databases "default" schema). This will issue the appropriate queries to the database in order to locate all properties of the table required for SQLAlchemy to use it effectively, including its column names and datatypes, foreign and primary key constraints, and in some cases its default-value generating attributes. To use `autoload=True`, the table's `MetaData` object need be bound to an `Engine` or `Connection`, or alternatively the `autoload_with=` argument can be passed. Below we illustrate autoloading a table and then iterating through the names of its columns: {python} - >>> messages = Table('messages', meta, autoload = True) + >>> messages = Table('messages', meta, autoload=True) >>> [c.name for c in messages.columns] ['message_id', 'message_name', 'date'] -At the moment the Table is constructed, it will query the database for the columns and constraints of the `messages` table. - -Note that if a reflected table has a foreign key referencing another table, then the metadata for the related table will be loaded as well, even if it has not been defined by the application: - - {python} - >>> shopping_cart_items = Table('shopping_cart_items', meta, autoload = True) - >>> print shopping_cart_items.c.cart_id.table.name - shopping_carts +Note that if a reflected table has a foreign key referencing another table, the related `Table` object will be automatically created within the `MetaData` object if it does not exist already. Below, suppose table `shopping_cart_items` references a table `shopping_carts`. After reflecting, the `shopping carts` table is present: -To get direct access to 'shopping_carts', simply instantiate it via the Table constructor. You'll get the same instance of the shopping cart Table as the one that is attached to shopping_cart_items: - {python} - >>> shopping_carts = Table('shopping_carts', meta) - >>> shopping_carts is shopping_cart_items.c.cart_id.table + >>> shopping_cart_items = Table('shopping_cart_items', meta, autoload=True) + >>> 'shopping_carts' in meta.tables: True -This works because when the Table constructor is called for a particular name and `MetaData` object, if the table has already been created then the instance returned will be the same as the original. This is a singleton constructor: +To get direct access to 'shopping_carts', simply instantiate it via the `Table` constructor. `Table` uses a special contructor that will return the already created `Table` instance if its already present: {python} - >>> news_articles = Table('news', meta, - ... Column('article_id', Integer, primary_key = True), - ... Column('url', String(250), nullable = False) - ... ) - >>> othertable = Table('news', meta) - >>> othertable is news_articles - True + shopping_carts = Table('shopping_carts', meta) + +Of course, its a good idea to use `autoload=True` with the above table regardless. This is so that if it hadn't been loaded already, the operation will load the table. The autoload operation only occurs for the table if it hasn't already been loaded; once loaded, new calls to `Table` will not re-issue any reflection queries. ##### Overriding Reflected Columns {@name=overriding} @@ -297,7 +265,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/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) + "
") + %>
diff --git a/lib/sqlalchemy/databases/mssql.py b/lib/sqlalchemy/databases/mssql.py index 78bf52dc50..e9e49bfbb0 100644 --- a/lib/sqlalchemy/databases/mssql.py +++ b/lib/sqlalchemy/databases/mssql.py @@ -32,9 +32,6 @@ Known issues / TODO: * No support for more than one ``IDENTITY`` column per table -* No support for table reflection of ``IDENTITY`` columns with - (seed,increment) values other than (1,1) - * No support for ``GUID`` type columns (yet) * pymssql has problems with binary and unicode data that this module @@ -289,6 +286,17 @@ class MSSQLExecutionContext_pyodbc (MSSQLExecutionContext): super(MSSQLExecutionContext_pyodbc, self).pre_exec() + # where appropriate, issue "select scope_identity()" in the same statement + if self.compiled.isinsert and self.HASIDENT and (not self.IINSERT) and self.dialect.use_scope_identity: + self.statement += "; select scope_identity()" + + def post_exec(self): + if self.compiled.isinsert and self.HASIDENT and (not self.IINSERT) and self.dialect.use_scope_identity: + # do nothing - id was fetched in dialect.do_execute() + self.HASIDENT = False + else: + super(MSSQLExecutionContext_pyodbc, self).post_exec() + class MSSQLDialect(ansisql.ANSIDialect): colspecs = { @@ -523,10 +531,8 @@ class MSSQLDialect(ansisql.ANSIDialect): raise exceptions.NoSuchTableError(table.name) # We also run an sp_columns to check for identity columns: - # FIXME: note that this only fetches the existence of an identity column, not it's properties like (seed, increment) - # also, add a check to make sure we specify the schema name of the table - # cursor = table.engine.execute("sp_columns " + table.name, {}) cursor = connection.execute("sp_columns " + table.name) + ic = None while True: row = cursor.fetchone() if row is None: @@ -536,6 +542,20 @@ class MSSQLDialect(ansisql.ANSIDialect): ic = table.c[col_name] # setup a psuedo-sequence to represent the identity attribute - we interpret this at table.create() time as the identity attribute ic.sequence = schema.Sequence(ic.name + '_identity') + # MSSQL: only one identity per table allowed + cursor.close() + break + if not ic is None: + try: + cursor = connection.execute("select ident_seed(?), ident_incr(?)", table.fullname, table.fullname) + row = cursor.fetchone() + cursor.close() + if not row is None: + ic.sequence.start=int(row[0]) + ic.sequence.increment=int(row[1]) + except: + # ignoring it, works just like before + pass # Add constraints RR = self.uppercase_table(ischema.ref_constraints) #information_schema.referential_constraints @@ -598,7 +618,7 @@ class MSSQLDialect_pymssql(MSSQLDialect): self.use_scope_identity = True def supports_sane_rowcount(self): - return True + return False def max_identifier_length(self): return 30 @@ -685,7 +705,10 @@ class MSSQLDialect_pyodbc(MSSQLDialect): def make_connect_string(self, keys): connectors = ["Driver={SQL Server}"] - connectors.append("Server=%s" % keys.get("host")) + if 'port' in keys: + connectors.append('Server=%s,%d' % (keys.get('host'), keys.get('port'))) + else: + connectors.append('Server=%s' % keys.get('host')) connectors.append("Database=%s" % keys.get("database")) user = keys.get("user") if user: @@ -696,11 +719,24 @@ class MSSQLDialect_pyodbc(MSSQLDialect): return [[";".join (connectors)], {}] def is_disconnect(self, e): - return isinstance(e, self.dbapi.Error) and '[08S01]' in e.args[1] + return isinstance(e, self.dbapi.Error) and '[08S01]' in str(e) def create_execution_context(self, *args, **kwargs): return MSSQLExecutionContext_pyodbc(self, *args, **kwargs) + def do_execute(self, cursor, statement, parameters, context=None, **kwargs): + super(MSSQLDialect_pyodbc, self).do_execute(cursor, statement, parameters, context=context, **kwargs) + if context and context.HASIDENT and (not context.IINSERT) and context.dialect.use_scope_identity: + import pyodbc + # fetch the last inserted id from the manipulated statement (pre_exec). + try: + row = cursor.fetchone() + except pyodbc.Error, e: + # if nocount OFF fetchone throws an exception and we have to jump over + # the rowcount to the resultset + cursor.nextset() + row = cursor.fetchone() + context._last_inserted_ids = [int(row[0])] class MSSQLDialect_adodbapi(MSSQLDialect): def import_dbapi(cls): diff --git a/lib/sqlalchemy/databases/postgres.py b/lib/sqlalchemy/databases/postgres.py index 4fd00198b5..717eacd942 100644 --- a/lib/sqlalchemy/databases/postgres.py +++ b/lib/sqlalchemy/databases/postgres.py @@ -460,9 +460,6 @@ class PGDialect(ansisql.ANSIDialect): table.append_constraint(schema.ForeignKeyConstraint(constrained_columns, refspec, conname)) def _load_domains(self, connection): - if hasattr(self, '_domains'): - return self._domains - ## Load data types for domains: SQL_DOMAINS = """ SELECT t.typname as "name", @@ -495,9 +492,7 @@ class PGDialect(ansisql.ANSIDialect): domains[name] = {'attype':attype, 'nullable': domain['nullable'], 'default': domain['default']} - self._domains = domains - - return self._domains + return domains diff --git a/lib/sqlalchemy/orm/session.py b/lib/sqlalchemy/orm/session.py index 53715cf1c6..46ce21a793 100644 --- a/lib/sqlalchemy/orm/session.py +++ b/lib/sqlalchemy/orm/session.py @@ -380,10 +380,11 @@ class Session(object): Cascading will be applied according to the *expunge* cascade rule. """ - + self._validate_persistent(object) for c in [object] + list(_object_mapper(object).cascade_iterator('expunge', object)): - self.uow._remove_deleted(c) - self._unattach(c) + if c in self: + self.uow._remove_deleted(c) + self._unattach(c) def save(self, object, entity_name=None): """Add a transient (unsaved) instance to this ``Session``. @@ -615,16 +616,9 @@ class Session(object): obj._sa_session_id = self.hash_key def _unattach(self, obj): - self._validate_attached(obj) - del obj._sa_session_id - - def _validate_attached(self, obj): - """Validate that the given object is either pending or - persistent within this Session. - """ - if not self._is_attached(obj): raise exceptions.InvalidRequestError("Instance '%s' not attached to this Session" % repr(obj)) + del obj._sa_session_id def _validate_persistent(self, obj): """Validate that the given object is persistent within this diff --git a/lib/sqlalchemy/schema.py b/lib/sqlalchemy/schema.py index bd94ea3940..9b9ae801a9 100644 --- a/lib/sqlalchemy/schema.py +++ b/lib/sqlalchemy/schema.py @@ -604,6 +604,7 @@ class Column(SchemaItem, sql._ColumnClause): c.table = selectable c.orig_set = self.orig_set c.__originating_column = self.__originating_column + c._distance = self._distance + 1 if not c._is_oid: selectable.columns.add(c) if self.primary_key: @@ -699,7 +700,7 @@ class ForeignKey(SchemaItem): raise exceptions.ArgumentError("Invalid foreign key column specification: " + self._colspec) if m.group(3) is None: (tname, colname) = m.group(1, 2) - schema = parenttable.schema + schema = None else: (schema,tname,colname) = m.group(1,2,3) table = Table(tname, parenttable.metadata, mustexist=True, schema=schema) diff --git a/lib/sqlalchemy/sql.py b/lib/sqlalchemy/sql.py index ebc504db62..60b2a3d326 100644 --- a/lib/sqlalchemy/sql.py +++ b/lib/sqlalchemy/sql.py @@ -1408,7 +1408,7 @@ class ColumnElement(Selectable, _CompareMixin): return True else: return False - + def _make_proxy(self, selectable, name=None): """Create a new ``ColumnElement`` representing this ``ColumnElement`` as it appears in the select list of a @@ -1568,6 +1568,7 @@ class FromClause(Selectable): if column in self.c: return column + if require_embedded and column not in util.Set(self._get_all_embedded_columns()): if not raiseerr: return None @@ -1638,9 +1639,12 @@ class FromClause(Selectable): for co in self._flatten_exportable_columns(): cp = self._proxy_column(co) for ci in cp.orig_set: - # note that some ambiguity is raised here, whereby a selectable might have more than - # one column that maps to an "original" column. examples include unions and joins - self._orig_cols[ci] = cp + cx = self._orig_cols.get(ci) + # TODO: the '=' thing here relates to the order of columns as they are placed in the + # "columns" collection of a CompositeSelect, illustrated in test/sql/selectable.SelectableTest.testunion + # make this relationship less brittle + if cx is None or cp._distance <= cx._distance: + self._orig_cols[ci] = cp if self.oid_column is not None: for ci in self.oid_column.orig_set: self._orig_cols[ci] = self.oid_column @@ -1948,7 +1952,8 @@ class _Cast(ColumnElement): self.type = sqltypes.to_instance(totype) self.clause = clause self.typeclause = _TypeClause(self.type) - + self._distance = 0 + def _copy_internals(self): self.clause = self.clause._clone() self.typeclause = self.typeclause._clone() @@ -1962,6 +1967,7 @@ class _Cast(ColumnElement): def _make_proxy(self, selectable, name=None): if name is not None: co = _ColumnClause(name, selectable, type=self.type) + co._distance = self._distance + 1 co.orig_set = self.orig_set selectable.columns[name]= co return co @@ -2401,6 +2407,7 @@ class _ColumnClause(ColumnElement): self.table = selectable self.type = sqltypes.to_instance(type) self._is_oid = _is_oid + self._distance = 0 self.__label = None self.case_sensitive = case_sensitive self.is_literal = is_literal @@ -2461,6 +2468,7 @@ class _ColumnClause(ColumnElement): is_literal = self.is_literal and (name is None or name == self.name) c = _ColumnClause(name or self.name, selectable=selectable, _is_oid=self._is_oid, type=self.type, is_literal=is_literal) c.orig_set = self.orig_set + c._distance = self._distance + 1 if not self._is_oid: selectable.columns[c.name] = c return c diff --git a/test/dialect/postgres.py b/test/dialect/postgres.py index 7606b35684..c9376d97d4 100644 --- a/test/dialect/postgres.py +++ b/test/dialect/postgres.py @@ -11,20 +11,21 @@ class DomainReflectionTest(AssertMixin): @testbase.supported('postgres') def setUpAll(self): - self.con = db.connect() - self.con.execute('CREATE DOMAIN testdomain INTEGER NOT NULL DEFAULT 42') - self.con.execute('CREATE DOMAIN alt_schema.testdomain INTEGER DEFAULT 0') - self.con.execute('CREATE TABLE testtable (question integer, answer testdomain)') - self.con.execute('CREATE TABLE alt_schema.testtable(question integer, answer alt_schema.testdomain, anything integer)') - self.con.execute('CREATE TABLE crosschema (question integer, answer alt_schema.testdomain)') + con = db.connect() + con.execute('CREATE DOMAIN testdomain INTEGER NOT NULL DEFAULT 42') + con.execute('CREATE DOMAIN alt_schema.testdomain INTEGER DEFAULT 0') + con.execute('CREATE TABLE testtable (question integer, answer testdomain)') + con.execute('CREATE TABLE alt_schema.testtable(question integer, answer alt_schema.testdomain, anything integer)') + con.execute('CREATE TABLE crosschema (question integer, answer alt_schema.testdomain)') @testbase.supported('postgres') def tearDownAll(self): - self.con.execute('DROP TABLE testtable') - self.con.execute('DROP TABLE alt_schema.testtable') - self.con.execute('DROP TABLE crosschema') - self.con.execute('DROP DOMAIN testdomain') - self.con.execute('DROP DOMAIN alt_schema.testdomain') + con = db.connect() + con.execute('DROP TABLE testtable') + con.execute('DROP TABLE alt_schema.testtable') + con.execute('DROP TABLE crosschema') + con.execute('DROP DOMAIN testdomain') + con.execute('DROP DOMAIN alt_schema.testdomain') @testbase.supported('postgres') def test_table_is_reflected(self): @@ -120,6 +121,50 @@ class MiscTest(AssertMixin): finally: meta1.drop_all() + @testbase.supported('postgres') + def test_schema_reflection_2(self): + meta1 = MetaData(testbase.db) + subject = Table("subject", meta1, + Column("id", Integer, primary_key=True), + ) + + referer = Table("referer", meta1, + Column("id", Integer, primary_key=True), + Column("ref", Integer, ForeignKey('subject.id')), + schema="alt_schema") + meta1.create_all() + try: + meta2 = MetaData(testbase.db) + subject = Table("subject", meta2, autoload=True) + referer = Table("referer", meta2, schema="alt_schema", autoload=True) + print str(subject.join(referer).onclause) + self.assert_((subject.c.id==referer.c.ref).compare(subject.join(referer).onclause)) + finally: + meta1.drop_all() + + @testbase.supported('postgres') + def test_schema_reflection_3(self): + meta1 = MetaData(testbase.db) + subject = Table("subject", meta1, + Column("id", Integer, primary_key=True), + schema='alt_schema_2' + ) + + referer = Table("referer", meta1, + Column("id", Integer, primary_key=True), + Column("ref", Integer, ForeignKey('alt_schema_2.subject.id')), + schema="alt_schema") + + meta1.create_all() + try: + meta2 = MetaData(testbase.db) + subject = Table("subject", meta2, autoload=True, schema="alt_schema_2") + referer = Table("referer", meta2, schema="alt_schema", autoload=True) + print str(subject.join(referer).onclause) + self.assert_((subject.c.id==referer.c.ref).compare(subject.join(referer).onclause)) + finally: + meta1.drop_all() + @testbase.supported('postgres') def test_preexecute_passivedefault(self): """test that when we get a primary key column back diff --git a/test/engine/reflection.py b/test/engine/reflection.py index a1c1e325d6..672d1bcd7c 100644 --- a/test/engine/reflection.py +++ b/test/engine/reflection.py @@ -301,6 +301,7 @@ class ReflectionTest(PersistTest): testbase.db.dialect.get_version_info(testbase.db) < (4, 1, 1)): return meta = MetaData(testbase.db) + table = Table( 'multi', meta, Column('multi_id', Integer, primary_key=True), @@ -444,6 +445,23 @@ class ReflectionTest(PersistTest): finally: table.drop() + @testbase.supported('mssql') + def testidentity(self): + meta = MetaData(testbase.db) + table = Table( + 'identity_test', meta, + Column('col1', Integer, Sequence('fred', 2, 3), primary_key=True) + ) + table.create() + + meta2 = MetaData(testbase.db) + try: + table2 = Table('identity_test', meta2, autoload=True) + print table2.c['col1'].sequence + finally: + table.drop() + + class CreateDropTest(PersistTest): def setUpAll(self): global metadata, users diff --git a/test/orm/assorted_eager.py b/test/orm/assorted_eager.py index e955c480de..f647a5cccd 100644 --- a/test/orm/assorted_eager.py +++ b/test/orm/assorted_eager.py @@ -674,6 +674,101 @@ class EagerTest7(ORMTest): i = ctx.current.query(Invoice).get(invoice_id) assert repr(i.company) == repr(c), repr(i.company) + " does not match " + repr(c) + +class EagerTest8(testbase.ORMTest): + def define_tables(self, metadata): + global project_t, task_t, task_status_t, task_type_t, message_t, message_type_t + + project_t = Table('prj', metadata, + Column('id', Integer, primary_key=True), + Column('created', DateTime , ), + Column('title', Unicode(100)), + ) + + task_t = Table('task', metadata, + Column('id', Integer, primary_key=True), + Column('status_id', Integer, ForeignKey('task_status.id'), nullable=False), + Column('title', Unicode(100)), + Column('task_type_id', Integer , ForeignKey('task_type.id'), nullable=False), + Column('prj_id', Integer , ForeignKey('prj.id'), nullable=False), + ) + + task_status_t = Table('task_status', metadata, + Column('id', Integer, primary_key=True), + ) + + task_type_t = Table('task_type', metadata, + Column('id', Integer, primary_key=True), + ) + + message_t = Table('msg', metadata, + Column('id', Integer, primary_key=True), + Column('posted', DateTime, index=True,), + Column('type_id', Integer, ForeignKey('msg_type.id')), + Column('task_id', Integer, ForeignKey('task.id')), + ) + + message_type_t = Table('msg_type', metadata, + Column('id', Integer, primary_key=True), + Column('name', Unicode(20)), + Column('display_name', Unicode(20)), + ) + + def setUp(self): + testbase.db.execute("INSERT INTO prj (title) values('project 1');") + testbase.db.execute("INSERT INTO task_status (id) values(1);") + testbase.db.execute("INSERT INTO task_type(id) values(1);") + testbase.db.execute("INSERT INTO task (title, task_type_id, status_id, prj_id) values('task 1',1,1,1);") + + def test_nested_joins(self): + # this is testing some subtle column resolution stuff, + # concerning corresponding_column() being extremely accurate + # as well as how mapper sets up its column properties + + class Task(object):pass + class Task_Type(object):pass + class Message(object):pass + class Message_Type(object):pass + + tsk_cnt_join = outerjoin(project_t, task_t, task_t.c.prj_id==project_t.c.id) + + ss = select([project_t.c.id.label('prj_id'), func.count(task_t.c.id).label('tasks_number')], + from_obj=[tsk_cnt_join], group_by=[project_t.c.id]).alias('prj_tsk_cnt_s') + j = join(project_t, ss, project_t.c.id == ss.c.prj_id) + + mapper(Task_Type, task_type_t) + + mapper( Task, task_t, + properties=dict(type=relation(Task_Type, lazy=False), + )) + + mapper(Message_Type, message_type_t) + + mapper(Message, message_t, + properties=dict(type=relation(Message_Type, lazy=False, uselist=False), + )) + + tsk_cnt_join = outerjoin(project_t, task_t, task_t.c.prj_id==project_t.c.id) + ss = select([project_t.c.id.label('prj_id'), func.count(task_t.c.id).label('tasks_number')], + from_obj=[tsk_cnt_join], group_by=[project_t.c.id]).alias('prj_tsk_cnt_s') + j = join(project_t, ss, project_t.c.id == ss.c.prj_id) + + j = outerjoin( task_t, message_t, task_t.c.id==message_t.c.task_id) + jj = select([ task_t.c.id.label('task_id'), + func.count(message_t.c.id).label('props_cnt')], + from_obj=[j], group_by=[task_t.c.id]).alias('prop_c_s') + jjj = join(task_t, jj, task_t.c.id == jj.c.task_id) + + class cls(object):pass + + props =dict(type=relation(Task_Type, lazy=False)) + print [c.key for c in jjj.c] + cls.mapper = mapper( cls, jjj, properties=props) + + session = create_session() + + for t in session.query(cls.mapper).limit(10).offset(0).list(): + print t.id, t.title, t.props_cnt if __name__ == "__main__": diff --git a/test/orm/mapper.py b/test/orm/mapper.py index b9198e91f7..26a0b80a44 100644 --- a/test/orm/mapper.py +++ b/test/orm/mapper.py @@ -321,7 +321,7 @@ class MapperTest(MapperSuperTest): class_mapper(User) except exceptions.ArgumentError, e: assert str(e) == "Column '%s' is not represented in mapper's table. Use the `column_property()` function to force this column to be mapped as a read-only attribute." % str(f) - clear_mappers() + clear_mappers() mapper(User, users, properties={ 'concat': column_property(f), @@ -616,14 +616,27 @@ class MapperTest(MapperSuperTest): # eagerload orders, orders.items, orders.items.keywords q2 = sess.query(User).options(eagerload('orders'), eagerload('orders.items'), eagerload('orders.items.keywords')) u = q2.select() + def go(): + print u[0].orders[1].items[0].keywords[1] print "-------MARK2----------" self.assert_sql_count(db, go, 0) + sess.clear() + + # same thing, with separate options calls + q2 = sess.query(User).options(eagerload('orders')).options(eagerload('orders.items')).options(eagerload('orders.items.keywords')) + u = q2.select() + def go(): + print u[0].orders[1].items[0].keywords[1] + print "-------MARK3----------" + self.assert_sql_count(db, go, 0) + print "-------MARK4----------" + sess.clear() # eagerload "keywords" on items. it will lazy load "orders", then lazy load # the "items" on the order, but on "items" it will eager load the "keywords" - print "-------MARK3----------" + print "-------MARK5----------" q3 = sess.query(User).options(eagerload('orders.items.keywords')) u = q3.select() self.assert_sql_count(db, go, 2) @@ -982,7 +995,6 @@ class MapperExtensionTest(MapperSuperTest): l = q.select(); self.assert_result(l, User, *user_address_result) - if __name__ == "__main__": testbase.main() diff --git a/test/orm/session.py b/test/orm/session.py index cf0b6d5d8a..1d337deef6 100644 --- a/test/orm/session.py +++ b/test/orm/session.py @@ -39,6 +39,21 @@ class SessionTest(AssertMixin): s.user_name = 'some other user' s.flush() + def test_expunge_cascade(self): + tables.data() + mapper(Address, addresses) + mapper(User, users, properties={ + 'addresses':relation(Address, backref=backref("user", cascade="all"), cascade="all") + }) + session = create_session() + u = session.query(User).filter_by(user_id=7).one() + + # get everything to load in both directions + print [a.user for a in u.addresses] + + # then see if expunge fails + session.expunge(u) + def test_transaction(self): class User(object):pass mapper(User, users) diff --git a/test/sql/selectable.py b/test/sql/selectable.py index bc85e74557..50b2fa6b4a 100755 --- a/test/sql/selectable.py +++ b/test/sql/selectable.py @@ -1,15 +1,16 @@ """tests that various From objects properly export their columns, as well as -useable primary keys and foreign keys. Full relational algebra depends on -every selectable unit behaving nicely with others..""" - +-useable primary keys and foreign keys. Full relational algebra depends on +-every selectable unit behaving nicely with others..""" + import testbase import unittest, sys, datetime from sqlalchemy import * from testbase import Table, Column - db = testbase.db metadata = MetaData(db) + + table = Table('table1', metadata, Column('col1', Integer, primary_key=True), Column('col2', String(20)), @@ -26,17 +27,42 @@ table2 = Table('table2', metadata, ) class SelectableTest(testbase.AssertMixin): + def testdistance(self): + s = select([table.c.col1.label('c2'), table.c.col1, table.c.col1.label('c1')]) + + # didnt do this yet...col.label().make_proxy() has same "distance" as col.make_proxy() so far + #assert s.corresponding_column(table.c.col1) is s.c.col1 + assert s.corresponding_column(s.c.col1) is s.c.col1 + assert s.corresponding_column(s.c.c1) is s.c.c1 + def testjoinagainstself(self): jj = select([table.c.col1.label('bar_col1')]) jjj = join(table, jj, table.c.col1==jj.c.bar_col1) + + # test column directly agaisnt itself assert jjj.corresponding_column(jjj.c.table1_col1) is jjj.c.table1_col1 + assert jjj.corresponding_column(jj.c.bar_col1) is jjj.c.bar_col1 + + # test alias of the join, targets the column with the least + # "distance" between the requested column and the returned column + # (i.e. there is less indirection between j2.c.table1_col1 and table.c.col1, than + # there is from j2.c.bar_col1 to table.c.col1) + j2 = jjj.alias('foo') + assert j2.corresponding_column(table.c.col1) is j2.c.table1_col1 + + def testjoinagainstjoin(self): j = outerjoin(table, table2, table.c.col1==table2.c.col2) jj = select([ table.c.col1.label('bar_col1')],from_obj=[j]).alias('foo') jjj = join(table, jj, table.c.col1==jj.c.bar_col1) assert jjj.corresponding_column(jjj.c.table1_col1) is jjj.c.table1_col1 + j2 = jjj.alias('foo') + print j2.corresponding_column(jjj.c.table1_col1) + assert j2.corresponding_column(jjj.c.table1_col1) is j2.c.table1_col1 + + assert jjj.corresponding_column(jj.c.bar_col1) is jj.c.bar_col1 def testtablealias(self): a = table.alias('a') @@ -59,6 +85,8 @@ class SelectableTest(testbase.AssertMixin): print ["%d %s" % (id(c),c.key) for c in u.c] c = u.corresponding_column(s1.c.table1_col2) print "%d %s" % (id(c), c.key) + print id(u.corresponding_column(s1.c.table1_col2).table) + print id(u.c.col2.table) assert u.corresponding_column(s1.c.table1_col2) is u.c.col2 assert u.corresponding_column(s2.c.table2_col2) is u.c.col2 @@ -109,8 +137,8 @@ class SelectableTest(testbase.AssertMixin): j = join(a, table2) criterion = a.c.col1 == table2.c.col2 - print - print str(j) + print criterion + print j.onclause self.assert_(criterion.compare(j.onclause)) def testselectlabels(self): diff --git a/test/testbase.py b/test/testbase.py index 10b6671d21..41eb38ddfc 100644 --- a/test/testbase.py +++ b/test/testbase.py @@ -396,7 +396,9 @@ class ExecutionContextWrapper(object): parameters = [p.get_original_dict() for p in ctx.compiled_parameters] query = self.convert_statement(query) - assert statement == query and (params is None or params == parameters), "Testing for query '%s' params %s, received '%s' with params %s" % (query, repr(params), statement, repr(parameters)) + if db.engine.name == 'mssql' and statement.endswith('; select scope_identity()'): + statement = statement[:-25] + testdata.unittest.assert_(statement == query and (params is None or params == parameters), "Testing for query '%s' params %s, received '%s' with params %s" % (query, repr(params), statement, repr(parameters))) testdata.sql_count += 1 self.ctx.post_exec() -- 2.47.3