Describing Databases with MetaData

Describing Databases with MetaData

This section discusses the fundamental Table, Columnand MetaData objects.

A collection of metadata entities is stored in an object aptly namedMetaData:

from sqlalchemy import *
 
metadata = MetaData()

MetaData is a container object that keeps togethermany different features of a database (or multiple databases) being described.

To represent a table, use the Table class. Its twoprimary arguments are the table name, then theMetaData object which it will be associated with.The remaining positional arguments are mostlyColumn objects describing each column:

user = Table('user', metadata,
    Column('user_id', Integer, primary_key=True),
    Column('user_name', String(16), nullable=False),
    Column('email_address', String(60)),
    Column('nickname', String(50), nullable=False)
)

Above, a table called user is described, which contains four columns. Theprimary key of the table consists of the userid column. Multiple columnsmay be assigned the primary_key=True flag which denotes a multi-columnprimary key, known as a _composite primary key.

Note also that each column describes its datatype using objects correspondingto genericized types, such as Integer andString. SQLAlchemy features dozens of types ofvarying levels of specificity as well as the ability to create custom types.Documentation on the type system can be found at Column and Data Types.

Accessing Tables and Columns

The MetaData object contains all of the schemaconstructs we’ve associated with it. It supports a few methods of accessingthese table objects, such as the sorted_tables accessor which returns alist of each Table object in order of foreign keydependency (that is, each table is preceded by all tables which itreferences):

>>> for t in metadata.sorted_tables:
...    print(t.name)
user
user_preference
invoice
invoice_item

In most cases, individual Table objects have beenexplicitly declared, and these objects are typically accessed directly asmodule-level variables in an application. Once aTable has been defined, it has a full set ofaccessors which allow inspection of its properties. Given the followingTable definition:

employees = Table('employees', metadata,
    Column('employee_id', Integer, primary_key=True),
    Column('employee_name', String(60), nullable=False),
    Column('employee_dept', Integer, ForeignKey("departments.department_id"))
)

Note the ForeignKey object used in this table -this construct defines a reference to a remote table, and is fully describedin Defining Foreign Keys. Methods of accessing information about thistable include:

# access the column "EMPLOYEE_ID":
employees.columns.employee_id
 
# or just
employees.c.employee_id
 
# via string
employees.c['employee_id']
 
# iterate through all columns
for c in employees.c:
    print(c)
 
# get the table's primary key columns
for primary_key in employees.primary_key:
    print(primary_key)
 
# get the table's foreign key objects:
for fkey in employees.foreign_keys:
    print(fkey)
 
# access the table's MetaData:
employees.metadata
 
# access the table's bound Engine or Connection, if its MetaData is bound:
employees.bind
 
# access a column's name, type, nullable, primary key, foreign key
employees.c.employee_id.name
employees.c.employee_id.type
employees.c.employee_id.nullable
employees.c.employee_id.primary_key
employees.c.employee_dept.foreign_keys
 
# get the "key" of a column, which defaults to its name, but can
# be any user-defined string:
employees.c.employee_name.key
 
# access a column's table:
employees.c.employee_id.table is employees
 
# get the table related by a foreign key
list(employees.c.employee_dept.foreign_keys)[0].column.table

Creating and Dropping Database Tables

Once you’ve defined some Table objects, assumingyou’re working with a brand new database one thing you might want to do isissue CREATE statements for those tables and their related constructs (as anaside, it’s also quite possible that you don’t want to do this, if youalready have some preferred methodology such as tools included with yourdatabase or an existing scripting system - if that’s the case, feel free toskip this section - SQLAlchemy has no requirement that it be used to createyour tables).

The usual way to issue CREATE is to usecreate_all() on theMetaData object. This method will issue queriesthat first check for the existence of each individual table, and if not foundwill issue the CREATE statements:

engine = create_engine('sqlite:///:memory:')metadata = MetaData()user = Table('user', metadata,    Column('user_id', Integer, primary_key=True),    Column('user_name', String(16), nullable=False),    Column('email_address', String(60), key='email'),    Column('nickname', String(50), nullable=False))user_prefs = Table('user_prefs', metadata,    Column('pref_id', Integer, primary_key=True),    Column('user_id', Integer, ForeignKey("user.user_id"), nullable=False),    Column('pref_name', String(40), nullable=False),    Column('pref_value', String(100)))sqlmetadata.create_all(engine)PRAGMA table_info(user){}CREATE TABLE user(        user_id INTEGER NOT NULL PRIMARY KEY,        user_name VARCHAR(16) NOT NULL,        email_address VARCHAR(60),        nickname VARCHAR(50) NOT NULL)PRAGMA table_info(user_prefs){}CREATE TABLE user_prefs(        pref_id INTEGER NOT NULL PRIMARY KEY,        user_id INTEGER NOT NULL REFERENCES user(user_id),        pref_name VARCHAR(40) NOT NULL,        pref_value VARCHAR(100))

create_all() creates foreign key constraintsbetween tables usually inline with the table definition itself, and for thisreason it also generates the tables in order of their dependency. There areoptions to change this behavior such that ALTER TABLE is used instead.

Dropping all tables is similarly achieved using thedrop_all() method. This method does theexact opposite of create_all() - thepresence of each table is checked first, and tables are dropped in reverseorder of dependency.

Creating and dropping individual tables can be done via the create() anddrop() methods of Table. These methods bydefault issue the CREATE or DROP regardless of the table being present:

engine = create_engine('sqlite:///:memory:')
 
meta = MetaData()
 
employees = Table('employees', meta,
    Column('employee_id', Integer, primary_key=True),
    Column('employee_name', String(60), nullable=False, key='name'),
    Column('employee_dept', Integer, ForeignKey("departments.department_id"))
)
sqlemployees.create(engine)
CREATE TABLE employees(
employee_id SERIAL NOT NULL PRIMARY KEY,
employee_name VARCHAR(60) NOT NULL,
employee_dept INTEGER REFERENCES departments(department_id)
)
{}

drop() method:

sqlemployees.drop(engine)
DROP TABLE employees
{}

To enable the “check first for the table existing” logic, add thecheckfirst=True argument to create() or drop():

employees.create(engine, checkfirst=True)
employees.drop(engine, checkfirst=False)

Altering Schemas through Migrations

While SQLAlchemy directly supports emitting CREATE and DROP statements for schemaconstructs, the ability to alter those constructs, usually via the ALTER statementas well as other database-specific constructs, is outside of the scope of SQLAlchemyitself. While it’s easy enough to emit ALTER statements and similar by hand,such as by passing a string to Connection.execute() or by using theDDL construct, it’s a common practice to automate the maintenance ofdatabase schemas in relation to application code using schema migration tools.

There are two major migration tools available for SQLAlchemy:

Alembic - Written by the author of SQLAlchemy,Alembic features a highly customizable environment and a minimalistic usage pattern,supporting such features as transactional DDL, automatic generation of “candidate”migrations, an “offline” mode which generates SQL scripts, and support for branchresolution.
SQLAlchemy-Migrate - The originalmigration tool for SQLAlchemy, SQLAlchemy-Migrate is still used by projectssuch as Openstack, however is being superseded by Alembic.

Specifying the Schema Name

Some databases support the concept of multiple schemas. ATable can reference this by specifying theschema keyword argument:

financial_info = Table('financial_info', meta,
    Column('id', Integer, primary_key=True),
    Column('value', String(100), nullable=False),
    schema='remote_banks'
)

Within the MetaData collection, this table will beidentified by the combination of financial_info and remote_banks. Ifanother table called financial_info is referenced without theremote_banks schema, it will refer to a differentTable. ForeignKeyobjects can specify references to columns in this table using the formremote_banks.financial_info.id.

