This section discusses the fundamental Table
, Column
and MetaData
objects.
A collection of metadata entities is stored in an object aptly named
MetaData
:
from sqlalchemy import *
metadata = MetaData()
MetaData
is a container object that keeps together
many different features of a database (or multiple databases) being described.
To represent a table, use the Table
class. Its two
primary arguments are the table name, then the
MetaData
object which it will be associated with.
The remaining positional arguments are mostly
Column
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. The
primary key of the table consists of the user_id
column. Multiple columns
may be assigned the primary_key=True
flag which denotes a multi-column
primary key, known as a composite primary key.
Note also that each column describes its datatype using objects corresponding
to genericized types, such as Integer
and
String
. SQLAlchemy features dozens of types of
varying 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.
The MetaData
object contains all of the schema
constructs we’ve associated with it. It supports a few methods of accessing
these table objects, such as the sorted_tables
accessor which returns a
list of each Table
object in order of foreign key
dependency (that is, each table is preceded by all tables which it
references):
>>> for t in metadata.sorted_tables:
... print(t.name)
user
user_preference
invoice
invoice_item
In most cases, individual Table
objects have been
explicitly declared, and these objects are typically accessed directly as
module-level variables in an application. Once a
Table
has been defined, it has a full set of
accessors which allow inspection of its properties. Given the following
Table
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 described
in Defining Foreign Keys. Methods of accessing information about this
table 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
Once you’ve defined some Table
objects, assuming
you’re working with a brand new database one thing you might want to do is
issue CREATE statements for those tables and their related constructs (as an
aside, it’s also quite possible that you don’t want to do this, if you
already have some preferred methodology such as tools included with your
database or an existing scripting system - if that’s the case, feel free to
skip this section - SQLAlchemy has no requirement that it be used to create
your tables).
The usual way to issue CREATE is to use
create_all()
on the
MetaData
object. This method will issue queries
that first check for the existence of each individual table, and if not found
will 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 constraints
between tables usually inline with the table definition itself, and for this
reason it also generates the tables in order of their dependency. There are
options to change this behavior such that ALTER TABLE
is used instead.
Dropping all tables is similarly achieved using the
drop_all()
method. This method does the
exact opposite of create_all()
- the
presence of each table is checked first, and tables are dropped in reverse
order of dependency.
Creating and dropping individual tables can be done via the create()
and
drop()
methods of Table
. These methods by
default 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 the
checkfirst=True
argument to create()
or drop()
:
employees.create(engine, checkfirst=True)
employees.drop(engine, checkfirst=False)
While SQLAlchemy directly supports emitting CREATE and DROP statements for schema
constructs, the ability to alter those constructs, usually via the ALTER statement
as well as other database-specific constructs, is outside of the scope of SQLAlchemy
itself. 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 the
DDL
construct, it’s a common practice to automate the maintenance of
database schemas in relation to application code using schema migration tools.
The SQLAlchemy project offers the Alembic migration tool for this purpose. 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 branch resolution.
Alembic supersedes the SQLAlchemy-Migrate project, which is the original migration tool for SQLAlchemy and is now considered legacy.
Some databases support the concept of multiple schemas. A
Table
can reference this by specifying the
schema
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 be
identified by the combination of financial_info
and remote_banks
. If
another table called financial_info
is referenced without the
remote_banks
schema, it will refer to a different
Table
. ForeignKey
objects can specify references to columns in this table using the form
remote_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 a
dotted name for longer schemes:
schema="dbo.scott"
Table
supports database-specific options. For
example, MySQL has different table backend types, including “MyISAM” and
“InnoDB”. This can be expressed with Table
using
mysql_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 be described in the individual documentation sections for each dialect.
Object Name | Description |
---|---|
Represents a column in a database table. |
|
A collection of |
|
Base class for items that define a database schema. |
|
Symbol indicating that a |
|
Represent a table in a database. |
|
A MetaData variant that presents a different |
sqlalchemy.schema.sqlalchemy.schema.
sqlalchemy.schema.
BLANK_SCHEMA
¶Symbol indicating that a Table
or Sequence
should have ‘None’ for its schema, even if the parent
MetaData
has specified a schema.
New in version 1.0.14.
sqlalchemy.schema.
Column
(*args, **kwargs)¶Represents a column in a database table.
Class signature
class sqlalchemy.schema.Column
(sqlalchemy.sql.base.DialectKWArgs
, sqlalchemy.schema.SchemaItem
, sqlalchemy.sql.expression.ColumnClause
)
sqlalchemy.schema.Column.
__eq__
(other)¶inherited from the sqlalchemy.sql.operators.ColumnOperators.__eq__
method of ColumnOperators
Implement the ==
operator.
In a column context, produces the clause a = b
.
If the target is None
, produces a IS NULL
.
sqlalchemy.schema.Column.
__init__
(*args, **kwargs)¶Construct a new Column
object.
name¶ –
The name of this column as represented in the database. This argument may be the first positional argument, or specified via keyword.
Names which contain no upper case characters will be treated as case insensitive names, and will not be quoted unless they are a reserved word. Names with any number of upper case characters will be quoted and sent exactly. Note that this behavior applies even for databases which standardize upper case names as case insensitive such as Oracle.
The name field may be omitted at construction time and applied
later, at any time before the Column is associated with a
Table
. This is to support convenient
usage within the declarative
extension.
type_¶ –
The column’s type, indicated using an instance which
subclasses TypeEngine
. If no arguments
are required for the type, the class of the type can be sent
as 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 argument
or specified by keyword.
If the type
is None
or is omitted, it will first default to
the special type NullType
. If and when this
Column
is made to refer to another column using
ForeignKey
and/or
ForeignKeyConstraint
, the type
of the remote-referenced column will be copied to this column as
well, at the moment that the foreign key is resolved against that
remote Column
object.
Changed in version 0.9.0: Support for propagation of type to a Column
from its
ForeignKey
object has been improved and should be
more reliable and timely.
*args¶ – Additional positional arguments include various
SchemaItem
derived constructs which will be applied
as options to the column. These include instances of
Constraint
, ForeignKey
,
ColumnDefault
,
Sequence
, Computed
. In some cases an
equivalent keyword argument is available such as server_default
,
default
and unique
.
autoincrement¶ –
Set up “auto increment” semantics for an integer
primary key column. The default value is the string "auto"
which indicates that a single-column primary key that is of
an INTEGER type with no stated client-side or python-side defaults
should receive auto increment semantics automatically;
all other varieties of primary key columns will not. This
includes that DDL such as PostgreSQL SERIAL or MySQL
AUTO_INCREMENT will be emitted for this column during a table
create, as well as that the column is assumed to generate new
integer primary key values when an INSERT statement invokes which
will be retrieved by the dialect.
The flag may be set to True
to indicate that a column which
is part of a composite (e.g. multi-column) primary key should
have autoincrement semantics, though note that only one column
within a primary key may have this setting. It can also
be set to True
to indicate autoincrement semantics on a
column that has a client-side or server-side default configured,
however note that not all dialects can accommodate all styles
of default as an “autoincrement”. It can also be
set to False
on a single-column primary key that has a
datatype of INTEGER in order to disable auto increment semantics
for that column.
Changed in version 1.1: The autoincrement flag now defaults to
"auto"
which indicates autoincrement semantics by default
for single-column integer primary keys only; for composite
(multi-column) primary keys, autoincrement is never implicitly
enabled; as always, autoincrement=True
will allow for
at most one of those columns to be an “autoincrement” column.
autoincrement=True
may also be set on a
Column
that 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
,
unless
the 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 a column that refers to another via foreign key, as such a column is required to refer to a value that originates from elsewhere.
The setting has these two effects on columns that meet the above criteria:
DDL issued for the column will include database-specific keywords intended to signify this column as an “autoincrement” column, such as AUTO INCREMENT on MySQL, SERIAL on PostgreSQL, and IDENTITY on MS-SQL. It does not issue AUTOINCREMENT for SQLite since this is a special SQLite flag that is not required for autoincrementing behavior.
See also
The column will be considered to be available using an
“autoincrement” method specific to the backend database, such
as calling upon cursor.lastrowid
, using RETURNING in an
INSERT statement to get at a sequence-generated value, or using
special functions such as “SELECT scope_identity()”.
These methods are highly specific to the DBAPIs and databases in
use and vary greatly, so care should be taken when associating
autoincrement=True
with a custom default generation function.
default¶ –
A scalar, Python callable, or
ColumnElement
expression representing the
default value for this column, which will be invoked upon insert
if this column is otherwise not specified in the VALUES clause of
the insert. This is a shortcut to using ColumnDefault
as
a positional argument; see that class for full detail on the
structure of the argument.
Contrast this argument to
Column.server_default
which creates a default generator on the database side.
See also
doc¶ – optional String that can be used by the ORM or similar
to document attributes on the Python side. This attribute does
not render SQL comments; use the
Column.comment
parameter for this purpose.
key¶ – An optional string identifier which will identify this
Column
object on the Table
.
When a key is provided,
this is the only identifier referencing the Column
within the
application, including ORM attribute mapping; the name
field
is used only when rendering SQL.
index¶ –
When True
, indicates that a Index
construct will be automatically generated for this
Column
, which will result in a “CREATE INDEX”
statement being emitted for the Table
when the DDL
create operation is invoked.
Using this flag is equivalent to making use of the
Index
construct explicitly at the level of the
Table
construct itself:
Table(
"some_table",
metadata,
Column("x", Integer),
Index("ix_some_table_x", "x")
)
To add the Index.unique
flag to the
Index
, set both the
Column.unique
and
Column.index
flags to True simultaneously,
which will have the effect of rendering the “CREATE UNIQUE INDEX”
DDL instruction instead of “CREATE INDEX”.
The name of the index is generated using the
default naming convention
which for the Index
construct is of the form
ix_<tablename>_<columnname>
.
As this flag is intended only as a convenience for the common case
of adding a single-column, default configured index to a table
definition, explicit use of the Index
construct
should be preferred for most use cases, including composite indexes
that encompass more than one column, indexes with SQL expressions
or ordering, backend-specific index configuration options, and
indexes that use a specific name.
Note
the Column.index
attribute on
Column
does not indicate if this column is indexed or not, only
if this flag was explicitly set here. To view indexes on
a column, view the Table.indexes
collection
or use Inspector.get_indexes()
.
info¶ – Optional data dictionary which will be populated into the
SchemaItem.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. When
True
, will normally generate nothing (in SQL this defaults to
“NULL”), except in some very specific backend-specific edge cases
where “NULL” may render explicitly. Defaults to True
unless
Column.primary_key
is also True
,
in which case it
defaults to False
. This parameter is only used when issuing
CREATE TABLE statements.
onupdate¶ –
A scalar, Python callable, or
ClauseElement
representing a
default value to be applied to the column within UPDATE
statements, which will be invoked upon update if this column is not
present in the SET clause of the update. This is a shortcut to
using ColumnDefault
as a positional argument with
for_update=True
.
See also
Column INSERT/UPDATE Defaults - complete discussion of onupdate
primary_key¶ – If True
, marks this column as a primary key
column. Multiple columns can have this flag set to specify
composite primary keys. As an alternative, the primary key of a
Table
can be specified via an explicit
PrimaryKeyConstraint
object.
server_default¶ –
A FetchedValue
instance, str, Unicode
or text()
construct representing
the 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 be
rendered as-is, without quotes:
Column('y', DateTime, server_default=text('NOW()'))
y DATETIME DEFAULT NOW()
Strings and text() will be converted into a
DefaultClause
object upon initialization.
Use FetchedValue
to indicate that an already-existing
column will generate a default value on the database side which
will be available to SQLAlchemy for post-fetch after inserts. This
construct does not specify any DDL and the implementation is left
to the database, such as via a trigger.
See also
Server-invoked DDL-Explicit Default Expressions - complete discussion of server side defaults
server_onupdate¶ –
A FetchedValue
instance
representing a database-side default generation function,
such as a trigger. This
indicates to SQLAlchemy that a newly generated value will be
available after updates. This construct does not actually
implement any kind of generation function within the database,
which instead must be specified separately.
Warning
This directive does not currently produce MySQL’s “ON UPDATE CURRENT_TIMESTAMP()” clause. See Rendering ON UPDATE CURRENT TIMESTAMP for MySQL’s explicit_defaults_for_timestamp for background on how to produce this clause.
quote¶ – Force quoting of this column’s name on or off,
corresponding to True
or False
. When left at its default
of None
, the column identifier will be quoted according to
whether the name is case sensitive (identifiers with at least one
upper case character are treated as case sensitive), or if it’s a
reserved word. This flag is only needed to force quoting of a
reserved word which is not known by the SQLAlchemy dialect.
unique¶ –
When True
, and the Column.index
parameter is left at its default value of False
,
indicates that a UniqueConstraint
construct will be automatically generated for this
Column
,
which will result in a “UNIQUE CONSTRAINT” clause referring
to this column being included
in the CREATE TABLE
statement emitted, when the DDL create
operation for the Table
object is invoked.
When this flag is True
while the
Column.index
parameter is simultaneously
set to True
, the effect instead is that a
Index
construct which includes the
Index.unique
parameter set to True
is generated. See the documentation for
Column.index
for additional detail.
Using this flag is equivalent to making use of the
UniqueConstraint
construct explicitly at the
level of the Table
construct itself:
Table(
"some_table",
metadata,
Column("x", Integer),
UniqueConstraint("x")
)
The UniqueConstraint.name
parameter
of the unique constraint object is left at its default value
of None
; in the absence of a naming convention
for the enclosing MetaData
, the UNIQUE CONSTRAINT
construct will be emitted as unnamed, which typically invokes
a database-specific naming convention to take place.
As this flag is intended only as a convenience for the common case
of adding a single-column, default configured unique constraint to a table
definition, explicit use of the UniqueConstraint
construct
should be preferred for most use cases, including composite constraints
that encompass more than one column, backend-specific index configuration options, and
constraints that use a specific name.
Note
the Column.unique
attribute on
Column
does not indicate if this column has a unique constraint or
not, only if this flag was explicitly set here. To view
indexes and unique constraints that may involve this column,
view the
Table.indexes
and/or
Table.constraints
collections or use
Inspector.get_indexes()
and/or
Inspector.get_unique_constraints()
system¶ –
When True
, indicates this is a “system” column,
that is a column which is automatically made available by the
database, and should not be included in the columns list for a
CREATE TABLE
statement.
For more elaborate scenarios where columns should be
conditionally rendered differently on different backends,
consider custom compilation rules for CreateColumn
.
comment¶ –
Optional string that will render an SQL comment on table creation.
New in version 1.2: Added the
Column.comment
parameter to Column
.
sqlalchemy.schema.Column.
__le__
(other)¶inherited from the sqlalchemy.sql.operators.ColumnOperators.__le__
method of ColumnOperators
Implement the <=
operator.
In a column context, produces the clause a <= b
.
sqlalchemy.schema.Column.
__lt__
(other)¶inherited from the sqlalchemy.sql.operators.ColumnOperators.__lt__
method of ColumnOperators
Implement the <
operator.
In a column context, produces the clause a < b
.
sqlalchemy.schema.Column.
__ne__
(other)¶inherited from the sqlalchemy.sql.operators.ColumnOperators.__ne__
method of ColumnOperators
Implement the !=
operator.
In a column context, produces the clause a != b
.
If the target is None
, produces a IS NOT NULL
.
sqlalchemy.schema.Column.
all_
()¶inherited from the ColumnOperators.all_()
method of ColumnOperators
Produce a all_()
clause against the
parent object.
This operator is only appropriate against a scalar subquery object, or for some backends an column expression that is against 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_()
New in version 1.1.
sqlalchemy.schema.Column.
anon_label
¶inherited from the ColumnElement.anon_label
attribute of ColumnElement
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 so
that expressions can reference anon_label
multiple times,
producing the same label name at compile time.
The compiler uses this function automatically at compile time for expressions that are known to be ‘unnamed’ like binary expressions and function calls.
sqlalchemy.schema.Column.
any_
()¶inherited from the ColumnOperators.any_()
method of ColumnOperators
Produce a any_()
clause against the
parent object.
This operator is only appropriate against a scalar subquery object, or for some backends an column expression that is against 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_()
New in version 1.1.
sqlalchemy.schema.Column.
classmethod argument_for
(dialect_name, argument_name, default)¶inherited from the DialectKWArgs.argument_for()
method of DialectKWArgs
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-argument
way adding extra arguments to the
DefaultDialect.construct_arguments
dictionary. This
dictionary provides a list of argument names accepted by various
schema-level constructs on behalf of a dialect.
New dialects should typically specify this dictionary all at once as a data member of the dialect class. The use case for ad-hoc addition of argument names is typically for end-user code that is also using a custom compilation scheme which consumes the additional arguments.
dialect_name¶ – name of a dialect. The dialect must be
locatable, else a NoSuchModuleError
is raised. The
dialect must also include an existing
DefaultDialect.construct_arguments
collection, indicating
that it participates in the keyword-argument validation and default
system, else ArgumentError
is raised. If the dialect does
not include this collection, then any keyword argument can be
specified on behalf of this dialect already. All dialects packaged
within SQLAlchemy include this collection, however for third party
dialects, support may vary.
argument_name¶ – name of the parameter.
default¶ – default value of the parameter.
New in version 0.9.4.
sqlalchemy.schema.Column.
asc
()¶inherited from the ColumnOperators.asc()
method of ColumnOperators
Produce a asc()
clause against the
parent object.
sqlalchemy.schema.Column.
between
(cleft, cright, symmetric=False)¶inherited from the ColumnOperators.between()
method of ColumnOperators
Produce a between()
clause against
the parent object, given the lower and upper range.
sqlalchemy.schema.Column.
bool_op
(opstring, precedence=0)¶inherited from the Operators.bool_op()
method of Operators
Return a custom boolean operator.
This method is shorthand for calling
Operators.op()
and passing the
Operators.op.is_comparison
flag with True.
New in version 1.2.0b3.
See also
sqlalchemy.schema.Column.
cast
(type_)¶inherited from the ColumnElement.cast()
method of ColumnElement
Produce a type cast, i.e. CAST(<expression> AS <type>)
.
This is a shortcut to the cast()
function.
New in version 1.0.7.
sqlalchemy.schema.Column.
collate
(collation)¶inherited from the ColumnOperators.collate()
method of ColumnOperators
Produce a collate()
clause against
the parent object, given the collation string.
See also
sqlalchemy.schema.Column.
compare
(other, use_proxies=False, equivalents=None, **kw)¶inherited from the ColumnElement.compare()
method of ColumnElement
Compare this ColumnElement to another.
Special arguments understood:
use_proxies¶ – when True, consider two columns that share a common base column as equivalent (i.e. shares_lineage())
equivalents¶ – a dictionary of columns as keys mapped to sets of columns. If the given “other” column is present in this dictionary, if any of the columns in the corresponding set() pass the comparison test, the result is True. This is used to expand the comparison to other columns that may be known to be equivalent to this one via foreign key or other criterion.
sqlalchemy.schema.Column.
compile
(default, bind=None, dialect=None, **kw)¶inherited from the ClauseElement.compile()
method of ClauseElement
Compile this SQL expression.
The return value is a Compiled
object.
Calling str()
or unicode()
on the returned value will yield a
string representation of the result. The
Compiled
object also can return a
dictionary of bind parameter names and values
using the params
accessor.
bind¶ – An Engine
or Connection
from which a
Compiled
will be acquired. This argument takes precedence over
this ClauseElement
’s bound engine, if any.
column_keys¶ – Used for INSERT and UPDATE statements, a list of
column names which should be present in the VALUES clause of the
compiled statement. If None
, all columns from the target table
object are rendered.
dialect¶ – A Dialect
instance from which a Compiled
will 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 does not support inline retrieval of newly generated primary key columns, will force the expression used to create the new primary key value to be rendered inline within the INSERT statement’s VALUES clause. This typically refers to Sequence execution but may also refer to any server-side default generation function associated with a primary key Column.
compile_kwargs¶ –
optional dictionary of additional parameters
that will be passed through to the compiler within all “visit”
methods. This allows any custom flag to be passed through to
a custom compilation construct, for example. It is also used
for 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.
sqlalchemy.schema.Column.
concat
(other)¶inherited from the ColumnOperators.concat()
method of ColumnOperators
Implement the ‘concat’ operator.
In a column context, produces the clause a || b
,
or uses the concat()
operator on MySQL.
sqlalchemy.schema.Column.
contains
(other, **kwargs)¶inherited from the ColumnOperators.contains()
method of ColumnOperators
Implement the ‘contains’ operator.
Produces a LIKE expression that tests against a match for the middle of 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 <other> expression
will behave like wildcards as well. For literal string
values, the ColumnOperators.contains.autoescape
flag
may be set to True
to apply escaping to occurrences of these
characters within the string value so that they match as themselves
and not as wildcard characters. Alternatively, the
ColumnOperators.contains.escape
parameter will establish
a given character as an escape character which can be of use when
the target expression is not a literal string.
other¶ – expression to be compared. This is usually a plain
string value, but can also be an arbitrary SQL expression. LIKE
wildcard characters %
and _
are not escaped by default unless
the ColumnOperators.contains.autoescape
flag is
set to True.
autoescape¶ –
boolean; when True, establishes an escape character
within the LIKE expression, then applies it to all occurrences of
"%"
, "_"
and the escape character itself within the
comparison value, which is assumed to be a literal string and not a
SQL 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: The
ColumnOperators.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 the
ESCAPE
keyword to establish that character as the escape
character. This character can then be placed preceding occurrences
of %
and _
to allow them to act as themselves and not
wildcard characters.
An expression such as:
somecolumn.contains("foo/%bar", escape="^")
Will render as:
somecolumn LIKE '%' || :param || '%' ESCAPE '^'
The parameter may also be combined with
ColumnOperators.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.
sqlalchemy.schema.Column.
copy
(**kw)¶Create a copy of this Column
, uninitialized.
This is used in Table.tometadata()
.
sqlalchemy.schema.Column.
desc
()¶inherited from the ColumnOperators.desc()
method of ColumnOperators
Produce a desc()
clause against the
parent object.
sqlalchemy.schema.Column.
dialect_kwargs
¶inherited from the DialectKWArgs.dialect_kwargs
attribute of DialectKWArgs
A collection of keyword arguments specified as dialect-specific options 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, which
contains all options known by this dialect including defaults.
The collection is also writable; keys are accepted of the
form <dialect>_<kwarg>
where the value will be assembled
into the list of options.
New in version 0.9.2.
Changed in version 0.9.4: The DialectKWArgs.dialect_kwargs
collection is now writable.
See also
DialectKWArgs.dialect_options
- nested dictionary form
sqlalchemy.schema.Column.
dialect_options
¶inherited from the DialectKWArgs.dialect_options
attribute of DialectKWArgs
A collection of keyword arguments specified as dialect-specific options to this construct.
This is a two-level nested registry, keyed to <dialect_name>
and <argument_name>
. For example, the postgresql_where
argument 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
sqlalchemy.schema.Column.
distinct
()¶inherited from the ColumnOperators.distinct()
method of ColumnOperators
Produce a distinct()
clause against the
parent object.
sqlalchemy.schema.Column.
endswith
(other, **kwargs)¶inherited from the ColumnOperators.endswith()
method of ColumnOperators
Implement the ‘endswith’ operator.
Produces a LIKE expression that tests against a match for the end of 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 <other> expression
will behave like wildcards as well. For literal string
values, the ColumnOperators.endswith.autoescape
flag
may be set to True
to apply escaping to occurrences of these
characters within the string value so that they match as themselves
and not as wildcard characters. Alternatively, the
ColumnOperators.endswith.escape
parameter will establish
a given character as an escape character which can be of use when
the target expression is not a literal string.
other¶ – expression to be compared. This is usually a plain
string value, but can also be an arbitrary SQL expression. LIKE
wildcard characters %
and _
are not escaped by default unless
the ColumnOperators.endswith.autoescape
flag is
set to True.
autoescape¶ –
boolean; when True, establishes an escape character
within the LIKE expression, then applies it to all occurrences of
"%"
, "_"
and the escape character itself within the
comparison value, which is assumed to be a literal string and not a
SQL expression.
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: The
ColumnOperators.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¶ –
a character which when given will render with the
ESCAPE
keyword to establish that character as the escape
character. This character can then be placed preceding occurrences
of %
and _
to allow them to act as themselves and not
wildcard characters.
An expression such as:
somecolumn.endswith("foo/%bar", escape="^")
Will render as:
somecolumn LIKE '%' || :param ESCAPE '^'
The parameter may also be combined with
ColumnOperators.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.
sqlalchemy.schema.Column.
expression
¶inherited from the ColumnElement.expression
attribute of ColumnElement
Return a column expression.
Part of the inspection interface; returns self.
sqlalchemy.schema.Column.
get_children
(schema_visitor=False, **kwargs)¶used to allow SchemaVisitor access
sqlalchemy.schema.Column.
ilike
(other, escape=None)¶inherited from the ColumnOperators.ilike()
method of ColumnOperators
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%"))
See also
sqlalchemy.schema.Column.
in_
(other)¶inherited from the ColumnOperators.in_()
method of ColumnOperators
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 of bound parameters the same length as the list given:
WHERE COL IN (?, ?, ?)
A list of tuples may be provided if the comparison is against a
tuple_()
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 behaviors
in older SQLAlchemy versions, see
create_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 it
includes the bindparam.expanding
flag:
stmt.where(column.in_(bindparam('value', expanding=True)))
In this calling form, the expression renders a special non-SQL placeholder expression that looks like:
WHERE COL IN ([EXPANDING_value])
This placeholder expression is intercepted at statement execution time to be converted into the variable number of bound parameter form 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. On SQLite this would be:
WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
New in version 1.3: “expanding” bound parameters now support empty lists
a select()
construct,
which is usually a correlated
scalar 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)
other¶ – a list of literals, a select()
construct,
or a bindparam()
construct that includes the
bindparam.expanding
flag set to True.
sqlalchemy.schema.Column.
info
¶inherited from the SchemaItem.info
attribute of SchemaItem
Info dictionary associated with the object, allowing user-defined
data 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
.
sqlalchemy.schema.Column.
is_
(other)¶inherited from the ColumnOperators.is_()
method of ColumnOperators
Implement the IS
operator.
Normally, IS
is generated automatically when comparing to a
value of None
, which resolves to NULL
. However, explicit
usage of IS
may be desirable if comparing to boolean values
on certain platforms.
See also
sqlalchemy.schema.Column.
is_distinct_from
(other)¶inherited from the ColumnOperators.is_distinct_from()
method of ColumnOperators
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.
sqlalchemy.schema.Column.
isnot
(other)¶inherited from the ColumnOperators.isnot()
method of ColumnOperators
Implement the IS NOT
operator.
Normally, IS NOT
is generated automatically when comparing to a
value of None
, which resolves to NULL
. However, explicit
usage of IS NOT
may be desirable if comparing to boolean values
on certain platforms.
See also
sqlalchemy.schema.Column.
isnot_distinct_from
(other)¶inherited from the ColumnOperators.isnot_distinct_from()
method of ColumnOperators
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.
sqlalchemy.schema.Column.
kwargs
¶inherited from the DialectKWArgs.kwargs
attribute of DialectKWArgs
A synonym for DialectKWArgs.dialect_kwargs
.
sqlalchemy.schema.Column.
label
(name)¶inherited from the ColumnElement.label()
method of ColumnElement
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.
sqlalchemy.schema.Column.
like
(other, escape=None)¶inherited from the ColumnOperators.like()
method of ColumnOperators
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%"))
See also
sqlalchemy.schema.Column.
match
(other, **kwargs)¶inherited from the ColumnOperators.match()
method of ColumnOperators
Implements a database-specific ‘match’ operator.
ColumnOperators.match()
attempts to resolve to
a 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 emit the operator as “MATCH”. This is compatible with SQLite, for example.
sqlalchemy.schema.Column.
notilike
(other, escape=None)¶inherited from the ColumnOperators.notilike()
method of ColumnOperators
implement the NOT ILIKE
operator.
This is equivalent to using negation with
ColumnOperators.ilike()
, i.e. ~x.ilike(y)
.
See also
sqlalchemy.schema.Column.
notin_
(other)¶inherited from the ColumnOperators.notin_()
method of ColumnOperators
implement the NOT IN
operator.
This is equivalent to using negation with
ColumnOperators.in_()
, i.e. ~x.in_(y)
.
In the case that other
is an empty sequence, the compiler
produces an “empty not in” expression. This defaults to the
expression “1 = 1” to produce true in all cases. The
create_engine.empty_in_strategy
may be used to
alter this behavior.
Changed in version 1.2: The ColumnOperators.in_()
and
ColumnOperators.notin_()
operators
now produce a “static” expression for an empty IN sequence
by default.
See also
sqlalchemy.schema.Column.
notlike
(other, escape=None)¶inherited from the ColumnOperators.notlike()
method of ColumnOperators
implement the NOT LIKE
operator.
This is equivalent to using negation with
ColumnOperators.like()
, i.e. ~x.like(y)
.
See also
sqlalchemy.schema.Column.
nullsfirst
()¶inherited from the ColumnOperators.nullsfirst()
method of ColumnOperators
Produce a nullsfirst()
clause against the
parent object.
sqlalchemy.schema.Column.
nullslast
()¶inherited from the ColumnOperators.nullslast()
method of ColumnOperators
Produce a nullslast()
clause against the
parent object.
sqlalchemy.schema.Column.
op
(opstring, precedence=0, is_comparison=False, return_type=None)¶inherited from the Operators.op()
method of Operators
Produce a generic operator function.
e.g.:
somecolumn.op("*")(5)
produces:
somecolumn * 5
This function can also be used to make bitwise operators explicit. For example:
somecolumn.op('&')(0xff)
is a bitwise AND of the value in somecolumn
.
operator¶ – a string which will be output as the infix operator between this element and the expression passed to the generated function.
precedence¶ – precedence to apply to the operator, when
parenthesizing expressions. A lower number will cause the expression
to be parenthesized when applied against another operator with
higher precedence. The default value of 0
is lower than all
operators except for the comma (,
) and AS
operators.
A value of 100 will be higher or equal to all operators, and -100
will 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 boolean
true/false value, like ==
, >
, etc. This flag should be set
so that ORM relationships can establish that the operator is a
comparison operator when used in a custom join condition.
New in version 0.9.2: - added the
Operators.op.is_comparison
flag.
return_type¶ –
a TypeEngine
class or object that will
force the return type of an expression produced by this operator
to be of that type. By default, operators that specify
Operators.op.is_comparison
will resolve to
Boolean
, and those that do not will be of the same
type as the left-hand operand.
New in version 1.2.0b3: - added the
Operators.op.return_type
argument.
sqlalchemy.schema.Column.
operate
(op, *other, **kwargs)¶inherited from the ColumnElement.operate()
method of ColumnElement
Operate on an argument.
This is the lowest level of operation, raises
NotImplementedError
by default.
Overriding this on a subclass can allow common
behavior to be applied to all operations.
For example, overriding ColumnOperators
to apply func.lower()
to the left and right
side:
class MyComparator(ColumnOperators):
def operate(self, op, other):
return op(func.lower(self), func.lower(other))
sqlalchemy.schema.Column.
quote
¶inherited from the SchemaItem.quote
attribute of SchemaItem
Return the value of the quote
flag passed
to this schema object, for those schema items which
have 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.
sqlalchemy.schema.Column.
references
(column)¶Return True if this Column references the given column via foreign key.
sqlalchemy.schema.Column.
reverse_operate
(op, other, **kwargs)¶inherited from the ColumnElement.reverse_operate()
method of ColumnElement
Reverse operate on an argument.
Usage is the same as operate()
.
sqlalchemy.schema.Column.
self_group
(against=None)¶inherited from the ColumnElement.self_group()
method of ColumnElement
Apply a ‘grouping’ to this ClauseElement
.
This method is overridden by subclasses to return a “grouping”
construct, i.e. parenthesis. In particular it’s used by “binary”
expressions to provide a grouping around themselves when placed into a
larger expression, as well as by select()
constructs when placed into the FROM clause of another
select()
. (Note that subqueries should be
normally created using the Select.alias()
method,
as many
platforms require nested SELECT statements to be named).
As expressions are composed together, the application of
self_group()
is automatic - end-user code should never
need to use this method directly. Note that SQLAlchemy’s
clause constructs take operator precedence into account -
so parenthesis might not be needed, for example, in
an expression like x OR (y AND z)
- AND takes precedence
over OR.
The base self_group()
method of
ClauseElement
just returns self.
sqlalchemy.schema.Column.
shares_lineage
(othercolumn)¶inherited from the ColumnElement.shares_lineage()
method of ColumnElement
Return True if the given ColumnElement
has a common ancestor to this ColumnElement
.
sqlalchemy.schema.Column.
startswith
(other, **kwargs)¶inherited from the ColumnOperators.startswith()
method of ColumnOperators
Implement the startswith
operator.
Produces a LIKE expression that tests against a match for the start of 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 <other> expression
will behave like wildcards as well. For literal string
values, the ColumnOperators.startswith.autoescape
flag
may be set to True
to apply escaping to occurrences of these
characters within the string value so that they match as themselves
and not as wildcard characters. Alternatively, the
ColumnOperators.startswith.escape
parameter will establish
a given character as an escape character which can be of use when
the target expression is not a literal string.
other¶ – expression to be compared. This is usually a plain
string value, but can also be an arbitrary SQL expression. LIKE
wildcard characters %
and _
are not escaped by default unless
the ColumnOperators.startswith.autoescape
flag is
set to True.
autoescape¶ –
boolean; when True, establishes an escape character
within the LIKE expression, then applies it to all occurrences of
"%"
, "_"
and the escape character itself within the
comparison value, which is assumed to be a literal string and not a
SQL expression.
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: The
ColumnOperators.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¶ –
a character which when given will render with the
ESCAPE
keyword to establish that character as the escape
character. This character can then be placed preceding occurrences
of %
and _
to allow them to act as themselves and not
wildcard characters.
An expression such as:
somecolumn.startswith("foo/%bar", escape="^")
Will render as:
somecolumn LIKE :param || '%' ESCAPE '^'
The parameter may also be combined with
ColumnOperators.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.
sqlalchemy.schema.
MetaData
(bind=None, reflect=False, schema=None, quote_schema=None, naming_convention=None, info=None)¶A collection of Table
objects and their associated schema
constructs.
Holds a collection of Table
objects as well as
an optional binding to an Engine
or
Connection
. If bound, the Table
objects
in the collection and their columns may participate in implicit SQL
execution.
The Table
objects themselves are stored in the
MetaData.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
Class signature
class sqlalchemy.schema.MetaData
(sqlalchemy.schema.SchemaItem
)
sqlalchemy.schema.MetaData.
__init__
(bind=None, reflect=False, schema=None, quote_schema=None, naming_convention=None, info=None)¶Create a new MetaData object.
bind¶ – An Engine or Connection to bind to. May also be a string or URL
instance, these are passed to create_engine()
and
this MetaData
will
be bound to the resulting engine.
reflect¶ –
Optional, automatically load all tables from the bound database.
Defaults to False. MetaData.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 with
this MetaData
. Defaults to None
.
When this value is set, any Table
or
Sequence
which specifies None
for the schema parameter will instead
have this schema name defined. To build a Table
or Sequence
that still has None
for the schema
even when this parameter is present, use the BLANK_SCHEMA
symbol.
Note
As referred above, the MetaData.schema
parameter
only refers to the default value that will be applied to
the Table.schema
parameter of an incoming
Table
object. It does not refer to how the
Table
is catalogued within the
MetaData
,
which remains consistent vs. a MetaData
collection
that does not define this parameter. The
Table
within the MetaData
will still be keyed based on its
schema-qualified name, e.g.
my_metadata.tables["some_schema.my_table"]
.
The current behavior of the ForeignKey
object is to
circumvent this restriction, where it can locate a table given
the table name alone, where the schema will be assumed to be
present from this value as specified on the owning
MetaData
collection. However,
this implies that a
table qualified with BLANK_SCHEMA cannot currently be referred
to by string name from ForeignKey
.
Other parts of
SQLAlchemy such as Declarative may not have similar behaviors
built in, however may do so in a future release, along with a
consistent method of referring to a table in BLANK_SCHEMA.
quote_schema¶ – Sets the quote_schema
flag for those Table
,
Sequence
, and other objects which make usage of the
local schema
name.
info¶ –
Optional data dictionary which will be populated into the
SchemaItem.info
attribute of this object.
New in version 1.0.0.
naming_convention¶ –
a dictionary referring to values which
will establish default naming conventions for Constraint
and Index
objects, for those objects which are not given
a 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 used to define new naming tokens.
The values associated with each “constraint class” or “constraint
mnemonic” key are string naming templates, such as
"uq_%(table_name)s_%(column_0_name)s"
,
which describe how the name should be composed. The values
associated with user-defined “token” keys should be callables of the
form fn(constraint, table)
, which accepts the constraint/index
object and Table
as arguments, returning a string
result.
The built-in names are as follows, some of which may only be available for certain types of constraint:
%(table_name)s
- the name of theTable
object associated with the constraint.
%(referred_table_name)s
- the name of theTable
object associated with the referencing target of aForeignKeyConstraint
.
%(column_0_name)s
- the name of theColumn
at index position “0” within the constraint.
%(column_0N_name)s
- the name of allColumn
objects in order within the constraint, joined without a separator.
%(column_0_N_name)s
- the name of allColumn
objects in order within the constraint, joined with an underscore as a separator.
%(column_0_label)s
,%(column_0N_label)s
,%(column_0_N_label)s
- the label of either the zerothColumn
or allColumns
, separated with or without an underscore
%(column_0_key)s
,%(column_0N_key)s
,%(column_0_N_key)s
- the key of either the zerothColumn
or allColumns
, separated with or 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 which render the names/keys/labels of columns that are referenced by aForeignKeyConstraint
.
%(constraint_name)s
- a special key that refers to the existing name given to the constraint. When this key is present, theConstraint
object’s existing name will be replaced with one that is composed from template string that uses this token. When this token is present, it is required that theConstraint
is given an explicit name ahead of time.user-defined: any additional token may be implemented by passing it along with a
fn(constraint, table)
callable to the naming_convention dictionary.
New in version 1.3.0: - added new %(column_0N_name)s
,
%(column_0_N_name)s
, and related tokens that produce
concatenations of names, keys, or labels for all columns referred
to by a given constraint.
See also
Configuring Constraint Naming Conventions - for detailed usage examples.
sqlalchemy.schema.MetaData.
append_ddl_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
.
sqlalchemy.schema.MetaData.
bind
¶An Engine
or Connection
to which this
MetaData
is bound.
Typically, a Engine
is assigned to this attribute
so that “implicit execution” may be used, or alternatively
as a means of providing engine binding information to an
ORM Session
object:
engine = create_engine("someurl://")
metadata.bind = engine
See also
Connectionless Execution, Implicit Execution - background on “bound metadata”
sqlalchemy.schema.MetaData.
clear
()¶Clear all Table objects from this MetaData.
sqlalchemy.schema.MetaData.
create_all
(bind=None, tables=None, checkfirst=True)¶Create all tables stored in this metadata.
Conditional by default, will not attempt to recreate tables already present in the target database.
bind¶ – A Connectable
used to access the
database; if None, uses the existing bind on this MetaData
, if
any.
tables¶ – Optional list of Table
objects, which is a subset of the total
tables in the MetaData
(others are ignored).
checkfirst¶ – Defaults to True, don’t issue CREATEs for tables already present in the target database.
sqlalchemy.schema.MetaData.
drop_all
(bind=None, tables=None, checkfirst=True)¶Drop all tables stored in this metadata.
Conditional by default, will not attempt to drop tables not present in the target database.
bind¶ – A Connectable
used to access the
database; if None, uses the existing bind on this MetaData
, if
any.
tables¶ – Optional list of Table
objects, which is a subset of the
total tables in the MetaData
(others are ignored).
checkfirst¶ – Defaults to True, only issue DROPs for tables confirmed to be present in the target database.
sqlalchemy.schema.MetaData.
is_bound
()¶True if this MetaData is bound to an Engine or Connection.
sqlalchemy.schema.MetaData.
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 any
table available in the database but not yet present in the
MetaData
. May be called multiple times to pick up tables recently
added to the database, however no special action is taken if a table
in this MetaData
no longer exists in the database.
bind¶ – A Connectable
used to access the database; if None, uses
the existing bind on this MetaData
, if any.
schema¶ – Optional, query and reflect tables from an alternate schema.
If None, the schema associated with this MetaData
is used, if any.
views¶ – If True, also reflect views.
only¶ –
Optional. Load only a sub-set of available named tables. May be specified as a sequence of names or a callable.
If a sequence of names is provided, only those tables will be
reflected. An error is raised if a table is requested but not
available. Named tables already present in this MetaData
are
ignored.
If a callable is provided, it will be used as a boolean predicate to
filter the list of potential table names. The callable is called
with a table name and this MetaData
instance as positional
arguments and should return a true value for any table to reflect.
extend_existing¶ –
Passed along to each Table
as
Table.extend_existing
.
New in version 0.9.1.
autoload_replace¶ –
Passed along to each Table
as
Table.autoload_replace
.
New in version 0.9.1.
resolve_fks¶ –
if True, reflect Table
objects linked
to ForeignKey
objects located in each
Table
.
For MetaData.reflect()
,
this has the effect of reflecting
related tables that might otherwise not be in the list of tables
being reflected, for example if the referenced table is in a
different schema or is omitted via the
MetaData.reflect.only
parameter. When False,
ForeignKey
objects are not followed to the
Table
in which they link, however if the related table is also part of the
list of tables that would be reflected in any case, the
ForeignKey
object will still resolve to its related
Table
after the MetaData.reflect()
operation is
complete. Defaults to True.
New in version 1.3.0.
See also
**dialect_kwargs¶ –
Additional keyword arguments not mentioned
above are dialect specific, and passed in the form
<dialectname>_<argname>
. See the documentation regarding an
individual dialect at Dialects for detail on
documented arguments.
New in version 0.9.2: - Added
MetaData.reflect.**dialect_kwargs
to support dialect-level reflection options for allTable
objects reflected.
sqlalchemy.schema.MetaData.
remove
(table)¶Remove the given Table object from this MetaData.
sqlalchemy.schema.MetaData.
sorted_tables
¶Returns a list of Table
objects sorted in order of
foreign key dependency.
The sorting will place Table
objects that have dependencies
first, before the dependencies themselves, representing the
order in which they can be created. To get the order in which
the tables would be dropped, use the reversed()
Python built-in.
Warning
The MetaData.sorted_tables
attribute cannot by itself
accommodate automatic resolution of dependency cycles between
tables, which are usually caused by mutually dependent foreign key
constraints. When these cycles are detected, the foreign keys
of these tables are omitted from consideration in the sort.
A warning is emitted when this condition occurs, which will be an
exception raise in a future release. Tables which are not part
of the cycle will still be returned in dependency order.
To resolve these cycles, the
ForeignKeyConstraint.use_alter
parameter may be
applied to those constraints which create a cycle. Alternatively,
the sort_tables_and_constraints()
function will
automatically return foreign key constraints in a separate
collection when cycles are detected so that they may be applied
to a schema separately.
Changed in version 1.3.17: - a warning is emitted when
MetaData.sorted_tables
cannot perform a proper sort
due to cyclical dependencies. This will be an exception in a
future release. Additionally, the sort will continue to return
other tables not involved in the cycle in dependency order which
was not the case previously.
sqlalchemy.schema.MetaData.
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 same
as Table.name
. For a table with a schema,
it is typically of the
form schemaname.tablename
.
See also
sqlalchemy.schema.
SchemaItem
¶Base class for items that define a database schema.
Class signature
class sqlalchemy.schema.SchemaItem
(sqlalchemy.sql.expression.SchemaEventTarget
, sqlalchemy.sql.visitors.Visitable
)
sqlalchemy.schema.SchemaItem.
get_children
(**kwargs)¶used to allow SchemaVisitor access
sqlalchemy.schema.SchemaItem.
info
¶Info dictionary associated with the object, allowing user-defined
data 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
.
sqlalchemy.schema.SchemaItem.
quote
¶Return the value of the quote
flag passed
to this schema object, for those schema items which
have 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.
sqlalchemy.schema.
Table
(*args, **kw)¶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 based
on its name and optional schema name within the given
MetaData
object. Calling the Table
constructor with the same name and same MetaData
argument
a second time will return the same Table
object - in this way
the Table
constructor acts as a registry function.
See also
Describing Databases with MetaData - Introduction to database metadata
Constructor arguments are as follows:
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
within
the 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 characters will be treated as case insensitive names, and will not be quoted unless they are a reserved word or contain special characters. A name with any number of upper case characters is considered to be case sensitive, and will be sent as quoted.
To enable unconditional quoting for the table name, specify the flag
quote=True
to the constructor, or use the quoted_name
construct to specify the name.
metadata¶ – a MetaData
object which will contain this
table. The metadata is used as a point of association of this table
with other tables which are referenced via foreign key. It also
may be used to associate this table with a particular
Connectable
.
*args¶ – Additional positional arguments are used primarily
to add the list of Column
objects contained within this
table. Similar to the style of a CREATE TABLE statement, other
SchemaItem
constructs may be added here, including
PrimaryKeyConstraint
, and
ForeignKeyConstraint
.
autoload¶ –
Defaults to False, unless
Table.autoload_with
is set in which case it defaults to True; Column
objects
for this table should be reflected from the database, possibly
augmenting or replacing existing Column
objects that were
explicitly specified.
Changed in version 1.0.0: setting the
Table.autoload_with
parameter implies that Table.autoload
will default
to True.
See also
autoload_replace¶ –
Defaults to True
; when using
Table.autoload
in conjunction with Table.extend_existing
,
indicates
that Column
objects present in the already-existing
Table
object should be replaced with columns of the same
name retrieved from the autoload process. When False
, columns
already present under existing names will be omitted from the
reflection process.
Note that this setting does not impact Column
objects
specified programmatically within the call to Table
that
also is autoloading; those Column
objects will always
replace existing columns of the same name when
Table.extend_existing
is True
.
autoload_with¶ –
An Engine
or
Connection
object
with which this Table
object will be reflected; when
set to a non-None value, it implies that
Table.autoload
is True
. If left unset, but Table.autoload
is
explicitly set to True
, an autoload operation will attempt to
proceed by locating an Engine
or
Connection
bound
to the underlying MetaData
object.
See also
extend_existing¶ –
When True
, indicates that if this
Table
is already present in the given
MetaData
,
apply further arguments within the constructor to the existing
Table
.
If Table.extend_existing
or
Table.keep_existing
are not set,
and the given name
of the new Table
refers to a Table
that is
already present in the target MetaData
collection,
and
this Table
specifies additional columns or other constructs
or flags that modify the table’s state, an
error is raised. The purpose of these two mutually-exclusive flags
is to specify what action should be taken when a
Table
is specified that matches an existing Table
,
yet specifies
additional constructs.
Table.extend_existing
will also work in conjunction
with Table.autoload
to run a new reflection
operation against the database, even if a Table
of the same name is already present in the target
MetaData
; newly reflected Column
objects
and other options will be added into the state of the
Table
, potentially overwriting existing columns
and options of the same name.
As is always the case with Table.autoload
,
Column
objects can be specified in the same
Table
constructor, which will take precedence. Below, the existing
table mytable
will be augmented with Column
objects
both reflected from the database, as well as the given
Column
named “y”:
Table("mytable", metadata,
Column('y', Integer),
extend_existing=True,
autoload=True,
autoload_with=engine
)
implicit_returning¶ – True by default - indicates that
RETURNING can be used by default to fetch newly inserted primary key
values, for backends which support this. Note that
create_engine()
also provides an implicit_returning
flag.
include_columns¶ – A list of strings indicating a subset of
columns to be loaded via the autoload
operation; table columns who
aren’t present in this list will not be represented on the resulting
Table
object. Defaults to None
which indicates all columns
should be reflected.
resolve_fks¶ –
Whether or not to reflect Table
objects
related to this one via ForeignKey
objects, when
Table.autoload
or
Table.autoload_with
is
specified. Defaults to True. Set to False to disable reflection of
related tables as ForeignKey
objects are encountered; may be
used either to save on SQL calls or to avoid issues with related tables
that can’t be accessed. Note that if a related table is already present
in the MetaData
collection, or becomes present later,
a
ForeignKey
object associated with this
Table
will
resolve to that table normally.
New in version 1.3.
See also
info¶ – Optional data dictionary which will be populated into the
SchemaItem.info
attribute of this object.
keep_existing¶ –
When True
, indicates that if this Table
is already present in the given MetaData
, ignore
further arguments within the constructor to the existing
Table
, and return the Table
object as
originally created. This is to allow a function that wishes
to define a new Table
on first call, but on
subsequent calls will return the same Table
,
without any of the declarations (particularly constraints)
being applied a second time.
If Table.extend_existing
or
Table.keep_existing
are not set,
and the given name
of the new Table
refers to a Table
that is
already present in the target MetaData
collection,
and
this Table
specifies additional columns or other constructs
or flags that modify the table’s state, an
error is raised. The purpose of these two mutually-exclusive flags
is to specify what action should be taken when a
Table
is specified that matches an existing Table
,
yet specifies
additional constructs.
See also
listeners¶ –
A list of tuples of the form (<eventname>, <fn>)
which will be passed to listen()
upon construction.
This alternate hook to listen()
allows the establishment
of a listener function specific to this Table
before
the “autoload” process begins. Particularly useful for
the 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 already
be present in the given MetaData
collection, else
an exception is raised.
prefixes¶ – A list of strings to insert after CREATE in the CREATE TABLE statement. They will be separated by spaces.
quote¶ – Force quoting of this table’s name on or off, corresponding
to True
or False
. When left at its default of None
,
the column identifier will be quoted according to whether the name is
case sensitive (identifiers with at least one upper case character are
treated as case sensitive), or if it’s a reserved word. This flag
is only needed to force quoting of a reserved word which is not known
by the SQLAlchemy dialect.
quote_schema¶ – same as ‘quote’ but applies to the schema identifier.
schema¶ –
The schema name for this table, which is required if
the table resides in a schema other than the default selected schema
for the engine’s database connection. Defaults to None
.
If the owning MetaData
of this Table
specifies its
own MetaData.schema
parameter,
then that schema name will
be applied to this Table
if the schema parameter here is set
to None
. To set a blank schema name on a Table
that
would 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 to
allow a Table
to have a blank schema name even when the
parent MetaData
specifies
MetaData.schema
.
The quoting rules for the schema name are the same as those for the
name
parameter, in that quoting is applied for reserved words or
case-sensitive names; to enable unconditional quoting for the schema
name, specify the flag quote_schema=True
to the constructor, or use
the quoted_name
construct to specify the name.
useexisting¶ – the same as Table.extend_existing
.
comment¶ –
Optional string that will render an SQL comment on table creation.
New in version 1.2: Added the Table.comment
parameter
to Table
.
**kw¶ – Additional keyword arguments not mentioned above are
dialect specific, and passed in the form <dialectname>_<argname>
.
See the documentation regarding an individual dialect at
Dialects for detail on documented arguments.
Class signature
class sqlalchemy.schema.Table
(sqlalchemy.sql.base.DialectKWArgs
, sqlalchemy.schema.SchemaItem
, sqlalchemy.sql.expression.TableClause
)
sqlalchemy.schema.Table.
__init__
(*args, **kw)¶Constructor for Table
.
This method is a no-op. See the top-level
documentation for Table
for constructor arguments.
sqlalchemy.schema.Table.
add_is_dependent_on
(table)¶Add a ‘dependency’ for this Table.
This is another Table object which must be created first before this one can, or dropped after this one.
Usually, dependencies between tables are determined via ForeignKey objects. However, for other situations that create dependencies outside of foreign keys (rules, inheriting), this method can manually establish such a link.
sqlalchemy.schema.Table.
alias
(name=None, flat=False)¶inherited from the FromClause.alias()
method of FromClause
Return an alias of this FromClause
.
E.g.:
a2 = some_table.alias('a2')
The above code creates an Alias
object which can be used
as a FROM clause in any SELECT statement.
sqlalchemy.schema.Table.
append_column
(column)¶Append a Column
to this Table
.
The “key” of the newly added Column
, i.e. the
value of its .key
attribute, will then be available
in the .c
collection of this Table
, and the
column definition will be included in any CREATE TABLE, SELECT,
UPDATE, etc. statements generated from this Table
construct.
Note that this does not change the definition of the table as it exists within any underlying database, assuming that table has already been created in the database. Relational databases support the addition of columns to existing tables using the SQL ALTER command, which would need to be emitted for an already-existing table that doesn’t contain the newly added column.
sqlalchemy.schema.Table.
append_constraint
(constraint)¶Append a Constraint
to this
Table
.
This has the effect of the constraint being included in any
future CREATE TABLE statement, assuming specific DDL creation
events have not been associated with the given
Constraint
object.
Note that this does not produce the constraint within the
relational database automatically, for a table that already exists
in the database. To add a constraint to an
existing relational database table, the SQL ALTER command must
be used. SQLAlchemy also provides the
AddConstraint
construct which can produce this SQL when
invoked as an executable clause.
sqlalchemy.schema.Table.
append_ddl_listener
(event_name, listener)¶Append a DDL event listener to this Table
.
Deprecated since version 0.7: the Table.append_ddl_listener()
method is deprecated and will be removed in a future release. Please refer to DDLEvents
.
sqlalchemy.schema.Table.
classmethod argument_for
(dialect_name, argument_name, default)¶inherited from the DialectKWArgs.argument_for()
method of DialectKWArgs
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-argument
way adding extra arguments to the
DefaultDialect.construct_arguments
dictionary. This
dictionary provides a list of argument names accepted by various
schema-level constructs on behalf of a dialect.
New dialects should typically specify this dictionary all at once as a data member of the dialect class. The use case for ad-hoc addition of argument names is typically for end-user code that is also using a custom compilation scheme which consumes the additional arguments.
dialect_name¶ – name of a dialect. The dialect must be
locatable, else a NoSuchModuleError
is raised. The
dialect must also include an existing
DefaultDialect.construct_arguments
collection, indicating
that it participates in the keyword-argument validation and default
system, else ArgumentError
is raised. If the dialect does
not include this collection, then any keyword argument can be
specified on behalf of this dialect already. All dialects packaged
within SQLAlchemy include this collection, however for third party
dialects, support may vary.
argument_name¶ – name of the parameter.
default¶ – default value of the parameter.
New in version 0.9.4.
sqlalchemy.schema.Table.
bind
¶Return the connectable associated with this Table.
sqlalchemy.schema.Table.
c
¶inherited from the FromClause.c
attribute of FromClause
An alias for the columns
attribute.
sqlalchemy.schema.Table.
columns
¶inherited from the FromClause.columns
attribute of FromClause
A named-based collection of ColumnElement
objects
maintained by this FromClause
.
The columns
, or c
collection, is the gateway
to the construction of SQL expressions using table-bound or
other selectable-bound columns:
select([mytable]).where(mytable.c.somecolumn == 5)
sqlalchemy.schema.Table.
compare
(other, **kw)¶inherited from the ClauseElement.compare()
method of ClauseElement
Compare this ClauseElement
to
the given ClauseElement
.
Subclasses should override the default behavior, which is a straight identity comparison.
**kw are arguments consumed by subclass compare()
methods and
may be used to modify the criteria for comparison
(see ColumnElement
).
sqlalchemy.schema.Table.
compile
(default, bind=None, dialect=None, **kw)¶inherited from the ClauseElement.compile()
method of ClauseElement
Compile this SQL expression.
The return value is a Compiled
object.
Calling str()
or unicode()
on the returned value will yield a
string representation of the result. The
Compiled
object also can return a
dictionary of bind parameter names and values
using the params
accessor.
bind¶ – An Engine
or Connection
from which a
Compiled
will be acquired. This argument takes precedence over
this ClauseElement
’s bound engine, if any.
column_keys¶ – Used for INSERT and UPDATE statements, a list of
column names which should be present in the VALUES clause of the
compiled statement. If None
, all columns from the target table
object are rendered.
dialect¶ – A Dialect
instance from which a Compiled
will 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 does not support inline retrieval of newly generated primary key columns, will force the expression used to create the new primary key value to be rendered inline within the INSERT statement’s VALUES clause. This typically refers to Sequence execution but may also refer to any server-side default generation function associated with a primary key Column.
compile_kwargs¶ –
optional dictionary of additional parameters
that will be passed through to the compiler within all “visit”
methods. This allows any custom flag to be passed through to
a custom compilation construct, for example. It is also used
for 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.
sqlalchemy.schema.Table.
correspond_on_equivalents
(column, equivalents)¶inherited from the FromClause.correspond_on_equivalents()
method of FromClause
Return corresponding_column for the given column, or if None search for a match in the given dictionary.
sqlalchemy.schema.Table.
corresponding_column
(column, require_embedded=False)¶inherited from the FromClause.corresponding_column()
method of FromClause
Given a ColumnElement
, return the exported
ColumnElement
object from this
expression.Selectable
which corresponds to that original
Column
via a common ancestor
column.
column¶ – the target ColumnElement
to be matched
require_embedded¶ – only return corresponding columns for
the given ColumnElement
, if the given
ColumnElement
is actually present within a sub-element
of this FromClause
.
Normally the column will match if
it merely shares a common ancestor with one of the exported
columns of this FromClause
.
sqlalchemy.schema.Table.
count
(functions, whereclause=None, **params)¶inherited from the FromClause.count()
method of FromClause
Return a SELECT COUNT generated against this
FromClause
.
Deprecated since version 1.1: The FromClause.count()
method is deprecated, and will be removed in a future release. Please use the count
function available from the func
namespace.
See also
sqlalchemy.schema.Table.
create
(bind=None, checkfirst=False)¶Issue a CREATE
statement for this
Table
, using the given Connectable
for connectivity.
See also
sqlalchemy.schema.Table.
delete
(dml, whereclause=None, **kwargs)¶inherited from the TableClause.delete()
method of TableClause
Generate a delete()
construct against this
TableClause
.
E.g.:
table.delete().where(table.c.id==7)
See delete()
for argument and usage information.
sqlalchemy.schema.Table.
dialect_kwargs
¶inherited from the DialectKWArgs.dialect_kwargs
attribute of DialectKWArgs
A collection of keyword arguments specified as dialect-specific options 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, which
contains all options known by this dialect including defaults.
The collection is also writable; keys are accepted of the
form <dialect>_<kwarg>
where the value will be assembled
into the list of options.
New in version 0.9.2.
Changed in version 0.9.4: The DialectKWArgs.dialect_kwargs
collection is now writable.
See also
DialectKWArgs.dialect_options
- nested dictionary form
sqlalchemy.schema.Table.
dialect_options
¶inherited from the DialectKWArgs.dialect_options
attribute of DialectKWArgs
A collection of keyword arguments specified as dialect-specific options to this construct.
This is a two-level nested registry, keyed to <dialect_name>
and <argument_name>
. For example, the postgresql_where
argument 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
sqlalchemy.schema.Table.
drop
(bind=None, checkfirst=False)¶Issue a DROP
statement for this
Table
, using the given Connectable
for connectivity.
See also
sqlalchemy.schema.Table.
exists
(bind=None)¶Return True if this table exists.
sqlalchemy.schema.Table.
foreign_key_constraints
¶ForeignKeyConstraint
objects referred to by this
Table
.
This list is produced from the collection of
ForeignKey
objects currently associated.
New in version 1.0.0.
sqlalchemy.schema.Table.
foreign_keys
¶inherited from the FromClause.foreign_keys
attribute of FromClause
Return the collection of ForeignKey
objects
which this FromClause references.
sqlalchemy.schema.Table.
get_children
(column_collections=True, schema_visitor=False, **kw)¶used to allow SchemaVisitor access
sqlalchemy.schema.Table.
info
¶inherited from the SchemaItem.info
attribute of SchemaItem
Info dictionary associated with the object, allowing user-defined
data 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
.
sqlalchemy.schema.Table.
insert
(dml, values=None, inline=False, **kwargs)¶inherited from the TableClause.insert()
method of TableClause
Generate an insert()
construct against this
TableClause
.
E.g.:
table.insert().values(name='foo')
See insert()
for argument and usage information.
sqlalchemy.schema.Table.
is_derived_from
(fromclause)¶inherited from the FromClause.is_derived_from()
method of FromClause
Return True
if this FromClause
is
‘derived’ from the given FromClause
.
An example would be an Alias of a Table is derived from that Table.
sqlalchemy.schema.Table.
join
(right, onclause=None, isouter=False, full=False)¶inherited from the FromClause.join()
method of FromClause
Return a Join
from this
FromClause
to another
FromClause
.
E.g.:
from sqlalchemy import join
j = user_table.join(address_table,
user_table.c.id == address_table.c.user_id)
stmt = select([user_table]).select_from(j)
would emit SQL along the lines of:
SELECT user.id, user.name FROM user
JOIN address ON user.id = address.user_id
right¶ – the right side of the join; this is any
FromClause
object such as a
Table
object, and
may also be a selectable-compatible object such as an ORM-mapped
class.
onclause¶ – a SQL expression representing the ON clause of the
join. If left at None
, FromClause.join()
will attempt to
join the two tables based on a foreign key relationship.
isouter¶ – if True, render a LEFT OUTER JOIN, instead of JOIN.
full¶ –
if True, render a FULL OUTER JOIN, instead of LEFT OUTER
JOIN. Implies FromClause.join.isouter
.
New in version 1.1.
sqlalchemy.schema.Table.
key
¶Return the ‘key’ for this Table
.
This value is used as the dictionary key within the
MetaData.tables
collection. It is typically the same
as that of Table.name
for a table with no
Table.schema
set; otherwise it is typically of the form
schemaname.tablename
.
sqlalchemy.schema.Table.
kwargs
¶inherited from the DialectKWArgs.kwargs
attribute of DialectKWArgs
A synonym for DialectKWArgs.dialect_kwargs
.
sqlalchemy.schema.Table.
lateral
(name=None)¶inherited from the FromClause.lateral()
method of FromClause
Return a LATERAL alias of this FromClause
.
The return value is the Lateral
construct also
provided by the top-level lateral()
function.
New in version 1.1.
See also
LATERAL correlation - overview of usage.
sqlalchemy.schema.Table.
outerjoin
(right, onclause=None, full=False)¶inherited from the FromClause.outerjoin()
method of FromClause
Return a Join
from this
FromClause
to another FromClause
, with the “isouter” flag set to
True.
E.g.:
from sqlalchemy import outerjoin
j = user_table.outerjoin(address_table,
user_table.c.id == address_table.c.user_id)
The above is equivalent to:
j = user_table.join(
address_table,
user_table.c.id == address_table.c.user_id,
isouter=True)
right¶ – the right side of the join; this is any
FromClause
object such as a
Table
object, and
may also be a selectable-compatible object such as an ORM-mapped
class.
onclause¶ – a SQL expression representing the ON clause of the
join. If left at None
, FromClause.join()
will attempt to
join the two tables based on a foreign key relationship.
full¶ –
if True, render a FULL OUTER JOIN, instead of LEFT OUTER JOIN.
New in version 1.1.
sqlalchemy.schema.Table.
primary_key
¶inherited from the FromClause.primary_key
attribute of FromClause
Return the collection of Column
objects
which comprise the primary key of this FromClause.
sqlalchemy.schema.Table.
quote
¶inherited from the SchemaItem.quote
attribute of SchemaItem
Return the value of the quote
flag passed
to this schema object, for those schema items which
have 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.
sqlalchemy.schema.Table.
quote_schema
¶Return the value of the quote_schema
flag passed
to this Table
.
Deprecated since version 0.9: The SchemaItem.quote()
method is deprecated and will be removed in a future release. Use the quoted_name.quote
attribute on the schema
field of the target schema item to retrieve quoted status.
sqlalchemy.schema.Table.
replace_selectable
(sqlutil, old, alias)¶inherited from the FromClause.replace_selectable()
method of FromClause
Replace all occurrences of FromClause ‘old’ with the given Alias
object, returning a copy of this FromClause
.
sqlalchemy.schema.Table.
select
(whereclause=None, **params)¶inherited from the FromClause.select()
method of FromClause
Return a SELECT of this FromClause
.
See also
select()
- general purpose
method which allows for arbitrary column lists.
sqlalchemy.schema.Table.
self_group
(against=None)¶inherited from the ClauseElement.self_group()
method of ClauseElement
Apply a ‘grouping’ to this ClauseElement
.
This method is overridden by subclasses to return a “grouping”
construct, i.e. parenthesis. In particular it’s used by “binary”
expressions to provide a grouping around themselves when placed into a
larger expression, as well as by select()
constructs when placed into the FROM clause of another
select()
. (Note that subqueries should be
normally created using the Select.alias()
method,
as many
platforms require nested SELECT statements to be named).
As expressions are composed together, the application of
self_group()
is automatic - end-user code should never
need to use this method directly. Note that SQLAlchemy’s
clause constructs take operator precedence into account -
so parenthesis might not be needed, for example, in
an expression like x OR (y AND z)
- AND takes precedence
over OR.
The base self_group()
method of
ClauseElement
just returns self.
sqlalchemy.schema.Table.
tablesample
(sampling, name=None, seed=None)¶inherited from the FromClause.tablesample()
method of FromClause
Return a TABLESAMPLE alias of this FromClause
.
The return value is the TableSample
construct also
provided by the top-level tablesample()
function.
New in version 1.1.
See also
tablesample()
- usage guidelines and parameters
sqlalchemy.schema.Table.
tometadata
(metadata, schema=symbol('retain_schema'), referred_schema_fn=None, name=None)¶Return a copy of this Table
associated with a different
MetaData
.
E.g.:
m1 = MetaData()
user = Table('user', m1, Column('id', Integer, primary_key=True))
m2 = MetaData()
user_copy = user.tometadata(m2)
metadata¶ – Target MetaData
object,
into which the
new Table
object will be created.
schema¶ –
optional string name indicating the target schema.
Defaults to the special symbol RETAIN_SCHEMA
which indicates
that no change to the schema name should be made in the new
Table
. If set to a string name, the new
Table
will have this new name as the .schema
. If set to None
, the
schema will be set to that of the schema set on the target
MetaData
, which is typically None
as well,
unless
set explicitly:
m2 = MetaData(schema='newschema')
# user_copy_one will have "newschema" as the schema name
user_copy_one = user.tometadata(m2, schema=None)
m3 = MetaData() # schema defaults to None
# user_copy_two will have None as the schema name
user_copy_two = user.tometadata(m3, schema=None)
referred_schema_fn¶ –
optional callable which can be supplied
in order to provide for the schema name that should be assigned
to the referenced table of a ForeignKeyConstraint
.
The callable accepts this parent Table
, the
target schema that we are changing to, the
ForeignKeyConstraint
object, and the existing
“target schema” of that constraint. The function should return the
string schema name that should be applied.
E.g.:
def referred_schema_fn(table, to_schema,
constraint, referred_schema):
if referred_schema == 'base_tables':
return referred_schema
else:
return to_schema
new_table = table.tometadata(m2, schema="alt_schema",
referred_schema_fn=referred_schema_fn)
New in version 0.9.2.
name¶ –
optional string name indicating the target table name.
If not specified or None, the table name is retained. This allows
a Table
to be copied to the same
MetaData
target
with a new name.
New in version 1.0.0.
sqlalchemy.schema.Table.
update
(dml, whereclause=None, values=None, inline=False, **kwargs)¶inherited from the TableClause.update()
method of TableClause
Generate an update()
construct against this
TableClause
.
E.g.:
table.update().where(table.c.id==7).values(name='foo')
See update()
for argument and usage information.
sqlalchemy.schema.
ThreadLocalMetaData
¶A MetaData variant that presents a different bind
in every thread.
Makes the bind
property of the MetaData a thread-local value, allowing
this collection of tables to be bound to different Engine
implementations or connections in each thread.
The ThreadLocalMetaData starts off bound to None in each thread. Binds
must be made explicitly by assigning to the bind
property or using
connect()
. You can also re-bind dynamically multiple times per
thread, just like a regular MetaData
.
Class signature
class sqlalchemy.schema.ThreadLocalMetaData
(sqlalchemy.schema.MetaData
)
sqlalchemy.schema.ThreadLocalMetaData.
__init__
()¶Construct a ThreadLocalMetaData.
sqlalchemy.schema.ThreadLocalMetaData.
bind
¶The bound Engine or Connection for this thread.
This property may be assigned an Engine or Connection, or assigned a
string or URL to automatically create a basic Engine for this bind
with create_engine()
.
sqlalchemy.schema.ThreadLocalMetaData.
dispose
()¶Dispose all bound engines, in all thread contexts.
sqlalchemy.schema.ThreadLocalMetaData.
is_bound
()¶True if there is a bind for this thread.
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
Created using Sphinx 3.5.3.