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。

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

Python.SQLAlchemy.1,布布扣,bubuko.com

Python.SQLAlchemy.1

上一篇:Java 之 MYSQL 数据库搭建


下一篇:oracle批量导出AWR报告