41
41
To force the usage of RETURNING by default off, specify the flag
42
42
``implicit_returning=False`` to :func:`.create_engine`.
44
.. _postgresql_isolation_level:
44
46
Transaction Isolation Level
45
47
---------------------------
47
:func:`.create_engine` accepts an ``isolation_level`` parameter which results
48
in the command ``SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL
49
<level>`` being invoked for every new connection. Valid values for this
50
parameter are ``READ COMMITTED``, ``READ UNCOMMITTED``, ``REPEATABLE READ``,
51
and ``SERIALIZABLE``::
49
All Postgresql dialects support setting of transaction isolation level
50
both via a dialect-specific parameter ``isolation_level``
51
accepted by :func:`.create_engine`,
52
as well as the ``isolation_level`` argument as passed to :meth:`.Connection.execution_options`.
53
When using a non-psycopg2 dialect, this feature works by issuing the
54
command ``SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL
55
<level>`` for each new connection.
57
To set isolation level using :func:`.create_engine`::
53
59
engine = create_engine(
54
60
"postgresql+pg8000://scott:tiger@localhost/test",
55
61
isolation_level="READ UNCOMMITTED"
58
When using the psycopg2 dialect, a psycopg2-specific method of setting
59
transaction isolation level is used, but the API of ``isolation_level``
60
remains the same - see :ref:`psycopg2_isolation`.
64
To set using per-connection execution options::
66
connection = engine.connect()
67
connection = connection.execution_options(isolation_level="READ COMMITTED")
69
Valid values for ``isolation_level`` include:
72
* ``READ UNCOMMITTED``
76
The :mod:`~sqlalchemy.dialects.postgresql.psycopg2` dialect also offers the special level ``AUTOCOMMIT``. See
77
:ref:`psycopg2_isolation_level` for details.
63
80
Remote / Cross-Schema Table Introspection
324
class ARRAY(sqltypes.MutableType, sqltypes.Concatenable, sqltypes.TypeEngine):
373
class _Slice(expression.ColumnElement):
374
__visit_name__ = 'slice'
375
type = sqltypes.NULLTYPE
377
def __init__(self, slice_, source_comparator):
378
self.start = source_comparator._check_literal(
379
source_comparator.expr,
380
operators.getitem, slice_.start)
381
self.stop = source_comparator._check_literal(
382
source_comparator.expr,
383
operators.getitem, slice_.stop)
386
class Any(expression.ColumnElement):
387
"""Represent the clause ``left operator ANY (right)``. ``right`` must be
392
:class:`.postgresql.ARRAY`
394
:meth:`.postgresql.ARRAY.Comparator.any` - ARRAY-bound method
397
__visit_name__ = 'any'
399
def __init__(self, left, right, operator=operators.eq):
400
self.type = sqltypes.Boolean()
401
self.left = expression._literal_as_binds(left)
403
self.operator = operator
406
class All(expression.ColumnElement):
407
"""Represent the clause ``left operator ALL (right)``. ``right`` must be
412
:class:`.postgresql.ARRAY`
414
:meth:`.postgresql.ARRAY.Comparator.all` - ARRAY-bound method
417
__visit_name__ = 'all'
419
def __init__(self, left, right, operator=operators.eq):
420
self.type = sqltypes.Boolean()
421
self.left = expression._literal_as_binds(left)
423
self.operator = operator
426
class array(expression.Tuple):
427
"""A Postgresql ARRAY literal.
429
This is used to produce ARRAY literals in SQL expressions, e.g.::
431
from sqlalchemy.dialects.postgresql import array
432
from sqlalchemy.dialects import postgresql
433
from sqlalchemy import select, func
436
array([1,2]) + array([3,4,5])
439
print stmt.compile(dialect=postgresql.dialect())
443
SELECT ARRAY[%(param_1)s, %(param_2)s] ||
444
ARRAY[%(param_3)s, %(param_4)s, %(param_5)s]) AS anon_1
446
An instance of :class:`.array` will always have the datatype
447
:class:`.ARRAY`. The "inner" type of the array is inferred from
448
the values present, unless the ``type_`` keyword argument is passed::
450
array(['foo', 'bar'], type_=CHAR)
452
.. versionadded:: 0.8 Added the :class:`~.postgresql.array` literal type.
456
:class:`.postgresql.ARRAY`
459
__visit_name__ = 'array'
461
def __init__(self, clauses, **kw):
462
super(array, self).__init__(*clauses, **kw)
463
self.type = ARRAY(self.type)
465
def _bind_param(self, operator, obj):
467
expression.BindParameter(None, o, _compared_to_operator=operator,
468
_compared_to_type=self.type, unique=True)
472
def self_group(self, against=None):
476
class ARRAY(sqltypes.Concatenable, sqltypes.TypeEngine):
325
477
"""Postgresql ARRAY type.
327
479
Represents values as Python lists.
329
The ARRAY type may not be supported on all DBAPIs.
481
An :class:`.ARRAY` type is constructed given the "type"
484
mytable = Table("mytable", metadata,
485
Column("data", ARRAY(Integer))
488
The above type represents an N-dimensional array,
489
meaning Postgresql will interpret values with any number
490
of dimensions automatically. To produce an INSERT
491
construct that passes in a 1-dimensional array of integers::
498
The :class:`.ARRAY` type can be constructed given a fixed number
501
mytable = Table("mytable", metadata,
502
Column("data", ARRAY(Integer, dimensions=2))
505
This has the effect of the :class:`.ARRAY` type
506
specifying that number of bracketed blocks when a :class:`.Table`
507
is used in a CREATE TABLE statement, or when the type is used
508
within a :func:`.expression.cast` construct; it also causes
509
the bind parameter and result set processing of the type
510
to optimize itself to expect exactly that number of dimensions.
511
Note that Postgresql itself still allows N dimensions with such a type.
513
SQL expressions of type :class:`.ARRAY` have support for "index" and
514
"slice" behavior. The Python ``[]`` operator works normally here, given
515
integer indexes or slices. Note that Postgresql arrays default
516
to 1-based indexing. The operator produces binary expression
517
constructs which will produce the appropriate SQL, both for
520
select([mytable.c.data[5], mytable.c.data[2:7]])
522
as well as UPDATE statements when the :meth:`.Update.values` method
525
mytable.update().values({
526
mytable.c.data[5]: 7,
527
mytable.c.data[2:7]: [1, 2, 3]
530
:class:`.ARRAY` provides special methods for containment operations,
533
mytable.c.data.contains([1, 2])
535
For a full list of special methods see :class:`.ARRAY.Comparator`.
537
.. versionadded:: 0.8 Added support for index and slice operations
538
to the :class:`.ARRAY` type, including support for UPDATE
539
statements, and special array containment operations.
541
The :class:`.ARRAY` type may not be supported on all DBAPIs.
330
542
It is known to work on psycopg2 and not pg8000.
546
:class:`.postgresql.array` - produce a literal array value.
334
549
__visit_name__ = 'ARRAY'
336
def __init__(self, item_type, mutable=False, as_tuple=False):
551
class Comparator(sqltypes.Concatenable.Comparator):
552
"""Define comparison operations for :class:`.ARRAY`."""
554
def __getitem__(self, index):
555
if isinstance(index, slice):
556
index = _Slice(index, self)
557
return_type = self.type
559
return_type = self.type.item_type
560
return self._binary_operate(self.expr, operators.getitem, index,
561
result_type=return_type)
563
def any(self, other, operator=operators.eq):
564
"""Return ``other operator ANY (array)`` clause.
566
Argument places are switched, because ANY requires array
567
expression to be on the right hand-side.
571
from sqlalchemy.sql import operators
574
select([table.c.data]).where(
575
table.c.data.any(7, operator=operators.lt)
579
:param other: expression to be compared
580
:param operator: an operator object from the
581
:mod:`sqlalchemy.sql.operators`
582
package, defaults to :func:`.operators.eq`.
586
:class:`.postgresql.Any`
588
:meth:`.postgresql.ARRAY.Comparator.all`
591
return Any(other, self.expr, operator=operator)
593
def all(self, other, operator=operators.eq):
594
"""Return ``other operator ALL (array)`` clause.
596
Argument places are switched, because ALL requires array
597
expression to be on the right hand-side.
601
from sqlalchemy.sql import operators
604
select([table.c.data]).where(
605
table.c.data.all(7, operator=operators.lt)
609
:param other: expression to be compared
610
:param operator: an operator object from the
611
:mod:`sqlalchemy.sql.operators`
612
package, defaults to :func:`.operators.eq`.
616
:class:`.postgresql.All`
618
:meth:`.postgresql.ARRAY.Comparator.any`
621
return All(other, self.expr, operator=operator)
623
def contains(self, other, **kwargs):
624
"""Boolean expression. Test if elements are a superset of the
625
elements of the argument array expression.
627
return self.expr.op('@>')(other)
629
def contained_by(self, other):
630
"""Boolean expression. Test if elements are a proper subset of the
631
elements of the argument array expression.
633
return self.expr.op('<@')(other)
635
def overlap(self, other):
636
"""Boolean expression. Test if array has elements in common with
637
an argument array expression.
639
return self.expr.op('&&')(other)
641
def _adapt_expression(self, op, other_comparator):
642
if isinstance(op, operators.custom_op):
643
if op.opstring in ['@>', '<@', '&&']:
644
return op, sqltypes.Boolean
645
return sqltypes.Concatenable.Comparator.\
646
_adapt_expression(self, op, other_comparator)
648
comparator_factory = Comparator
650
def __init__(self, item_type, as_tuple=False, dimensions=None):
337
651
"""Construct an ARRAY.
378
681
if isinstance(item_type, type):
379
682
item_type = item_type()
380
683
self.item_type = item_type
381
self.mutable = mutable
382
if mutable and as_tuple:
383
raise exc.ArgumentError(
384
"mutable must be set to False if as_tuple is True."
386
684
self.as_tuple = as_tuple
388
def copy_value(self, value):
685
self.dimensions = dimensions
396
687
def compare_values(self, x, y):
399
def is_mutable(self):
690
def _proc_array(self, arr, itemproc, dim, collection):
693
if dim == 1 or dim is None and (
694
# this has to be (list, tuple), or at least
695
# not hasattr('__iter__'), since Py3K strings
697
not arr or not isinstance(arr[0], (list, tuple))):
699
return collection(itemproc(x) for x in arr)
701
return collection(arr)
706
dim - 1 if dim is not None else None,
402
711
def bind_processor(self, dialect):
403
item_proc = self.item_type.dialect_impl(dialect).bind_processor(dialect)
405
def convert_item(item):
406
if isinstance(item, (list, tuple)):
407
return [convert_item(child) for child in item]
409
return item_proc(item)
411
def convert_item(item):
412
if isinstance(item, (list, tuple)):
413
return [convert_item(child) for child in item]
712
item_proc = self.item_type.\
713
dialect_impl(dialect).\
714
bind_processor(dialect)
416
716
def process(value):
417
717
if value is None:
419
return [convert_item(item) for item in value]
720
return self._proc_array(
422
727
def result_processor(self, dialect, coltype):
423
item_proc = self.item_type.dialect_impl(dialect).result_processor(dialect, coltype)
425
def convert_item(item):
426
if isinstance(item, list):
427
r = [convert_item(child) for child in item]
432
return item_proc(item)
434
def convert_item(item):
435
if isinstance(item, list):
436
r = [convert_item(child) for child in item]
728
item_proc = self.item_type.\
729
dialect_impl(dialect).\
730
result_processor(dialect, coltype)
442
732
def process(value):
443
733
if value is None:
445
r = [convert_item(item) for item in value]
736
return self._proc_array(
740
tuple if self.as_tuple else list)
452
746
class ENUM(sqltypes.Enum):
453
747
"""Postgresql ENUM type.
585
879
self.drop(bind=bind, checkfirst=checkfirst)
588
sqltypes.Interval:INTERVAL,
882
sqltypes.Interval: INTERVAL,
592
886
ischema_names = {
595
'smallint' : SMALLINT,
596
'character varying' : VARCHAR,
598
'"char"' : sqltypes.String,
599
'name' : sqltypes.String,
889
'smallint': SMALLINT,
890
'character varying': VARCHAR,
892
'"char"': sqltypes.String,
893
'name': sqltypes.String,
608
902
'bit varying': BIT,
609
903
'macaddr': MACADDR,
610
'double precision' : DOUBLE_PRECISION,
611
'timestamp' : TIMESTAMP,
612
'timestamp with time zone' : TIMESTAMP,
613
'timestamp without time zone' : TIMESTAMP,
614
'time with time zone' : TIME,
615
'time without time zone' : TIME,
904
'double precision': DOUBLE_PRECISION,
905
'timestamp': TIMESTAMP,
906
'timestamp with time zone': TIMESTAMP,
907
'timestamp without time zone': TIMESTAMP,
908
'time with time zone': TIME,
909
'time without time zone': TIME,
621
'interval year to month':INTERVAL,
622
'interval day to second':INTERVAL,
914
'interval': INTERVAL,
915
'interval year to month': INTERVAL,
916
'interval day to second': INTERVAL,
627
920
class PGCompiler(compiler.SQLCompiler):
629
def visit_match_op(self, binary, **kw):
922
def visit_array(self, element, **kw):
923
return "ARRAY[%s]" % self.visit_clauselist(element, **kw)
925
def visit_slice(self, element, **kw):
927
self.process(element.start, **kw),
928
self.process(element.stop, **kw),
931
def visit_any(self, element, **kw):
932
return "%s%sANY (%s)" % (
933
self.process(element.left, **kw),
934
compiler.OPERATORS[element.operator],
935
self.process(element.right, **kw)
938
def visit_all(self, element, **kw):
939
return "%s%sALL (%s)" % (
940
self.process(element.left, **kw),
941
compiler.OPERATORS[element.operator],
942
self.process(element.right, **kw)
945
def visit_getitem_binary(self, binary, operator, **kw):
947
self.process(binary.left, **kw),
948
self.process(binary.right, **kw)
951
def visit_match_op_binary(self, binary, operator, **kw):
630
952
return "%s @@ to_tsquery(%s)" % (
631
self.process(binary.left),
632
self.process(binary.right))
953
self.process(binary.left, **kw),
954
self.process(binary.right, **kw))
634
def visit_ilike_op(self, binary, **kw):
956
def visit_ilike_op_binary(self, binary, operator, **kw):
635
957
escape = binary.modifiers.get("escape", None)
636
958
return '%s ILIKE %s' % \
637
(self.process(binary.left), self.process(binary.right)) \
959
(self.process(binary.left, **kw),
960
self.process(binary.right, **kw)) \
639
962
(' ESCAPE ' + self.render_literal_value(escape, None))
642
def visit_notilike_op(self, binary, **kw):
965
def visit_notilike_op_binary(self, binary, operator, **kw):
643
966
escape = binary.modifiers.get("escape", None)
644
967
return '%s NOT ILIKE %s' % \
645
(self.process(binary.left), self.process(binary.right)) \
968
(self.process(binary.left, **kw),
969
self.process(binary.right, **kw)) \
647
971
(' ESCAPE ' + self.render_literal_value(escape, None))
693
1022
def returning_clause(self, stmt, returning_cols):
697
self.label_select_column(None, c, asfrom=False),
698
within_columns_clause=True,
699
result_map=self.result_map)
1025
self._label_select_column(None, c, True, False, {})
700
1026
for c in expression._select_iterables(returning_cols)
703
1029
return 'RETURNING ' + ', '.join(columns)
705
def visit_extract(self, extract, **kwargs):
706
field = self.extract_map.get(extract.field, extract.field)
707
if extract.expr.type:
708
affinity = extract.expr.type._type_affinity
713
sqltypes.Date:'date',
714
sqltypes.DateTime:'timestamp',
715
sqltypes.Interval:'interval', sqltypes.Time:'time'
717
cast = casts.get(affinity, None)
718
if isinstance(extract.expr, sql.ColumnElement) and cast is not None:
719
expr = extract.expr.op('::')(sql.literal_column(cast))
1032
def visit_substring_func(self, func, **kw):
1033
s = self.process(func.clauses.clauses[0], **kw)
1034
start = self.process(func.clauses.clauses[1], **kw)
1035
if len(func.clauses.clauses) > 2:
1036
length = self.process(func.clauses.clauses[2], **kw)
1037
return "SUBSTRING(%s FROM %s FOR %s)" % (s, start, length)
722
return "EXTRACT(%s FROM %s)" % (
723
field, self.process(expr))
1039
return "SUBSTRING(%s FROM %s)" % (s, start)
725
1041
class PGDDLCompiler(compiler.DDLCompiler):
726
1042
def get_column_specification(self, column, **kwargs):
1337
1701
# format columns
1339
1703
for name, format_type, default, notnull, attnum, table_oid in rows:
1340
## strip (5) from character varying(5), timestamp(5)
1341
# with time zone, etc
1342
attype = re.sub(r'\([\d,]+\)', '', format_type)
1344
# strip '[]' from integer[], etc.
1345
attype = re.sub(r'\[\]', '', attype)
1347
nullable = not notnull
1348
is_array = format_type.endswith('[]')
1349
charlen = re.search('\(([\d,]+)\)', format_type)
1351
charlen = charlen.group(1)
1355
if attype == 'numeric':
1357
prec, scale = charlen.split(',')
1358
args = (int(prec), int(scale))
1361
elif attype == 'double precision':
1363
elif attype == 'integer':
1365
elif attype in ('timestamp with time zone',
1366
'time with time zone'):
1367
kwargs['timezone'] = True
1369
kwargs['precision'] = int(charlen)
1371
elif attype in ('timestamp without time zone',
1372
'time without time zone', 'time'):
1373
kwargs['timezone'] = False
1375
kwargs['precision'] = int(charlen)
1377
elif attype == 'bit varying':
1378
kwargs['varying'] = True
1380
args = (int(charlen),)
1383
elif attype in ('interval','interval year to month',
1384
'interval day to second'):
1386
kwargs['precision'] = int(charlen)
1389
args = (int(charlen),)
1394
if attype in self.ischema_names:
1395
coltype = self.ischema_names[attype]
1397
elif attype in enums:
1398
enum = enums[attype]
1401
kwargs['schema'], kwargs['name'] = attype.split('.')
1403
kwargs['name'] = attype
1404
args = tuple(enum['labels'])
1406
elif attype in domains:
1407
domain = domains[attype]
1408
attype = domain['attype']
1409
# A table can't override whether the domain is nullable.
1410
nullable = domain['nullable']
1411
if domain['default'] and not default:
1412
# It can, however, override the default
1413
# value, but can't set it to null.
1414
default = domain['default']
1421
coltype = coltype(*args, **kwargs)
1423
coltype = ARRAY(coltype)
1425
util.warn("Did not recognize type '%s' of column '%s'" %
1427
coltype = sqltypes.NULLTYPE
1428
# adjust the default value
1429
autoincrement = False
1430
if default is not None:
1431
match = re.search(r"""(nextval\(')([^']+)('.*$)""", default)
1432
if match is not None:
1433
autoincrement = True
1434
# the default is related to a Sequence
1436
if '.' not in match.group(2) and sch is not None:
1437
# unconditionally quote the schema name. this could
1438
# later be enhanced to obey quoting rules /
1440
default = match.group(1) + \
1441
('"%s"' % sch) + '.' + \
1442
match.group(2) + match.group(3)
1444
column_info = dict(name=name, type=coltype, nullable=nullable,
1445
default=default, autoincrement=autoincrement)
1704
column_info = self._get_column_info(
1705
name, format_type, default, notnull, domains, enums, schema)
1446
1706
columns.append(column_info)
1709
def _get_column_info(self, name, format_type, default,
1710
notnull, domains, enums, schema):
1711
## strip (*) from character varying(5), timestamp(5)
1712
# with time zone, geometry(POLYGON), etc.
1713
attype = re.sub(r'\(.*\)', '', format_type)
1715
# strip '[]' from integer[], etc.
1716
attype = re.sub(r'\[\]', '', attype)
1718
nullable = not notnull
1719
is_array = format_type.endswith('[]')
1720
charlen = re.search('\(([\d,]+)\)', format_type)
1722
charlen = charlen.group(1)
1723
args = re.search('\((.*)\)', format_type)
1724
if args and args.group(1):
1725
args = tuple(re.split('\s*,\s*', args.group(1)))
1730
if attype == 'numeric':
1732
prec, scale = charlen.split(',')
1733
args = (int(prec), int(scale))
1736
elif attype == 'double precision':
1738
elif attype == 'integer':
1740
elif attype in ('timestamp with time zone',
1741
'time with time zone'):
1742
kwargs['timezone'] = True
1744
kwargs['precision'] = int(charlen)
1746
elif attype in ('timestamp without time zone',
1747
'time without time zone', 'time'):
1748
kwargs['timezone'] = False
1750
kwargs['precision'] = int(charlen)
1752
elif attype == 'bit varying':
1753
kwargs['varying'] = True
1755
args = (int(charlen),)
1758
elif attype in ('interval', 'interval year to month',
1759
'interval day to second'):
1761
kwargs['precision'] = int(charlen)
1764
args = (int(charlen),)
1767
if attype in self.ischema_names:
1768
coltype = self.ischema_names[attype]
1770
elif attype in enums:
1771
enum = enums[attype]
1774
kwargs['schema'], kwargs['name'] = attype.split('.')
1776
kwargs['name'] = attype
1777
args = tuple(enum['labels'])
1779
elif attype in domains:
1780
domain = domains[attype]
1781
attype = domain['attype']
1782
# A table can't override whether the domain is nullable.
1783
nullable = domain['nullable']
1784
if domain['default'] and not default:
1785
# It can, however, override the default
1786
# value, but can't set it to null.
1787
default = domain['default']
1794
coltype = coltype(*args, **kwargs)
1796
coltype = ARRAY(coltype)
1798
util.warn("Did not recognize type '%s' of column '%s'" %
1800
coltype = sqltypes.NULLTYPE
1801
# adjust the default value
1802
autoincrement = False
1803
if default is not None:
1804
match = re.search(r"""(nextval\(')([^']+)('.*$)""", default)
1805
if match is not None:
1806
autoincrement = True
1807
# the default is related to a Sequence
1809
if '.' not in match.group(2) and sch is not None:
1810
# unconditionally quote the schema name. this could
1811
# later be enhanced to obey quoting rules /
1813
default = match.group(1) + \
1814
('"%s"' % sch) + '.' + \
1815
match.group(2) + match.group(3)
1817
column_info = dict(name=name, type=coltype, nullable=nullable,
1818
default=default, autoincrement=autoincrement)
1449
1821
@reflection.cache
1450
def get_primary_keys(self, connection, table_name, schema=None, **kw):
1822
def get_pk_constraint(self, connection, table_name, schema=None, **kw):
1451
1823
table_oid = self.get_table_oid(connection, table_name, schema,
1452
1824
info_cache=kw.get('info_cache'))