1# engine/reflection.py
2# Copyright (C) 2005-2024 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7
8"""Provides an abstraction for obtaining database schema information.
9
10Usage Notes:
11
12Here are some general conventions when accessing the low level inspector
13methods such as get_table_names, get_columns, etc.
14
151. Inspector methods return lists of dicts in most cases for the following
16 reasons:
17
18 * They're both standard types that can be serialized.
19 * Using a dict instead of a tuple allows easy expansion of attributes.
20 * Using a list for the outer structure maintains order and is easy to work
21 with (e.g. list comprehension [d['name'] for d in cols]).
22
232. Records that contain a name, such as the column name in a column record
24 use the key 'name'. So for most return values, each record will have a
25 'name' attribute..
26"""
27
28import contextlib
29
30from .base import Connectable
31from .base import Connection
32from .base import Engine
33from .. import exc
34from .. import inspection
35from .. import sql
36from .. import util
37from ..sql import operators
38from ..sql import schema as sa_schema
39from ..sql.type_api import TypeEngine
40from ..util import topological
41
42
43@util.decorator
44def cache(fn, self, con, *args, **kw):
45 info_cache = kw.get("info_cache", None)
46 if info_cache is None:
47 return fn(self, con, *args, **kw)
48 key = (
49 fn.__name__,
50 tuple(a for a in args if isinstance(a, util.string_types)),
51 tuple((k, v) for k, v in kw.items() if k != "info_cache"),
52 )
53 ret = info_cache.get(key)
54 if ret is None:
55 ret = fn(self, con, *args, **kw)
56 info_cache[key] = ret
57 return ret
58
59
60@inspection._self_inspects
61class Inspector(object):
62 """Performs database schema inspection.
63
64 The Inspector acts as a proxy to the reflection methods of the
65 :class:`~sqlalchemy.engine.interfaces.Dialect`, providing a
66 consistent interface as well as caching support for previously
67 fetched metadata.
68
69 A :class:`_reflection.Inspector` object is usually created via the
70 :func:`_sa.inspect` function, which may be passed an
71 :class:`_engine.Engine`
72 or a :class:`_engine.Connection`::
73
74 from sqlalchemy import inspect, create_engine
75 engine = create_engine('...')
76 insp = inspect(engine)
77
78 Where above, the :class:`~sqlalchemy.engine.interfaces.Dialect` associated
79 with the engine may opt to return an :class:`_reflection.Inspector`
80 subclass that
81 provides additional methods specific to the dialect's target database.
82
83 """
84
85 @util.deprecated(
86 "1.4",
87 "The __init__() method on :class:`_reflection.Inspector` "
88 "is deprecated and "
89 "will be removed in a future release. Please use the "
90 ":func:`.sqlalchemy.inspect` "
91 "function on an :class:`_engine.Engine` or "
92 ":class:`_engine.Connection` "
93 "in order to "
94 "acquire an :class:`_reflection.Inspector`.",
95 )
96 def __init__(self, bind):
97 """Initialize a new :class:`_reflection.Inspector`.
98
99 :param bind: a :class:`~sqlalchemy.engine.Connectable`,
100 which is typically an instance of
101 :class:`~sqlalchemy.engine.Engine` or
102 :class:`~sqlalchemy.engine.Connection`.
103
104 For a dialect-specific instance of :class:`_reflection.Inspector`, see
105 :meth:`_reflection.Inspector.from_engine`
106
107 """
108 return self._init_legacy(bind)
109
110 @classmethod
111 def _construct(cls, init, bind):
112
113 if hasattr(bind.dialect, "inspector"):
114 cls = bind.dialect.inspector
115
116 self = cls.__new__(cls)
117 init(self, bind)
118 return self
119
120 def _init_legacy(self, bind):
121 if hasattr(bind, "exec_driver_sql"):
122 self._init_connection(bind)
123 else:
124 self._init_engine(bind)
125
126 def _init_engine(self, engine):
127 self.bind = self.engine = engine
128 engine.connect().close()
129 self._op_context_requires_connect = True
130 self.dialect = self.engine.dialect
131 self.info_cache = {}
132
133 def _init_connection(self, connection):
134 self.bind = connection
135 self.engine = connection.engine
136 self._op_context_requires_connect = False
137 self.dialect = self.engine.dialect
138 self.info_cache = {}
139
140 @classmethod
141 @util.deprecated(
142 "1.4",
143 "The from_engine() method on :class:`_reflection.Inspector` "
144 "is deprecated and "
145 "will be removed in a future release. Please use the "
146 ":func:`.sqlalchemy.inspect` "
147 "function on an :class:`_engine.Engine` or "
148 ":class:`_engine.Connection` "
149 "in order to "
150 "acquire an :class:`_reflection.Inspector`.",
151 )
152 def from_engine(cls, bind):
153 """Construct a new dialect-specific Inspector object from the given
154 engine or connection.
155
156 :param bind: a :class:`~sqlalchemy.engine.Connectable`,
157 which is typically an instance of
158 :class:`~sqlalchemy.engine.Engine` or
159 :class:`~sqlalchemy.engine.Connection`.
160
161 This method differs from direct a direct constructor call of
162 :class:`_reflection.Inspector` in that the
163 :class:`~sqlalchemy.engine.interfaces.Dialect` is given a chance to
164 provide a dialect-specific :class:`_reflection.Inspector` instance,
165 which may
166 provide additional methods.
167
168 See the example at :class:`_reflection.Inspector`.
169
170 """
171 return cls._construct(cls._init_legacy, bind)
172
173 @inspection._inspects(Connectable)
174 def _connectable_insp(bind):
175 # this method should not be used unless some unusual case
176 # has subclassed "Connectable"
177
178 return Inspector._construct(Inspector._init_legacy, bind)
179
180 @inspection._inspects(Engine)
181 def _engine_insp(bind):
182 return Inspector._construct(Inspector._init_engine, bind)
183
184 @inspection._inspects(Connection)
185 def _connection_insp(bind):
186 return Inspector._construct(Inspector._init_connection, bind)
187
188 @contextlib.contextmanager
189 def _operation_context(self):
190 """Return a context that optimizes for multiple operations on a single
191 transaction.
192
193 This essentially allows connect()/close() to be called if we detected
194 that we're against an :class:`_engine.Engine` and not a
195 :class:`_engine.Connection`.
196
197 """
198 if self._op_context_requires_connect:
199 conn = self.bind.connect()
200 else:
201 conn = self.bind
202 try:
203 yield conn
204 finally:
205 if self._op_context_requires_connect:
206 conn.close()
207
208 @contextlib.contextmanager
209 def _inspection_context(self):
210 """Return an :class:`_reflection.Inspector`
211 from this one that will run all
212 operations on a single connection.
213
214 """
215
216 with self._operation_context() as conn:
217 sub_insp = self._construct(self.__class__._init_connection, conn)
218 sub_insp.info_cache = self.info_cache
219 yield sub_insp
220
221 @property
222 def default_schema_name(self):
223 """Return the default schema name presented by the dialect
224 for the current engine's database user.
225
226 E.g. this is typically ``public`` for PostgreSQL and ``dbo``
227 for SQL Server.
228
229 """
230 return self.dialect.default_schema_name
231
232 def get_schema_names(self):
233 """Return all schema names."""
234
235 if hasattr(self.dialect, "get_schema_names"):
236 with self._operation_context() as conn:
237 return self.dialect.get_schema_names(
238 conn, info_cache=self.info_cache
239 )
240 return []
241
242 def get_table_names(self, schema=None):
243 """Return all table names in referred to within a particular schema.
244
245 The names are expected to be real tables only, not views.
246 Views are instead returned using the
247 :meth:`_reflection.Inspector.get_view_names`
248 method.
249
250
251 :param schema: Schema name. If ``schema`` is left at ``None``, the
252 database's default schema is
253 used, else the named schema is searched. If the database does not
254 support named schemas, behavior is undefined if ``schema`` is not
255 passed as ``None``. For special quoting, use :class:`.quoted_name`.
256
257 .. seealso::
258
259 :meth:`_reflection.Inspector.get_sorted_table_and_fkc_names`
260
261 :attr:`_schema.MetaData.sorted_tables`
262
263 """
264
265 with self._operation_context() as conn:
266 return self.dialect.get_table_names(
267 conn, schema, info_cache=self.info_cache
268 )
269
270 def has_table(self, table_name, schema=None):
271 """Return True if the backend has a table of the given name.
272
273
274 :param table_name: name of the table to check
275 :param schema: schema name to query, if not the default schema.
276
277 .. versionadded:: 1.4 - the :meth:`.Inspector.has_table` method
278 replaces the :meth:`_engine.Engine.has_table` method.
279
280 """
281 # TODO: info_cache?
282 with self._operation_context() as conn:
283 return self.dialect.has_table(conn, table_name, schema)
284
285 def has_sequence(self, sequence_name, schema=None):
286 """Return True if the backend has a table of the given name.
287
288 :param sequence_name: name of the table to check
289 :param schema: schema name to query, if not the default schema.
290
291 .. versionadded:: 1.4
292
293 """
294 # TODO: info_cache?
295 with self._operation_context() as conn:
296 return self.dialect.has_sequence(conn, sequence_name, schema)
297
298 def get_sorted_table_and_fkc_names(self, schema=None):
299 """Return dependency-sorted table and foreign key constraint names in
300 referred to within a particular schema.
301
302 This will yield 2-tuples of
303 ``(tablename, [(tname, fkname), (tname, fkname), ...])``
304 consisting of table names in CREATE order grouped with the foreign key
305 constraint names that are not detected as belonging to a cycle.
306 The final element
307 will be ``(None, [(tname, fkname), (tname, fkname), ..])``
308 which will consist of remaining
309 foreign key constraint names that would require a separate CREATE
310 step after-the-fact, based on dependencies between tables.
311
312 .. versionadded:: 1.0.-
313
314 .. seealso::
315
316 :meth:`_reflection.Inspector.get_table_names`
317
318 :func:`.sort_tables_and_constraints` - similar method which works
319 with an already-given :class:`_schema.MetaData`.
320
321 """
322
323 with self._operation_context() as conn:
324 tnames = self.dialect.get_table_names(
325 conn, schema, info_cache=self.info_cache
326 )
327
328 tuples = set()
329 remaining_fkcs = set()
330
331 fknames_for_table = {}
332 for tname in tnames:
333 fkeys = self.get_foreign_keys(tname, schema)
334 fknames_for_table[tname] = set([fk["name"] for fk in fkeys])
335 for fkey in fkeys:
336 if tname != fkey["referred_table"]:
337 tuples.add((fkey["referred_table"], tname))
338 try:
339 candidate_sort = list(topological.sort(tuples, tnames))
340 except exc.CircularDependencyError as err:
341 for edge in err.edges:
342 tuples.remove(edge)
343 remaining_fkcs.update(
344 (edge[1], fkc) for fkc in fknames_for_table[edge[1]]
345 )
346
347 candidate_sort = list(topological.sort(tuples, tnames))
348 return [
349 (tname, fknames_for_table[tname].difference(remaining_fkcs))
350 for tname in candidate_sort
351 ] + [(None, list(remaining_fkcs))]
352
353 def get_temp_table_names(self):
354 """Return a list of temporary table names for the current bind.
355
356 This method is unsupported by most dialects; currently
357 only SQLite implements it.
358
359 .. versionadded:: 1.0.0
360
361 """
362
363 with self._operation_context() as conn:
364 return self.dialect.get_temp_table_names(
365 conn, info_cache=self.info_cache
366 )
367
368 def get_temp_view_names(self):
369 """Return a list of temporary view names for the current bind.
370
371 This method is unsupported by most dialects; currently
372 only SQLite implements it.
373
374 .. versionadded:: 1.0.0
375
376 """
377 with self._operation_context() as conn:
378 return self.dialect.get_temp_view_names(
379 conn, info_cache=self.info_cache
380 )
381
382 def get_table_options(self, table_name, schema=None, **kw):
383 """Return a dictionary of options specified when the table of the
384 given name was created.
385
386 This currently includes some options that apply to MySQL tables.
387
388 :param table_name: string name of the table. For special quoting,
389 use :class:`.quoted_name`.
390
391 :param schema: string schema name; if omitted, uses the default schema
392 of the database connection. For special quoting,
393 use :class:`.quoted_name`.
394
395 """
396 if hasattr(self.dialect, "get_table_options"):
397 with self._operation_context() as conn:
398 return self.dialect.get_table_options(
399 conn, table_name, schema, info_cache=self.info_cache, **kw
400 )
401 return {}
402
403 def get_view_names(self, schema=None):
404 """Return all view names in `schema`.
405
406 :param schema: Optional, retrieve names from a non-default schema.
407 For special quoting, use :class:`.quoted_name`.
408
409 """
410
411 with self._operation_context() as conn:
412 return self.dialect.get_view_names(
413 conn, schema, info_cache=self.info_cache
414 )
415
416 def get_sequence_names(self, schema=None):
417 """Return all sequence names in `schema`.
418
419 :param schema: Optional, retrieve names from a non-default schema.
420 For special quoting, use :class:`.quoted_name`.
421
422 """
423
424 with self._operation_context() as conn:
425 return self.dialect.get_sequence_names(
426 conn, schema, info_cache=self.info_cache
427 )
428
429 def get_view_definition(self, view_name, schema=None):
430 """Return definition for `view_name`.
431
432 :param schema: Optional, retrieve names from a non-default schema.
433 For special quoting, use :class:`.quoted_name`.
434
435 """
436
437 with self._operation_context() as conn:
438 return self.dialect.get_view_definition(
439 conn, view_name, schema, info_cache=self.info_cache
440 )
441
442 def get_columns(self, table_name, schema=None, **kw):
443 """Return information about columns in `table_name`.
444
445 Given a string `table_name` and an optional string `schema`, return
446 column information as a list of dicts with these keys:
447
448 * ``name`` - the column's name
449
450 * ``type`` - the type of this column; an instance of
451 :class:`~sqlalchemy.types.TypeEngine`
452
453 * ``nullable`` - boolean flag if the column is NULL or NOT NULL
454
455 * ``default`` - the column's server default value - this is returned
456 as a string SQL expression.
457
458 * ``autoincrement`` - indicates that the column is auto incremented -
459 this is returned as a boolean or 'auto'
460
461 * ``comment`` - (optional) the comment on the column. Only some
462 dialects return this key
463
464 * ``computed`` - (optional) when present it indicates that this column
465 is computed by the database. Only some dialects return this key.
466 Returned as a dict with the keys:
467
468 * ``sqltext`` - the expression used to generate this column returned
469 as a string SQL expression
470
471 * ``persisted`` - (optional) boolean that indicates if the column is
472 stored in the table
473
474 .. versionadded:: 1.3.16 - added support for computed reflection.
475
476 * ``identity`` - (optional) when present it indicates that this column
477 is a generated always column. Only some dialects return this key.
478 For a list of keywords on this dict see :class:`_schema.Identity`.
479
480 .. versionadded:: 1.4 - added support for identity column reflection.
481
482 * ``dialect_options`` - (optional) a dict with dialect specific options
483
484 :param table_name: string name of the table. For special quoting,
485 use :class:`.quoted_name`.
486
487 :param schema: string schema name; if omitted, uses the default schema
488 of the database connection. For special quoting,
489 use :class:`.quoted_name`.
490
491 :return: list of dictionaries, each representing the definition of
492 a database column.
493
494 """
495
496 with self._operation_context() as conn:
497 col_defs = self.dialect.get_columns(
498 conn, table_name, schema, info_cache=self.info_cache, **kw
499 )
500 for col_def in col_defs:
501 # make this easy and only return instances for coltype
502 coltype = col_def["type"]
503 if not isinstance(coltype, TypeEngine):
504 col_def["type"] = coltype()
505 return col_defs
506
507 def get_pk_constraint(self, table_name, schema=None, **kw):
508 """Return information about primary key constraint on `table_name`.
509
510 Given a string `table_name`, and an optional string `schema`, return
511 primary key information as a dictionary with these keys:
512
513 * ``constrained_columns`` -
514 a list of column names that make up the primary key
515
516 * ``name`` -
517 optional name of the primary key constraint.
518
519 :param table_name: string name of the table. For special quoting,
520 use :class:`.quoted_name`.
521
522 :param schema: string schema name; if omitted, uses the default schema
523 of the database connection. For special quoting,
524 use :class:`.quoted_name`.
525
526 """
527 with self._operation_context() as conn:
528 return self.dialect.get_pk_constraint(
529 conn, table_name, schema, info_cache=self.info_cache, **kw
530 )
531
532 def get_foreign_keys(self, table_name, schema=None, **kw):
533 """Return information about foreign_keys in `table_name`.
534
535 Given a string `table_name`, and an optional string `schema`, return
536 foreign key information as a list of dicts with these keys:
537
538 * ``constrained_columns`` -
539 a list of column names that make up the foreign key
540
541 * ``referred_schema`` -
542 the name of the referred schema
543
544 * ``referred_table`` -
545 the name of the referred table
546
547 * ``referred_columns`` -
548 a list of column names in the referred table that correspond to
549 constrained_columns
550
551 * ``name`` -
552 optional name of the foreign key constraint.
553
554 :param table_name: string name of the table. For special quoting,
555 use :class:`.quoted_name`.
556
557 :param schema: string schema name; if omitted, uses the default schema
558 of the database connection. For special quoting,
559 use :class:`.quoted_name`.
560
561 """
562
563 with self._operation_context() as conn:
564 return self.dialect.get_foreign_keys(
565 conn, table_name, schema, info_cache=self.info_cache, **kw
566 )
567
568 def get_indexes(self, table_name, schema=None, **kw):
569 """Return information about indexes in `table_name`.
570
571 Given a string `table_name` and an optional string `schema`, return
572 index information as a list of dicts with these keys:
573
574 * ``name`` -
575 the index's name
576
577 * ``column_names`` -
578 list of column names in order
579
580 * ``unique`` -
581 boolean
582
583 * ``column_sorting`` -
584 optional dict mapping column names to tuple of sort keywords,
585 which may include ``asc``, ``desc``, ``nulls_first``, ``nulls_last``.
586
587 .. versionadded:: 1.3.5
588
589 * ``dialect_options`` -
590 dict of dialect-specific index options. May not be present
591 for all dialects.
592
593 .. versionadded:: 1.0.0
594
595 :param table_name: string name of the table. For special quoting,
596 use :class:`.quoted_name`.
597
598 :param schema: string schema name; if omitted, uses the default schema
599 of the database connection. For special quoting,
600 use :class:`.quoted_name`.
601
602 """
603
604 with self._operation_context() as conn:
605 return self.dialect.get_indexes(
606 conn, table_name, schema, info_cache=self.info_cache, **kw
607 )
608
609 def get_unique_constraints(self, table_name, schema=None, **kw):
610 """Return information about unique constraints in `table_name`.
611
612 Given a string `table_name` and an optional string `schema`, return
613 unique constraint information as a list of dicts with these keys:
614
615 * ``name`` -
616 the unique constraint's name
617
618 * ``column_names`` -
619 list of column names in order
620
621 :param table_name: string name of the table. For special quoting,
622 use :class:`.quoted_name`.
623
624 :param schema: string schema name; if omitted, uses the default schema
625 of the database connection. For special quoting,
626 use :class:`.quoted_name`.
627
628 """
629
630 with self._operation_context() as conn:
631 return self.dialect.get_unique_constraints(
632 conn, table_name, schema, info_cache=self.info_cache, **kw
633 )
634
635 def get_table_comment(self, table_name, schema=None, **kw):
636 """Return information about the table comment for ``table_name``.
637
638 Given a string ``table_name`` and an optional string ``schema``,
639 return table comment information as a dictionary with these keys:
640
641 * ``text`` -
642 text of the comment.
643
644 Raises ``NotImplementedError`` for a dialect that does not support
645 comments.
646
647 .. versionadded:: 1.2
648
649 """
650
651 with self._operation_context() as conn:
652 return self.dialect.get_table_comment(
653 conn, table_name, schema, info_cache=self.info_cache, **kw
654 )
655
656 def get_check_constraints(self, table_name, schema=None, **kw):
657 """Return information about check constraints in `table_name`.
658
659 Given a string `table_name` and an optional string `schema`, return
660 check constraint information as a list of dicts with these keys:
661
662 * ``name`` -
663 the check constraint's name
664
665 * ``sqltext`` -
666 the check constraint's SQL expression
667
668 * ``dialect_options`` -
669 may or may not be present; a dictionary with additional
670 dialect-specific options for this CHECK constraint
671
672 .. versionadded:: 1.3.8
673
674 :param table_name: string name of the table. For special quoting,
675 use :class:`.quoted_name`.
676
677 :param schema: string schema name; if omitted, uses the default schema
678 of the database connection. For special quoting,
679 use :class:`.quoted_name`.
680
681 .. versionadded:: 1.1.0
682
683 """
684
685 with self._operation_context() as conn:
686 return self.dialect.get_check_constraints(
687 conn, table_name, schema, info_cache=self.info_cache, **kw
688 )
689
690 @util.deprecated_20(
691 ":meth:`_reflection.Inspector.reflecttable`",
692 "The :meth:`_reflection.Inspector.reflecttable` "
693 "method was renamed to "
694 ":meth:`_reflection.Inspector.reflect_table`. This deprecated alias "
695 "will be removed in a future release.",
696 )
697 def reflecttable(self, *args, **kwargs):
698 "See reflect_table. This method name is deprecated"
699 return self.reflect_table(*args, **kwargs)
700
701 def reflect_table(
702 self,
703 table,
704 include_columns,
705 exclude_columns=(),
706 resolve_fks=True,
707 _extend_on=None,
708 ):
709 """Given a :class:`_schema.Table` object, load its internal
710 constructs based on introspection.
711
712 This is the underlying method used by most dialects to produce
713 table reflection. Direct usage is like::
714
715 from sqlalchemy import create_engine, MetaData, Table
716 from sqlalchemy import inspect
717
718 engine = create_engine('...')
719 meta = MetaData()
720 user_table = Table('user', meta)
721 insp = inspect(engine)
722 insp.reflect_table(user_table, None)
723
724 .. versionchanged:: 1.4 Renamed from ``reflecttable`` to
725 ``reflect_table``
726
727 :param table: a :class:`~sqlalchemy.schema.Table` instance.
728 :param include_columns: a list of string column names to include
729 in the reflection process. If ``None``, all columns are reflected.
730
731 """
732
733 if _extend_on is not None:
734 if table in _extend_on:
735 return
736 else:
737 _extend_on.add(table)
738
739 dialect = self.bind.dialect
740
741 with self._operation_context() as conn:
742 schema = conn.schema_for_object(table)
743
744 table_name = table.name
745
746 # get table-level arguments that are specifically
747 # intended for reflection, e.g. oracle_resolve_synonyms.
748 # these are unconditionally passed to related Table
749 # objects
750 reflection_options = dict(
751 (k, table.dialect_kwargs.get(k))
752 for k in dialect.reflection_options
753 if k in table.dialect_kwargs
754 )
755
756 # reflect table options, like mysql_engine
757 tbl_opts = self.get_table_options(
758 table_name, schema, **table.dialect_kwargs
759 )
760 if tbl_opts:
761 # add additional kwargs to the Table if the dialect
762 # returned them
763 table._validate_dialect_kwargs(tbl_opts)
764
765 if util.py2k:
766 if isinstance(schema, str):
767 schema = schema.decode(dialect.encoding)
768 if isinstance(table_name, str):
769 table_name = table_name.decode(dialect.encoding)
770
771 found_table = False
772 cols_by_orig_name = {}
773
774 for col_d in self.get_columns(
775 table_name, schema, **table.dialect_kwargs
776 ):
777 found_table = True
778
779 self._reflect_column(
780 table,
781 col_d,
782 include_columns,
783 exclude_columns,
784 cols_by_orig_name,
785 )
786
787 # NOTE: support tables/views with no columns
788 if not found_table and not self.has_table(table_name, schema):
789 raise exc.NoSuchTableError(table_name)
790
791 self._reflect_pk(
792 table_name, schema, table, cols_by_orig_name, exclude_columns
793 )
794
795 self._reflect_fk(
796 table_name,
797 schema,
798 table,
799 cols_by_orig_name,
800 include_columns,
801 exclude_columns,
802 resolve_fks,
803 _extend_on,
804 reflection_options,
805 )
806
807 self._reflect_indexes(
808 table_name,
809 schema,
810 table,
811 cols_by_orig_name,
812 include_columns,
813 exclude_columns,
814 reflection_options,
815 )
816
817 self._reflect_unique_constraints(
818 table_name,
819 schema,
820 table,
821 cols_by_orig_name,
822 include_columns,
823 exclude_columns,
824 reflection_options,
825 )
826
827 self._reflect_check_constraints(
828 table_name,
829 schema,
830 table,
831 cols_by_orig_name,
832 include_columns,
833 exclude_columns,
834 reflection_options,
835 )
836
837 self._reflect_table_comment(
838 table_name, schema, table, reflection_options
839 )
840
841 def _reflect_column(
842 self, table, col_d, include_columns, exclude_columns, cols_by_orig_name
843 ):
844
845 orig_name = col_d["name"]
846
847 table.metadata.dispatch.column_reflect(self, table, col_d)
848 table.dispatch.column_reflect(self, table, col_d)
849
850 # fetch name again as column_reflect is allowed to
851 # change it
852 name = col_d["name"]
853 if (include_columns and name not in include_columns) or (
854 exclude_columns and name in exclude_columns
855 ):
856 return
857
858 coltype = col_d["type"]
859
860 col_kw = dict(
861 (k, col_d[k])
862 for k in [
863 "nullable",
864 "autoincrement",
865 "quote",
866 "info",
867 "key",
868 "comment",
869 ]
870 if k in col_d
871 )
872
873 if "dialect_options" in col_d:
874 col_kw.update(col_d["dialect_options"])
875
876 colargs = []
877 if col_d.get("default") is not None:
878 default = col_d["default"]
879 if isinstance(default, sql.elements.TextClause):
880 default = sa_schema.DefaultClause(default, _reflected=True)
881 elif not isinstance(default, sa_schema.FetchedValue):
882 default = sa_schema.DefaultClause(
883 sql.text(col_d["default"]), _reflected=True
884 )
885
886 colargs.append(default)
887
888 if "computed" in col_d:
889 computed = sa_schema.Computed(**col_d["computed"])
890 colargs.append(computed)
891
892 if "identity" in col_d:
893 computed = sa_schema.Identity(**col_d["identity"])
894 colargs.append(computed)
895
896 if "sequence" in col_d:
897 self._reflect_col_sequence(col_d, colargs)
898
899 cols_by_orig_name[orig_name] = col = sa_schema.Column(
900 name, coltype, *colargs, **col_kw
901 )
902
903 if col.key in table.primary_key:
904 col.primary_key = True
905 table.append_column(col, replace_existing=True)
906
907 def _reflect_col_sequence(self, col_d, colargs):
908 if "sequence" in col_d:
909 # TODO: mssql and sybase are using this.
910 seq = col_d["sequence"]
911 sequence = sa_schema.Sequence(seq["name"], 1, 1)
912 if "start" in seq:
913 sequence.start = seq["start"]
914 if "increment" in seq:
915 sequence.increment = seq["increment"]
916 colargs.append(sequence)
917
918 def _reflect_pk(
919 self, table_name, schema, table, cols_by_orig_name, exclude_columns
920 ):
921 pk_cons = self.get_pk_constraint(
922 table_name, schema, **table.dialect_kwargs
923 )
924 if pk_cons:
925 pk_cols = [
926 cols_by_orig_name[pk]
927 for pk in pk_cons["constrained_columns"]
928 if pk in cols_by_orig_name and pk not in exclude_columns
929 ]
930
931 # update pk constraint name
932 table.primary_key.name = pk_cons.get("name")
933
934 # tell the PKConstraint to re-initialize
935 # its column collection
936 table.primary_key._reload(pk_cols)
937
938 def _reflect_fk(
939 self,
940 table_name,
941 schema,
942 table,
943 cols_by_orig_name,
944 include_columns,
945 exclude_columns,
946 resolve_fks,
947 _extend_on,
948 reflection_options,
949 ):
950 fkeys = self.get_foreign_keys(
951 table_name, schema, **table.dialect_kwargs
952 )
953 for fkey_d in fkeys:
954 conname = fkey_d["name"]
955 # look for columns by orig name in cols_by_orig_name,
956 # but support columns that are in-Python only as fallback
957 constrained_columns = [
958 cols_by_orig_name[c].key if c in cols_by_orig_name else c
959 for c in fkey_d["constrained_columns"]
960 ]
961
962 if (
963 exclude_columns
964 and set(constrained_columns).intersection(exclude_columns)
965 or (
966 include_columns
967 and set(constrained_columns).difference(include_columns)
968 )
969 ):
970 continue
971
972 referred_schema = fkey_d["referred_schema"]
973 referred_table = fkey_d["referred_table"]
974 referred_columns = fkey_d["referred_columns"]
975 refspec = []
976 if referred_schema is not None:
977 if resolve_fks:
978 sa_schema.Table(
979 referred_table,
980 table.metadata,
981 schema=referred_schema,
982 autoload_with=self.bind,
983 _extend_on=_extend_on,
984 **reflection_options
985 )
986 for column in referred_columns:
987 refspec.append(
988 ".".join([referred_schema, referred_table, column])
989 )
990 else:
991 if resolve_fks:
992 sa_schema.Table(
993 referred_table,
994 table.metadata,
995 autoload_with=self.bind,
996 schema=sa_schema.BLANK_SCHEMA,
997 _extend_on=_extend_on,
998 **reflection_options
999 )
1000 for column in referred_columns:
1001 refspec.append(".".join([referred_table, column]))
1002 if "options" in fkey_d:
1003 options = fkey_d["options"]
1004 else:
1005 options = {}
1006
1007 table.append_constraint(
1008 sa_schema.ForeignKeyConstraint(
1009 constrained_columns,
1010 refspec,
1011 conname,
1012 link_to_name=True,
1013 **options
1014 )
1015 )
1016
1017 _index_sort_exprs = [
1018 ("asc", operators.asc_op),
1019 ("desc", operators.desc_op),
1020 ("nulls_first", operators.nulls_first_op),
1021 ("nulls_last", operators.nulls_last_op),
1022 ]
1023
1024 def _reflect_indexes(
1025 self,
1026 table_name,
1027 schema,
1028 table,
1029 cols_by_orig_name,
1030 include_columns,
1031 exclude_columns,
1032 reflection_options,
1033 ):
1034 # Indexes
1035 indexes = self.get_indexes(table_name, schema)
1036 for index_d in indexes:
1037 name = index_d["name"]
1038 columns = index_d["column_names"]
1039 column_sorting = index_d.get("column_sorting", {})
1040 unique = index_d["unique"]
1041 flavor = index_d.get("type", "index")
1042 dialect_options = index_d.get("dialect_options", {})
1043
1044 duplicates = index_d.get("duplicates_constraint")
1045 if include_columns and not set(columns).issubset(include_columns):
1046 util.warn(
1047 "Omitting %s key for (%s), key covers omitted columns."
1048 % (flavor, ", ".join(columns))
1049 )
1050 continue
1051 if duplicates:
1052 continue
1053 # look for columns by orig name in cols_by_orig_name,
1054 # but support columns that are in-Python only as fallback
1055 idx_cols = []
1056 for c in columns:
1057 try:
1058 idx_col = (
1059 cols_by_orig_name[c]
1060 if c in cols_by_orig_name
1061 else table.c[c]
1062 )
1063 except KeyError:
1064 util.warn(
1065 "%s key '%s' was not located in "
1066 "columns for table '%s'" % (flavor, c, table_name)
1067 )
1068 continue
1069 c_sorting = column_sorting.get(c, ())
1070 for k, op in self._index_sort_exprs:
1071 if k in c_sorting:
1072 idx_col = op(idx_col)
1073 idx_cols.append(idx_col)
1074
1075 sa_schema.Index(
1076 name,
1077 *idx_cols,
1078 _table=table,
1079 **dict(list(dialect_options.items()) + [("unique", unique)])
1080 )
1081
1082 def _reflect_unique_constraints(
1083 self,
1084 table_name,
1085 schema,
1086 table,
1087 cols_by_orig_name,
1088 include_columns,
1089 exclude_columns,
1090 reflection_options,
1091 ):
1092
1093 # Unique Constraints
1094 try:
1095 constraints = self.get_unique_constraints(table_name, schema)
1096 except NotImplementedError:
1097 # optional dialect feature
1098 return
1099
1100 for const_d in constraints:
1101 conname = const_d["name"]
1102 columns = const_d["column_names"]
1103 duplicates = const_d.get("duplicates_index")
1104 if include_columns and not set(columns).issubset(include_columns):
1105 util.warn(
1106 "Omitting unique constraint key for (%s), "
1107 "key covers omitted columns." % ", ".join(columns)
1108 )
1109 continue
1110 if duplicates:
1111 continue
1112 # look for columns by orig name in cols_by_orig_name,
1113 # but support columns that are in-Python only as fallback
1114 constrained_cols = []
1115 for c in columns:
1116 try:
1117 constrained_col = (
1118 cols_by_orig_name[c]
1119 if c in cols_by_orig_name
1120 else table.c[c]
1121 )
1122 except KeyError:
1123 util.warn(
1124 "unique constraint key '%s' was not located in "
1125 "columns for table '%s'" % (c, table_name)
1126 )
1127 else:
1128 constrained_cols.append(constrained_col)
1129 table.append_constraint(
1130 sa_schema.UniqueConstraint(*constrained_cols, name=conname)
1131 )
1132
1133 def _reflect_check_constraints(
1134 self,
1135 table_name,
1136 schema,
1137 table,
1138 cols_by_orig_name,
1139 include_columns,
1140 exclude_columns,
1141 reflection_options,
1142 ):
1143 try:
1144 constraints = self.get_check_constraints(table_name, schema)
1145 except NotImplementedError:
1146 # optional dialect feature
1147 return
1148
1149 for const_d in constraints:
1150 table.append_constraint(sa_schema.CheckConstraint(**const_d))
1151
1152 def _reflect_table_comment(
1153 self, table_name, schema, table, reflection_options
1154 ):
1155 try:
1156 comment_dict = self.get_table_comment(table_name, schema)
1157 except NotImplementedError:
1158 return
1159 else:
1160 table.comment = comment_dict.get("text", None)