The schema argument should be used for any name qualifiers required,including Oracle’s “owner” attribute and similar. It also can accommodate adotted name for longer schemes:

schema="dbo.scott"

Backend-Specific Options

Table supports database-specific options. Forexample, MySQL has different table backend types, including “MyISAM” and“InnoDB”. This can be expressed with Table usingmysql_engine:

addresses = Table('engine_email_addresses', meta,
    Column('address_id', Integer, primary_key=True),
    Column('remote_user_id', Integer, ForeignKey(users.c.user_id)),
    Column('email_address', String(20)),
    mysql_engine='InnoDB'
)

Other backends may support table-level options as well - these would bedescribed in the individual documentation sections for each dialect.

Column, Table, MetaData API

sqlalchemy.schema.BLANK_SCHEMA
Symbol indicating that a Table or Sequenceshould have ‘None’ for its schema, even if the parentMetaData has specified a schema.

See also

MetaData.schema

Table.schema

Sequence.schema

New in version 1.0.14.

class sqlalchemy.schema.Column(*args, **kwargs)
Bases: sqlalchemy.sql.base.DialectKWArgs, sqlalchemy.schema.SchemaItem, sqlalchemy.sql.expression.ColumnClause

Represents a column in a database table.

eq(other)

inherited from theeq()method ofColumnOperators

Implement the == operator.

In a column context, produces the clause a = b.If the target is None, produces a IS NULL.

init(*args, **kwargs)
Construct a new Column object.
- Parameters
- - name –

The name of this column as represented in the database.This argument may be the first positional argument, or specifiedvia keyword.

Names which contain no upper case characterswill be treated as case insensitive names, and will not be quotedunless they are a reserved word. Names with any number of uppercase characters will be quoted and sent exactly. Note that thisbehavior applies even for databases which standardize uppercase names as case insensitive such as Oracle.

The name field may be omitted at construction time and appliedlater, at any time before the Column is associated with aTable. This is to support convenientusage within the declarative extension.

type_ –

The column’s type, indicated using an instance whichsubclasses TypeEngine. If no argumentsare required for the type, the class of the type can be sentas well, e.g.:

# use a type with arguments
Column('data', String(50))
 
# use no arguments
Column('level', Integer)

The type argument may be the second positional argumentor specified by keyword.

If the type is None or is omitted, it will first default tothe special type NullType. If and when thisColumn is made to refer to another column usingForeignKey and/or ForeignKeyConstraint, the typeof the remote-referenced column will be copied to this column aswell, at the moment that the foreign key is resolved against thatremote Column object.

Changed in version 0.9.0: Support for propagation of type to a Column from itsForeignKey object has been improved and should bemore reliable and timely.

*args – Additional positional arguments include variousSchemaItem derived constructs which will be appliedas options to the column. These include instances ofConstraint, ForeignKey, ColumnDefault,and Sequence. In some cases an equivalent keywordargument is available such as server_default, defaultand unique.

autoincrement –

Set up “auto increment” semantics for an integerprimary key column. The default value is the string "auto"which indicates that a single-column primary key that is ofan INTEGER type with no stated client-side or python-side defaultsshould receive auto increment semantics automatically;all other varieties of primary key columns will not. Thisincludes that DDL such as PostgreSQL SERIAL or MySQLAUTO_INCREMENT will be emitted for this column during a tablecreate, as well as that the column is assumed to generate newinteger primary key values when an INSERT statement invokes whichwill be retrieved by the dialect.

The flag may be set to True to indicate that a column whichis part of a composite (e.g. multi-column) primary key shouldhave autoincrement semantics, though note that only one columnwithin a primary key may have this setting. It can alsobe set to True to indicate autoincrement semantics on acolumn that has a client-side or server-side default configured,however note that not all dialects can accommodate all stylesof default as an “autoincrement”. It can also beset to False on a single-column primary key that has adatatype of INTEGER in order to disable auto increment semanticsfor that column.

Changed in version 1.1: The autoincrement flag now defaults to"auto" which indicates autoincrement semantics by defaultfor single-column integer primary keys only; for composite(multi-column) primary keys, autoincrement is never implicitlyenabled; as always, autoincrement=True will allow forat most one of those columns to be an “autoincrement” column.autoincrement=True may also be set on a Columnthat has an explicit client-side or server-side default,subject to limitations of the backend database and dialect.

The setting only has an effect for columns which are:

Integer derived (i.e. INT, SMALLINT, BIGINT).

Part of the primary key

Not referring to another column via ForeignKey, unlessthe value is specified as 'ignore_fk':

# turn on autoincrement for this column despite
# the ForeignKey()
Column('id', ForeignKey('other.id'),
            primary_key=True, autoincrement='ignore_fk')

It is typically not desirable to have “autoincrement” enabled on acolumn that refers to another via foreign key, as such a column isrequired to refer to a value that originates from elsewhere.

The setting has these two effects on columns that meet theabove criteria:

DDL issued for the column will include database-specifickeywords intended to signify this column as an“autoincrement” column, such as AUTO INCREMENT on MySQL,SERIAL on PostgreSQL, and IDENTITY on MS-SQL. It doesnot issue AUTOINCREMENT for SQLite since this is aspecial SQLite flag that is not required for autoincrementingbehavior.

See also

SQLite Auto Incrementing Behavior

The column will be considered to be available using an“autoincrement” method specific to the backend database, suchas calling upon cursor.lastrowid, using RETURNING in anINSERT statement to get at a sequence-generated value, or usingspecial functions such as “SELECT scope_identity()”.These methods are highly specific to the DBAPIs and databases inuse and vary greatly, so care should be taken when associatingautoincrement=True with a custom default generation function.

default –

A scalar, Python callable, orColumnElement expression representing thedefault value for this column, which will be invoked upon insertif this column is otherwise not specified in the VALUES clause ofthe insert. This is a shortcut to using ColumnDefault asa positional argument; see that class for full detail on thestructure of the argument.

Contrast this argument to Column.server_defaultwhich creates a default generator on the database side.

See also

Column INSERT/UPDATE Defaults

doc – optional String that can be used by the ORM or similarto document attributes on the Python side. This attribute doesnot render SQL comments; use the Column.commentparameter for this purpose.

key – An optional string identifier which will identify thisColumn object on the Table. When a key is provided,this is the only identifier referencing the Column within theapplication, including ORM attribute mapping; the name fieldis used only when rendering SQL.

index – When True, indicates that the column is indexed.This is a shortcut for using a Index construct on thetable. To specify indexes with explicit names or indexes thatcontain multiple columns, use the Index constructinstead.

info – Optional data dictionary which will be populated into theSchemaItem.info attribute of this object.

nullable – When set to False, will cause the “NOT NULL”phrase to be added when generating DDL for the column. WhenTrue, will normally generate nothing (in SQL this defaults to“NULL”), except in some very specific backend-specific edge caseswhere “NULL” may render explicitly. Defaults to True unlessprimary_key is also True, in which case itdefaults to False. This parameter is only used when issuingCREATE TABLE statements.

onupdate –

A scalar, Python callable, orClauseElement representing adefault value to be applied to the column within UPDATEstatements, which will be invoked upon update if this column is notpresent in the SET clause of the update. This is a shortcut tousing ColumnDefault as a positional argument withfor_update=True.

See also

Column INSERT/UPDATE Defaults - complete discussion of onupdate

primary_key – If True, marks this column as a primary keycolumn. Multiple columns can have this flag set to specifycomposite primary keys. As an alternative, the primary key of aTable can be specified via an explicitPrimaryKeyConstraint object.

