1
1
# engine/reflection.py
2
# Copyright (C) 2005-2012 the SQLAlchemy authors and contributors <see AUTHORS file>
2
# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file>
4
4
# This module is part of SQLAlchemy and is released under
5
5
# the MIT License: http://www.opensource.org/licenses/mit-license.php
28
from sqlalchemy import exc, sql
29
from sqlalchemy import util
30
from sqlalchemy.util import topological
31
from sqlalchemy.types import TypeEngine
32
from sqlalchemy import schema as sa_schema
27
from .. import exc, sql
28
from .. import schema as sa_schema
30
from ..types import TypeEngine
31
from ..util import deprecated
32
from ..util import topological
33
from .. import inspection
34
from .base import Connectable
53
55
"""Performs database schema inspection.
55
57
The Inspector acts as a proxy to the reflection methods of the
56
:class:`~sqlalchemy.engine.base.Dialect`, providing a
58
:class:`~sqlalchemy.engine.interfaces.Dialect`, providing a
57
59
consistent interface as well as caching support for previously
60
The preferred method to construct an :class:`.Inspector` is via the
61
:meth:`Inspector.from_engine` method. I.e.::
62
A :class:`.Inspector` object is usually created via the
63
:func:`.inspect` function::
65
from sqlalchemy import inspect, create_engine
66
engine = create_engine('...')
67
insp = inspect(engine)
69
The inspection method above is equivalent to using the
70
:meth:`.Inspector.from_engine` method, i.e.::
63
72
engine = create_engine('...')
64
73
insp = Inspector.from_engine(engine)
66
Where above, the :class:`~sqlalchemy.engine.base.Dialect` may opt
75
Where above, the :class:`~sqlalchemy.engine.interfaces.Dialect` may opt
67
76
to return an :class:`.Inspector` subclass that provides additional
68
77
methods specific to the dialect's target database.
72
81
def __init__(self, bind):
73
82
"""Initialize a new :class:`.Inspector`.
75
:param bind: a :class:`~sqlalchemy.engine.base.Connectable`,
84
:param bind: a :class:`~sqlalchemy.engine.Connectable`,
76
85
which is typically an instance of
77
:class:`~sqlalchemy.engine.base.Engine` or
78
:class:`~sqlalchemy.engine.base.Connection`.
86
:class:`~sqlalchemy.engine.Engine` or
87
:class:`~sqlalchemy.engine.Connection`.
80
89
For a dialect-specific instance of :class:`.Inspector`, see
81
:meth:`Inspector.from_engine`
90
:meth:`.Inspector.from_engine`
84
93
# this might not be a connection, it could be an engine.
101
110
def from_engine(cls, bind):
102
"""Construct a new dialect-specific Inspector object from the given engine or connection.
111
"""Construct a new dialect-specific Inspector object from the given
112
engine or connection.
104
:param bind: a :class:`~sqlalchemy.engine.base.Connectable`,
114
:param bind: a :class:`~sqlalchemy.engine.Connectable`,
105
115
which is typically an instance of
106
:class:`~sqlalchemy.engine.base.Engine` or
107
:class:`~sqlalchemy.engine.base.Connection`.
116
:class:`~sqlalchemy.engine.Engine` or
117
:class:`~sqlalchemy.engine.Connection`.
109
This method differs from direct a direct constructor call of :class:`.Inspector`
110
in that the :class:`~sqlalchemy.engine.base.Dialect` is given a chance to provide
111
a dialect-specific :class:`.Inspector` instance, which may provide additional
119
This method differs from direct a direct constructor call of
120
:class:`.Inspector` in that the
121
:class:`~sqlalchemy.engine.interfaces.Dialect` is given a chance to
122
provide a dialect-specific :class:`.Inspector` instance, which may
123
provide additional methods.
114
125
See the example at :class:`.Inspector`.
118
129
return bind.dialect.inspector(bind)
119
130
return Inspector(bind)
132
@inspection._inspects(Connectable)
134
return Inspector.from_engine(bind)
122
137
def default_schema_name(self):
123
138
"""Return the default schema name presented by the dialect
141
156
def get_table_names(self, schema=None, order_by=None):
142
"""Return all table names in `schema`.
144
:param schema: Optional, retrieve names from a non-default schema.
157
"""Return all table names in referred to within a particular schema.
159
The names are expected to be real tables only, not views.
160
Views are instead returned using the :meth:`.get_view_names`
164
:param schema: Schema name. If ``schema`` is left at ``None``, the
165
database's default schema is
166
used, else the named schema is searched. If the database does not
167
support named schemas, behavior is undefined if ``schema`` is not
145
170
:param order_by: Optional, may be the string "foreign_key" to sort
146
the result on foreign key dependencies.
148
This should probably not return view names or maybe it should return
149
them with an indicator t or v.
171
the result on foreign key dependencies.
173
.. versionchanged:: 0.8 the "foreign_key" sorting sorts tables
174
in order of dependee to dependent; that is, in creation
175
order, rather than in drop order. This is to maintain
176
consistency with similar features such as
177
:attr:`.MetaData.sorted_tables` and :func:`.util.sort_tables`.
181
:attr:`.MetaData.sorted_tables`
152
185
if hasattr(self.dialect, 'get_table_names'):
156
189
tnames = self.engine.table_names(schema)
157
190
if order_by == 'foreign_key':
159
random.shuffle(tnames)
162
192
for tname in tnames:
163
193
for fkey in self.get_foreign_keys(tname, schema):
164
194
if tname != fkey['referred_table']:
165
tuples.append((tname, fkey['referred_table']))
195
tuples.append((fkey['referred_table'], tname))
166
196
tnames = list(topological.sort(tuples, tnames))
169
199
def get_table_options(self, table_name, schema=None, **kw):
170
"""Return a dictionary of options specified when the table of the given name was created.
200
"""Return a dictionary of options specified when the table of the
201
given name was created.
172
203
This currently includes some options that apply to MySQL tables.
175
206
if hasattr(self.dialect, 'get_table_options'):
176
return self.dialect.get_table_options(self.bind, table_name, schema,
177
info_cache=self.info_cache,
207
return self.dialect.get_table_options(
208
self.bind, table_name, schema,
209
info_cache=self.info_cache, **kw)
181
212
def get_view_names(self, schema=None):
228
259
col_def['type'] = coltype()
262
@deprecated('0.7', 'Call to deprecated method get_primary_keys.'
263
' Use get_pk_constraint instead.')
231
264
def get_primary_keys(self, table_name, schema=None, **kw):
232
265
"""Return information about primary keys in `table_name`.
235
268
primary key information as a list of column names.
238
pkeys = self.dialect.get_primary_keys(self.bind, table_name, schema,
239
info_cache=self.info_cache,
271
return self.dialect.get_pk_constraint(self.bind, table_name, schema,
272
info_cache=self.info_cache,
273
**kw)['constrained_columns']
244
275
def get_pk_constraint(self, table_name, schema=None, **kw):
245
276
"""Return information about primary key constraint on `table_name`.
254
285
optional name of the primary key constraint.
257
pkeys = self.dialect.get_pk_constraint(self.bind, table_name, schema,
288
return self.dialect.get_pk_constraint(self.bind, table_name, schema,
258
289
info_cache=self.info_cache,
264
292
def get_foreign_keys(self, table_name, schema=None, **kw):
265
293
"""Return information about foreign_keys in `table_name`.
291
fk_defs = self.dialect.get_foreign_keys(self.bind, table_name, schema,
319
return self.dialect.get_foreign_keys(self.bind, table_name, schema,
292
320
info_cache=self.info_cache,
296
323
def get_indexes(self, table_name, schema=None, **kw):
297
324
"""Return information about indexes in `table_name`.
312
339
other options passed to the dialect's get_indexes() method.
315
indexes = self.dialect.get_indexes(self.bind, table_name,
342
return self.dialect.get_indexes(self.bind, table_name,
317
344
info_cache=self.info_cache, **kw)
320
346
def reflecttable(self, table, include_columns, exclude_columns=()):
321
"""Given a Table object, load its internal constructs based on introspection.
347
"""Given a Table object, load its internal constructs based on
323
350
This is the underlying method used by most dialects to produce
324
351
table reflection. Direct usage is like::
369
396
found_table = False
370
397
for col_d in self.get_columns(table_name, schema, **tblkw):
371
398
found_table = True
372
table.dispatch.column_reflect(table, col_d)
399
table.dispatch.column_reflect(self, table, col_d)
374
401
name = col_d['name']
375
402
if include_columns and name not in include_columns:
389
416
if col_d.get('default') is not None:
390
# the "default" value is assumed to be a literal SQL expression,
391
# so is wrapped in text() so that no quoting occurs on re-issuance.
417
# the "default" value is assumed to be a literal SQL
418
# expression, so is wrapped in text() so that no quoting
419
# occurs on re-issuance.
393
421
sa_schema.DefaultClause(
394
422
sql.text(col_d['default']), _reflected=True
415
443
pk_cons = self.get_pk_constraint(table_name, schema, **tblkw)
417
pk_cols = [table.c[pk]
418
for pk in pk_cons['constrained_columns']
419
if pk in table.c and pk not in exclude_columns
420
] + [pk for pk in table.primary_key if pk.key in exclude_columns]
421
primary_key_constraint = sa_schema.PrimaryKeyConstraint(name=pk_cons.get('name'),
447
for pk in pk_cons['constrained_columns']
448
if pk in table.c and pk not in exclude_columns
452
for pk in table.primary_key
453
if pk.key in exclude_columns
455
primary_key_constraint = sa_schema.PrimaryKeyConstraint(
456
name=pk_cons.get('name'),
429
464
for fkey_d in fkeys:
430
465
conname = fkey_d['name']
431
466
constrained_columns = fkey_d['constrained_columns']
467
if exclude_columns and set(constrained_columns).intersection(
432
470
referred_schema = fkey_d['referred_schema']
433
471
referred_table = fkey_d['referred_table']
434
472
referred_columns = fkey_d['referred_columns']