metadata.txt

SQLAlchemy. 经典的Python ORM框架。学习必看。

[alpha_api]: javascript:alphaApi()
[alpha_implementation]: javascript:alphaImplementation()

Database Meta Data {@name=metadata}
==================

### Describing Databases with MetaData {@name=tables}    

The core of SQLAlchemy's query and object mapping operations are supported by **database metadata**, which is comprised of Python objects that describe tables and other schema-level objects.  These objects can be created by explicitly naming the various components and their properties, using the Table, Column, ForeignKey, Index, and Sequence objects imported from `sqlalchemy.schema`.  There is also support for **reflection** of some entities, which means you only specify the *name* of the entities and they are recreated from the database automatically.

A collection of metadata entities is stored in an object aptly named `MetaData`:

    {python}
    from sqlalchemy import *
    
    metadata = MetaData()

To represent a Table, use the `Table` class:

    {python}
    users = Table('users', metadata, 
        Column('user_id', Integer, primary_key = True),
        Column('user_name', String(16), nullable = False),
        Column('email_address', String(60), key='email'),
        Column('password', String(20), nullable = False)
    )
    
    user_prefs = Table('user_prefs', metadata, 
        Column('pref_id', Integer, primary_key=True),
        Column('user_id', Integer, ForeignKey("users.user_id"), nullable=False),
        Column('pref_name', String(40), nullable=False),
        Column('pref_value', String(100))
    )

The specific datatypes for each Column, such as Integer, String, etc. are described in [types](rel:types), and exist within the module `sqlalchemy.types` as well as the global `sqlalchemy` namespace.

Foreign keys are most easily specified by the `ForeignKey` object within a `Column` object.  For a composite foreign key, i.e. a foreign key that contains multiple columns referencing multiple columns to a composite primary key, an explicit syntax is provided which allows the correct table CREATE statements to be generated:

    {python}
    # a table with a composite primary key
    invoices = Table('invoices', metadata, 
        Column('invoice_id', Integer, primary_key=True),
        Column('ref_num', Integer, primary_key=True),
        Column('description', String(60), nullable=False)
    )
    
    # a table with a composite foreign key referencing the parent table
    invoice_items = Table('invoice_items', metadata, 
        Column('item_id', Integer, primary_key=True),
        Column('item_name', String(60), nullable=False),
        Column('invoice_id', Integer, nullable=False),
        Column('ref_num', Integer, nullable=False),
        ForeignKeyConstraint(['invoice_id', 'ref_num'], ['invoices.invoice_id', 'invoices.ref_num'])
    )
    
Above, the `invoice_items` table will have `ForeignKey` objects automatically added to the `invoice_id` and `ref_num` `Column` objects as a result of the additional `ForeignKeyConstraint` object.

The `MetaData` object supports some handy methods, such as getting a list of Tables in the order (or reverse) of their dependency:

    {python}
    >>> for t in metadata.table_iterator(reverse=False):
    ...    print t.name
    users
    user_prefs
        
And `Table` provides an interface to the table's properties as well as that of its columns:
        
    {python}
    employees = Table('employees', metadata, 
        Column('employee_id', Integer, primary_key=True),
        Column('employee_name', String(60), nullable=False, key='name'),
        Column('employee_dept', Integer, ForeignKey("departments.department_id"))
    )
    
    # 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:
        # ...
        
    # get the table's primary key columns
    for primary_key in employees.primary_key:
        # ...
    
    # get the table's foreign key objects:
    for fkey in employees.foreign_keys:
        # ...
        
    # 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_key
    
    # get the "key" of a column, which defaults to its name, but can 
    # be any user-defined string:
    employees.c.name.key
    
    # access a column's table:
    employees.c.employee_id.table is employees
    >>> True
    
    # get the table related by a foreign key
    fcolumn = employees.c.employee_dept.foreign_key.column.table

#### Binding MetaData to an Engine or Connection {@name=binding}

A `MetaData` object can be associated with an `Engine` or an individual `Connection`; this process is called **binding**.  The term used to describe "an engine or a connection" is often referred to as a **connectable**.  Binding allows the `MetaData` and the elements which it contains to perform operations against the database directly, using the connection resources to which it's bound.   Common operations which are made more convenient through binding include being able to generate SQL constructs which know how to execute themselves, creating `Table` objects which query the database for their column and constraint information, and issuing CREATE or DROP statements.

To bind `MetaData` to an `Engine`, use the `bind` attribute:

    {python}
    engine = create_engine('sqlite://', **kwargs)
    
    # create MetaData 
    meta = MetaData()

    # bind to an engine
    meta.bind = engine