server_default –

A FetchedValue instance, str, Unicodeor text() construct representingthe DDL DEFAULT value for the column.

String types will be emitted as-is, surrounded by single quotes:

Column('x', Text, server_default="val")
 
x TEXT DEFAULT 'val'

A text() expression will berendered as-is, without quotes:

Column('y', DateTime, server_default=text('NOW()'))
 
y DATETIME DEFAULT NOW()

Strings and text() will be converted into aDefaultClause object upon initialization.

Use FetchedValue to indicate that an already-existingcolumn will generate a default value on the database side whichwill be available to SQLAlchemy for post-fetch after inserts. Thisconstruct does not specify any DDL and the implementation is leftto the database, such as via a trigger.

See also

Server-invoked DDL-Explicit Default Expressions - complete discussion of server sidedefaults

server_onupdate –

    - A [<code>FetchedValue</code>](https://docs.sqlalchemy.org/en/13/core/defaults.html#sqlalchemy.schema.FetchedValue) instance
    -

representing a database-side default generation function,such as a trigger. Thisindicates to SQLAlchemy that a newly generated value will beavailable after updates. This construct does not actuallyimplement any kind of generation function within the database,which instead must be specified separately.

quote – Force quoting of this column’s name on or off,corresponding to True or False. When left at its defaultof None, the column identifier will be quoted according towhether the name is case sensitive (identifiers with at least oneupper case character are treated as case sensitive), or if it’s areserved word. This flag is only needed to force quoting of areserved word which is not known by the SQLAlchemy dialect.

unique – When True, indicates that this column contains aunique constraint, or if index is True as well, indicatesthat the Index should be created with the unique flag.To specify multiple columns in the constraint/index or to specifyan explicit name, use the UniqueConstraint orIndex constructs explicitly.

system –

When True, indicates this is a “system” column,that is a column which is automatically made available by thedatabase, and should not be included in the columns list for aCREATE TABLE statement.

For more elaborate scenarios where columns should beconditionally rendered differently on different backends,consider custom compilation rules for CreateColumn.

comment –

Optional string that will render an SQL comment ontable creation.

New in version 1.2: Added the Column.commentparameter to Column.

le(other)

inherited from thele()method ofColumnOperators

Implement the <= operator.

In a column context, produces the clause a <= b.

lt(other)

inherited from thelt()method ofColumnOperators

Implement the < operator.

In a column context, produces the clause a < b.

ne(other)

inherited from thene()method ofColumnOperators

Implement the != operator.

In a column context, produces the clause a != b.If the target is None, produces a IS NOT NULL.

all()[](https://docs.sqlalchemy.org/en/13/core/#sqlalchemy.schema.Column.all)

inherited from theall_()method ofColumnOperators

Produce a all_() clause against theparent object.

This operator is only appropriate against a scalar subqueryobject, or for some backends an column expression that isagainst the ARRAY type, e.g.:

# postgresql '5 = ALL (somearray)'
expr = 5 == mytable.c.somearray.all_()
 
# mysql '5 = ALL (SELECT value FROM table)'
expr = 5 == select([table.c.value]).as_scalar().all_()

See also

all_() - standalone version

any_() - ANY operator

New in version 1.1.

anon_label

inherited from theanon_labelattribute ofColumnElement

provides a constant ‘anonymous label’ for this ColumnElement.

This is a label() expression which will be named at compile time.The same label() is returned each time anon_label is called sothat expressions can reference anon_label multiple times, producingthe same label name at compile time.

the compiler uses this function automatically at compile timefor expressions that are known to be ‘unnamed’ like binaryexpressions and function calls.

any()[](https://docs.sqlalchemy.org/en/13/core/#sqlalchemy.schema.Column.any)

inherited from theany_()method ofColumnOperators

Produce a any_() clause against theparent object.

This operator is only appropriate against a scalar subqueryobject, or for some backends an column expression that isagainst the ARRAY type, e.g.:

# postgresql '5 = ANY (somearray)'
expr = 5 == mytable.c.somearray.any_()
 
# mysql '5 = ANY (SELECT value FROM table)'
expr = 5 == select([table.c.value]).as_scalar().any_()

See also

any_() - standalone version

all_() - ALL operator

New in version 1.1.

classmethod argumentfor(_dialect_name, argument_name, default)

inherited from theargument_for()method ofDialectKWArgs

Add a new kind of dialect-specific keyword argument for this class.

E.g.:

Index.argument_for("mydialect", "length", None)
 
some_index = Index('a', 'b', mydialect_length=5)

The DialectKWArgs.argument_for() method is a per-argumentway adding extra arguments to theDefaultDialect.construct_arguments dictionary. Thisdictionary provides a list of argument names accepted by variousschema-level constructs on behalf of a dialect.

New dialects should typically specify this dictionary all at once as adata member of the dialect class. The use case for ad-hoc addition ofargument names is typically for end-user code that is also usinga custom compilation scheme which consumes the additional arguments.

- Parameters
- 
  -

dialect_name – name of a dialect. The dialect must belocatable, else a NoSuchModuleError is raised. Thedialect must also include an existingDefaultDialect.construct_arguments collection, indicatingthat it participates in the keyword-argument validation and defaultsystem, else ArgumentError is raised. If the dialect doesnot include this collection, then any keyword argument can bespecified on behalf of this dialect already. All dialects packagedwithin SQLAlchemy include this collection, however for third partydialects, support may vary.

argument_name – name of the parameter.

default – default value of the parameter.

New in version 0.9.4.

asc()

inherited from theasc()method ofColumnOperators

Produce a asc() clause against theparent object.

between(cleft, cright, symmetric=False)

inherited from thebetween()method ofColumnOperators

Produce a between() clause againstthe parent object, given the lower and upper range.

boolop(_opstring, precedence=0)

inherited from thebool_op()method ofOperators

Return a custom boolean operator.

This method is shorthand for callingOperators.op() and passing theOperators.op.is_comparisonflag with True.

New in version 1.2.0b3.

See also

Operators.op()

cast(type_)

inherited from thecast()method ofColumnElement

Produce a type cast, i.e. CAST(<expression> AS <type>).

This is a shortcut to the cast() function.

See also

Data Casts and Type Coercion

cast()

type_coerce()

New in version 1.0.7.

collate(collation)

inherited from thecollate()method ofColumnOperators

Produce a collate() clause againstthe parent object, given the collation string.

See also

collate()

compare(other, use_proxies=False, equivalents=None, **kw)

inherited from thecompare()method ofColumnElement

Compare this ColumnElement to another.

Special arguments understood:

- Parameters
- 
  -

use_proxies – when True, consider two columns thatshare a common base column as equivalent (i.e. shares_lineage())

equivalents – a dictionary of columns as keys mapped to setsof columns. If the given “other” column is present in thisdictionary, if any of the columns in the corresponding set() passthe comparison test, the result is True. This is used to expand thecomparison to other columns that may be known to be equivalent tothis one via foreign key or other criterion.

compile(default, bind=None, dialect=None, **kw)

inherited from thecompile()method ofClauseElement

Compile this SQL expression.

The return value is a Compiled object.Calling str() or unicode() on the returned value will yield astring representation of the result. TheCompiled object also can return adictionary of bind parameter names and valuesusing the params accessor.

- Parameters
- 
  -

bind – An Engine or Connection from which aCompiled will be acquired. This argument takes precedence overthis ClauseElement’s bound engine, if any.

column_keys – Used for INSERT and UPDATE statements, a list ofcolumn names which should be present in the VALUES clause of thecompiled statement. If None, all columns from the target tableobject are rendered.

dialect – A Dialect instance from which a Compiledwill be acquired. This argument takes precedence over the _bind_argument as well as this ClauseElement’s bound engine,if any.

inline – Used for INSERT statements, for a dialect which doesnot support inline retrieval of newly generated primary keycolumns, will force the expression used to create the new primarykey value to be rendered inline within the INSERT statement’sVALUES clause. This typically refers to Sequence execution but mayalso refer to any server-side default generation functionassociated with a primary key Column.

compile_kwargs –

optional dictionary of additional parametersthat will be passed through to the compiler within all “visit”methods. This allows any custom flag to be passed through toa custom compilation construct, for example. It is also usedfor the case of passing the literal_binds flag through:

from sqlalchemy.sql import table, column, select
 
t = table('t', column('x'))
 
s = select([t]).where(t.c.x == 5)
 
print s.compile(compile_kwargs={"literal_binds": True})

New in version 0.9.0.

concat(other)

inherited from theconcat()method ofColumnOperators

Implement the ‘concat’ operator.

In a column context, produces the clause a || b,or uses the concat() operator on MySQL.

contains(other, **kwargs)

inherited from thecontains()method ofColumnOperators

Implement the ‘contains’ operator.

Produces a LIKE expression that tests against a match for the middleof a string value:

column LIKE '%' || <other> || '%'

E.g.:

stmt = select([sometable]).\
    where(sometable.c.column.contains("foobar"))

Since the operator uses LIKE, wildcard characters"%" and "_" that are present inside the expressionwill behave like wildcards as well. For literal stringvalues, the ColumnOperators.contains.autoescape flagmay be set to True to apply escaping to occurrences of thesecharacters within the string value so that they match as themselvesand not as wildcard characters. Alternatively, theColumnOperators.contains.escape parameter will establisha given character as an escape character which can be of use whenthe target expression is not a literal string.

- Parameters
- 
  -

other – expression to be compared. This is usually a plainstring value, but can also be an arbitrary SQL expression. LIKEwildcard characters % and _ are not escaped by default unlessthe ColumnOperators.contains.autoescape flag isset to True.

autoescape –

boolean; when True, establishes an escape characterwithin the LIKE expression, then applies it to all occurrences of"%", "_" and the escape character itself within thecomparison value, which is assumed to be a literal string and not aSQL expression.

An expression such as:

somecolumn.contains("foo%bar", autoescape=True)

Will render as:

somecolumn LIKE '%' || :param || '%' ESCAPE '/'

With the value of :param as "foo/%bar".

New in version 1.2.

Changed in version 1.2.0: TheColumnOperators.contains.autoescape parameter is now a simple boolean rather than a character; the escape character itself is also escaped, and defaults to a forwards slash, which itself can be customized using the ColumnOperators.contains.escape parameter.

escape –

a character which when given will render with theESCAPE keyword to establish that character as the escapecharacter. This character can then be placed preceding occurrencesof % and _ to allow them to act as themselves and notwildcard characters.

An expression such as:

somecolumn.contains("foo/%bar", escape="^")

Will render as:

somecolumn LIKE '%' || :param || '%' ESCAPE '^'

The parameter may also be combined withColumnOperators.contains.autoescape:

somecolumn.contains("foo%bar^bat", escape="^", autoescape=True)

Where above, the given literal parameter will be converted to"foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.startswith()

ColumnOperators.endswith()

ColumnOperators.like()

copy(**kw)
Create a copy of this Column, uninitialized.

This is used in Table.tometadata.

desc()

inherited from thedesc()method ofColumnOperators

Produce a desc() clause against theparent object.

dialect_kwargs

inherited from thedialect_kwargsattribute ofDialectKWArgs

A collection of keyword arguments specified as dialect-specificoptions to this construct.

The arguments are present here in their original <dialect>_<kwarg>format. Only arguments that were actually passed are included;unlike the DialectKWArgs.dialect_options collection, whichcontains all options known by this dialect including defaults.

The collection is also writable; keys are accepted of theform <dialect>_<kwarg> where the value will be assembledinto the list of options.

New in version 0.9.2.

Changed in version 0.9.4: The DialectKWArgs.dialect_kwargscollection is now writable.

See also

DialectKWArgs.dialect_options - nested dictionary form

dialect_options

inherited from thedialect_optionsattribute ofDialectKWArgs

A collection of keyword arguments specified as dialect-specificoptions to this construct.

This is a two-level nested registry, keyed to <dialect_name>and <argument_name>. For example, the postgresql_whereargument would be locatable as:

arg = my_object.dialect_options['postgresql']['where']

New in version 0.9.2.

See also

DialectKWArgs.dialect_kwargs - flat dictionary form

distinct()

inherited from thedistinct()method ofColumnOperators

Produce a distinct() clause against theparent object.

endswith(other, **kwargs)

inherited from theendswith()method ofColumnOperators

Implement the ‘endswith’ operator.

Produces a LIKE expression that tests against a match for the endof a string value:

column LIKE '%' || <other>

E.g.:

stmt = select([sometable]).\
    where(sometable.c.column.endswith("foobar"))

Since the operator uses LIKE, wildcard characters"%" and "_" that are present inside the expressionwill behave like wildcards as well. For literal stringvalues, the ColumnOperators.endswith.autoescape flagmay be set to True to apply escaping to occurrences of thesecharacters within the string value so that they match as themselvesand not as wildcard characters. Alternatively, theColumnOperators.endswith.escape parameter will establisha given character as an escape character which can be of use whenthe target expression is not a literal string.

- Parameters
- 
  -

autoescape –

An expression such as:

somecolumn.endswith("foo%bar", autoescape=True)

Will render as:

somecolumn LIKE '%' || :param ESCAPE '/'

With the value of :param as "foo/%bar".

New in version 1.2.

Changed in version 1.2.0: TheColumnOperators.endswith.autoescape parameter is now a simple boolean rather than a character; the escape character itself is also escaped, and defaults to a forwards slash, which itself can be customized using the ColumnOperators.endswith.escape parameter.

escape –

An expression such as:

somecolumn.endswith("foo/%bar", escape="^")

Will render as:

somecolumn LIKE '%' || :param ESCAPE '^'

The parameter may also be combined withColumnOperators.endswith.autoescape:

somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)

Where above, the given literal parameter will be converted to"foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.startswith()

ColumnOperators.contains()

ColumnOperators.like()

property expression
Return a column expression.

Part of the inspection interface; returns self.

getchildren(_schema_visitor=False, **kwargs)
used to allow SchemaVisitor access
ilike(other, escape=None)

inherited from theilike()method ofColumnOperators

Implement the ilike operator, e.g. case insensitive LIKE.

In a column context, produces an expression either of the form:

lower(a) LIKE lower(other)

Or on backends that support the ILIKE operator:

a ILIKE other

E.g.:

stmt = select([sometable]).\
    where(sometable.c.column.ilike("%foobar%"))

- Parameters
- 
  -

other – expression to be compared

escape –

optional escape character, renders the ESCAPEkeyword, e.g.:

somecolumn.ilike("foo/%bar", escape="/")

See also

ColumnOperators.like()

in(_other)

inherited from thein_()method ofColumnOperators

Implement the in operator.

In a column context, produces the clause column IN <other>.

The given parameter other may be:

A list of literal values, e.g.:

stmt.where(column.in_([1, 2, 3]))

In this calling form, the list of items is converted to a set ofbound parameters the same length as the list given:

WHERE COL IN (?, ?, ?)

A list of tuples may be provided if the comparison is against atuple_() containing multiple expressions:

from sqlalchemy import tuple_
stmt.where(tuple_(col1, col2).in_([(1, 10), (2, 20), (3, 30)]))

An empty list, e.g.:

stmt.where(column.in_([]))

In this calling form, the expression renders a “false” expression,e.g.:

WHERE 1 != 1

This “false” expression has historically had different behaviorsin older SQLAlchemy versions, seecreate_engine.empty_in_strategy for behavioral options.

Changed in version 1.2: simplified the behavior of “empty in”expressions

A bound parameter, e.g. bindparam(), may be used if itincludes the bindparam.expanding flag:

stmt.where(column.in_(bindparam('value', expanding=True)))

In this calling form, the expression renders a special non-SQLplaceholder expression that looks like:

WHERE COL IN ([EXPANDING_value])

This placeholder expression is intercepted at statement executiontime to be converted into the variable number of bound parameterform illustrated earlier. If the statement were executed as:

connection.execute(stmt, {"value": [1, 2, 3]})

The database would be passed a bound parameter for each value:

WHERE COL IN (?, ?, ?)

New in version 1.2: added “expanding” bound parameters

If an empty list is passed, a special “empty list” expression,which is specific to the database in use, is rendered. OnSQLite this would be:

WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)

New in version 1.3: “expanding” bound parameters now supportempty lists

a select() construct, which is usually a correlatedscalar select:

stmt.where(
    column.in_(
        select([othertable.c.y]).
        where(table.c.x == othertable.c.x)
    )
)

In this calling form, ColumnOperators.in_() renders as given:

WHERE COL IN (SELECT othertable.y
FROM othertable WHERE othertable.x = table.x)

- Parameters
-

other – a list of literals, a select() construct,or a bindparam() construct that includes thebindparam.expanding flag set to True.

info

inherited from theinfoattribute ofSchemaItem

Info dictionary associated with the object, allowing user-defineddata to be associated with this SchemaItem.

The dictionary is automatically generated when first accessed.It can also be specified in the constructor of some objects,such as Table and Column.

is(_other)

inherited from theis_()method ofColumnOperators

Implement the IS operator.

Normally, IS is generated automatically when comparing to avalue of None, which resolves to NULL. However, explicitusage of IS may be desirable if comparing to boolean valueson certain platforms.

See also

ColumnOperators.isnot()

isdistinct_from(_other)

inherited from theis_distinct_from()method ofColumnOperators

Implement the IS DISTINCT FROM operator.

Renders “a IS DISTINCT FROM b” on most platforms;on some such as SQLite may render “a IS NOT b”.

New in version 1.1.

isnot(other)

inherited from theisnot()method ofColumnOperators

Implement the IS NOT operator.

Normally, IS NOT is generated automatically when comparing to avalue of None, which resolves to NULL. However, explicitusage of IS NOT may be desirable if comparing to boolean valueson certain platforms.

See also

ColumnOperators.is_()

isnotdistinct_from(_other)

inherited from theisnot_distinct_from()method ofColumnOperators

Implement the IS NOT DISTINCT FROM operator.

Renders “a IS NOT DISTINCT FROM b” on most platforms;on some such as SQLite may render “a IS b”.

New in version 1.1.

property kwargs
A synonym for DialectKWArgs.dialect_kwargs.
label(name)

inherited from thelabel()method ofColumnElement

Produce a column label, i.e. <columnname> AS <name>.

This is a shortcut to the label() function.

if ‘name’ is None, an anonymous label name will be generated.

like(other, escape=None)

inherited from thelike()method ofColumnOperators

Implement the like operator.

In a column context, produces the expression:

a LIKE other

E.g.:

stmt = select([sometable]).\
    where(sometable.c.column.like("%foobar%"))

- Parameters
- 
  -

other – expression to be compared

escape –

optional escape character, renders the ESCAPEkeyword, e.g.:

somecolumn.like("foo/%bar", escape="/")

See also

ColumnOperators.ilike()

match(other, **kwargs)

inherited from thematch()method ofColumnOperators

Implements a database-specific ‘match’ operator.

match() attempts to resolve toa MATCH-like function or operator provided by the backend.Examples include:

PostgreSQL - renders x @@ to_tsquery(y)

MySQL - renders MATCH (x) AGAINST (y IN BOOLEAN MODE)

Oracle - renders CONTAINS(x, y)

other backends may provide special implementations.

Backends without any special implementation will emitthe operator as “MATCH”. This is compatible with SQLite, forexample.

notilike(other, escape=None)

inherited from thenotilike()method ofColumnOperators

implement the NOT ILIKE operator.

This is equivalent to using negation withColumnOperators.ilike(), i.e. ~x.ilike(y).

See also

ColumnOperators.ilike()

notin(_other)

inherited from thenotin_()method ofColumnOperators

implement the NOT IN operator.

This is equivalent to using negation withColumnOperators.in_(), i.e. ~x.in_(y).

In the case that other is an empty sequence, the compilerproduces an “empty not in” expression. This defaults to theexpression “1 = 1” to produce true in all cases. Thecreate_engine.empty_in_strategy may be used toalter this behavior.

Changed in version 1.2: The ColumnOperators.in_() andColumnOperators.notin_() operatorsnow produce a “static” expression for an empty IN sequenceby default.

See also

ColumnOperators.in_()

notlike(other, escape=None)

inherited from thenotlike()method ofColumnOperators

implement the NOT LIKE operator.

This is equivalent to using negation withColumnOperators.like(), i.e. ~x.like(y).

See also

ColumnOperators.like()

nullsfirst()

inherited from thenullsfirst()method ofColumnOperators

Produce a nullsfirst() clause against theparent object.

nullslast()

inherited from thenullslast()method ofColumnOperators

Produce a nullslast() clause against theparent object.

op(opstring, precedence=0, is_comparison=False, return_type=None)

inherited from theop()method ofOperators

produce a generic operator function.

e.g.:

somecolumn.op("*")(5)

produces:

somecolumn * 5

This function can also be used to make bitwise operators explicit. Forexample:

somecolumn.op('&')(0xff)

is a bitwise AND of the value in somecolumn.

- Parameters
- 
  -

operator – a string which will be output as the infix operatorbetween this element and the expression passed to thegenerated function.

precedence – precedence to apply to the operator, whenparenthesizing expressions. A lower number will cause the expressionto be parenthesized when applied against another operator withhigher precedence. The default value of 0 is lower than alloperators except for the comma (,) and AS operators.A value of 100 will be higher or equal to all operators, and -100will be lower than or equal to all operators.

is_comparison –

if True, the operator will be considered as a“comparison” operator, that is which evaluates to a booleantrue/false value, like ==, >, etc. This flag should be setso that ORM relationships can establish that the operator is acomparison operator when used in a custom join condition.

New in version 0.9.2: - added theOperators.op.is_comparison flag.

return_type –

a TypeEngine class or object that willforce the return type of an expression produced by this operatorto be of that type. By default, operators that specifyOperators.op.is_comparison will resolve toBoolean, and those that do not will be of the sametype as the left-hand operand.

New in version 1.2.0b3: - added theOperators.op.return_type argument.

See also

Redefining and Creating New Operators

Using custom operators in join conditions

operate(op, *other, **kwargs)

inherited from theoperate()method ofColumnElement

Operate on an argument.

This is the lowest level of operation, raisesNotImplementedError by default.

Overriding this on a subclass can allow commonbehavior to be applied to all operations.For example, overriding ColumnOperatorsto apply func.lower() to the left and rightside:

class MyComparator(ColumnOperators):
    def operate(self, op, other):
        return op(func.lower(self), func.lower(other))

- Parameters
- 
  -

op – Operator callable.

*other – the ‘other’ side of the operation. Willbe a single scalar for most operations.

**kwargs – modifiers. These may be passed by specialoperators such as ColumnOperators.contains().

property quote
Return the value of the quote flag passedto this schema object, for those schema items whichhave a name field.

Deprecated since version 0.9: The SchemaItem.quote attribute is deprecated and will be removed in a future release. Use the quoted_name.quote attribute on the name field of the target schema item to retrievequoted status.

references(column)
Return True if this Column references the given column via foreignkey.
reverseoperate(_op, other, **kwargs)

inherited from thereverse_operate()method ofColumnElement

Reverse operate on an argument.

Usage is the same as operate().

selfgroup(_against=None)

inherited from theself_group()method ofColumnElement

Apply a ‘grouping’ to this ClauseElement.

This method is overridden by subclasses to return a“grouping” construct, i.e. parenthesis. In particularit’s used by “binary” expressions to provide a groupingaround themselves when placed into a larger expression,as well as by select() constructs when placed intothe FROM clause of another select(). (Note thatsubqueries should be normally created using theSelect.alias() method, as many platforms requirenested SELECT statements to be named).

As expressions are composed together, the application ofself_group() is automatic - end-user code should neverneed to use this method directly. Note that SQLAlchemy’sclause constructs take operator precedence into account -so parenthesis might not be needed, for example, inan expression like x OR (y AND z) - AND takes precedenceover OR.

The base self_group() method of ClauseElementjust returns self.

shareslineage(_othercolumn)

inherited from theshares_lineage()method ofColumnElement

Return True if the given ColumnElementhas a common ancestor to this ColumnElement.

startswith(other, **kwargs)

inherited from thestartswith()method ofColumnOperators

Implement the startswith operator.

Produces a LIKE expression that tests against a match for the startof a string value:

column LIKE <other> || '%'

E.g.:

stmt = select([sometable]).\
    where(sometable.c.column.startswith("foobar"))

Since the operator uses LIKE, wildcard characters"%" and "_" that are present inside the expressionwill behave like wildcards as well. For literal stringvalues, the ColumnOperators.startswith.autoescape flagmay be set to True to apply escaping to occurrences of thesecharacters within the string value so that they match as themselvesand not as wildcard characters. Alternatively, theColumnOperators.startswith.escape parameter will establisha given character as an escape character which can be of use whenthe target expression is not a literal string.

- Parameters
- 
  -

autoescape –

An expression such as:

somecolumn.startswith("foo%bar", autoescape=True)

Will render as:

somecolumn LIKE :param || '%' ESCAPE '/'

With the value of :param as "foo/%bar".

New in version 1.2.

Changed in version 1.2.0: TheColumnOperators.startswith.autoescape parameter is now a simple boolean rather than a character; the escape character itself is also escaped, and defaults to a forwards slash, which itself can be customized using the ColumnOperators.startswith.escape parameter.

escape –

An expression such as:

somecolumn.startswith("foo/%bar", escape="^")

Will render as:

somecolumn LIKE :param || '%' ESCAPE '^'

The parameter may also be combined withColumnOperators.startswith.autoescape:

somecolumn.startswith("foo%bar^bat", escape="^", autoescape=True)

Where above, the given literal parameter will be converted to"foo^%bar^^bat" before being passed to the database.

See also

ColumnOperators.endswith()

ColumnOperators.contains()

ColumnOperators.like()

class sqlalchemy.schema.MetaData(bind=None, reflect=False, schema=None, quote_schema=None, naming_convention=None, info=None)
Bases: sqlalchemy.schema.SchemaItem

A collection of Table objects and their associated schemaconstructs.

Holds a collection of Table objects as well asan optional binding to an Engine orConnection. If bound, the Table objectsin the collection and their columns may participate in implicit SQLexecution.

The Table objects themselves are stored in theMetaData.tables dictionary.

MetaData is a thread-safe object for read operations.Construction of new tables within a single MetaData object,either explicitly or via reflection, may not be completely thread-safe.

See also

Describing Databases with MetaData - Introduction to database metadata

init(bind=None, reflect=False, schema=None, quote_schema=None, naming_convention=None, info=None)
Create a new MetaData object.
- Parameters
- - bind – An Engine or Connection to bind to. May also be a string or URLinstance, these are passed to create_engine() and this MetaData willbe bound to the resulting engine.
  - reflect –

Optional, automatically load all tables from the bound database.Defaults to False. bind is required when this option is set.

Deprecated since version 0.8: The MetaData.reflect flag is deprecated and will be removed in a future release. Please use the MetaData.reflect() method.

schema –

The default schema to use for the Table,Sequence, and potentially other objects associated withthis MetaData. Defaults to None.

When this value is set, any Table or Sequencewhich specifies None for the schema parameter will insteadhave this schema name defined. To build a Tableor Sequence that still has None for the schemaeven when this parameter is present, use the BLANK_SCHEMAsymbol.

Note

As referred above, the MetaData.schema parameteronly refers to the default value that will be applied tothe Table.schema parameter of an incomingTable object. It does not refer to how theTable is catalogued within the MetaData,which remains consistent vs. a MetaData collectionthat does not define this parameter. The Tablewithin the MetaData will still be keyed based on itsschema-qualified name, e.g.my_metadata.tables["some_schema.my_table"].

The current behavior of the ForeignKey object is tocircumvent this restriction, where it can locate a table giventhe table name alone, where the schema will be assumed to bepresent from this value as specified on the owningMetaData collection. However, this implies that atable qualified with BLANK_SCHEMA cannot currently be referredto by string name from ForeignKey. Other parts ofSQLAlchemy such as Declarative may not have similar behaviorsbuilt in, however may do so in a future release, along with aconsistent method of referring to a table in BLANK_SCHEMA.

See also

Table.schema

Sequence.schema

quote_schema – Sets the quote_schema flag for those Table,Sequence, and other objects which make usage of thelocal schema name.

info –

Optional data dictionary which will be populated into theSchemaItem.info attribute of this object.

New in version 1.0.0.

naming_convention –

a dictionary referring to values whichwill establish default naming conventions for Constraintand Index objects, for those objects which are not givena name explicitly.

The keys of this dictionary may be:

a constraint or Index class, e.g. the UniqueConstraint,ForeignKeyConstraint class, the Index class

a string mnemonic for one of the known constraint classes;"fk", "pk", "ix", "ck", "uq" for foreign key,primary key, index, check, and unique constraint, respectively.

the string name of a user-defined “token” that can be usedto define new naming tokens.

The values associated with each “constraint class” or “constraintmnemonic” key are string naming templates, such as"uq%(table_name)s%(column_0_name)s",which describe how the name should be composed. The valuesassociated with user-defined “token” keys should be callables of theform fn(constraint, table), which accepts the constraint/indexobject and Table as arguments, returning a stringresult.

The built-in names are as follows, some of which may only beavailable for certain types of constraint:

%(table_name)s - the name of the Table objectassociated with the constraint.
%(referred_table_name)s - the name of the Tableobject associated with the referencing target of aForeignKeyConstraint.
%(column_0_name)s - the name of the Column atindex position “0” within the constraint.
%(column_0N_name)s - the name of all Columnobjects in order within the constraint, joined without aseparator.
%(column_0_N_name)s - the name of all Columnobjects in order within the constraint, joined with anunderscore as a separator.
%(column_0_label)s, %(column_0N_label)s,%(column_0_N_label)s - the label of either the zerothColumn or all Columns, separated withor without an underscore
%(column_0_key)s, %(column_0N_key)s,%(column_0_N_key)s - the key of either the zerothColumn or all Columns, separated withor without an underscore
%(referred_column_0_name)s, %(referred_column_0N_name)s%(referred_column_0_N_name)s, %(referred_column_0_key)s,%(referred_column_0N_key)s, … column tokens whichrender the names/keys/labels of columns that are referencedby a ForeignKeyConstraint.
%(constraint_name)s - a special key that refers to theexisting name given to the constraint. When this key ispresent, the Constraint object’s existing name will bereplaced with one that is composed from template string thatuses this token. When this token is present, it is required thatthe Constraint is given an explicit name ahead of time.
user-defined: any additional token may be implemented by passingit along with a fn(constraint, table) callable to thenaming_convention dictionary.

New in version 1.3.0: - added new %(column_0N_name)s,%(column_0_N_name)s, and related tokens that produceconcatenations of names, keys, or labels for all columns referredto by a given constraint.

See also

Configuring Constraint Naming Conventions - for detailed usageexamples.

appendddl_listener(_event_name, listener)
Append a DDL event listener to this MetaData.

Deprecated since version 0.7: the MetaData.append_ddl_listener() method is deprecated and will be removed in a future release. Please refer to DDLEvents.

property bind
An Engine or Connection to which thisMetaData is bound.

Typically, a Engine is assigned to this attributeso that “implicit execution” may be used, or alternativelyas a means of providing engine binding information to anORM Session object:

engine = create_engine("someurl://")
metadata.bind = engine

See also

Connectionless Execution, Implicit Execution - background on “bound metadata”

clear()
Clear all Table objects from this MetaData.
createall(_bind=None, tables=None, checkfirst=True)
Create all tables stored in this metadata.

Conditional by default, will not attempt to recreate tables alreadypresent in the target database.

- Parameters
- 
  -

bind – A Connectable used to access thedatabase; if None, uses the existing bind on this MetaData, ifany.

tables – Optional list of Table objects, which is a subset of the totaltables in the MetaData (others are ignored).

checkfirst – Defaults to True, don’t issue CREATEs for tables already presentin the target database.

dropall(_bind=None, tables=None, checkfirst=True)
Drop all tables stored in this metadata.

Conditional by default, will not attempt to drop tables not present inthe target database.

- Parameters
- 
  -

bind – A Connectable used to access thedatabase; if None, uses the existing bind on this MetaData, ifany.

tables – Optional list of Table objects, which is a subset of thetotal tables in the MetaData (others are ignored).

checkfirst – Defaults to True, only issue DROPs for tables confirmed to bepresent in the target database.

is_bound()
True if this MetaData is bound to an Engine or Connection.
reflect(bind=None, schema=None, views=False, only=None, extend_existing=False, autoload_replace=True, resolve_fks=True, **dialect_kwargs)
Load all available table definitions from the database.

Automatically creates Table entries in this MetaData for anytable available in the database but not yet present in theMetaData. May be called multiple times to pick up tables recentlyadded to the database, however no special action is taken if a tablein this MetaData no longer exists in the database.

- Parameters
- 
  -

bind – A Connectable used to access the database; if None, usesthe existing bind on this MetaData, if any.

schema – Optional, query and reflect tables from an alternate schema.If None, the schema associated with this MetaDatais used, if any.

views – If True, also reflect views.

only –

Optional. Load only a sub-set of available named tables. May bespecified as a sequence of names or a callable.

If a sequence of names is provided, only those tables will bereflected. An error is raised if a table is requested but notavailable. Named tables already present in this MetaData areignored.

If a callable is provided, it will be used as a boolean predicate tofilter the list of potential table names. The callable is calledwith a table name and this MetaData instance as positionalarguments and should return a true value for any table to reflect.

extend_existing –

Passed along to each Table asTable.extend_existing.

New in version 0.9.1.

autoload_replace –

Passed along to each Table asTable.autoload_replace.

New in version 0.9.1.

resolve_fks –

if True, reflect Table objects linkedto ForeignKey objects located in each Table.For MetaData.reflect(), this has the effect of reflectingrelated tables that might otherwise not be in the list of tablesbeing reflected, for example if the referenced table is in adifferent schema or is omitted via theMetaData.reflect.only parameter. When False,ForeignKey objects are not followed to the Tablein which they link, however if the related table is also part of thelist of tables that would be reflected in any case, theForeignKey object will still resolve to its relatedTable after the MetaData.reflect() operation iscomplete. Defaults to True.

New in version 1.3.0.

See also

Table.resolve_fks

**dialect_kwargs –

Additional keyword arguments not mentionedabove are dialect specific, and passed in the form<dialectname>_<argname>. See the documentation regarding anindividual dialect at Dialects for detail ondocumented arguments.

New in version 0.9.2: - AddedMetaData.reflect.**dialect_kwargs to supportdialect-level reflection options for all Tableobjects reflected.

remove(table)
Remove the given Table object from this MetaData.
property sorted_tables
Returns a list of Table objects sorted in order offoreign key dependency.

The sorting will place Table objects that have dependenciesfirst, before the dependencies themselves, representing theorder in which they can be created. To get the order in whichthe tables would be dropped, use the reversed() Python built-in.

Warning

The sorted_tables accessor cannot by itself accommodateautomatic resolution of dependency cycles between tables, whichare usually caused by mutually dependent foreign key constraints.To resolve these cycles, either theForeignKeyConstraint.use_alter parameter may beapplied to those constraints, or use theschema.sort_tables_and_constraints() function which willbreak out foreign key constraints involved in cycles separately.

See also

schema.sort_tables()

schema.sort_tables_and_constraints()

MetaData.tables

Inspector.get_table_names()

Inspector.get_sorted_table_and_fkc_names()

tables = None
A dictionary of Table objects keyed to their name or “table key”.

The exact key is that determined by the Table.key attribute;for a table with no Table.schema attribute, this is the sameas Table.name. For a table with a schema, it is typically of theform schemaname.tablename.

See also

MetaData.sorted_tables

class sqlalchemy.schema.SchemaItem
Bases: sqlalchemy.sql.expression.SchemaEventTarget, sqlalchemy.sql.visitors.Visitable

Base class for items that define a database schema.

getchildren(**kwargs_)
used to allow SchemaVisitor access
info
Info dictionary associated with the object, allowing user-defineddata to be associated with this SchemaItem.

The dictionary is automatically generated when first accessed.It can also be specified in the constructor of some objects,such as Table and Column.

property quote
Return the value of the quote flag passedto this schema object, for those schema items whichhave a name field.

class sqlalchemy.schema.Table(*args, **kw)
Bases: sqlalchemy.sql.base.DialectKWArgs, sqlalchemy.schema.SchemaItem, sqlalchemy.sql.expression.TableClause

Represent a table in a database.

e.g.:

mytable = Table("mytable", metadata,
                Column('mytable_id', Integer, primary_key=True),
                Column('value', String(50))
           )

The Table object constructs a unique instance of itself basedon its name and optional schema name within the givenMetaData object. Calling the Tableconstructor with the same name and same MetaData argumenta second time will return the sameTable object - in this waythe Table constructor acts as a registry function.

See also

Describing Databases with MetaData - Introduction to database metadata

Constructor arguments are as follows:

Parameters
- name –

The name of this table as represented in the database.

The table name, along with the value of the schema parameter,forms a key which uniquely identifies this Table withinthe owning MetaData collection.Additional calls to Table with the same name, metadata,and schema name will return the same Table object.

Names which contain no upper case characterswill be treated as case insensitive names, and will not be quotedunless they are a reserved word or contain special characters.A name with any number of upper case characters is consideredto be case sensitive, and will be sent as quoted.

To enable unconditional quoting for the table name, specify the flagquote=True to the constructor, or use the quoted_nameconstruct to specify the name.

metadata – a MetaData object which will contain thistable. The metadata is used as a point of association of this tablewith other tables which are referenced via foreign key. It alsomay be used to associate this table with a particularConnectable.

*args – Additional positional arguments are used primarilyto add the list of Column objects contained within thistable. Similar to the style of a CREATE TABLE statement, otherSchemaItem constructs may be added here, includingPrimaryKeyConstraint, and ForeignKeyConstraint.

autoload –

Defaults to False, unless Table.autoload_withis set in which case it defaults to True; Column objectsfor this table should be reflected from the database, possiblyaugmenting or replacing existing Column objects that wereexplicitly specified.

Changed in version 1.0.0: setting the Table.autoload_withparameter implies that Table.autoload will defaultto True.

See also

Reflecting Database Objects

autoload_replace –

Defaults to True; when usingTable.autoloadin conjunction with Table.extend_existing, indicatesthat Column objects present in the already-existingTable object should be replaced with columns of the samename retrieved from the autoload process. When False, columnsalready present under existing names will be omitted from thereflection process.

Note that this setting does not impact Column objectsspecified programmatically within the call to Table thatalso is autoloading; those Column objects will alwaysreplace existing columns of the same name whenTable.extend_existing is True.

See also

Table.autoload

Table.extend_existing

autoload_with –

An Engine or Connection objectwith which this Table object will be reflected; whenset to a non-None value, it implies that Table.autoloadis True. If left unset, but Table.autoload isexplicitly set to True, an autoload operation will attempt toproceed by locating an Engine or Connection boundto the underlying MetaData object.

See also

Table.autoload

extend_existing –

When True, indicates that if thisTable is already present in the given MetaData,apply further arguments within the constructor to the existingTable.

If Table.extend_existing orTable.keep_existing are not set, and the given nameof the new Table refers to a Table that isalready present in the target MetaData collection, andthis Table specifies additional columns or other constructsor flags that modify the table’s state, anerror is raised. The purpose of these two mutually-exclusive flagsis to specify what action should be taken when a Tableis specified that matches an existing Table, yet specifiesadditional constructs.

Table.extend_existing will also work in conjunctionwith Table.autoload to run a new reflectionoperation against the database, even if a Tableof the same name is already present in the targetMetaData; newly reflected Column objectsand other options will be added into the state of theTable, potentially overwriting existing columnsand options of the same name.

As is always the case with Table.autoload,Column objects can be specified in the same Tableconstructor, which will take precedence. Below, the existingtable mytable will be augmented with Column objectsboth reflected from the database, as well as the given Columnnamed “y”:

Table("mytable", metadata,
            Column('y', Integer),
            extend_existing=True,
            autoload=True,
            autoload_with=engine
        )

See also

Table.autoload

Table.autoload_replace

Table.keep_existing

implicit_returning – True by default - indicates thatRETURNING can be used by default to fetch newly inserted primary keyvalues, for backends which support this. Note thatcreate_engine() also provides an implicit_returning flag.

include_columns – A list of strings indicating a subset ofcolumns to be loaded via the autoload operation; table columns whoaren’t present in this list will not be represented on the resultingTable object. Defaults to None which indicates all columnsshould be reflected.

resolve_fks –

Whether or not to reflect Table objectsrelated to this one via ForeignKey objects, whenTable.autoload or Table.autoload_with isspecified. Defaults to True. Set to False to disable reflection ofrelated tables as ForeignKey objects are encountered; may beused either to save on SQL calls or to avoid issues with related tablesthat can’t be accessed. Note that if a related table is already presentin the MetaData collection, or becomes present later, aForeignKey object associated with this Table willresolve to that table normally.

New in version 1.3.

See also

MetaData.reflect.resolve_fks

info – Optional data dictionary which will be populated into theSchemaItem.info attribute of this object.

keep_existing –

When True, indicates that if this Tableis already present in the given MetaData, ignorefurther arguments within the constructor to the existingTable, and return the Table object asoriginally created. This is to allow a function that wishesto define a new Table on first call, but onsubsequent calls will return the same Table,without any of the declarations (particularly constraints)being applied a second time.

See also

Table.extend_existing

listeners –

A list of tuples of the form (<eventname>, <fn>)which will be passed to event.listen() upon construction.This alternate hook to event.listen() allows the establishmentof a listener function specific to this Table beforethe “autoload” process begins. Particularly useful forthe DDLEvents.column_reflect() event:

def listen_for_reflect(table, column_info):
    "handle the column reflection event"
    # ...
 
t = Table(
    'sometable',
    autoload=True,
    listeners=[
        ('column_reflect', listen_for_reflect)
    ])

mustexist – When True, indicates that this Table must alreadybe present in the given MetaData collection, elsean exception is raised.

prefixes – A list of strings to insert after CREATE in the CREATE TABLEstatement. They will be separated by spaces.

quote – Force quoting of this table’s name on or off, correspondingto True or False. When left at its default of None,the column identifier will be quoted according to whether the name iscase sensitive (identifiers with at least one upper case character aretreated as case sensitive), or if it’s a reserved word. This flagis only needed to force quoting of a reserved word which is not knownby the SQLAlchemy dialect.

quote_schema – same as ‘quote’ but applies to the schema identifier.

schema –

The schema name for this table, which is required ifthe table resides in a schema other than the default selected schemafor the engine’s database connection. Defaults to None.

If the owning MetaData of this Table specifies itsown MetaData.schema parameter, then that schema name willbe applied to this Table if the schema parameter here is setto None. To set a blank schema name on a Table thatwould otherwise use the schema set on the owning MetaData,specify the special symbol BLANK_SCHEMA.

New in version 1.0.14: Added the BLANK_SCHEMA symbol toallow a Table to have a blank schema name even when theparent MetaData specifies MetaData.schema.

The quoting rules for the schema name are the same as those for thename parameter, in that quoting is applied for reserved words orcase-sensitive names; to enable unconditional quoting for the schemaname, specify the flag quote_schema=True to the constructor, or usethe quoted_name construct to specify the name.

useexisting – the same as Table.extend_existing.

comment –

Optional string that will render an SQL comment on tablecreation.

New in version 1.2: Added the Table.comment parameterto Table.

**kw – Additional keyword arguments not mentioned above aredialect specific, and passed in the form <dialectname>_<argname>.See the documentation regarding an individual dialect atDialects for detail on documented arguments.

eq()

inherited from the eq() method of object

Return self==value.

init(*args, **kw)
Constructor for Table.

This method is a no-op. See the top-leveldocumentation for Tablefor constructor arguments.

le()

inherited from the le() method of object

Return self<=value.

lt()

inherited from the lt() method of object

Return self

class sqlalchemy.schema.ThreadLocalMetaData
Bases: sqlalchemy.schema.MetaData

A MetaData variant that presents a different bind in every thread.

Makes the bind property of the MetaData a thread-local value, allowingthis collection of tables to be bound to different Engineimplementations or connections in each thread.

The ThreadLocalMetaData starts off bound to None in each thread. Bindsmust be made explicitly by assigning to the bind property or usingconnect(). You can also re-bind dynamically multiple times perthread, just like a regular MetaData.

init()
Construct a ThreadLocalMetaData.
property bind
The bound Engine or Connection for this thread.

This property may be assigned an Engine or Connection, or assigned astring or URL to automatically create a basic Engine for this bindwith create_engine().

dispose()
Dispose all bound engines, in all thread contexts.
is_bound()
True if there is a bind for this thread.