Reflecting Database Objects¶

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",
...     metadata_obj,
...     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 that of a table:

my_view = Table("some_view", metadata, autoload_with=engine)

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_with=engine,
)

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:

metadata_obj = MetaData()
metadata_obj.reflect(bind=someengine)
users_table = metadata_obj.tables["users"]
addresses_table = metadata_obj.tables["addresses"]

metadata.reflect() also provides a handy way to clear or delete all the rows in a database:

metadata_obj = MetaData()
metadata_obj.reflect(bind=someengine)
for table in reversed(metadata_obj.sorted_tables):
    someengine.execute(table.delete())

Reflecting Tables from Other Schemas¶

The section Specifying the Schema Name introduces the concept of table schemas, which are namespaces within a database that contain tables and other objects, and which can be specified explicitly. The “schema” for a Table object, as well as for other objects like views, indexes and sequences, can be set up using the Table.schema parameter, and also as the default schema for a MetaData object using the MetaData.schema parameter.

The use of this schema parameter directly affects where the table reflection feature will look when it is asked to reflect objects. For example, given a MetaData object configured with a default schema name “project” via its MetaData.schema parameter:

>>> metadata_obj = MetaData(schema="project")

The MetaData.reflect() will then utilize that configured .schema for reflection:

>>> # uses `schema` configured in metadata_obj
>>> metadata_obj.reflect(someengine)

The end result is that Table objects from the “project” schema will be reflected, and they will be populated as schema-qualified with that name:

>>> metadata_obj.tables["project.messages"]
Table('messages', MetaData(), Column('message_id', INTEGER(), table=<messages>), schema='project')

Similarly, an individual Table object that includes the Table.schema parameter will also be reflected from that database schema, overriding any default schema that may have been configured on the owning MetaData collection:

>>> messages = Table("messages", metadata_obj, schema="project", autoload_with=someengine)
>>> messages
Table('messages', MetaData(), Column('message_id', INTEGER(), table=<messages>), schema='project')

Finally, the MetaData.reflect() method itself also allows a MetaData.reflect.schema parameter to be passed, so we could also load tables from the “project” schema for a default configured MetaData object:

>>> metadata_obj = MetaData()
>>> metadata_obj.reflect(someengine, schema="project")

We can call MetaData.reflect() any number of times with different MetaData.schema arguments (or none at all) to continue populating the MetaData object with more objects:

>>> # add tables from the "customer" schema
>>> metadata_obj.reflect(someengine, schema="customer")
>>> # add tables from the default schema
>>> metadata_obj.reflect(someengine)

-- schema qualified
SELECT message_id FROM project.messages

-- non-schema qualified
SELECT message_id FROM messages

>>> # reflect in non-schema qualified fashion
>>> messages_table_1 = Table("messages", metadata_obj, autoload_with=someengine)
>>> # reflect in schema qualified fashion
>>> messages_table_2 = Table(
...     "messages", metadata_obj, schema="project", autoload_with=someengine
... )
>>> # two different objects
>>> messages_table_1 is messages_table_2
False
>>> # stored in two different ways
>>> metadata.tables["messages"] is messages_table_1
True
>>> metadata.tables["project.messages"] is messages_table_2
True

>>> # reflect "messages" in a schema qualified fashion
>>> messages_table_1 = Table(
...     "messages", metadata_obj, schema="project", autoload_with=someengine
... )

>>> messages_table_1.c.project_id
Column('project_id', INTEGER(), ForeignKey('project.projects.project_id'), table=<messages>)

>>> # reflect "projects" in a non-schema qualified fashion
>>> projects_table_1 = Table("projects", metadata_obj, autoload_with=someengine)

>>> # messages does not refer to projects_table_1 above
>>> messages_table_1.c.project_id.references(projects_table_1.c.project_id)
False

>>> it refers to this one
>>> projects_table_2 = metadata_obj.tables["project.projects"]
>>> messages_table_1.c.project_id.references(projects_table_2.c.project_id)
True

>>> they're different, as one non-schema qualified and the other one is
>>> projects_table_1 is projects_table_2
False

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 import inspect

