1
.. module:: sqlalchemy.schema
3
.. _metadata_reflection_toplevel:
4
.. _metadata_reflection:
7
Reflecting Database Objects
8
===========================
10
A :class:`~sqlalchemy.schema.Table` object can be instructed to load
11
information about itself from the corresponding database schema object already
12
existing within the database. This process is called *reflection*. In the
13
most simple case you need only specify the table name, a :class:`~sqlalchemy.schema.MetaData`
14
object, and the ``autoload=True`` flag. If the
15
:class:`~sqlalchemy.schema.MetaData` is not persistently bound, also add the
16
``autoload_with`` argument::
18
>>> messages = Table('messages', meta, autoload=True, autoload_with=engine)
19
>>> [c.name for c in messages.columns]
20
['message_id', 'message_name', 'date']
22
The above operation will use the given engine to query the database for
23
information about the ``messages`` table, and will then generate
24
:class:`~sqlalchemy.schema.Column`, :class:`~sqlalchemy.schema.ForeignKey`,
25
and other objects corresponding to this information as though the
26
:class:`~sqlalchemy.schema.Table` object were hand-constructed in Python.
28
When tables are reflected, if a given table references another one via foreign
29
key, a second :class:`~sqlalchemy.schema.Table` object is created within the
30
:class:`~sqlalchemy.schema.MetaData` object representing the connection.
31
Below, assume the table ``shopping_cart_items`` references a table named
32
``shopping_carts``. Reflecting the ``shopping_cart_items`` table has the
33
effect such that the ``shopping_carts`` table will also be loaded::
35
>>> shopping_cart_items = Table('shopping_cart_items', meta, autoload=True, autoload_with=engine)
36
>>> 'shopping_carts' in meta.tables:
39
The :class:`~sqlalchemy.schema.MetaData` has an interesting "singleton-like"
40
behavior such that if you requested both tables individually,
41
:class:`~sqlalchemy.schema.MetaData` will ensure that exactly one
42
:class:`~sqlalchemy.schema.Table` object is created for each distinct table
43
name. The :class:`~sqlalchemy.schema.Table` constructor actually returns to
44
you the already-existing :class:`~sqlalchemy.schema.Table` object if one
45
already exists with the given name. Such as below, we can access the already
46
generated ``shopping_carts`` table just by naming it::
48
shopping_carts = Table('shopping_carts', meta)
50
Of course, it's a good idea to use ``autoload=True`` with the above table
51
regardless. This is so that the table's attributes will be loaded if they have
52
not been already. The autoload operation only occurs for the table if it
53
hasn't already been loaded; once loaded, new calls to
54
:class:`~sqlalchemy.schema.Table` with the same name will not re-issue any
57
Overriding Reflected Columns
58
-----------------------------
60
Individual columns can be overridden with explicit values when reflecting
61
tables; this is handy for specifying custom datatypes, constraints such as
62
primary keys that may not be configured within the database, etc.::
64
>>> mytable = Table('mytable', meta,
65
... Column('id', Integer, primary_key=True), # override reflected 'id' to have primary key
66
... Column('mydata', Unicode(50)), # override reflected 'mydata' to be Unicode
72
The reflection system can also reflect views. Basic usage is the same as that
75
my_view = Table("some_view", metadata, autoload=True)
77
Above, ``my_view`` is a :class:`~sqlalchemy.schema.Table` object with
78
:class:`~sqlalchemy.schema.Column` objects representing the names and types of
79
each column within the view "some_view".
81
Usually, it's desired to have at least a primary key constraint when
82
reflecting a view, if not foreign keys as well. View reflection doesn't
83
extrapolate these constraints.
85
Use the "override" technique for this, specifying explicitly those columns
86
which are part of the primary key or have foreign key constraints::
88
my_view = Table("some_view", metadata,
89
Column("view_id", Integer, primary_key=True),
90
Column("related_thing", Integer, ForeignKey("othertable.thing_id")),
94
Reflecting All Tables at Once
95
-----------------------------
97
The :class:`~sqlalchemy.schema.MetaData` object can also get a listing of
98
tables and reflect the full set. This is achieved by using the
99
:func:`~sqlalchemy.schema.MetaData.reflect` method. After calling it, all
100
located tables are present within the :class:`~sqlalchemy.schema.MetaData`
101
object's dictionary of tables::
104
meta.reflect(bind=someengine)
105
users_table = meta.tables['users']
106
addresses_table = meta.tables['addresses']
108
``metadata.reflect()`` also provides a handy way to clear or delete all the rows in a database::
111
meta.reflect(bind=someengine)
112
for table in reversed(meta.sorted_tables):
113
someengine.execute(table.delete())
115
.. _metadata_reflection_inspector:
117
Fine Grained Reflection with Inspector
118
--------------------------------------
120
A low level interface which provides a backend-agnostic system of loading
121
lists of schema, table, column, and constraint descriptions from a given
122
database is also available. This is known as the "Inspector"::
124
from sqlalchemy import create_engine
125
from sqlalchemy.engine import reflection
126
engine = create_engine('...')
127
insp = reflection.Inspector.from_engine(engine)
128
print insp.get_table_names()
130
.. autoclass:: sqlalchemy.engine.reflection.Inspector
134
Limitations of Reflection
135
-------------------------
137
It's important to note that the reflection process recreates :class:`.Table`
138
metadata using only information which is represented in the relational database.
139
This process by definition cannot restore aspects of a schema that aren't
140
actually stored in the database. State which is not available from reflection
141
includes but is not limited to:
143
* Client side defaults, either Python functions or SQL expressions defined using
144
the ``default`` keyword of :class:`.Column` (note this is separate from ``server_default``,
145
which specifically is what's available via reflection).
147
* Column information, e.g. data that might have been placed into the
148
:attr:`.Column.info` dictionary
150
* The value of the ``.quote`` setting for :class:`.Column` or :class:`.Table`
152
* The assocation of a particular :class:`.Sequence` with a given :class:`.Column`
154
The relational database also in many cases reports on table metadata in a
155
different format than what was specified in SQLAlchemy. The :class:`.Table`
156
objects returned from reflection cannot be always relied upon to produce the identical
157
DDL as the original Python-defined :class:`.Table` objects. Areas where
158
this occurs includes server defaults, column-associated sequences and various
159
idosyncrasies regarding constraints and datatypes. Server side defaults may
160
be returned with cast directives (typically Postgresql will include a ``::<type>``
161
cast) or different quoting patterns than originally specified.
163
Another category of limitation includes schema structures for which reflection
164
is only partially or not yet defined. Recent improvements to reflection allow
165
things like views, indexes and foreign key options to be reflected. As of this
166
writing, structures like CHECK constraints, table comments, and triggers are
added
removed
doc/_sources/core/reflection.txt
