编辑“︁SQLAlchemy”︁

{{noteTA|G1=IT}}
{{Infobox software
| name                   = SQLAlchemy
| title                  = 
| logo                   = File:SQLAlchemy.svg
| logo size              = 200px
| screenshot             = <!-- [[File: ]] -->
| caption                = 
| collapsible            = 
| author                 = 迈克尔·拜尔（Michael Bayer）<ref>{{Cite web |url=http://techspot.zzzeek.org/ |title=Mike Bayer是SQLAlchmey以及Mako Templates for Python的创始人。 |accessdate=2012-11-08 |archive-date=2012-10-26 |archive-url=https://web.archive.org/web/20121026090154/http://techspot.zzzeek.org/ |dead-url=no }}</ref>
| developer              = 
| released               = <!-- {{Start date|YYYY|MM|DD|df=yes/no}} -->2006年2月<ref>[http://onlamp.com/pub/a/python/2007/03/08/pycon-2007-wrapup.html PyCon 2007 Wrapup] {{Wayback|url=http://onlamp.com/pub/a/python/2007/03/08/pycon-2007-wrapup.html |date=20121021151911 }},''SQLAlchemy released 0.1.0 in February 2006'' - O'Reilly Media</ref>
| discontinued           = 
| latest release         = <!--不需要手工更新发行信息-->
| programming language   = [[Python]]
| operating system       = [[跨平台]]
| platform               = 
| size                   = 
| language               = 
| genre                  = [[对象关系映射]]
| license                = [[MIT许可证]]
| website                = {{url|http://www.sqlalchemy.org/}}
}}
'''SQLAlchemy'''是为[[Python]][[编程语言]]提供的[[开放源代码|开源]][[SQL]]工具包及[[对象关系映射器]]（ORM），是在[[MIT许可证]]下发行的软件。

==概述==
SQLAlchemy提供企业级{{le|持久化 (计算机科学)|Persistence (computer science)|持久化}}[[架构模式|模式]]，首次发行于2006年2月。SQLAlchemy的理念是：关系数据库随着规模变大并且性能开始成为顾虑，而表现得不像对象[[集合 (计算机科学)|搜集]]；而对象搜集随着更多的抽象被设计入其中，而表现得不像[[数据库表|表格]]和[[行 (数据库)|行]]。因此，SQLAlchmey采用了类似于[[Java]]里[[Hibernate]]的[[数据映射器模式]]<ref>{{Cite web |url=http://www.martinfowler.com/eaaCatalog/dataMapper.html |title=数据映射器 |accessdate=2012-11-08 |archive-date=2012-11-04 |archive-url=https://web.archive.org/web/20121104114229/http://www.martinfowler.com/eaaCatalog/dataMapper.html |dead-url=no }}</ref>，而不是其他[[对象关系映射|ORM]]框架采用的[[主动记录|主动记录模式]]。

== 示例 ==
下述示例描述了电影同它们的导演之间的多对一[[实体联系模型|联系]]。示例中说明了怎样从用户定义的Python[[类 (计算机科学)|类]]创建对应的数据库表格，怎样从[[实体联系模型|联系]]的任何一方创建有关联的[[实例 (计算机科学)|实例]]，最终怎样查询数据，演示了为延迟加载和预先加载二者自动生成的[[SQL]]查询。

=== 架构定义 ===
创建两个Python类以及在[[DBMS]]数据库[[Schema (数据库)|架构]]中对应的表格：

<syntaxhighlight lang="Python">
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relation, sessionmaker

Base = declarative_base()
 
class Movie(Base):
    __tablename__ = 'movies'
 
    id = Column(Integer, primary_key=True)
    title = Column(String(255), nullable=False)
    year = Column(Integer)
    directed_by = Column(Integer, ForeignKey('directors.id'))
 
    director = relation("Director", backref='movies', lazy=False)
 
    def __init__(self, title=None, year=None):
        self.title = title
        self.year = year
    def __repr__(self):
        return "Movie(%r, %r, %r)" % (self.title, self.year, self.director)
 
class Director(Base):
    __tablename__ = 'directors'
 
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False, unique=True)
 
    def __init__(self, name=None):
        self.name = name
 
    def __repr__(self):
        return "Director(%r)" % (self.name)
 
engine = create_engine('dbms://user:pwd@host/dbname')
Base.metadata.create_all(engine)

</syntaxhighlight>

=== 插入数据 ===
电影与导演[[实体联系模型|联系]]可以通过任何一方[[实体联系模型|实体]]插入：
<syntaxhighlight lang="Python">
Session = sessionmaker(bind=engine)
session = Session()

m1 = Movie("Star Trek", 2009)
m1.director = Director("JJ Abrams")

d2 = Director("George Lucas")
d2.movies = [Movie("Star Wars", 1977), Movie("THX 1138", 1971)]

try:
    session.add(m1)
    session.add(d2)
    session.commit()
except:
    session.rollback()
</syntaxhighlight>

=== 查询 ===
<syntaxhighlight lang="Python">
alldata = session.query(Movie).all()
for somedata in alldata:
    print somedata
</syntaxhighlight>

SQLAlchemy将向DBMS发起如下查询（忽略别名）：
<syntaxhighlight lang="SQL">
SELECT movies.id, movies.title, movies.year, movies.directed_by, directors.id, directors.name 
FROM movies LEFT OUTER JOIN directors ON directors.id = movies.directed_by
</syntaxhighlight>

输出结果：
<syntaxhighlight lang="python">
Movie('Star Trek', 2009L, Director('JJ Abrams'))
Movie('Star Wars', 1977L, Director('George Lucas'))
Movie('THX 1138', 1971L, Director('George Lucas'))
</syntaxhighlight>

假如在架构定义时设置<code>lazy=True</code>（默认值），SQLAlchemy将首先发起一个查询来获得一个电影列表，并在必要时（延迟）对每个导演发起查询来获得对应导演的名字：
<syntaxhighlight lang="SQL">
SELECT movies.id, movies.title, movies.year, movies.directed_by 
FROM movies

SELECT directors.id, directors.name
FROM directors 
WHERE directors.id = %s
</syntaxhighlight>

== 参考文献 ==
{{reflist|2}}
;注释
{{Div col|2}}
* {{cite web |title=Using SQLAlchemy |publisher=IBM |work=Developerworks |first=Noah |last=Gift |url=https://www.ibm.com/developerworks/aix/library/au-sqlalchemy/ |date=12 Aug 2008 |accessdate=8 Feb 2011 |deadurl=yes |archiveurl=https://web.archive.org/web/20110812053848/http://www.ibm.com/developerworks/aix/library/au-sqlalchemy/ |archivedate=2011-08-12 }}
* Rick Copeland, [http://oreilly.com/catalog/9780596516147/ Essential SQLAlchemy]{{Wayback|url=http://oreilly.com/catalog/9780596516147/ |date=20110906162952 }}, [[O'Reilly_Media|O'Reilly]], 2008, ISBN 0-596-51614-2
{{div col end}}

== 参见 ==
{{Portal|自由軟體}}
* [[SQLObject]]
* {{le|Storm (软件)|Storm (software)|Storm}}
* [[Pylons]]
* [[TurboGears]]

== 外部链接 ==
* [http://www.sqlalchemy.org/ SQLAlchemy主页]{{Wayback|url=http://www.sqlalchemy.org/ |date=20121207211306 }}
* {{SourceForge|sqlalchemy}}

[[Category:Python库]]
[[Category:对象关系映射]]
[[Category:使用MIT许可证的软件]]