Reflecting Database Objects
A Table object can be instructed to load
information about itself from the corresponding database schema object already
existing within the database. This process is called reflection. In the
most simple case you need only specify the table name, a MetaData
object, and the autoload=True flag. If the
MetaData is not persistently bound, also add the
autoload_with argument:
>>> messages = Table('messages', meta, autoload=True, autoload_with=engine)
>>> [c.name for c in messages.columns]
['message_id', 'message_name', 'date']
The above operation will use the given engine to query the database for
information about the messages table, and will then generate
Column, ForeignKey,
and other objects corresponding to this information as though the
Table object were hand-constructed in Python.
When tables are reflected, if a given table references another one via foreign
key, a second Table object is created within the
MetaData object representing the connection.
Below, assume the table shopping_cart_items references a table named
shopping_carts. Reflecting the shopping_cart_items table has the
effect such that the shopping_carts table will also be loaded:
>>> shopping_cart_items = Table('shopping_cart_items', meta, autoload=True, autoload_with=engine)
>>> 'shopping_carts' in meta.tables:
True
The MetaData has an interesting “singleton-like”
behavior such that if you requested both tables individually,
MetaData will ensure that exactly one
Table object is created for each distinct table
name. The Table constructor actually returns to
you the already-existing Table object if one
already exists with the given name. Such as below, we can access the already
generated shopping_carts table just by naming it:
shopping_carts = Table('shopping_carts', meta)
Of course, it’s a good idea to use autoload=True with the above table
regardless. This is so that the table’s attributes will be loaded if they have
not been already. The autoload operation only occurs for the table if it
hasn’t already been loaded; once loaded, new calls to
Table with the same name will not re-issue any
reflection queries.
Overriding Reflected Columns
Individual columns can be overridden with explicit values when reflecting
tables; this is handy for specifying custom datatypes, constraints such as
primary keys that may not be configured within the database, etc.:
>>> mytable = Table('mytable', meta,
... Column('id', Integer, primary_key=True), # override reflected 'id' to have primary key
... Column('mydata', Unicode(50)), # override reflected 'mydata' to be Unicode
... autoload=True)
Reflecting Views
The reflection system can also reflect views. Basic usage is the same as that
of a table:
my_view = Table("some_view", metadata, autoload=True)
Above, my_view is a Table object with
Column objects representing the names and types of
each column within the view “some_view”.
Usually, it’s desired to have at least a primary key constraint when
reflecting a view, if not foreign keys as well. View reflection doesn’t
extrapolate these constraints.
Use the “override” technique for this, specifying explicitly those columns
which are part of the primary key or have foreign key constraints:
my_view = Table("some_view", metadata,
Column("view_id", Integer, primary_key=True),
Column("related_thing", Integer, ForeignKey("othertable.thing_id")),
autoload=True
)
Reflecting All Tables at Once
The MetaData object can also get a listing of
tables and reflect the full set. This is achieved by using the
reflect() method. After calling it, all
located tables are present within the MetaData
object’s dictionary of tables:
meta = MetaData()
meta.reflect(bind=someengine)
users_table = meta.tables['users']
addresses_table = meta.tables['addresses']
metadata.reflect() also provides a handy way to clear or delete all the rows in a database:
meta = MetaData()
meta.reflect(bind=someengine)
for table in reversed(meta.sorted_tables):
someengine.execute(table.delete())
Fine Grained Reflection with Inspector
A low level interface which provides a backend-agnostic system of loading
lists of schema, table, column, and constraint descriptions from a given
database is also available. This is known as the “Inspector”:
from sqlalchemy import create_engine
from sqlalchemy.engine import reflection
engine = create_engine('...')
insp = reflection.Inspector.from_engine(engine)
print(insp.get_table_names())
-
class
sqlalchemy.engine.reflection.Inspector(bind)
Performs database schema inspection.
The Inspector acts as a proxy to the reflection methods of the
Dialect, providing a
consistent interface as well as caching support for previously
fetched metadata.
A Inspector object is usually created via the
inspect() function:
from sqlalchemy import inspect, create_engine
engine = create_engine('...')
insp = inspect(engine)
The inspection method above is equivalent to using the
Inspector.from_engine() method, i.e.:
engine = create_engine('...')
insp = Inspector.from_engine(engine)
Where above, the Dialect may opt
to return an Inspector subclass that provides additional
methods specific to the dialect’s target database.
-
__init__(bind)
Initialize a new Inspector.
- Parameters
bind – a Connectable,
which is typically an instance of
Engine or
Connection.
For a dialect-specific instance of Inspector, see
Inspector.from_engine()
-
property
default_schema_name
Return the default schema name presented by the dialect
for the current engine’s database user.
E.g. this is typically public for PostgreSQL and dbo
for SQL Server.
-
classmethod
from_engine(bind)
Construct a new dialect-specific Inspector object from the given
engine or connection.
- Parameters
bind – a Connectable,
which is typically an instance of
Engine or
Connection.
This method differs from direct a direct constructor call of
Inspector in that the
Dialect is given a chance to
provide a dialect-specific Inspector instance, which may
provide additional methods.
See the example at Inspector.
-
get_check_constraints(table_name, schema=None, **kw)
Return information about check constraints in table_name.
Given a string table_name and an optional string schema, return
check constraint information as a list of dicts with these keys:
- name
the check constraint’s name
- sqltext
the check constraint’s SQL expression
- Parameters
table_name – string name of the table. For special quoting,
use quoted_name.
schema – string schema name; if omitted, uses the default schema
of the database connection. For special quoting,
use quoted_name.
-
get_columns(table_name, schema=None, **kw)
Return information about columns in table_name.
Given a string table_name and an optional string schema, return
column information as a list of dicts with these keys:
name - the column’s name
type - the type of this column; an instance of
TypeEngine
nullable - boolean flag if the column is NULL or NOT NULL
default - the column’s server default value - this is returned
as a string SQL expression.
attrs - dict containing optional column attributes
- Parameters
table_name – string name of the table. For special quoting,
use quoted_name.
schema – string schema name; if omitted, uses the default schema
of the database connection. For special quoting,
use quoted_name.
- Returns
list of dictionaries, each representing the definition of
a database column.
-
get_foreign_keys(table_name, schema=None, **kw)
Return information about foreign_keys in table_name.
Given a string table_name, and an optional string schema, return
foreign key information as a list of dicts with these keys:
- constrained_columns
a list of column names that make up the foreign key
- referred_schema
the name of the referred schema
- referred_table
the name of the referred table
- referred_columns
a list of column names in the referred table that correspond to
constrained_columns
- name
optional name of the foreign key constraint.
- Parameters
table_name – string name of the table. For special quoting,
use quoted_name.
schema – string schema name; if omitted, uses the default schema
of the database connection. For special quoting,
use quoted_name.
-
get_indexes(table_name, schema=None, **kw)
Return information about indexes in table_name.
Given a string table_name and an optional string schema, return
index information as a list of dicts with these keys:
- name
the index’s name
- column_names
list of column names in order
- unique
boolean
- column_sorting
optional dict mapping column names to tuple of sort keywords,
which may include asc, desc, nullsfirst, nullslast.
- dialect_options
dict of dialect-specific index options. May not be present
for all dialects.
- Parameters
table_name – string name of the table. For special quoting,
use quoted_name.
schema – string schema name; if omitted, uses the default schema
of the database connection. For special quoting,
use quoted_name.
-
get_pk_constraint(table_name, schema=None, **kw)
Return information about primary key constraint on table_name.
Given a string table_name, and an optional string schema, return
primary key information as a dictionary with these keys:
- constrained_columns
a list of column names that make up the primary key
- name
optional name of the primary key constraint.
- Parameters
table_name – string name of the table. For special quoting,
use quoted_name.
schema – string schema name; if omitted, uses the default schema
of the database connection. For special quoting,
use quoted_name.
-
get_primary_keys(table_name, schema=None, **kw)
Return information about primary keys in table_name.
Given a string table_name, and an optional string schema, return
primary key information as a list of column names.
-
get_schema_names()
Return all schema names.
-
get_sorted_table_and_fkc_names(schema=None)
Return dependency-sorted table and foreign key constraint names in
referred to within a particular schema.
This will yield 2-tuples of
(tablename, [(tname, fkname), (tname, fkname), ...])
consisting of table names in CREATE order grouped with the foreign key
constraint names that are not detected as belonging to a cycle.
The final element
will be (None, [(tname, fkname), (tname, fkname), ..])
which will consist of remaining
foreign key constraint names that would require a separate CREATE
step after-the-fact, based on dependencies between tables.
Return information about the table comment for table_name.
Given a string table_name and an optional string schema,
return table comment information as a dictionary with these keys:
- text
text of the comment.
Raises NotImplementedError for a dialect that does not support
comments.
-
get_table_names(schema=None, order_by=None)
Return all table names in referred to within a particular schema.
The names are expected to be real tables only, not views.
Views are instead returned using the Inspector.get_view_names()
method.
- Parameters
schema – Schema name. If schema is left at None, the
database’s default schema is
used, else the named schema is searched. If the database does not
support named schemas, behavior is undefined if schema is not
passed as None. For special quoting, use quoted_name.
order_by –
Optional, may be the string “foreign_key” to sort
the result on foreign key dependencies. Does not automatically
resolve cycles, and will raise CircularDependencyError
if cycles exist.
-
get_table_options(table_name, schema=None, **kw)
Return a dictionary of options specified when the table of the
given name was created.
This currently includes some options that apply to MySQL tables.
- Parameters
table_name – string name of the table. For special quoting,
use quoted_name.
schema – string schema name; if omitted, uses the default schema
of the database connection. For special quoting,
use quoted_name.
-
get_temp_table_names()
return a list of temporary table names for the current bind.
This method is unsupported by most dialects; currently
only SQLite implements it.
-
get_temp_view_names()
return a list of temporary view names for the current bind.
This method is unsupported by most dialects; currently
only SQLite implements it.
-
get_unique_constraints(table_name, schema=None, **kw)
Return information about unique constraints in table_name.
Given a string table_name and an optional string schema, return
unique constraint information as a list of dicts with these keys:
- name
the unique constraint’s name
- column_names
list of column names in order
- Parameters
table_name – string name of the table. For special quoting,
use quoted_name.
schema – string schema name; if omitted, uses the default schema
of the database connection. For special quoting,
use quoted_name.
-
get_view_definition(view_name, schema=None)
Return definition for view_name.
- Parameters
schema – Optional, retrieve names from a non-default schema.
For special quoting, use quoted_name.
-
get_view_names(schema=None)
Return all view names in schema.
- Parameters
schema – Optional, retrieve names from a non-default schema.
For special quoting, use quoted_name.
-
reflecttable(table, include_columns, exclude_columns=(), resolve_fks=True, _extend_on=None)
Given a Table object, load its internal constructs based on
introspection.
This is the underlying method used by most dialects to produce
table reflection. Direct usage is like:
from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.engine.reflection import Inspector
engine = create_engine('...')
meta = MetaData()
user_table = Table('user', meta)
insp = Inspector.from_engine(engine)
insp.reflecttable(user_table, None)
- Parameters
table – a Table instance.
include_columns – a list of string column names to include
in the reflection process. If None, all columns are reflected.
Limitations of Reflection
It’s important to note that the reflection process recreates Table
metadata using only information which is represented in the relational database.
This process by definition cannot restore aspects of a schema that aren’t
actually stored in the database. State which is not available from reflection
includes but is not limited to:
Client side defaults, either Python functions or SQL expressions defined using
the default keyword of Column (note this is separate from server_default,
which specifically is what’s available via reflection).
Column information, e.g. data that might have been placed into the
Column.info dictionary
The value of the .quote setting for Column or Table
The association of a particular Sequence with a given Column
The relational database also in many cases reports on table metadata in a
different format than what was specified in SQLAlchemy. The Table
objects returned from reflection cannot be always relied upon to produce the identical
DDL as the original Python-defined Table objects. Areas where
this occurs includes server defaults, column-associated sequences and various
idiosyncrasies regarding constraints and datatypes. Server side defaults may
be returned with cast directives (typically PostgreSQL will include a ::<type>
cast) or different quoting patterns than originally specified.
Another category of limitation includes schema structures for which reflection
is only partially or not yet defined. Recent improvements to reflection allow
things like views, indexes and foreign key options to be reflected. As of this
writing, structures like CHECK constraints, table comments, and triggers are
not reflected.