Python.SQLAlchemy.1

SQLAlchemy ORM的学习笔记

ORM(Object Relational Mapper)为我们完成的工作：自动构造高水平的SQL; Python对象的持久化。

1. Object Relational Tutorial (对象关系)

SQLAlchemy ORM提供了一种方法，这种方法将用户定义的Python对象和database table关联起来；

提供了一个系统，这个系统透明地同步对象和相关行的所有变化(unit of work)；

提供了一个系统，该系统用用户定义的类和关系来表达数据库的查询。

1.1 Version Check

1.2 Connecting

create_engine() 返回Engine类型的对象。该对象代表操作数据库的核心接口。

在使用ORM时，Engine一旦创建后就不会被直接使用。

1.3 Declare a Mapping

使用ORM时，配置过程开始于描述数据库表，然后定义映射到这些表的类。

在SQLAlchemy中，这两项工作被Declarative这个系统同时完成了。该系统允许我们

创建类，这些类包含指令来描述该类对应的实际数据库表。

类的映射由Declarative系统来完成，这之中主要是declarative base class.

使用declarative_base()来创建declarative base class。

>>> from sqlalchemy.ext.declarative import declarative_base

>>> Base = declarative_base()

有了Base类后，其他映射的class在Base类基础上来定义：即其他Model类需要继承自Base。

例如：

>>> from sqlalchemy import Column, Integer, String
>>> class User(Base):
...     __tablename__ = 'users'
...
...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...     fullname = Column(String)
...     password = Column(String)
...
...     def __repr__(self):
...        return "<User(name='%s', fullname='%s', password='%s')>" % (
...                             self.name, self.fullname, self.password)

类使用Declarative至少需要__tablename__属性，一个Column(并且该Column是primary key的一部分)。

“When our class is constructed, Declarative replaces all the Column objects with special Python accessors known as descriptors; this is a process known as instrumentation. ” ----[Q1]

1.4 Create a Schema

"With our User class constructed via the Declarative system, we have defined information about our table, known as table metadata."

User.__table__ 是Table类型的对象。

"The Table object is a member of a larger collection known as MetaData."

1.5 Create an Instance of the Mapped Class

对于User类，使用Declarative系统定义，已经被提供了一个构造方法。

“Our User class, as defined using the Declarative system, has been provided with a constructor (e.g. __init__()method) which automatically accepts keyword names that match the columns we’ve mapped.”

1.6 Creating a Session

“The ORM’s “handle” to the database is theSession. ”

ORM对数据库的句柄是Session对象。Session类是和Engine绑定在一起的。

“Keep in mind, the Session is just a workspace for your objects, local to a particular database connection”。

1.7 Adding New Objects

持久化User对象，将User对象添加到Session(Session.add(user)), 然后调用

session.commit()

1.8 Rolling Back (回滚)

Session工作在一个事务(transaction)中. session.rollback().

1.9 Quering (查询)

TODO

1.10 Building a Relationship

>>> from sqlalchemy import ForeignKey
>>> from sqlalchemy.orm import relationship, backref

>>> class Address(Base):
...     __tablename__ = 'addresses'
...     id = Column(Integer, primary_key=True)
...     email_address = Column(String, nullable=False)
...     user_id = Column(Integer, ForeignKey('users.id'))
...
...     user = relationship("User", backref=backref('addresses', order_by=id))
...
...     def __repr__(self):
...         return "<Address(email_address='%s')>" % self.email_address

ForeignKey() relationShip()

Basic Relational Patterns (Relationship Configuration)

http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-patterns

Linking Relationships with Backref (Relationship Configuration)

http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-patterns

1.11 Working with Related Objects

在one-many的关系中, addresses collection在Python中默认list([]).

1.12 Querying with Joins (使用Join进行查询)

>>> for u, a in session.query(User, Address).
...                     filter(User.id==Address.user_id).
...                     filter(Address.email_address=='jack@google.com').
...                     all():   
...     print u
...     print a
<User(name='jack', fullname='Jack Bean', password='gjffdd')>
<Address(email_address='jack@google.com')>

Query.filter()是implicit join.

SQL JOIN用Query.join()来完成。

Query.outerjoin(): Left outer join.

1.12.1 Using Aliases

1.12.2 Using Subqueries

Query.subquery()

1.12.3 Selecting Entities from Subqueries

1.12.4 Using EXISTS

EXISTS 是SQL中关键字。

1.12.5 Common Relationship Operators

__eq__()

__ne__()

contains()

any()

has()

Query.with_parent()

1.13 Eager Loading

TODO

1.13.1 Subquery Load

1.13.2 Joined Load

1.13.3 Explicit Join + Eagerload

1.14 Deleting

1.14.1 Configuring delete/delete-orphan Cascade

配置删除操作级联

1.15 Building a Many To Many Relationship

Association Object