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。
1 >>> from sqlalchemy.ext.declarative import declarative_base 2 3 >>> Base = declarative_base()
有了Base类后，其他映射的class在Base类基础上来定义：即其他Model类需要继承自Base。
例如：
1 >>> from sqlalchemy import Column, Integer, String 2 >>> class User(Base): 3 ... __tablename__ = ‘users‘ 4 ... 5 ... id = Column(Integer, primary_key=True) 6 ... name = Column(String) 7 ... fullname = Column(String) 8 ... password = Column(String) 9 ... 10 ... def __repr__(self): 11 ... return "<User(name=‘%s‘, fullname=‘%s‘, password=‘%s‘)>" % ( 12 ... 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进行查询)
1 >>> for u, a in session.query(User, Address).2 ... filter(User.id==Address.user_id).3 ... filter(Address.email_address==‘jack@google.com‘).4 ... all(): 5 ... print u 6 ... print a 7 <User(name=‘jack‘, fullname=‘Jack Bean‘, password=‘gjffdd‘)> 8 <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