1.. currentmodule:: sqlalchemy.schema 2 3.. _metadata_reflection_toplevel: 4.. _metadata_reflection: 5 6 7Reflecting Database Objects 8=========================== 9 10A :class:`~sqlalchemy.schema.Table` object can be instructed to load 11information about itself from the corresponding database schema object already 12existing within the database. This process is called *reflection*. In the 13most simple case you need only specify the table name, a :class:`~sqlalchemy.schema.MetaData` 14object, and the ``autoload_with`` argument:: 15 16 >>> messages = Table('messages', meta, autoload_with=engine) 17 >>> [c.name for c in messages.columns] 18 ['message_id', 'message_name', 'date'] 19 20The above operation will use the given engine to query the database for 21information about the ``messages`` table, and will then generate 22:class:`~sqlalchemy.schema.Column`, :class:`~sqlalchemy.schema.ForeignKey`, 23and other objects corresponding to this information as though the 24:class:`~sqlalchemy.schema.Table` object were hand-constructed in Python. 25 26When tables are reflected, if a given table references another one via foreign 27key, a second :class:`~sqlalchemy.schema.Table` object is created within the 28:class:`~sqlalchemy.schema.MetaData` object representing the connection. 29Below, assume the table ``shopping_cart_items`` references a table named 30``shopping_carts``. Reflecting the ``shopping_cart_items`` table has the 31effect such that the ``shopping_carts`` table will also be loaded:: 32 33 >>> shopping_cart_items = Table('shopping_cart_items', meta, autoload_with=engine) 34 >>> 'shopping_carts' in meta.tables: 35 True 36 37The :class:`~sqlalchemy.schema.MetaData` has an interesting "singleton-like" 38behavior such that if you requested both tables individually, 39:class:`~sqlalchemy.schema.MetaData` will ensure that exactly one 40:class:`~sqlalchemy.schema.Table` object is created for each distinct table 41name. The :class:`~sqlalchemy.schema.Table` constructor actually returns to 42you the already-existing :class:`~sqlalchemy.schema.Table` object if one 43already exists with the given name. Such as below, we can access the already 44generated ``shopping_carts`` table just by naming it:: 45 46 shopping_carts = Table('shopping_carts', meta) 47 48Of course, it's a good idea to use ``autoload_with=engine`` with the above table 49regardless. This is so that the table's attributes will be loaded if they have 50not been already. The autoload operation only occurs for the table if it 51hasn't already been loaded; once loaded, new calls to 52:class:`~sqlalchemy.schema.Table` with the same name will not re-issue any 53reflection queries. 54 55.. _reflection_overriding_columns: 56 57Overriding Reflected Columns 58---------------------------- 59 60Individual columns can be overridden with explicit values when reflecting 61tables; this is handy for specifying custom datatypes, constraints such as 62primary keys that may not be configured within the database, etc.:: 63 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 67 ... # additional Column objects which require no change are reflected normally 68 ... autoload_with=some_engine) 69 70.. seealso:: 71 72 :ref:`custom_and_decorated_types_reflection` - illustrates how the above 73 column override technique applies to the use of custom datatypes with 74 table reflection. 75 76Reflecting Views 77---------------- 78 79The reflection system can also reflect views. Basic usage is the same as that 80of a table:: 81 82 my_view = Table("some_view", metadata, autoload_with=engine) 83 84Above, ``my_view`` is a :class:`~sqlalchemy.schema.Table` object with 85:class:`~sqlalchemy.schema.Column` objects representing the names and types of 86each column within the view "some_view". 87 88Usually, it's desired to have at least a primary key constraint when 89reflecting a view, if not foreign keys as well. View reflection doesn't 90extrapolate these constraints. 91 92Use the "override" technique for this, specifying explicitly those columns 93which are part of the primary key or have foreign key constraints:: 94 95 my_view = Table("some_view", metadata, 96 Column("view_id", Integer, primary_key=True), 97 Column("related_thing", Integer, ForeignKey("othertable.thing_id")), 98 autoload_with=engine 99 ) 100 101Reflecting All Tables at Once 102----------------------------- 103 104The :class:`~sqlalchemy.schema.MetaData` object can also get a listing of 105tables and reflect the full set. This is achieved by using the 106:func:`~sqlalchemy.schema.MetaData.reflect` method. After calling it, all 107located tables are present within the :class:`~sqlalchemy.schema.MetaData` 108object's dictionary of tables:: 109 110 metadata_obj = MetaData() 111 metadata_obj.reflect(bind=someengine) 112 users_table = metadata_obj.tables['users'] 113 addresses_table = metadata_obj.tables['addresses'] 114 115``metadata.reflect()`` also provides a handy way to clear or delete all the rows in a database:: 116 117 metadata_obj = MetaData() 118 metadata_obj.reflect(bind=someengine) 119 for table in reversed(metadata_obj.sorted_tables): 120 someengine.execute(table.delete()) 121 122.. _metadata_reflection_inspector: 123 124Fine Grained Reflection with Inspector 125-------------------------------------- 126 127A low level interface which provides a backend-agnostic system of loading 128lists of schema, table, column, and constraint descriptions from a given 129database is also available. This is known as the "Inspector":: 130 131 from sqlalchemy import create_engine 132 from sqlalchemy import inspect 133 engine = create_engine('...') 134 insp = inspect(engine) 135 print(insp.get_table_names()) 136 137.. autoclass:: sqlalchemy.engine.reflection.Inspector 138 :members: 139 :undoc-members: 140 141.. _metadata_reflection_dbagnostic_types: 142 143Reflecting with Database-Agnostic Types 144--------------------------------------- 145 146When the columns of a table are reflected, using either the 147:paramref:`_schema.Table.autoload_with` parameter of :class:`_schema.Table` or 148the :meth:`_reflection.Inspector.get_columns` method of 149:class:`_reflection.Inspector`, the datatypes will be as specific as possible 150to the target database. This means that if an "integer" datatype is reflected 151from a MySQL database, the type will be represented by the 152:class:`sqlalchemy.dialects.mysql.INTEGER` class, which includes MySQL-specific 153attributes such as "display_width". Or on PostgreSQL, a PostgreSQL-specific 154datatype such as :class:`sqlalchemy.dialects.postgresql.INTERVAL` or 155:class:`sqlalchemy.dialects.postgresql.ENUM` may be returned. 156 157There is a use case for reflection which is that a given :class:`_schema.Table` 158is to be transferred to a different vendor database. To suit this use case, 159there is a technique by which these vendor-specific datatypes can be converted 160on the fly to be instance of SQLAlchemy backend-agnostic datatypes, for 161the examples above types such as :class:`_types.Integer`, :class:`_types.Interval` 162and :class:`_types.Enum`. This may be achieved by intercepting the 163column reflection using the :meth:`_events.DDLEvents.column_reflect` event 164in conjunction with the :meth:`_types.TypeEngine.as_generic` method. 165 166Given a table in MySQL (chosen because MySQL has a lot of vendor-specific 167datatypes and options):: 168 169 CREATE TABLE IF NOT EXISTS my_table ( 170 id INTEGER PRIMARY KEY AUTO_INCREMENT, 171 data1 VARCHAR(50) CHARACTER SET latin1, 172 data2 MEDIUMINT(4), 173 data3 TINYINT(2) 174 ) 175 176The above table includes MySQL-only integer types ``MEDIUMINT`` and 177``TINYINT`` as well as a ``VARCHAR`` that includes the MySQL-only ``CHARACTER 178SET`` option. If we reflect this table normally, it produces a 179:class:`_schema.Table` object that will contain those MySQL-specific datatypes 180and options: 181 182.. sourcecode:: pycon+sql 183 184 >>> from sqlalchemy import MetaData, Table, create_engine 185 >>> mysql_engine = create_engine("mysql://scott:tiger@localhost/test") 186 >>> metadata_obj = MetaData() 187 >>> my_mysql_table = Table("my_table", metadata_obj, autoload_with=mysql_engine) 188 189The above example reflects the above table schema into a new :class:`_schema.Table` 190object. We can then, for demonstration purposes, print out the MySQL-specific 191"CREATE TABLE" statement using the :class:`_schema.CreateTable` construct: 192 193.. sourcecode:: pycon+sql 194 195 >>> from sqlalchemy.schema import CreateTable 196 >>> print(CreateTable(my_mysql_table).compile(mysql_engine)) 197 {opensql}CREATE TABLE my_table ( 198 id INTEGER(11) NOT NULL AUTO_INCREMENT, 199 data1 VARCHAR(50) CHARACTER SET latin1, 200 data2 MEDIUMINT(4), 201 data3 TINYINT(2), 202 PRIMARY KEY (id) 203 )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 204 205 206Above, the MySQL-specific datatypes and options were maintained. If we wanted 207a :class:`_schema.Table` that we could instead transfer cleanly to another 208database vendor, replacing the special datatypes 209:class:`sqlalchemy.dialects.mysql.MEDIUMINT` and 210:class:`sqlalchemy.dialects.mysql.TINYINT` with :class:`_types.Integer`, we can 211choose instead to "genericize" the datatypes on this table, or otherwise change 212them in any way we'd like, by establishing a handler using the 213:meth:`_events.DDLEvents.column_reflect` event. The custom handler will make use 214of the :meth:`_types.TypeEngine.as_generic` method to convert the above 215MySQL-specific type objects into generic ones, by replacing the ``"type"`` 216entry within the column dictionary entry that is passed to the event handler. 217The format of this dictionary is described at :meth:`_reflection.Inspector.get_columns`: 218 219.. sourcecode:: pycon+sql 220 221 >>> from sqlalchemy import event 222 >>> metadata_obj = MetaData() 223 224 >>> @event.listens_for(metadata_obj, "column_reflect") 225 >>> def genericize_datatypes(inspector, tablename, column_dict): 226 ... column_dict["type"] = column_dict["type"].as_generic() 227 228 >>> my_generic_table = Table("my_table", metadata_obj, autoload_with=mysql_engine) 229 230We now get a new :class:`_schema.Table` that is generic and uses 231:class:`_types.Integer` for those datatypes. We can now emit a 232"CREATE TABLE" statement for example on a PostgreSQL database: 233 234.. sourcecode:: pycon+sql 235 236 >>> pg_engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True) 237 >>> my_generic_table.create(pg_engine) 238 {opensql}CREATE TABLE my_table ( 239 id SERIAL NOT NULL, 240 data1 VARCHAR(50), 241 data2 INTEGER, 242 data3 INTEGER, 243 PRIMARY KEY (id) 244 ) 245 246Noting above also that SQLAlchemy will usually make a decent guess for other 247behaviors, such as that the MySQL ``AUTO_INCREMENT`` directive is represented 248in PostgreSQL most closely using the ``SERIAL`` auto-incrementing datatype. 249 250.. versionadded:: 1.4 Added the :meth:`_types.TypeEngine.as_generic` method 251 and additionally improved the use of the :meth:`_events.DDLEvents.column_reflect` 252 event such that it may be applied to a :class:`_schema.MetaData` object 253 for convenience. 254 255 256Limitations of Reflection 257------------------------- 258 259It's important to note that the reflection process recreates :class:`_schema.Table` 260metadata using only information which is represented in the relational database. 261This process by definition cannot restore aspects of a schema that aren't 262actually stored in the database. State which is not available from reflection 263includes but is not limited to: 264 265* Client side defaults, either Python functions or SQL expressions defined using 266 the ``default`` keyword of :class:`_schema.Column` (note this is separate from ``server_default``, 267 which specifically is what's available via reflection). 268 269* Column information, e.g. data that might have been placed into the 270 :attr:`_schema.Column.info` dictionary 271 272* The value of the ``.quote`` setting for :class:`_schema.Column` or :class:`_schema.Table` 273 274* The association of a particular :class:`.Sequence` with a given :class:`_schema.Column` 275 276The relational database also in many cases reports on table metadata in a 277different format than what was specified in SQLAlchemy. The :class:`_schema.Table` 278objects returned from reflection cannot be always relied upon to produce the identical 279DDL as the original Python-defined :class:`_schema.Table` objects. Areas where 280this occurs includes server defaults, column-associated sequences and various 281idiosyncrasies regarding constraints and datatypes. Server side defaults may 282be returned with cast directives (typically PostgreSQL will include a ``::<type>`` 283cast) or different quoting patterns than originally specified. 284 285Another category of limitation includes schema structures for which reflection 286is only partially or not yet defined. Recent improvements to reflection allow 287things like views, indexes and foreign key options to be reflected. As of this 288writing, structures like CHECK constraints, table comments, and triggers are 289not reflected. 290 291