拥抱SQLAlchemy 之二 拉拉手，我请你去看电影~
所有按照我Pao妞的程序，上次是一见钟情，那么这次一定是要约她看电影的，坐在情侣包，你挨着我，我爱着你，可以好好的了解了解，拉近感情了，哈哈，这又让我想起了初恋，都说初恋无限美，正所谓“举头望明月，低头思故乡”，好诗呀~~
今天看的电影名字叫《SQL Expression Language》，绝对引进大片，首映哦~
这次，我主要是要了解一下SQLAlchemy构建SQL的一些细节，如果你没有看到我们一见钟情的那篇，那要继续下面的练习前一定要先确认你已经正确的安装了相关库，具体安装，还是去看第一篇吧~~ ：P
检查一下你的版本，我的是SQLAlchemy 0.5.5的，用下面代码检查：
>>> import sqlalchemy
>>> sqlalchemy.__version__
0.5.0
所有的练习都是用内存SQLite数据库，建立一个连接先！
>>> from sqlalchemy import create_engine
>>> engine = create_engine(’sqlite:///:memory:’, echo=True)
构建练习需要用到的表模式，一定要建哦，否则无法继续练习~
>>> from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
>>> metadata = MetaData()
>>> users = Table(’users’, metadata,
... Column(’id’, Integer, primary_key=True),
... Column(’name’, String),
... Column(’fullname’, String),
... )
>>> addresses = Table(’addresses’, metadata,
... Column(’id’, Integer, primary_key=True),
... Column(’user_id’, None, ForeignKey(’users.id’)),
... Column(’email_address’, String, nullable=False)
... )
用create_all方法创建数据库中的真实表
>>> metadata.create_all(engine)
PRAGMA table_info("users")
{}
PRAGMA table_info("addresses")
{}
CREATE TABLE users (
id INTEGER NOT NULL,
name VARCHAR,
fullname VARCHAR,
PRIMARY KEY (id)
)
{}
COMMIT
CREATE TABLE addresses (
id INTEGER NOT NULL,
user_id INTEGER,
email_address VARCHAR NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(user_id) REFERENCES users (id)
)
{}
COMMIT
给users表中插入记录
>>> ins = users.insert()
让我们看看上面那句产生了什么样的SQL语句
>>> str(ins)
’INSERT INTO users (id, name, fullname) VALUES (:id, :name, :fullname)’
可以使用values来给插入的记录字段赋值
>>> ins = users.insert().values(name=’jack’, fullname=’Jack Jones’)
>>> str(ins)
’INSERT INTO users (name, fullname) VALUES (:name, :fullname)’
这样产生的SQL里并没有体现我们传递的字段值，字段到哪里了？原来字段值存储在了内建的结构中。
可以这样看刚才传入的字段值
>>> ins.compile().params
{’fullname’: ’Jack Jones’, ’name’: ’jack’}
下面看看如何执行语句,先获得一个数据库链接
>>> conn = engine.connect()
>>> conn
<sqlalchemy.engine.base.Connection object at 0x...>
然后通过连接执行刚才的查询语句,因为不是事务，执行完就提交了
>>> result = conn.execute(ins)
INSERT INTO users (name, fullname) VALUES (?, ?)
[’jack’, ’Jack Jones’]
COMMIT
也可以手动的绑定数据库链接，并查看绑定后生成的SQL语句有什么不同
>>> ins.bind = engine
>>> str(ins)
’INSERT INTO users (name, fullname) VALUES (?, ?)’
当调用了execute()后，就可以访问result变量了，执行结果就在这里面，聪明的SQLAlchemy有自己一套获得主键ID的办法，并且针对不同数据库。
>>> result.last_inserted_ids()
[1]
也可以这样执行插入语句
>>> ins = users.insert()
>>> conn.execute(ins, id=2, name=’wendy’, fullname=’Wendy Williams’)
INSERT INTO users (id, name, fullname) VALUES (?, ?, ?)
[2, ’wendy’, ’Wendy Williams’]
COMMIT<sqlalchemy.engine.base.ResultProxy object at 0x...>
现在让我要一次添加多个EMAIL地址到addresses表，SQLite自动为新纪录产生了主键
>>> conn.execute(addresses.insert(), [
... {’user_id’: 1, ’email_address’ : ’jack@yahoo.com’},
... {’user_id’: 1, ’email_address’ : ’jack@msn.com’},
... {’user_id’: 2, ’email_address’ : ’www@www.org’},
... {’user_id’: 2, ’email_address’ : ’wendy@aol.com’},
... ])
INSERT INTO addresses (user_id, email_address) VALUES (?, ?)
[[1, ’jack@yahoo.com’], [1, ’jack@msn.com’], [2, ’www@www.org’], [2, ’wendy@aol.com’]]
COMMIT<sqlalchemy.engine.base.ResultProxy object at 0x...>
使用数据库链接直接执行插入，不用手动OPEN和CLOSE
>>> result = engine.execute(users.insert(), name=’fred’, fullname="Fred Flintstone")
INSERT INTO users (name, fullname) VALUES (?, ?)
[’fred’, ’Fred Flintstone’]
COMMIT
如果当前的metadata和engine还绑定的话（别忘记了，一开始我们就用create_all创建表格的时候做过绑定，别紧张，没绑定的话，现在绑定也来得及），就可以这样执行了，这种执行方式被称为含蓄的执行。
>>> metadata.bind = engine #绑定
>>> result = users.insert().execute(name="mary", fullname="Mary Contrary")
INSERT INTO users (name, fullname) VALUES (?, ?)
[’mary’, ’Mary Contrary’]
COMMIT
解除绑定可以这样做
>>> metadata.bind = None
用一个很简单的语句就可以选择users表中的所有纪录
>>> from sqlalchemy.sql import select
>>> s = select([users])
>>> result = conn.execute(s)
SELECT users.id, users.name, users.fullname
FROM users
[]
结果被返回到result变量中，可以这样读取结果
>>> from sqlalchemy.sql import select
>>> s = select([users])
>>> result = conn.execute(s)
SELECT users.id, users.name, users.fullname
FROM users
[]
我个人感觉这样访问结果集的每一行更舒服些^_^
>>> result = conn.execute(s)
SELECT users.id, users.name, users.fullname
FROM users
[]>>> row = result.fetchone()
>>> print "name:", row[’name’], "; fullname:", row[’fullname’]
name: jack ; fullname: Jack Jones
对于列来说，用整数索引来访问也是可以的
>>> row = result.fetchone()
>>> print "name:", row[1], "; fullname:", row[2]
name: wendy ; fullname: Wendy Williams
你要是很喜欢整合语句，也可以这样搞~
>>> for row in conn.execute(s):
... print "name:", row[users.c.name], "; fullname:", row[users.c.fullname]
SELECT users.id, users.name, users.fullname
FROM users
[]name: jack ; fullname: Jack Jones
name: wendy ; fullname: Wendy Williams
name: fred ; fullname: Fred Flintstone
name: mary ; fullname: Mary Contrary
用完了，别忘记关门哦~
>>> result.close()
如果在选择纪录的时候希望指定输出的列，可以使用表对象的c属性
>>> s = select([users.c.name, users.c.fullname])
>>> result = conn.execute(s)
SELECT users.name, users.fullname
FROM users
[]>>> for row in result:
... print row
(u’jack’, u’Jack Jones’)
(u’wendy’, u’Wendy Williams’)
(u’fred’, u’Fred Flintstone’)
(u’mary’, u’Mary Contrary’)
从多个表里选择纪录，可以这么写，因为没有WHERE，结果看上去很混乱，用户和邮件地址之间对应关系不正确
>>> for row in conn.execute(select([users, addresses])):
... print row
SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address
FROM users, addresses
[](1, u’jack’, u’Jack Jones’, 1, 1, u’jack@yahoo.com’)
(1, u’jack’, u’Jack Jones’, 2, 1, u’jack@msn.com’)
(1, u’jack’, u’Jack Jones’, 3, 2, u’www@www.org’)
(1, u’jack’, u’Jack Jones’, 4, 2, u’wendy@aol.com’)
(2, u’wendy’, u’Wendy Williams’, 1, 1, u’jack@yahoo.com’)
(2, u’wendy’, u’Wendy Williams’, 2, 1, u’jack@msn.com’)
(2, u’wendy’, u’Wendy Williams’, 3, 2, u’www@www.org’)
(2, u’wendy’, u’Wendy Williams’, 4, 2, u’wendy@aol.com’)
(3, u’fred’, u’Fred Flintstone’, 1, 1, u’jack@yahoo.com’)
(3, u’fred’, u’Fred Flintstone’, 2, 1, u’jack@msn.com’)
(3, u’fred’, u’Fred Flintstone’, 3, 2, u’www@www.org’)
(3, u’fred’, u’Fred Flintstone’, 4, 2, u’wendy@aol.com’)
(4, u’mary’, u’Mary Contrary’, 1, 1, u’jack@yahoo.com’)
(4, u’mary’, u’Mary Contrary’, 2, 1, u’jack@msn.com’)
(4, u’mary’, u’Mary Contrary’, 3, 2, u’www@www.org’)
(4, u’mary’, u’Mary Contrary’, 4, 2, u’wendy@aol.com’)
为了使用户和自己的邮箱能对应起来，可以这么写
>>> s = select([users, addresses], users.c.id==addresses.c.user_id)
>>> for row in conn.execute(s):
... print row
SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address
FROM users, addresses
WHERE users.id = addresses.user_id
[](1, u’jack’, u’Jack Jones’, 1, 1, u’jack@yahoo.com’)
(1, u’jack’, u’Jack Jones’, 2, 1, u’jack@msn.com’)
(2, u’wendy’, u’Wendy Williams’, 3, 2, u’www@www.org’)
(2, u’wendy’, u’Wendy Williams’, 4, 2, u’wendy@aol.com’)
上面的where表达式这样执行一下，会发现生成了一个表达式对象
>>> users.c.id==addresses.c.user_id
<sqlalchemy.sql.expression._BinaryExpression object at 0x...>
用str看一下这个表达式，惊奇的是，返回的不是TRUE或FALSE,而是一个字符串语句，想想也是能想通的，这个语句是给SQL的where使用的。
>>> str(users.c.id==addresses.c.user_id)
’users.id = addresses.user_id’
这样查看表达式也行
>>> print users.c.id==addresses.c.user_id
users.id = addresses.user_id
下面的表达式赋值了7，但生成的语句中却是被映射到了一个绑定参数中
>>> print users.c.id==7
users.id = :id_1
要查看绑定的参数，要这样做
>>> (users.c.id==7).compile().params
{u’id_1’: 7}
会了等于，当然也就明白了不等于
>>> print users.c.id != 7
users.id != :id_1
下面自动把None转换为NULL
>>> print users.c.name == None
users.name IS NULL
虽然你前后颠倒，但是SQLAlchemy可不会，顺序它帮你顺过来，是不是越来越觉得SQLAlchemy很贤惠了？偷着乐吧~
>>> print ’fred’ > users.c.name
users.name < :name_1
如果要联合两个字段是integer类型，如下
>>> print users.c.id + addresses.c.id
users.id + addresses.id
更有趣的是，如果两个列类型为String，用+就会产生这样的效果
>>> print users.c.name + users.c.fullname
users.name || users.fullname
上面的||是字符串连接符，但并不都是这么写，在MySQL里会有其他的写法,如下：
>>> print (users.c.name + users.c.fullname).compile(bind=create_engine(’mysql://’))
concat(users.name, users.fullname)
可爱的SQLAlchemy是很智慧的，可以根据不同的数据库特征来进行翻译
下面这句咱没弄明白，咱也不在这里胡说~~嘿嘿
>>> print users.c.name.op(’tiddlywinks’)(’foo’)
users.name tiddlywinks :name_1
要是用逻辑连接符，也是有办法滴
>>> from sqlalchemy.sql import and_, or_, not_
>>> print and_(users.c.name.like(’j%’), users.c.id==addresses.c.user_id,
... or_(addresses.c.email_address==’wendy@aol.com’, addresses.c.email_address==’jack@yahoo.com’),
... not_(users.c.id>5))
users.name LIKE :name_1 AND users.id = addresses.user_id AND
(addresses.email_address = :email_address_1 OR addresses.email_address = :email_address_2)
AND users.id <= :id_1
你要是喜欢这么写逻辑连接符，SALAlchemy也没有意见
>>> print users.c.name.like(’j%’) & (users.c.id==addresses.c.user_id) & \
... ((addresses.c.email_address==’wendy@aol.com’) | (addresses.c.email_address==’jack@yahoo.com’)) \
... & ~(users.c.id>5)
users.name LIKE :name_1 AND users.id = addresses.user_id AND
(addresses.email_address = :email_address_1 OR addresses.email_address = :email_address_2)
AND users.id <= :id_1
下面让我们生成BETWEEN和AS语句
>>> s = select([(users.c.fullname + ", " + addresses.c.email_address).label(’title’)],
... and_(
... users.c.id==addresses.c.user_id,
... users.c.name.between(’m’, ’z’),
... or_(
... addresses.c.email_address.like(’%@aol.com’),
... addresses.c.email_address.like(’%@msn.com’)
... )
... )
... )
>>> print conn.execute(s).fetchall()
SELECT users.fullname || ? || addresses.email_address AS title
FROM users, addresses
WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND
(addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
[’, ’, ’m’, ’z’, ’%@aol.com’, ’%@msn.com’]
[(u’Wendy Williams, wendy@aol.com’,)]
写到这里原文说SQLAlchemy还可以支持ORDER BY,GROPY BY和HAVING,忧郁篇幅的原因，这里就不一一列举了，既然原文都不列举了，我也就不列举了，哈哈
通过text()来构建SQL语句，其中的变量必须是冒号加变量名，我想尽量不使用这种语法，我感觉写SQL实在是太痛苦了。
>>> from sqlalchemy.sql import text
>>> s = text("""SELECT users.fullname || ’, ’ || addresses.email_address AS title
... FROM users, addresses
... WHERE users.id = addresses.user_id AND users.name BETWEEN :x AND :y AND
... (addresses.email_address LIKE :e1 OR addresses.email_address LIKE :e2)
... """)
>>> print conn.execute(s, x=’m’, y=’z’, e1=’%@aol.com’, e2=’%@msn.com’).fetchall()
SELECT users.fullname || ’, ’ || addresses.email_address AS title
FROM users, addresses
WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND
(addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
[’m’, ’z’, ’%@aol.com’, ’%@msn.com’][(u’Wendy Williams, wendy@aol.com’,)]
使用text()却又想在select中单独列出from的表，可以使用from_obj参数来指定
>>> s = select([text("users.fullname || ’, ’ || addresses.email_address AS title")],
... and_(
... "users.id = addresses.user_id",
... "users.name BETWEEN ’m’ AND ’z’",
... "(addresses.email_address LIKE :x OR addresses.email_address LIKE :y)"
... ),
... from_obj=[’users’, ’addresses’]
... )
>>> print conn.execute(s, x=’%@aol.com’, y=’%@msn.com’).fetchall()
SELECT users.fullname || ’, ’ || addresses.email_address AS title
FROM users, addresses
WHERE users.id = addresses.user_id AND users.name BETWEEN ’m’ AND ’z’ AND (addresses.email_add
[’%@aol.com’, ’%@msn.com’][(u’Wendy Williams, wendy@aol.com’,)]
使用alias()来创建表的别名完成如下查询
>>> a1 = addresses.alias(’a1’)
>>> a2 = addresses.alias(’a2’)
>>> s = select([users], and_(
... users.c.id==a1.c.user_id,
... users.c.id==a2.c.user_id,
... a1.c.email_address==’jack@msn.com’,
... a2.c.email_address==’jack@yahoo.com’
... ))
>>> print conn.execute(s).fetchall()
SELECT users.id, users.name, users.fullname
FROM users, addresses AS a1, addresses AS a2
WHERE users.id = a1.user_id AND users.id = a2.user_id AND a1.email_address = ? AND a2.email_address = ?
[’jack@msn.com’, ’jack@yahoo.com’][(1, u’jack’, u’Jack Jones’)]
alias()也可以这样写,匿名别名，每次生成的别名都一样，这对于在oracle这种编译执行的数据库中，根据每次不同的SQL字符串编译的情况非常有用，编译次数少有利于性能提升吧~~~
>>> a1 = addresses.alias()
>>> a2 = addresses.alias()
>>> s = select([users], and_(
... users.c.id==a1.c.user_id,
... users.c.id==a2.c.user_id,
... a1.c.email_address==’jack@msn.com’,
... a2.c.email_address==’jack@yahoo.com’
... ))
>>> print conn.execute(s).fetchall()
SELECT users.id, users.name, users.fullname
FROM users, addresses AS addresses_1, addresses AS addresses_2
WHERE users.id = addresses_1.user_id AND users.id = addresses_2.user_id AND addresses_1.email_address = ? AND addresses_2.email_address = ?
[’jack@msn.com’, ’jack@yahoo.com’][(1, u’jack’, u’Jack Jones’)]
这句留着以后研究，暂时没看明白…
>>> a1 = s.correlate(None).alias()
>>> s = select([users.c.name], users.c.id==a1.c.id)
>>> print conn.execute(s).fetchall()
SELECT users.name
FROM users, (SELECT users.id AS id, users.name AS name, users.fullname AS fullname
FROM users, addresses AS addresses_1, addresses AS addresses_2
WHERE users.id = addresses_1.user_id AND users.id = addresses_2.user_id AND addresses_1.email_address = ? AND addresses
WHERE users.id = anon_1.id
[’jack@msn.com’, ’jack@yahoo.com’][(u’jack’,)]
下面看看join()如何工作的
>>> print users.join(addresses)
users JOIN addresses ON users.id = addresses.user_id
智能再次体现出来，根据外键ON字句自动被加上了
当然，你也可以自己决定ON后面的表达式
>>> print users.join(addresses, addresses.c.email_address.like(users.c.name + ’%’))
users JOIN addresses ON addresses.email_address LIKE users.name || :name_1
也可以使用select()的from_obj参数来指定from字句，并应用join()
>>> s = select([users.c.fullname], from_obj=[
... users.join(addresses, addresses.c.email_address.like(users.c.name + ’%’))
... ])
>>> print conn.execute(s).fetchall()
SELECT users.fullname
FROM users JOIN addresses ON addresses.email_address LIKE users.name || ?
[’%’][(u’Jack Jones’,), (u’Jack Jones’,), (u’Wendy Williams’,)]
outerjoin()的用法和join()雷同了
>>> s = select([users.c.fullname], from_obj=[users.outerjoin(addresses)])
>>> print s
SELECT users.fullname
FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id
对于oracle的SQL来说，有一些特别需要处理的地方，下面的语义需要查看ORACLE相关资料
>>> from sqlalchemy.databases.oracle import OracleDialect
>>> print s.compile(dialect=OracleDialect(use_ansi=False))
SELECT users.fullname
FROM users, addresses
WHERE users.id = addresses.user_id(+)
下面开始说说select()，用它可以组合语句片段，构建select语句
>>> query = users.select()
>>> print query
SELECT users.id, users.name, users.fullname
FROM users
给刚才的查询增加一个查询条件
>>> query = query.where(users.c.name==’jack’)
再让他根据fullname字段排序
>>> query = query.order_by(users.c.fullname.desc())
我们查看一下看看哪些用户有MSN邮箱地址
>>> from sqlalchemy.sql import exists
>>> query = query.where(
... exists([addresses.c.id],
... and_(addresses.c.user_id==users.c.id, addresses.c.email_address.like(’%@msn.com’))
... ).correlate(users))
一点一点的添加语句，到这里生成的SQL语句已经很复杂了，这个要手写，很辛苦哩~
>>> query = query.column(addresses).select_from(users.outerjoin(addresses)).apply_labels()
>>> conn.execute(query).fetchall()
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, addresses.id AS addresses_id, addresses.user_id AS address
FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id
WHERE users.name = ? AND (EXISTS (SELECT addresses.id
FROM addresses
WHERE addresses.user_id = users.id AND addresses.email_address LIKE ?)) ORDER BY users.fullname DESC
[’jack’, ’%@msn.com’][(1, u’jack’, u’Jack Jones’, 1, 1, u’jack@yahoo.com’), (1, u’jack’, u’Jack Jones’, 2, 1, u’jack@msn.com’)]
继续丰富SQL语句
>>> a1 = addresses.alias()
>>> query = query.replace_selectable(addresses, a1)
>>> print query
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, addresses_1.id
FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id
WHERE users.name = :name_1 AND (EXISTS (SELECT addresses_1.id
FROM addresses AS addresses_1
WHERE addresses_1.user_id = users.id AND addresses_1.email_address LIKE :email_address_1)) ORDER BY use
写了这么老半天，执行以下吧，乖乖，看来用ORM写SQL，优势是大大的！！
>>> for row in conn.execute(query):
... print "Name:", row[users.c.name], "; Email Address", row[a1.c.email_address]
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, addresses_1.id AS addresses_1_id,
FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id
WHERE users.name = ? AND (EXISTS (SELECT addresses_1.id
FROM addresses AS addresses_1
WHERE addresses_1.user_id = users.id AND addresses_1.email_address LIKE ?)) ORDER BY users.fullname DESC
[’jack’, ’%@msn.com’]Name: jack ; Email Address jack@yahoo.com
Name: jack ; Email Address jack@msn.com
使用bindparam()来绑定一个参数
>>> from sqlalchemy.sql import bindparam
>>> s = users.select(users.c.name==bindparam(’username’))
>>> conn.execute(s, username=’wendy’).fetchall()
SELECT users.id, users.name, users.fullname
FROM users
WHERE users.name = ?
[’wendy’][(2, u’wendy’, u’Wendy Williams’)]
使用bindparam()还可以指定参数类型和表达式
>>> s = users.select(users.c.name.like(bindparam(’username’, type_=String) + text("’%’")))
>>> conn.execute(s, username=’wendy’).fetchall()
SELECT users.id, users.name, users.fullname
FROM users
WHERE users.name LIKE ? || ’%’
[’wendy’][(2, u’wendy’, u’Wendy Williams’)]
同名的绑定参数可以多次出现，这样参数再输入的时候也省事些
>>> s = select([users, addresses],
... users.c.name.like(bindparam(’name’, type_=String) + text("’%’")) |
... addresses.c.email_address.like(bindparam(’name’, type_=String) + text("’@%’")),
... from_obj=[users.outerjoin(addresses)])
>>> conn.execute(s, name=’jack’).fetchall()
SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address
FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id
WHERE users.name LIKE ? || ’%’ OR addresses.email_address LIKE ? || ’@%’
[’jack’, ’jack’][(1, u’jack’, u’Jack Jones’, 1, 1, u’jack@yahoo.com’), (1, u’jack’, u’Jack Jones’, 2, 1, u’jack@msn.com’)]
看看如何使用函数
>>> from sqlalchemy.sql import func
>>> print func.now()
now()
>>> print func.concat(’x’, ’y’)
concat(:param_1, :param_2)
当前的时间戳
>>> print func.current_timestamp()
CURRENT_TIMESTAMP
使用scalar()返回函数结果
>>> print conn.execute(
... select([func.max(addresses.c.email_address, type_=String).label(’maxemail’)])
... ).scalar()
SELECT max(addresses.email_address) AS maxemail
FROM addresses
[]www@www.org
Oracle中可以定义函数,下面看看如何运行一个名为calculate()的函数
>>> from sqlalchemy.sql import column
>>> calculate = select([column(’q’), column(’z’), column(’r’)],
... from_obj=[func.calculate(bindparam(’x’), bindparam(’y’))])
>>> print select([users], users.c.id > calculate.c.z)
SELECT users.id, users.name, users.fullname
FROM users, (SELECT q, z, r
FROM calculate(:x, :y))
WHERE users.id > z
想使用不同的绑定参数两次，可以使用unique_params()
>>> s = select([users], users.c.id.between(
... calculate.alias(’c1’).unique_params(x=17, y=45).c.z,
... calculate.alias(’c2’).unique_params(x=5, y=12).c.z))
>>> print s
SELECT users.id, users.name, users.fullname
FROM users, (SELECT q, z, r
FROM calculate(:x_1, :y_1)) AS c1, (SELECT q, z, r
FROM calculate(:x_2, :y_2)) AS c2
WHERE users.id BETWEEN c1.z AND c2.z
>>> s.compile().params
{u’x_2’: 5, u’y_2’: 12, u’y_1’: 45, u’x_1’: 17}
>>> s.compile().params
{u’x_2’: 5, u’y_2’: 12, u’y_1’: 45, u’x_1’: 17}
联合两个记录集UNION和UNION ALL
>>> from sqlalchemy.sql import union
>>> u = union(
... addresses.select(addresses.c.email_address==’foo@bar.com’),
... addresses.select(addresses.c.email_address.like(’%@yahoo.com’)),
... ).order_by(addresses.c.email_address)
>>> print conn.execute(u).fetchall()
SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
WHERE addresses.email_address = ? UNION SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
WHERE addresses.email_address LIKE ? ORDER BY addresses.email_address
[’foo@bar.com’, ’%@yahoo.com’][(1, 1, u’jack@yahoo.com’)]
注意，不是所有的数据库都支持intersect(), intersect_all(), except_()和except_all()的
>>> from sqlalchemy.sql import except_
>>> u = except_(
... addresses.select(addresses.c.email_address.like(’%@%.com’)),
... addresses.select(addresses.c.email_address.like(’%@msn.com’))
... )
>>> print conn.execute(u).fetchall()
SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
WHERE addresses.email_address LIKE ? EXCEPT SELECT addresses.id, addresses.user_id, addresses.e
FROM addresses
WHERE addresses.email_address LIKE ?
[’%@%.com’, ’%@msn.com’][(1, 1, u’jack@yahoo.com’), (4, 2, u’wendy@aol.com’)]
使用Scalar()
>>> print conn.execute(select([
... users.c.name,
... select([func.count(addresses.c.id)], users.c.id==addresses.c.user_id).as_scalar()
... ])).fetchall()
SELECT users.name, (SELECT count(addresses.id) AS count_1
FROM addresses
WHERE users.id = addresses.user_id) AS anon_1
FROM users
[][(u’jack’, 2), (u’wendy’, 2), (u’fred’, 0), (u’mary’, 0)]
给上面的语句应用label()
>>> print conn.execute(select([
... users.c.name,
... select([func.count(addresses.c.id)], users.c.id==addresses.c.user_id).label(’address_count’)
... ])).fetchall()
SELECT users.name, (SELECT count(addresses.id) AS count_1
FROM addresses
WHERE users.id = addresses.user_id) AS address_count
FROM users
[][(u’jack’, 2), (u’wendy’, 2), (u’fred’, 0), (u’mary’, 0)]
correlate()关联子查询
>>> s = select([users.c.name], users.c.id==select([users.c.id]).correlate(None))
>>> print s
SELECT users.name
FROM users
WHERE users.id = (SELECT users.id
FROM users)
>>> s = select([users.c.name, addresses.c.email_address], users.c.id==
... select([users.c.id], users.c.id==addresses.c.user_id).correlate(addresses)
... )
>>> print s
SELECT users.name, addresses.email_address
FROM users, addresses
WHERE users.id = (SELECT users.id
FROM users
WHERE users.id = addresses.user_id)
关于order_by(asc,desc),group_by(having)，limit和offset,distinct=True的一些用法
>>> s = select([addresses.c.user_id, func.count(addresses.c.id)]).\
... group_by(addresses.c.user_id).having(func.count(addresses.c.id)>1)
>>> print conn.execute(s).fetchall()
SELECT addresses.user_id, count(addresses.id) AS count_1
FROM addresses GROUP BY addresses.user_id
HAVING count(addresses.id) > ?
[1][(1, 2), (2, 2)]
>>> s = select([addresses.c.email_address, addresses.c.id]).distinct().\
... order_by(addresses.c.email_address.desc(), addresses.c.id)
>>> conn.execute(s).fetchall()
SELECT DISTINCT addresses.email_address, addresses.id
FROM addresses ORDER BY addresses.email_address DESC, addresses.id
[][(u’www@www.org’, 3), (u’wendy@aol.com’, 4), (u’jack@yahoo.com’, 1), (u’jack@msn.com’, 2)]
>>> s = select([addresses]).offset(1).limit(1)
>>> print conn.execute(s).fetchall()
SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
LIMIT 1 OFFSET 1
[][(2, 1, u’jack@msn.com’)]
更新记录
>>> conn.execute(users.update().where(users.c.name==’jack’).values(name=’ed’))
UPDATE users SET name=? WHERE users.name = ?
[’ed’, ’jack’]
COMMIT<sqlalchemy.engine.base.ResultProxy object at 0x...>
使用绑定参数的更新语句
>>> u = users.update().where(users.c.name==bindparam(’oldname’)).values(name=bindparam(’newname
>>> conn.execute(u, oldname=’jack’, newname=’ed’)
UPDATE users SET name=? WHERE users.name = ?
[’ed’, ’jack’]
COMMIT<sqlalchemy.engine.base.ResultProxy object at 0x...>
使用表达式的更新语句
>>> conn.execute(users.update().values({users.c.fullname:"Fullname: " + users.c.name}))
UPDATE users SET fullname=(? || users.name)
[’Fullname: ’]
COMMIT<sqlalchemy.engine.base.ResultProxy object at 0x...>
关联更新
>>> conn.execute(users.update().values({users.c.fullname:"Fullname: " + users.c.name}))
UPDATE users SET fullname=(? || users.name)
[’Fullname: ’]
COMMIT<sqlalchemy.engine.base.ResultProxy object at 0x...>
删除表记录
>>> conn.execute(addresses.delete())
DELETE FROM addresses
[]
COMMIT<sqlalchemy.engine.base.ResultProxy object at 0x...>
根据条件删除表记录
>>> conn.execute(users.delete().where(users.c.name > ’m’))
DELETE FROM users WHERE users.name > ?
[’m’]
COMMIT<sqlalchemy.engine.base.ResultProxy object at 0x...>
电影完了，不亏是引进大片，太精彩了，不过也需要我花一段时间好好回味和消化一下，通过这次约会，我对SQLAlchemy有了进一步的了解，也更加喜欢了~~我要坚持和她拍拖下去，争取白头偕老~~坚持！！！！
这文章可能要暂停几天，我需要花些时间消化这两天学的知识，希望大家谅解~
转载请注明出处：http://booolee.cnblogs.com
