SQLAlchemy 1.4 Documentation
SQLAlchemy Core
- SQL Expression Language Tutorial (1.x API)
- SQL Statements and Expressions API
- Schema Definition Language
- Describing Databases with MetaData
- Reflecting Database Objects¶
- Overriding Reflected Columns
- Reflecting Views
- Reflecting All Tables at Once
- Reflecting Tables from Other Schemas
- Fine Grained Reflection with Inspector
Inspector
Inspector.__init__()
Inspector.default_schema_name
Inspector.from_engine()
Inspector.get_check_constraints()
Inspector.get_columns()
Inspector.get_foreign_keys()
Inspector.get_indexes()
Inspector.get_pk_constraint()
Inspector.get_schema_names()
Inspector.get_sequence_names()
Inspector.get_sorted_table_and_fkc_names()
Inspector.get_table_comment()
Inspector.get_table_names()
Inspector.get_table_options()
Inspector.get_temp_table_names()
Inspector.get_temp_view_names()
Inspector.get_unique_constraints()
Inspector.get_view_definition()
Inspector.get_view_names()
Inspector.has_sequence()
Inspector.has_table()
Inspector.reflect_table()
Inspector.reflecttable()
- Reflecting with Database-Agnostic Types
- Limitations of Reflection
- Column INSERT/UPDATE Defaults
- Defining Constraints and Indexes
- Customizing DDL
- SQL Datatype Objects
- Engine and Connection Use
- Core API Basics
- SQLAlchemy 2.0 Future (Core)
Project Versions
- Previous: Describing Databases with MetaData
- Next: Column INSERT/UPDATE Defaults
- Up: Home
- On this page:
- Reflecting Database Objects
- Overriding Reflected Columns
- Reflecting Views
- Reflecting All Tables at Once
- Reflecting Tables from Other Schemas
- Fine Grained Reflection with Inspector
Inspector
Inspector.__init__()
Inspector.default_schema_name
Inspector.from_engine()
Inspector.get_check_constraints()
Inspector.get_columns()
Inspector.get_foreign_keys()
Inspector.get_indexes()
Inspector.get_pk_constraint()
Inspector.get_schema_names()
Inspector.get_sequence_names()
Inspector.get_sorted_table_and_fkc_names()
Inspector.get_table_comment()
Inspector.get_table_names()
Inspector.get_table_options()
Inspector.get_temp_table_names()
Inspector.get_temp_view_names()
Inspector.get_unique_constraints()
Inspector.get_view_definition()
Inspector.get_view_names()
Inspector.has_sequence()
Inspector.has_table()
Inspector.reflect_table()
Inspector.reflecttable()
- Reflecting with Database-Agnostic Types
- Limitations of Reflection
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_with
argument:
>>> messages = Table("messages", metadata_obj, 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", metadata_obj, autoload_with=engine)
>>> 'shopping_carts' in metadata_obj.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", metadata_obj)
Of course, it’s a good idea to use autoload_with=engine
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",
... 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,
... )
See also
Working with Custom Types and Reflection - illustrates how the above column override technique applies to the use of custom datatypes with table reflection.
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)
Interaction of Schema-qualified Reflection with the Default Schema¶
Section Best Practices Summarized
In this section, we discuss SQLAlchemy’s reflection behavior regarding
tables that are visible in the “default schema” of a database session,
and how these interact with SQLAlchemy directives that include the schema
explicitly. As a best practice, ensure the “default” schema for a database
is just a single name, and not a list of names; for tables that are
part of this “default” schema and can be named without schema qualification
in DDL and SQL, leave corresponding Table.schema
and
similar schema parameters set to their default of None
.
As described at Specifying a Default Schema Name with MetaData, databases that have the concept of schemas usually also include the concept of a “default” schema. The reason for this is naturally that when one refers to table objects without a schema as is common, a schema-capable database will still consider that table to be in a “schema” somewhere. Some databases such as PostgreSQL take this concept further into the notion of a schema search path where multiple schema names can be considered in a particular database session to be “implicit”; referring to a table name that it’s any of those schemas will not require that the schema name be present (while at the same time it’s also perfectly fine if the schema name is present).
Since most relational databases therefore have the concept of a particular
table object which can be referred towards both in a schema-qualified way, as
well as an “implicit” way where no schema is present, this presents a
complexity for SQLAlchemy’s reflection
feature. Reflecting a table in
a schema-qualified manner will always populate its Table.schema
attribute and additionally affect how this Table
is organized
into the MetaData.tables
collection, that is, in a schema
qualified manner. Conversely, reflecting the same table in a non-schema
qualified manner will organize it into the MetaData.tables
collection without being schema qualified. The end result is that there
would be two separate Table
objects in the single
MetaData
collection representing the same table in the
actual database.
To illustrate the ramifications of this issue, consider tables from the
“project” schema in the previous example, and suppose also that the “project”
schema is the default schema of our database connection, or if using a database
such as PostgreSQL suppose the “project” schema is set up in the PostgreSQL
search_path
. This would mean that the database accepts the following
two SQL statements as equivalent:
-- schema qualified
SELECT message_id FROM project.messages
-- non-schema qualified
SELECT message_id FROM messages
This is not a problem as the table can be found in both ways. However
in SQLAlchemy, it’s the identity of the Table
object
that determines its semantic role within a SQL statement. Based on the current
decisions within SQLAlchemy, this means that if we reflect the same “messages” table in
both a schema-qualified as well as a non-schema qualified manner, we get
two Table
objects that will not be treated as
semantically equivalent:
>>> # 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
The above issue becomes more complicated when the tables being reflected contain
foreign key references to other tables. Suppose “messages” has a “project_id”
column which refers to rows in another schema-local table “projects”, meaning
there is a ForeignKeyConstraint
object that is part of the
definition of the “messages” table.
We can find ourselves in a situation where one MetaData
collection may contain as many as four Table
objects
representing these two database tables, where one or two of the additional
tables were generated by the reflection process; this is because when
the reflection process encounters a foreign key constraint on a table
being reflected, it branches out to reflect that referenced table as well.
The decision making it uses to assign the schema to this referenced
table is that SQLAlchemy will omit a default schema from the reflected
ForeignKeyConstraint
object if the owning
Table
also omits its schema name and also that these two objects
are in the same schema, but will include it if
it were not omitted.
The common scenario is when the reflection of a table in a schema qualified fashion then loads a related table that will also be performed in a schema qualified fashion:
>>> # reflect "messages" in a schema qualified fashion
>>> messages_table_1 = Table(
... "messages", metadata_obj, schema="project", autoload_with=someengine
... )
The above messages_table_1
will refer to projects
also in a schema
qualified fashion. This “projects” table will be reflected automatically by
the fact that “messages” refers to it:
>>> messages_table_1.c.project_id
Column('project_id', INTEGER(), ForeignKey('project.projects.project_id'), table=<messages>)
if some other part of the code reflects “projects” in a non-schema qualified fashion, there are now two projects tables that are not the same:
>>> # 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
The above confusion can cause problems within applications that use table
reflection to load up application-level Table
objects, as
well as within migration scenarios, in particular such as when using Alembic
Migrations to detect new tables and foreign key constraints.
The above behavior can be remedied by sticking to one simple practice:
Don’t include the
Table.schema
parameter for anyTable
that expects to be located in the default schema of the database.
For PostgreSQL and other databases that support a “search” path for schemas, add the following additional practice:
Keep the “search path” narrowed down to one schema only, which is the default schema.
See also
Remote-Schema Table Introspection and PostgreSQL search_path - additional details of this behavior as regards the PostgreSQL database.
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())
Object Name | Description |
---|---|
Performs database schema inspection. |
- 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 theinspect()
function, which may be passed anEngine
or aConnection
:from sqlalchemy import inspect, create_engine engine = create_engine('...') insp = inspect(engine)
Members
__init__(), default_schema_name, from_engine(), get_check_constraints(), get_columns(), get_foreign_keys(), get_indexes(), get_pk_constraint(), get_schema_names(), get_sequence_names(), get_sorted_table_and_fkc_names(), get_table_comment(), get_table_names(), get_table_options(), get_temp_table_names(), get_temp_view_names(), get_unique_constraints(), get_view_definition(), get_view_names(), has_sequence(), has_table(), reflect_table(), reflecttable()
Where above, the
Dialect
associated with the engine may opt to return anInspector
subclass that provides additional methods specific to the dialect’s target database.-
method
sqlalchemy.engine.reflection.Inspector.
__init__(bind)¶ Initialize a new
Inspector
.Deprecated since version 1.4: The __init__() method on
Inspector
is deprecated and will be removed in a future release. Please use theinspect()
function on anEngine
orConnection
in order to acquire anInspector
.- Parameters:
bind¶ – a
Connectable
, which is typically an instance ofEngine
orConnection
.
For a dialect-specific instance of
Inspector
, seeInspector.from_engine()
-
attribute
sqlalchemy.engine.reflection.Inspector.
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 anddbo
for SQL Server.
-
classmethod
sqlalchemy.engine.reflection.Inspector.
from_engine(bind)¶ Construct a new dialect-specific Inspector object from the given engine or connection.
Deprecated since version 1.4: The from_engine() method on
Inspector
is deprecated and will be removed in a future release. Please use theinspect()
function on anEngine
orConnection
in order to acquire anInspector
.- Parameters:
bind¶ – a
Connectable
, which is typically an instance ofEngine
orConnection
.
This method differs from direct a direct constructor call of
Inspector
in that theDialect
is given a chance to provide a dialect-specificInspector
instance, which may provide additional methods.See the example at
Inspector
.
-
method
sqlalchemy.engine.reflection.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 namesqltext
- the check constraint’s SQL expressiondialect_options
- may or may not be present; a dictionary with additional dialect-specific options for this CHECK constraintNew 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 schema of the database connection. For special quoting, use
quoted_name
.
New in version 1.1.0.
-
method
sqlalchemy.engine.reflection.Inspector.
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 nametype
- the type of this column; an instance ofTypeEngine
nullable
- boolean flag if the column is NULL or NOT NULLdefault
- the column’s server default value - this is returned as a string SQL expression.autoincrement
- indicates that the column is auto incremented - this is returned as a boolean or ‘auto’comment
- (optional) the comment on the column. Only some dialects return this keycomputed
- (optional) when present it indicates that this column is computed by the database. Only some dialects return this key. Returned as a dict with the keys:sqltext
- the expression used to generate this column returned as a string SQL expressionpersisted
- (optional) boolean that indicates if the column is stored in the table
New in version 1.3.16: - added support for computed reflection.
identity
- (optional) when present it indicates that this column is a generated always column. Only some dialects return this key. For a list of keywords on this dict seeIdentity
.New in version 1.4: - added support for identity column reflection.
dialect_options
- (optional) a dict with dialect specific options
- 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.
-
method
sqlalchemy.engine.reflection.Inspector.
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 keyreferred_schema
- the name of the referred schemareferred_table
- the name of the referred tablereferred_columns
- a list of column names in the referred table that correspond to constrained_columnsname
- 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
.
-
method
sqlalchemy.engine.reflection.Inspector.
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 namecolumn_names
- list of column names in orderunique
- booleancolumn_sorting
- optional dict mapping column names to tuple of sort keywords, which may includeasc
,desc
,nulls_first
,nulls_last
.New in version 1.3.5.
dialect_options
- dict of dialect-specific index options. May not be present for 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 schema of the database connection. For special quoting, use
quoted_name
.
-
method
sqlalchemy.engine.reflection.Inspector.
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 keyname
- 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
.
-
method
sqlalchemy.engine.reflection.Inspector.
get_schema_names()¶ Return all schema names.
-
method
sqlalchemy.engine.reflection.Inspector.
get_sequence_names(schema=None)¶ Return all sequence names in schema.
- Parameters:
schema¶ – Optional, retrieve names from a non-default schema. For special quoting, use
quoted_name
.
-
method
sqlalchemy.engine.reflection.Inspector.
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.New in version 1.0.-.
See also
sort_tables_and_constraints()
- similar method which works with an already-givenMetaData
.
-
method
sqlalchemy.engine.reflection.Inspector.
get_table_comment(table_name, schema=None, **kw)¶ Return information about the table comment for
table_name
.Given a string
table_name
and an optional stringschema
, 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.New in version 1.2.
-
method
sqlalchemy.engine.reflection.Inspector.
get_table_names(schema=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 atNone
, the database’s default schema is used, else the named schema is searched. If the database does not support named schemas, behavior is undefined ifschema
is not passed asNone
. For special quoting, usequoted_name
.
-
method
sqlalchemy.engine.reflection.Inspector.
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
.
-
method
sqlalchemy.engine.reflection.Inspector.
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.
New in version 1.0.0.
-
method
sqlalchemy.engine.reflection.Inspector.
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.
New in version 1.0.0.
-
method
sqlalchemy.engine.reflection.Inspector.
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 namecolumn_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
.
-
method
sqlalchemy.engine.reflection.Inspector.
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
.
-
method
sqlalchemy.engine.reflection.Inspector.
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
.
-
method
sqlalchemy.engine.reflection.Inspector.
has_sequence(sequence_name, schema=None)¶ Return True if the backend has a table of the given name.
- Parameters:
New in version 1.4.
-
method
sqlalchemy.engine.reflection.Inspector.
has_table(table_name, schema=None)¶ Return True if the backend has a table of the given name.
- Parameters:
New in version 1.4: - the
Inspector.has_table()
method replaces theEngine.has_table()
method.
-
method
sqlalchemy.engine.reflection.Inspector.
reflect_table(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 import inspect engine = create_engine('...') meta = MetaData() user_table = Table('user', meta) insp = inspect(engine) insp.reflect_table(user_table, None)
Changed in version 1.4: Renamed from
reflecttable
toreflect_table
-
method
sqlalchemy.engine.reflection.Inspector.
reflecttable(*args, **kwargs)¶ See reflect_table. This method name is deprecated
Deprecated since version 1.4: The
Inspector.reflecttable()
method is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. TheInspector.reflecttable()
method was renamed toInspector.reflect_table()
. This deprecated alias will be removed in a future release. (Background on SQLAlchemy 2.0 at: Migrating to SQLAlchemy 2.0)
-
method
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.
New in version 1.4: Added the TypeEngine.as_generic()
method
and additionally improved the use of the DDLEvents.column_reflect()
event such that it may be applied to a MetaData
object
for convenience.
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 ofColumn
(note this is separate fromserver_default
, which specifically is what’s available via reflection).Column information, e.g. data that might have been placed into the
Column.info
dictionaryThe association of a particular
Sequence
with a givenColumn
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.
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
Created using Sphinx 7.2.6. Documentation last generated: Wed 30 Oct 2024 02:18:58 PM EDT