engine = create_engine("...")
insp = inspect(engine)
print(insp.get_table_names())

Reflecting with Database-Agnostic Types¶

When the columns of a table are reflected, using either the Table.autoload_with parameter of Table or the Inspector.get_columns() method of Inspector, the datatypes will be as specific as possible to the target database. This means that if an “integer” datatype is reflected from a MySQL database, the type will be represented by the sqlalchemy.dialects.mysql.INTEGER class, which includes MySQL-specific attributes such as “display_width”. Or on PostgreSQL, a PostgreSQL-specific datatype such as sqlalchemy.dialects.postgresql.INTERVAL or sqlalchemy.dialects.postgresql.ENUM may be returned.

There is a use case for reflection which is that a given Table is to be transferred to a different vendor database. To suit this use case, there is a technique by which these vendor-specific datatypes can be converted on the fly to be instance of SQLAlchemy backend-agnostic datatypes, for the examples above types such as Integer, Interval and Enum. This may be achieved by intercepting the column reflection using the DDLEvents.column_reflect() event in conjunction with the TypeEngine.as_generic() method.

Given a table in MySQL (chosen because MySQL has a lot of vendor-specific datatypes and options):

CREATE TABLE IF NOT EXISTS my_table (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    data1 VARCHAR(50) CHARACTER SET latin1,
    data2 MEDIUMINT(4),
    data3 TINYINT(2)
)

The above table includes MySQL-only integer types MEDIUMINT and TINYINT as well as a VARCHAR that includes the MySQL-only CHARACTER SET option. If we reflect this table normally, it produces a Table object that will contain those MySQL-specific datatypes and options:

>>> from sqlalchemy import MetaData, Table, create_engine
>>> mysql_engine = create_engine("mysql://scott:tiger@localhost/test")
>>> metadata_obj = MetaData()
>>> my_mysql_table = Table("my_table", metadata_obj, autoload_with=mysql_engine)

The above example reflects the above table schema into a new Table object. We can then, for demonstration purposes, print out the MySQL-specific “CREATE TABLE” statement using the CreateTable construct:

>>> from sqlalchemy.schema import CreateTable
>>> print(CreateTable(my_mysql_table).compile(mysql_engine))
CREATE TABLE my_table (
id INTEGER(11) NOT NULL AUTO_INCREMENT,
data1 VARCHAR(50) CHARACTER SET latin1,
data2 MEDIUMINT(4),
data3 TINYINT(2),
PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Above, the MySQL-specific datatypes and options were maintained. If we wanted a Table that we could instead transfer cleanly to another database vendor, replacing the special datatypes sqlalchemy.dialects.mysql.MEDIUMINT and sqlalchemy.dialects.mysql.TINYINT with Integer, we can choose instead to “genericize” the datatypes on this table, or otherwise change them in any way we’d like, by establishing a handler using the DDLEvents.column_reflect() event. The custom handler will make use of the TypeEngine.as_generic() method to convert the above MySQL-specific type objects into generic ones, by replacing the "type" entry within the column dictionary entry that is passed to the event handler. The format of this dictionary is described at Inspector.get_columns():

>>> from sqlalchemy import event
>>> metadata_obj = MetaData()

>>> @event.listens_for(metadata_obj, "column_reflect")
>>> def genericize_datatypes(inspector, tablename, column_dict):
...     column_dict["type"] = column_dict["type"].as_generic()

>>> my_generic_table = Table("my_table", metadata_obj, autoload_with=mysql_engine)

We now get a new Table that is generic and uses Integer for those datatypes. We can now emit a “CREATE TABLE” statement for example on a PostgreSQL database:

>>> pg_engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
>>> my_generic_table.create(pg_engine)
CREATE TABLE my_table (
    id SERIAL NOT NULL,
    data1 VARCHAR(50),
    data2 INTEGER,
    data3 INTEGER,
    PRIMARY KEY (id)
)

Noting above also that SQLAlchemy will usually make a decent guess for other behaviors, such as that the MySQL AUTO_INCREMENT directive is represented in PostgreSQL most closely using the SERIAL auto-incrementing datatype.

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.