sqlalchemy是一个使用python来表示关系数据库结构的语言.这个结构尽可能模拟底层数据库.以少量的抽象方法应对不同的数据库后台。针对后台的一致性,允许接受当前一致实现.它们不隐藏那些后端中特定的子集(小部分后端)所独有的有用的概念.这种表达语言因此提供了一种方法来写入后台中立的SQL表达式.但是它不接受标准的sql语句。
ORM与表达式语言正好相反，它是建立于表达式语言（后者）之上的独特的API。ORM引进目标关系教程.提供了一个更高的层次分离了使用模式.ORM本身就是使用表达式用法的例子.sql语言直接提供了一个初级的关系数据库模型系统。
ORM使用模式和sql语言有重叠的部分.相似之处很浅显
快速查看当前版本(sqlalchemy版本至少0.9以上):
>>>import sqlalchemy
>>>sqlalchemy.__version__
0.9.0
下面的教程我们将会使用sqlite嵌入式数据库,这是一种简单的测试语句的方法，它不需要设置现有的数据库。我们使用create_engine()进行连接:
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///:memory:', echo=True)
echo标志能快捷的设置sqlalchemy日志,它通过了python标准的日志方法.当语句执行，我们将会看到所有产生的sql语句.如果你按照这个教程不想看到sql语句输出,将echo设置成False。~~教程会格式化sql弹出窗口.他不会在乎我们的方式,只需要点击'sql'链接就会看到它被建立.
create_engine()的返回值是引擎的例子,它表现核心接口在数据库中,允许使用方言来操作数据库和数据库接口的细节部分.为了防止sqlite方言
会向python 内置的sqlite方法解释操作.
Lazy connecting
当Engine.execute()或engine.connect()首次被调用时，Engine建立了一个DBAPI连接数据库,它被用来传输sql语句.
See also: Database urls - 包括了create_engine()通过链接连接不同数据库的例子
SQL语言构造器的表达大部分和表列相反.在sqlalchemy中列通过column对象来表示.所有的Column都与Tabel关联.Table对象和它联系的子对象的采集都是引用元数据.教程中我们会设计一些Table对象.注意sa可以自动导入现存数据库的所有表(这个过程叫数据库反射)
我们定义所有的表的目录为metadata.使用Table框架，它类似于传统sql创建表的语句.我们将会建立2张表.其中一个表在应用中 代表'user'.另外一个张表示有0个或者多个'email addresses' 'user '表的所有行:
>>> 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)
... )
所有关于定义Table 对象,和如何从一个已有的数据库自动创建它们,具体描述在数据库和元数据章节里面
接下来,将要讲解MetaData .我们将会从内置的SQLite数据库创建所选择的表,我们使用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
提示:熟悉创建数据库表的使用者可能会注意 varchar 表单没用标明长度.在sqlite 和 postgresql .这是一个可变数据类型.但是在其他数据库中这是不允许的.如果你使用这个教程在这些数据库中,你希望用sqlalchemy来解决这个创建数据库表的问题,如下例子提供了一个长度给string 类型
Column('name', String(50))
Interger Numeric的长度也和String的类似.此外它的长度不是参照Sqlalchemy而是表创建时。
另外 firebird 和 oracle 需要通过序列化方式来创建新主键标识符的数据库 , SQLAlchemy 并不会自动为其生成。可以指定Sequence 生成
from sqlalchemy import Sequence
Column('id', Integer, Sequence('user_id_seq'), primary_key=True)
下面是一个超级简单的Table
users = Table('users', metadata,
Column('id', Integer, Sequence('user_id_seq'), primary_key=True),
Column('name', String(50)),
Column('fullname', String(50)),
Column('password', String(12))
)
我们包含了许多重复的table框架分别标记
在第一个sql表达式我们将会创建一个insert构造,它表示插入语句.它目标表:
>>> ins = user.insert()
为了看到sql 框架如何实现的简单例子,我们使用str()函数:
>>> str(ins)
'INSERT INTO users (id, name, fullname) VALUES (:id, :name, :fullname)'
从上面可以发现insert语句把users表中的每一列都命名了.它使用values()方法.创建了valuse 插入明确的条例.
>>> ins = users.insert().values(name=’jack’, fullname=’Jack Jones’)
>>> str(ins)
'INSERT INTO users (name, fullname) VALUES (:name, :fullname)'
在values方法中它将values里面的值放入到2个列中,我们放置在values中现有的数据不需要在string里面寻找;而是绑定我们命名的元素. 当它执行结束数据将会被放置在insert框架中,但在通常只会在执行相关语句时才会出现.sqlalchemy会自动的绑定这些值,我们可以通过compiled表单语句来获取这些数据的信息。
>>> ins.compile().params
{’fullname’: ’Jack Jones’, ’name’: ’jack’}
执行插入语句是插入操作中是有趣的一部分.在教程中我们将会着重讲解执行sql框架的方法.接着接触用一些shortcut方法来完成相关操作.引擎对象是我们所创建的数据库连接源，它可以对数据库操作SQL语句.我们使用connect()方法来获得这个连接。
>>> conn = engine.connect()
>>> conn
<sqlalchemy.engine.base.Connection object at 0x...>
Connection对象表示检测出一个活跃的DBAPI连接资源.我们通过执行insert对象观察它是如何发生的.
>>> result = conn.execute(ins)
INSERT INTO users (name, fullname) VALUES (?, ?)
('jack', 'Jack Jones')
COMMIT
现在插入语句已经发送到数据库.虽然我们输出了'qmark'绑定的参数而不是'named'绑定的参数.这是为什么呢?因为Connection是使用sqlite方言来产生相关语句的.当我们使用str()函数时它不知道sqlite的方言语句，于是就返回我们默认声明的参数.看接下来的帮助:
>>> ins.bind = engine
>>> str(ins)
’INSERT INTO users (name, fullname) VALUES (?, ?)’
当我们调用execute()时result变量会发送什么变化?Sqlalchemy的connection对象引用了DBAPI的连接.最后我们了解到ResultProxy对象就是类似于DBAPI游标对象.我们可以从insert中得到很重要的信息.例如从结果语句中查看主码
>>> result.inserted_primary_key
[1]
1的值通过sqlite自动产生.但这只是因为我们没有在insert语句里声明id列，另外我们已经在使用明确的值.在这2种情况中，sqlalchemy始终清楚如何得到一个新的被引用的主码的值.尽管在操作不同的数据库产生它们的方法不一样，但这些数据库方言明白如何用确定的步骤来确定正确的值(注意inserted_primary_key返回的是一个列表,所有它支持复合主码)
在上面插入语句的例子中，我们刻意展示一些表达式语言框架不同的操作,在通常情况下，插入语句使用Connection将编译好的参数传送到execute()方法中,它们不需要使用 values。我们创建一个insert语句。
>>> 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.result.ResultProxy object at 0x...>
我们在上面的execute()方法里定义了3个列,编译的insert语句包含了全部的3列.insert语句在编译时依据我们定义的参数。如果我们定义参数的参数越少,insert语句的values子句就会越短。
我们使用executemany()方法发送一个字典列表其中包含了要插入的参数来执行多条Inserts语句。下面我们有个插入一些email地址的例子
>>> conn.execute(addresses.insert(), [
... {'user_id': 1, 'email_address' : '[email protected]'},
... {'user_id': 1, 'email_address' : '[email protected]'},
... {'user_id': 2, 'email_address' : '[email protected]'},
... {'user_id': 2, 'email_address' : '[email protected]'},
... ])
INSERT INTO addresses (user_id, email_address) VALUES (?, ?)
((1, '[email protected]'), (1, '[email protected]'), (2, '[email protected]'), (2, '[email protected]'))
COMMIT
<sqlalchemy.engine.result.ResultProxy object at 0x...>
我们通过sqlite表示的主码来确定对应的每个地址行。
当执行多个参数设定时,每个字典必须有相同的key设置。这是因为insert语句在编译列表第一个字典时,它是假设接下来的字典的key都与第一列字典的相同。
我们使用inserts语句仅仅是为了测试向数据库里面写入一些数据，更有趣是是查询数据是部分。在后面我们将会介绍更新和删除语句.初级框架通常使用select函数生产select语句:
>>> from sqlalchemy.sql import select
>>> s = select([users])
>>> result = conn.execute(s)
SELECT users.id, users.name, users.fullname
FROM users ()
我们在上面写了一个select()基本调用方法的语句.把users表放在select查询子句中再执行.sqlalchemy扩展到users表的每一列中, 同时也为我们产生了FROM子句.返回了resultproxy对象作为结果,这种行为就像dbapi的游标,包含了像ferchone()和fetchall()的方法,然后通过迭代得到每一个行的值。
>>> for row in result:
... print row
(1, u'jack', u'Jack Jones')
(2, u'wendy', u'Wendy Williams')
我们可以看到上面每一行的结果都是一个简单的元组.我们有很多种方法访问每一行的值,常用的一种方法是通过字典使用表名字符串查询。
>>> 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
下面是一种实用的方法,它使用column对象直接作用在key上：
>>> 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
还有待查询数据行的结果集在被内存丢弃前应当显示关闭,当处于垃圾对象机制时对象游标和引用resultprox连接代码将会分别被关闭然后返回到连接池中,它让此变得更直接即使一些数据库接口对于这些事非常严格。
>>> result.close()
如果你想控制column查询子句中列的属性,我们可以从表中参考Column对象.他们等同于表对象的列属性。
>>> s = select([users.c.name, users.c.fullname])
SQL>>> result = conn.execute(s)
>>> for row in result:
... print row
(u'jack', u'Jack Jones')
(u'wendy', u'Wendy Williams')
我们展示了from子句有趣的部分.然而产生的语句包含了2个不同的选项,“SELECT columns”部分和“FROM table”部分. 我们的select()框架仅仅有一个列表控制colums。它是如何工作的？让我们放2个表在select()语句中：
>>> for row in conn.execute(select([users, addresses])):
... print row
(1, u'jack', u'Jack Jones', 1, 1, u'[email protected]')
(1, u'jack', u'Jack Jones', 2, 1, u'[email protected]')
(1, u'jack', u'Jack Jones', 3, 2, u'[email protected]')
(1, u'jack', u'Jack Jones', 4, 2, u'[email protected]')
(2, u'wendy', u'Wendy Williams', 1, 1, u'[email protected]')
(2, u'wendy', u'Wendy Williams', 2, 1, u'[email protected]')
(2, u'wendy', u'Wendy Williams', 3, 2, u'[email protected]')
(2, u'wendy', u'Wendy Williams', 4, 2, u'[email protected]')
我们放了2个表在from子句中,但是它其实看起来很混乱.熟悉sql连接查询的人知道这就是一个笛卡尔乘积.user表的每一行都和address表的每一行对应.为了让语句更加清晰我们需要where子句,我们使用Select.where()：
>>> for row in conn.execute(select([users, addresses])):
... print row
(1, u'jack', u'Jack Jones', 1, 1, u'[email protected]')
(1, u'jack', u'Jack Jones', 2, 1, u'[email protected]')
(1, u'jack', u'Jack Jones', 3, 2, u'[email protected]')
(1, u'jack', u'Jack Jones', 4, 2, u'[email protected]')
(2, u'wendy', u'Wendy Williams', 1, 1, u'[email protected]')
(2, u'wendy', u'Wendy Williams', 2, 1, u'[email protected]')
(2, u'wendy', u'Wendy Williams', 3, 2, u'[email protected]')
(2, u'wendy', u'Wendy Williams', 4, 2, u'[email protected]')
这样看起来是不是好了一点.我们在select()语句建立了WHERE users.id = addresses.user_id的联系，我们得到的结果经过处理为了让users和addresses行关联。如果我们观察表达式？它仅仅用在两个不同的column对象中使用了一个等于操作。这样就明白了。1==1 为真 1==2 为假，没有where子句，让我们仔细观察一下这表达式是如何运作的。
>>> users.c.id == addresses.c.user_id
<sqlalchemy.sql.expression.BinaryExpression object at 0x...>
太奇怪了，这没有真假，它到底是什么。
>>> str(users.c.id == addresses.c.user_id)
'users.id = addresses.user_id'
你可以看到，==操作产生了一个十分类似insert()和select()的对象。非常感谢eq()内置函数，你在里面调用了str（）它产生了sql语句，现在我们可以看到所有的事最后作用的都是相同的对象。sqlalchemy所有表达式的基本类都是作用在columnelement
从刚开始使用sqlalchemy操作范式到现在，我们已经学会了一些相关的操作。让我们看看它的两列是如何进行等于操作的。
>>> print users.c.id == addresses.c.user_id
users.id = addresses.user_id
如果我们使用一个字面量（一个字面的意思，不是sqlalchemy子句对象）我们会得到一个绑定值。
>>> print users.c.id == 7
users.id = :id_1
字面量7已经植入到ColumnElement，我们使用在Insert对象中的方法来查看它:
>>> (users.c.id == 7).compile().params
{u'id_1': 7}
大部分python操作产生的sql表达式的例子，例如等于和不等于:
>>> print users.c.id != 7
users.id != :id_1
>>> # None converts to IS NULL
>>> print users.c.name == None
users.name IS NULL
>>> # reverse works too
>>> print 'fred' > users.c.name
users.name < :name_1
如果想要把2个整形的列加在一起，我们可以使用一个加操作：
>>> 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)
上面显示的是Engine连接MySql数据库产生的sql语句，MySql的concat（）函数编译了||操作。