SQLAlchemy 概述

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

如图所述,SQLAlchemy主要包括两个大方面: COREORM,其中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.executeEngine.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的语法,详细可参考ColumnElementSelectTableSQLAlchemy的选择运算符包括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')

关于查询再看看JOINSUBQUERY

SQLALchemy ORM