Once this is done, the `MetaData` and its contained `Table` objects can access the database directly:

    {python}
    meta.create_all()  # issue CREATE statements for all tables
    
    # describe a table called 'users', query the database for its columns
    users_table = Table('users', meta, autoload=True)
    
    # generate a SELECT statement and execute
    result = users_table.select().execute()

Note that the feature of binding engines is **completely optional**.  All of the operations which take advantage of "bound" `MetaData` also can be given an `Engine` or `Connection` explicitly with which to perform the operation.   The equivalent "non-bound" of the above would be:

    {python}
    meta.create_all(engine)  # issue CREATE statements for all tables
    
    # describe a table called 'users',  query the database for its columns
    users_table = Table('users', meta, autoload=True, autoload_with=engine)
    
    # generate a SELECT statement and execute
    result = engine.execute(users_table.select())

#### Reflecting Tables

A `Table` object can be created without specifying any of its contained attributes, using the argument `autoload=True` in conjunction with the table's name and possibly its schema (if not the databases "default" schema).  (You can also specify a list or set of column names to autoload as the kwarg include_columns, if you only want to load a subset of the columns in the actual database.)  This will issue the appropriate queries to the database in order to locate all properties of the table required for SQLAlchemy to use it effectively, including its column names and datatypes, foreign and primary key constraints, and in some cases its default-value generating attributes.   To use `autoload=True`, the table's `MetaData` object need be bound to an `Engine` or `Connection`, or alternatively the `autoload_with=<some connectable>` argument can be passed.  Below we illustrate autoloading a table and then iterating through the names of its columns:

    {python}
    >>> messages = Table('messages', meta, autoload=True)
    >>> [c.name for c in messages.columns]
    ['message_id', 'message_name', 'date']

Note that if a reflected table has a foreign key referencing another table, the related `Table` object  will be automatically created within the `MetaData` object if it does not exist already.  Below, suppose table `shopping_cart_items` references a table `shopping_carts`.  After reflecting, the `shopping carts` table is present:
        
    {python}
    >>> shopping_cart_items = Table('shopping_cart_items', meta, autoload=True)
    >>> 'shopping_carts' in meta.tables:
    True
        
To get direct access to 'shopping_carts', simply instantiate it via the `Table` constructor.  `Table` uses a special contructor that will return the already created `Table` instance if it's already present:

    {python}
    shopping_carts = Table('shopping_carts', meta)

Of course, it's a good idea to use `autoload=True` with the above table regardless.  This is so that if it hadn't been loaded already, the operation will load the table.  The autoload operation only occurs for the table if it hasn't already been loaded; once loaded, new calls to `Table` will not re-issue any reflection queries.

##### Overriding Reflected Columns {@name=overriding}

Individual columns can be overridden with explicit values when reflecting tables; this is handy for specifying custom datatypes, constraints such as primary keys that may not be configured within the database, etc.

    {python}
    >>> mytable = Table('mytable', meta,
    ... Column('id', Integer, primary_key=True),   # override reflected 'id' to have primary key
    ... Column('mydata', Unicode(50)),    # override reflected 'mydata' to be Unicode
    ... autoload=True)
    
#### Specifying the Schema Name {@name=schema}

Some databases support the concept of multiple schemas.  A `Table` can reference this by specifying the `schema` keyword argument:

    {python}
    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 reference columns in this table using the form `remote_banks.financial_info.id`.

#### ON UPDATE and ON DELETE {@name=onupdate}

`ON UPDATE` and `ON DELETE` clauses to a table create are specified within the `ForeignKeyConstraint` object, using the `onupdate` and `ondelete` keyword arguments:

    {python}
    foobar = Table('foobar', meta,
        Column('id', Integer, primary_key=True),
        Column('lala', String(40)),
        ForeignKeyConstraint(['lala'],['hoho.lala'], onupdate="CASCADE", ondelete="CASCADE"))

Note that these clauses are not supported on SQLite, and require `InnoDB` tables when used with MySQL.  They may also not be supported on other databases.

#### Other Options {@name=options}

`Tables` may support database-specific options, such as MySQL's `engine` option that can specify "MyISAM", "InnoDB", and other backends for the table:

    {python}
    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'
    )
    
### Creating and Dropping Database Tables {@name=creating}    

Creating and dropping individual tables can be done via the `create()` and `drop()` methods of `Table`; these methods take an optional `bind` parameter which references an `Engine` or a `Connection`.  If not supplied, the `Engine` bound to the `MetaData` will be used, else an error is raised:

    {python}
    meta = MetaData()
    meta.bind = 'sqlite:///:memory:'

    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"))
    )
    {sql}employees.create()
    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:
    
    {python}
    {sql}employees.drop(bind=e)