Reflecting Database Objects

Reflecting Database Objects

A Table object can be instructed to loadinformation about itself from the corresponding database schema object alreadyexisting within the database. This process is called reflection. In themost simple case you need only specify the table name, a MetaDataobject, and the autoload=True flag. If theMetaData is not persistently bound, also add theautoload_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 forinformation about the messages table, and will then generateColumn, ForeignKey,and other objects corresponding to this information as though theTable object were hand-constructed in Python.

When tables are reflected, if a given table references another one via foreignkey, a second Table object is created within theMetaData object representing the connection.Below, assume the table shopping_cart_items references a table namedshopping_carts. Reflecting the shopping_cart_items table has theeffect 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 oneTable object is created for each distinct tablename. The Table constructor actually returns toyou the already-existing Table object if onealready exists with the given name. Such as below, we can access the alreadygenerated 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 tableregardless. This is so that the table’s attributes will be loaded if they havenot been already. The autoload operation only occurs for the table if ithasn’t already been loaded; once loaded, new calls toTable with the same name will not re-issue anyreflection queries.

Overriding Reflected Columns

Individual columns can be overridden with explicit values when reflectingtables; this is handy for specifying custom datatypes, constraints such asprimary 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
... # additional Column objects which require no change are reflected normally
... autoload_with=some_engine)

Reflecting Views

The reflection system can also reflect views. Basic usage is the same as thatof a table:

my_view = Table("some_view", metadata, autoload=True)

Above, my_view is a Table object withColumn objects representing the names and types ofeach column within the view “some_view”.

Usually, it’s desired to have at least a primary key constraint whenreflecting a view, if not foreign keys as well. View reflection doesn’textrapolate these constraints.

Use the “override” technique for this, specifying explicitly those columnswhich 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 oftables and reflect the full set. This is achieved by using thereflect() method. After calling it, alllocated tables are present within the MetaDataobject’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 loadinglists of schema, table, column, and constraint descriptions from a givendatabase 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 theDialect, providing aconsistent interface as well as caching support for previouslyfetched metadata.

A Inspector object is usually created via theinspect() function:

from sqlalchemy import inspect, create_engine
engine = create_engine('...')
insp = inspect(engine)

The inspection method above is equivalent to using theInspector.from_engine() method, i.e.:

engine = create_engine('...')
insp = Inspector.from_engine(engine)

Where above, the Dialect may optto return an Inspector subclass that provides additionalmethods specific to the dialect’s target database.

init(bind)
Initialize a new Inspector.
- Parameters
- bind – a Connectable,which is typically an instance ofEngine orConnection.

For a dialect-specific instance of Inspector, seeInspector.from_engine()

property default_schema_name
Return the default schema name presented by the dialectfor the current engine’s database user.

E.g. this is typically public for PostgreSQL and dbofor SQL Server.

classmethod fromengine(_bind)
Construct a new dialect-specific Inspector object from the givenengine or connection.
- Parameters
- bind – a Connectable,which is typically an instance ofEngine orConnection.

This method differs from direct a direct constructor call ofInspector in that theDialect is given a chance toprovide a dialect-specific Inspector instance, which mayprovide additional methods.

See the example at Inspector.

getcheck_constraints(_table_name, schema=None, **kw)
Return information about check constraints in table_name.

Given a string table_name and an optional string schema, returncheck constraint information as a list of dicts with these keys:

- name
-

the check constraint’s name

- sqltext
-

the check constraint’s SQL expression

- dialect_options
-

may or may not be present; a dictionary with additionaldialect-specific options for this CHECK constraint

New in version 1.3.8.

- Parameters
- 
  -

table_name – string name of the table. For special quoting,use quoted_name.

schema – string schema name; if omitted, uses the default schemaof the database connection. For special quoting,use quoted_name.

New in version 1.1.0.

getcolumns(_table_name, schema=None, **kw)
Return information about columns in table_name.

Given a string table_name and an optional string schema, returncolumn information as a list of dicts with these keys:

name - the column’s name

type - the type of this column; an instance ofTypeEngine

nullable - boolean flag if the column is NULL or NOT NULL

default - the column’s server default value - this is returnedas 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 schemaof the database connection. For special quoting,use quoted_name.

- Returns
-

list of dictionaries, each representing the definition ofa database column.

getforeign_keys(_table_name, schema=None, **kw)
Return information about foreignkeys in _table_name.

Given a string table_name, and an optional string schema, returnforeign 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 toconstrained_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 schemaof the database connection. For special quoting,use quoted_name.

getindexes(_table_name, schema=None, **kw)
Return information about indexes in table_name.

Given a string table_name and an optional string schema, returnindex 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.

New in version 1.3.5.

- dialect_options
-

dict of dialect-specific index options. May not be presentfor all dialects.

New in version 1.0.0.

- Parameters
- 
  -

table_name – string name of the table. For special quoting,use quoted_name.

schema – string schema name; if omitted, uses the default schemaof the database connection. For special quoting,use quoted_name.

getpk_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, returnprimary 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 schemaof the database connection. For special quoting,use quoted_name.

getprimary_keys(_table_name, schema=None, **kw)
Return information about primary keys in table_name.

Deprecated since version 0.7: The Inspector.get_primary_keys() method is deprecated and will be removed in a future release. Please refer to the Inspector.get_pk_constraint() method.

Given a string table_name, and an optional string schema, returnprimary key information as a list of column names.

get_schema_names()
Return all schema names.
getsorted_table_and_fkc_names(_schema=None)
Return dependency-sorted table and foreign key constraint names inreferred 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 keyconstraint names that are not detected as belonging to a cycle.The final elementwill be (None, [(tname, fkname), (tname, fkname), ..])which will consist of remainingforeign key constraint names that would require a separate CREATEstep after-the-fact, based on dependencies between tables.

New in version 1.0.-.

Limitations of Reflection

It’s important to note that the reflection process recreates Tablemetadata using only information which is represented in the relational database.This process by definition cannot restore aspects of a schema that aren’tactually stored in the database. State which is not available from reflectionincludes but is not limited to:

Client side defaults, either Python functions or SQL expressions defined usingthe 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 theColumn.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 adifferent format than what was specified in SQLAlchemy. The Tableobjects returned from reflection cannot be always relied upon to produce the identicalDDL as the original Python-defined Table objects. Areas wherethis occurs includes server defaults, column-associated sequences and variousidiosyncrasies regarding constraints and datatypes. Server side defaults maybe 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 reflectionis only partially or not yet defined. Recent improvements to reflection allowthings like views, indexes and foreign key options to be reflected. As of thiswriting, structures like CHECK constraints, table comments, and triggers arenot reflected.