sqlalchemy orm 概览

SQLAlchemy 概述

在python的orm世界里，SQLAlchemy,无疑是数据库工具链里面的一个重量级的工具。SQLAlchemy简略构架图如下:

如图所述，SQLAlchemy主要包括两个大方面: CORE和ORM，其中CORE里面最重要的是SQL Expression Language(SQL EL), ORM是建立在SQL EL之上，而SQL EL通过ENGINE,DIALECT建立在具体的DBAPI之上。

数据库连接

使用SQLAlchemy，必须关联一个数据库后端，后端使用create_egine创建，如：

1
2


    >>> from sqlalchemy import * #导入sqlalchemy所有包
    >>> engine = create_engine("sqlite:///:memory:", echo=True) #绑定数据库后端

create_engine通过不同的DB URL来识别不同数据库后端，数据库接口必须是符合python dbapi规范的。需要注意的是，create_engine并不实际和数据库进行连接，等到第一次执行Engine.execute或Engine.connect显式连接时，才进行真正的数据库连接。

库表数据表示

SQLAlchemy有3种方式表示关系数据库的库表： - 用户定义的Table对象 - 使用declerative基类 - 从现有的数据里面反射建立

数据类型关系映射

SQLAlchemy数据关系映射

SQLAlchemy Core

SQLAlchemy EL提供了用python构建数据库对象的方式，而且这些python对象是数据库中立的，EL对数据操作也是数据库中立的。
SQLAlchemy使用的DTATBASE METADATA和内部数据库结构绑定，能加速SQLAlchemy对数据库访问。如:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


    from sqlalchemy import *
    engine = create_engine("sqlite:///test.db", echo=True)
    metadata = MetaData()
    user = Table('user', metadata,
      Column('id', Integer, primary_key=True),
      Column('name', String(64)),
      Column('sex', String(2)),
    )
    
    address = Table('address', metadata,
      Column('id', Integer, primary_key=True),
      Column('user_id', None, ForeignKey('user.id')),
      Column('address', String(256), nullable=False)
    )
    metadata.create_all(engine)

执行后输出如下:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21


    ……忽略无关内容
    CREATE TABLE user (
            id INTEGER NOT NULL,
            name VARCHAR(64),
            sex VARCHAR(2),
            PRIMARY KEY (id)
    )

    2014-12-31 12:21:45,555 INFO sqlalchemy.engine.base.Engine ()
    2014-12-31 12:21:45,704 INFO sqlalchemy.engine.base.Engine COMMIT
    2014-12-31 12:21:45,707 INFO sqlalchemy.engine.base.Engine
    CREATE TABLE address (
            id INTEGER NOT NULL,
            user_id INTEGER,
            address VARCHAR(256) NOT NULL,
            PRIMARY KEY (id),
            FOREIGN KEY(user_id) REFERENCES user (id)
    )
    
    2014-12-31 12:21:45,710 INFO sqlalchemy.engine.base.Engine ()
    2014-12-31 12:21:45,716 INFO sqlalchemy.engine.base.Engine COMMIT

create_engine会根据数据库表名而决定是否需要在数据库创建库表，如果通过metadata增加库表，数据库如果没有该库表的话，就会创建。但如果修改列或在metadata删除表，则不受影响，不会执行。另外还有另外一种方法描述库表：TABLE REFLECTION，在已有的数据库的基础上通过反射获取库表的描述，这种方法虽然方便，但是可控程度稍低。具体可参考Refelction Database Objects。

SQLAlchemy Core 数据操作 – 插入

数据的插入可通过table.insert()获取INSERT对象，然后通过SQLAlchemy获取Connection对象来执行，这是最直观的方式，默认是一执行完毕就提交数据库的。INSERT对象式可以通过绑定变量或者在Connection对象绑定变量构建数据。Connection可以执行一条或多条数据，如：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


    conn = engine.connect()
    inst = user.insert()
    rst = conn.execute(inst, id = 1, name="hello", sex="F")
    
    conn.execute(address.insert(), [ 
       {'user_id': 1, 'address' : 'Guangdong'},
       {'user_id': 1, 'address' : 'Beijing'},
       {'user_id': 1, 'address' : 'Shanghai'},
       {'user_id': 1, 'address' : 'Hongkong'},
    ])

结果如下：

1
2
3
4
5


    sqlite> select a.*, b.* from user a left join address b on b.user_id = a.id;
    1|hello|F|1|1|Guangdong
    1|hello|F|2|1|Beijing
    1|hello|F|3|1|Shanghai
    1|hello|F|4|1|Hongkong

如果需要事务控制，则需要获取Transection对象，事务一般需要在try/except块中控制，如:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


try:
    addr_inst = address.insert()

    r = conn.execute(addr_inst, [ 
       {'user_id': 1, 'address' : 'Guangdong'},
       {'user_id': 1, 'address' : 'Beijing'},
       {'user_id': 1, 'address' : 'Shanghai'},
       {'user_id': 1, 'address' : 'Hongkong'},
    ])
    r.close()
    trans.commit()
except:
    trans.rollback()

同理，数据的查询可通过table.select()获取SELECT对象。但这种方法只能针对一张表，如果用使用多张表或者选择不同对象，则需要使用select函数。如：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28


s = select([user, address]).where(user.c.id == address.c.user_id)
r = conn.execute(s)

for t in r:
    print t

print 'xxx'
s = user.select().where(user.c.id == 2)
r = conn.execute(s)
for t in r:
    print t
结果如下：
2014-12-31 15:29:07,825 INFO sqlalchemy.engine.base.Engine SELECT user.id, user.
name, user.sex, address.id, address.user_id, address.address
FROM user, address
WHERE user.id = address.user_id
2014-12-31 15:29:07,825 INFO sqlalchemy.engine.base.Engine ()
(1, u'ZhangSan', u'M', 1, 1, u'Guangdong')
(1, u'ZhangSan', u'M', 2, 1, u'Beijing')
(1, u'ZhangSan', u'M', 3, 1, u'Shanghai')
(1, u'ZhangSan', u'M', 4, 1, u'Hongkong')
xxx
2014-12-31 15:29:07,828 INFO sqlalchemy.engine.base.Engine SELECT user.id, user.
name, user.sex
FROM user
WHERE user.id = ?
2014-12-31 15:29:07,828 INFO sqlalchemy.engine.base.Engine (2,)
(2, u'LiShi', u'M')

select函数需要的参数是列表，需要选择的结果集放到这里面，具体的列可以通过table.c.colum的方式来获取。为了一致性处理，通常对于select出来的结果集，如果不需要使用，还需调用结果集的close方法来关闭，从而释放数据库资源。这里里例子里面包含了whereCLAUSE，这是个布尔表达式，使用的是python而非sql的语法，详细可参考ColumnElement和SelectTable。SQLAlchemy的选择运算符包括and, or, not_, group_by,func.sum聚集函数等，使用这些基本上可以构建日常使用的查询语句。如果还不行，还可以像普通sql一样，直接写，如：

1
2
3
4
5
6
7
8
9


s = text("select * from user where id = :id")
r = conn.execute(s, id=1)
for t in r:
    print t
结果：
2014-12-31 16:00:32,207 INFO sqlalchemy.engine.base.Engine select * from user wh
ere id = ?
2014-12-31 16:00:32,207 INFO sqlalchemy.engine.base.Engine (1,)
(1, u'ZhangSan', u'M')

关于查询再看看JOIN和SUBQUERY。