SQLAlchemy学习

SQLAlchemy官方文档有三大类：Getting Started、SQLAlchemy ORM、SQLAlchemy Core。
• Getting Started，以一个全局视角介绍SQLALchemy，并给出了一些初步使用的实例。
• SQLAlchemy ORM，介绍了对象-关系映射，如果要了解sqlalchemy通过python类生成数据库表的自动化细节，就需要学习这部分文档。比较常见的是如何定义表与表之间的参照关系，一对多、多对多等，以及反向引用。
• SQLAlchemy Core，介绍了sqlalchemy的sql语句处理机制（如何将python对象调用语句映射为sql）、数据库函数DBAPI、事务处理、模式描述服务等等。
1. Getting Started
1.1 Overview
SQLALchemy SQL工具和ORM是一套联接python与数据库的综合工具集。它由几个相互区别的功能部分组成，可以独立或组合使用。SQLAlchemy的主要概念如下图所示：

SQLAlchemy主要由两个层次组成：ORM和SQL表达式语言。SQL表达式语言可以独立于ORM使用。使用ORM时，需要SQL表达式语言的支持。SQL表达式语言保留了部分表层的公共API，这些API用于对象-关系配置和查询。
1.2 SQLalchemy的安装
在已经安装了python及pip工具的系统下，如果能访问Internet，那么安装SQLAlchemy很简单，运行下列命令：
pip install sqlalchemy
个人建议，可以先建立一个virtualenv，然后再其中安装，例如在Ubuntu Linux下执行下列命令安装虚拟环境并安装sqlalchemy，命令如下：
pip install virtualenv –python=python3.5
mkdir somedevworkspace
virtualenv somedevworkspace
cd somedevworkspace
source bin/activate
pip install sqlalchemy
如果没有连接Internet，也可以下载sqlalchemy之后，在sqlalchemy文件夹内找到setup.py,然后运行下列命令：
python setup.py install
安装完成后，可以在python环境下，输入下列命令检查sqlalchemy的版本，这也是一种查看安装是否成功的检查方法。

import sqlalchemy
sqlalchemy.version # doctest: +SKIP
1.2.0

1. SQLAlchemy ORM
   2.1 Object Relational Tutorial
   SQLALchemy 对象关系映射（ORM）表达了一种用户自定义的python类与数据库表相关联的方法，表达了python对象与数据表行的对应关系。
   （未完待续）

2. SQLAlchemy Core
   官方文档推荐首先阅读“SQLALchemy表达式语言向导”
   3.1 SQL Expression Language Tutorial
   SQLALchemy表达式语言是一种使用python语句结构描述关系数据库结构和表达式的系统。这些python语句结构被构建的与相关数据库（操作语句）十分相似，对采用不同实现方式的数据库后台提供了一定的抽象。这使得在使用sqlalchemy语句结构表达不同数据库产品的同一数据库概念时（例如：定义表的结构），可以保持一致性。所以说，Sqlalchemy的表达式语言是一种中性语句，与各数据库后台实现无关，而且并不隐藏那些数据库产品的关键特性。Sqlalchemy 表达式语言与ORM相比，ORM是一种构建在表达式语言之上的API。ORM是一种抽象和用例的抽象模式，是一种表达式语言的用例。
   （未完待续）

The Expression Language is in contrast to the Object Relational Mapper, which is a distinct API that builds on top of the Expression Language. Whereas the ORM, introduced in Object Relational Tutorial, presents a high level and abstracted pattern of usage, which itself is an example of applied usage of the Expression Language, the Expression Language presents a system of representing the primitive constructs of the relational database directly without opinion.
While there is overlap among the usage patterns of the ORM and the Expression Language, the similarities are more superficial than they may at first appear. One approaches the structure and content of data from the perspective of a user-defined domain model which is transparently persisted and refreshed from its underlying storage model. The other approaches it from the perspective of literal schema and SQL expression representations which are explicitly composed into messages consumed individually by the database.
A successful application may be constructed using the Expression Language exclusively, though the application will need to define its own system of translating application concepts into individual database messages and from individual database result sets. Alternatively, an application constructed with the ORM may, in advanced scenarios, make occasional usage of the Expression Language directly in certain areas where specific database interactions are required.
The following tutorial is in doctest format, meaning each >>> line represents something you can type at a Python command prompt, and the following text represents the expected return value. The tutorial has no prerequisites.

3.2 SQL Statements and Expressions API
This section presents the API reference for the SQL Expression Language.
• Column Elements and Expressions
• Selectables, Tables, FROM objects
• Insert, Updates, Deletes
• SQL and Generic Functions
• Custom SQL Constructs and Compilation Extension

Expression Serializer Extension