diff options
Diffstat (limited to 'lib/sqlalchemy/dialects/postgresql/base.py')
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/base.py | 123 |
1 files changed, 94 insertions, 29 deletions
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 5292b3901..9b6c632da 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -225,6 +225,46 @@ SERIALIZABLE isolation. .. versionadded:: 1.4 added support for the ``postgresql_readonly`` and ``postgresql_deferrable`` execution options. +.. _postgresql_alternate_search_path: + +Setting Alternate Search Paths on Connect +------------------------------------------ + +The PostgreSQL ``search_path`` variable refers to the list of schema names +that will be implicitly referred towards when a particular table or other +object is referenced in a SQL statement. As detailed in the next section +:ref:`postgresql_schema_reflection`, SQLAlchemy is generally organized around +the concept of keeping this variable at its default value of ``public``, +however, in order to have it set to any arbirary name or names when connections +are used automatically, the "SET SESSION search_path" command may be invoked +for all connections in a pool using the following event handler, as discussed +at :ref:`schema_set_default_connections`:: + + from sqlalchemy import event + from sqlalchemy import create_engine + + engine = create_engine("postgresql+psycopg2://scott:tiger@host/dbname") + + @event.listens_for(engine, "connect", insert=True) + def set_search_path(dbapi_connection, connection_record): + existing_autocommit = dbapi_connection.autocommit + dbapi_connection.autocommit = True + cursor = dbapi_connection.cursor() + cursor.execute("SET SESSION search_path='%s'" % schema_name) + cursor.close() + dbapi_connection.autocommit = existing_autocommit + +The reason the recipe is complicated by use of the ``.autocommit`` DBAPI +attribute is so that when the ``SET SESSION search_path`` directive is invoked, +it is invoked outside of the scope of any tranasction and therefore will not +be reverted when the DBAPI connection has a rollback. + +.. seealso:: + + :ref:`schema_set_default_connections` - in the :ref:`metadata_toplevel` documentation + + + .. _postgresql_schema_reflection: @@ -309,7 +349,7 @@ reflection process as follows:: ... conn.execute(text("SET search_path TO test_schema, public")) ... meta = MetaData() ... referring = Table('referring', meta, - ... autoload=True, autoload_with=conn) + ... autoload_with=conn) ... <sqlalchemy.engine.result.CursorResult object at 0x101612ed0> @@ -329,7 +369,7 @@ dialect-specific argument to both :class:`_schema.Table` as well as >>> with engine.connect() as conn: ... conn.execute(text("SET search_path TO test_schema, public")) ... meta = MetaData() - ... referring = Table('referring', meta, autoload=True, + ... referring = Table('referring', meta, ... autoload_with=conn, ... postgresql_ignore_search_path=True) ... @@ -781,6 +821,8 @@ using the ``postgresql_where`` keyword argument:: Index('my_index', my_table.c.id, postgresql_where=my_table.c.value > 10) +.. _postgresql_operator_classes: + Operator Classes ^^^^^^^^^^^^^^^^ @@ -797,11 +839,10 @@ The :class:`.Index` construct allows these to be specified via the 'id': 'int4_ops' }) -Note that the keys in the ``postgresql_ops`` dictionary are the "key" name of -the :class:`_schema.Column`, i.e. the name used to access it from the ``.c`` -collection of :class:`_schema.Table`, -which can be configured to be different than -the actual name of the column as expressed in the database. +Note that the keys in the ``postgresql_ops`` dictionaries are the +"key" name of the :class:`_schema.Column`, i.e. the name used to access it from +the ``.c`` collection of :class:`_schema.Table`, which can be configured to be +different than the actual name of the column as expressed in the database. If ``postgresql_ops`` is to be used against a complex SQL expression such as a function call, then to apply to the column it must be given a label @@ -815,6 +856,14 @@ that is identified in the dictionary by name, e.g.:: 'id': 'int4_ops' }) +Operator classes are also supported by the +:class:`_postgresql.ExcludeConstraint` construct using the +:paramref:`_postgresql.ExcludeConstraint.ops` parameter. See that parameter for +details. + +.. versionadded:: 1.3.21 added support for operator classes with + :class:`_postgresql.ExcludeConstraint`. + Index Types ^^^^^^^^^^^ @@ -914,7 +963,7 @@ will report on these two constructs distinctly; in the case of the index, the key ``duplicates_constraint`` will be present in the index entry if it is detected as mirroring a constraint. When performing reflection using -``Table(..., autoload=True)``, the UNIQUE INDEX is **not** returned +``Table(..., autoload_with=engine)``, the UNIQUE INDEX is **not** returned in :attr:`_schema.Table.indexes` when it is detected as mirroring a :class:`.UniqueConstraint` in the :attr:`_schema.Table.constraints` collection . @@ -1425,6 +1474,9 @@ class INTERVAL(sqltypes.NativeForEmulated, sqltypes._AbstractInterval): def _type_affinity(self): return sqltypes.Interval + def as_generic(self, allow_nulltype=False): + return sqltypes.Interval(native=True, second_precision=self.precision) + @property def python_type(self): return dt.timedelta @@ -2138,22 +2190,28 @@ class PGCompiler(compiler.SQLCompiler): cols = insert_statement.table.c for c in cols: col_key = c.key + if col_key in set_parameters: value = set_parameters.pop(col_key) - if coercions._is_literal(value): - value = elements.BindParameter(None, value, type_=c.type) + elif c in set_parameters: + value = set_parameters.pop(c) + else: + continue - else: - if ( - isinstance(value, elements.BindParameter) - and value.type._isnull - ): - value = value._clone() - value.type = c.type - value_text = self.process(value.self_group(), use_schema=False) - - key_text = self.preparer.quote(col_key) - action_set_ops.append("%s = %s" % (key_text, value_text)) + if coercions._is_literal(value): + value = elements.BindParameter(None, value, type_=c.type) + + else: + if ( + isinstance(value, elements.BindParameter) + and value.type._isnull + ): + value = value._clone() + value.type = c.type + value_text = self.process(value.self_group(), use_schema=False) + + key_text = self.preparer.quote(col_key) + action_set_ops.append("%s = %s" % (key_text, value_text)) # check for names that don't match columns if set_parameters: @@ -2423,9 +2481,13 @@ class PGDDLCompiler(compiler.DDLCompiler): elements = [] for expr, name, op in constraint._render_exprs: kw["include_table"] = False - elements.append( - "%s WITH %s" % (self.sql_compiler.process(expr, **kw), op) + exclude_element = self.sql_compiler.process(expr, **kw) + ( + (" " + constraint.ops[expr.key]) + if hasattr(expr, "key") and expr.key in constraint.ops + else "" ) + + elements.append("%s WITH %s" % (exclude_element, op)) text += "EXCLUDE USING %s (%s)" % ( self.preparer.validate_sql_phrase( constraint.using, IDX_USING @@ -2928,12 +2990,15 @@ class PGDialect(default.DefaultDialect): # http://www.postgresql.org/docs/9.3/static/release-9-2.html#AEN116689 self.supports_smallserial = self.server_version_info >= (9, 2) - std_string = connection.exec_driver_sql( - "show standard_conforming_strings" - ).scalar() - self._backslash_escapes = ( - self.server_version_info < (8, 2) or std_string == "off" - ) + if self.server_version_info < (8, 2): + self._backslash_escapes = False + else: + # ensure this query is not emitted on server version < 8.2 + # as it will fail + std_string = connection.exec_driver_sql( + "show standard_conforming_strings" + ).scalar() + self._backslash_escapes = std_string == "off" self._supports_create_index_concurrently = ( self.server_version_info >= (8, 2) |