SQLAlchemy 使用指导

最新推荐文章于 2024-06-22 13:00:54 发布

一只小水鱼

最新推荐文章于 2024-06-22 13:00:54 发布

阅读量1.5k

点赞数 1

分类专栏： python 数据库

本文链接：https://blog.csdn.net/u011546766/article/details/78325452

版权

python 同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

数据库

1 篇文章 0 订阅

订阅专栏

http://docs.sqlalchemy.org/en/latest/orm/tutorial.html

本文是带有自我理解的官方翻译文档。

对象关系映射是一组方法、用来关联用户自定义的 python 对象和数据库表，也是那些代表数据库元素的 python 对象的实例。它包含一个称为工作单元的系统，用来透明的同步 python 对象和数据库元素之间的所有变化，也是用用户定义的类和彼此定义的关系来表示数据库查询的系统。

尽管ORM和表达式语言的使用模式有重叠，但它们之间的相似之处比乍看上去更肤浅。一种方法是从用户定义的域模型的角度来处理数据的结构和内容，该模型透明地持久化并刷新其底层存储模型。另一种方法是从字面模式和SQL表达式表示的角度来处理，这些表示被显式地组合到数据库中单独使用的消息中。

一个好的应用程序应该使用对象关系映射器来构造。但在高级情况下，用ORM构造的应用程序可能会在需要特定数据库交互的特定区域中直接使用表达式语言。

检查版本

import sqlalchemy
sqlalchemy.\__version__

连接数据库

本文档使用内存中的sqllite 数据库，使用 create_engine 来创建引擎连接数据库：

from sqlalchemy imort create_engine
engine = create_engine('sqlite:///:memory:', echo=True)

echo 是设置 SQLAlchemy 日志打印的，基于 python 的 logging 模块。echo 设置为 true 时，控制台会打印出所有生成的 sql 语句。
返回值是引擎实例，表示与数据库连接的核心接口，通过方言适配各个数据库和DBAPI的细节。本章使用 sqlite ，会选择内置的 sqlite3 模块。

只有在类似 Engine.execute() 或者 Engine.connect() 方法首次被调用的时候，Engine 才会真正的创建 DBAPI 连接数据库，然后才调用这个 DBAPI 发送 sql 语句。使用 ORM 时，并不会直接使用创建的引擎，而是在后台调用。

创建映射

要使用ORM，需要先做好映射配置，用 python 对象描述好我们要处理的数据库表，这个配置使用 Declarative 可以很方便的配置好。
使用 Declarative 系统创建对象映射，是基于 declarative base class 基类的，这个类用来关联 python 对象和数据库表的。在一个应用中，这个基类通常只需要一个。使用 declarative_base() 来创建这个基类，如下：

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

现在创建好了 Base，我们就靠它可以定义无数个映射类了。我们先创建 users 表好了，用来保存我们应用的用户数据，映射类命名为 User。User 类中，我们会定义好需要映射的细节，主要是表名、和列名：

from sqlchemy import Column, Integer, String
class User(Base):
__tablename__ = 'users'

id = Column(Interger, primary_key=True)
name = Column(String)
fullname = Column(String)
password = Column(String)

def __repr__(self):
return "<User(name='%s', fullname='%s', password='%s')>" % (self.name, self.fullname, self.password)

集成自 Declarative 的类至少需要 __tablename__ 属性和至少一个主键列。SQLAlchemy从不对类引用的表进行任何假设，包括它没有内置的名称、数据类型或约束的约定。
除了映射过程对我们的类有什么作用外，类仍然是一个普通的Python类，在这个类中，我们可以定义应用程序所需的任意数量的普通属性和方法。

创建模式

通过 Declarative 系统构建的用户类，我们定义了关于表的信息，称为表元数据。SQLAlchemy用来表示特定表的这些信息的对象称为表对象，而这里 Declarative 为我们生成了一个。我们可以通过检查表属性来查看这个对象:

User.__table__

当我们声明类时，Declarative 使用了一个Python元类，以便在类声明完成后执行额外的活动;在这个阶段中，它根据我们的规范创建了一个表对象，并通过构造一个Mapper对象将它与类相关联。这个对象是我们通常不需要直接处理的幕后对象(尽管在需要时它可以提供很多关于映射的信息)。

Table对象是一个更大的集合的成员，这个集合称为 metaData 元数据。

元数据是一个注册表，其中包括向数据库发送有限的模式生成命令的能力。由于我们的SQLite数据库实际上没有一个用户表，所以我们可以使用元数据向数据库发出CREATE table语句，以创建所有尚未存在的表。下面，我们调用MetaData.create all()方法，将我们的引擎作为数据库连接的来源。我们将看到，首先发出特殊的命令来检查用户表的存在，并遵循实际的CREATE table语句:

Base.metadata.create_all(engine)

创建映射对象的实例

映射完成后，现在来创建并检查 User 对象：

ed_user = User(name='ed', fullname='Ed Jones', password='edspwd')

此时 ed_user.id 为空。
Declarative 系统会实现 __init__() 的构造方法，自动接收已经映射的列的参数。

创建会话

现在已经准备好与数据库交互了。ORM 与数据库交互的处理对象是 Session - 会话。一开始启动app的时候，与 create_engine 语句同级别的地方，我们要定义 Session 类，用来作为工厂，给每个新的 Session 创建实例：

from sqlalchemy.orm import sessionmaker
Session = sessionmake(bind=engine)

上面的会话与我们的可用 sqlite引擎相关联，但是它还没有打开任何连接。当它第一次使用时，它从一个由引擎维护的连接池中获取一个连接，并一直保存到它，直到我们提交所有的更改和/或关闭会话对象。

添加和更新对象

要保存 User 对象，需要 add() 到 Session 里：

ed_user = User(name='ed', fullname='Ed Jones', password='edspwd')
session.add(ed_user)

此时，ed_user 正等待添加，尚无 sql 语句执行，对象也还不是数据库中的一行。Session会在需要的时候执行相应的 sql，这个过程称为 flush。当我们执行查询的时候，所有正在等待添加的对象都会 flush，查询命令会在此后立刻发出。

可以通过修改 python 对象的值来更新数据库：

ed_user.password = 'wwwppp'

此时，Session 知道 ed_user 被修改了，可以查看到：

session.dirty

新建的对象也能查看到：

session.add(User(name='ej', fullname='Ed json', password='asdasd'))
session.new

调用 commit 将提交所有的修改、事务到数据库里：

session.commit()

此时，session 的连接资源会被返回到连接池中，该session的后续操作会请求一个新的事务、也会重新请求连接资源。

当 session 向数据库插入了新的数据时，所有新生成的标识符和数据库生成的默认值都可以在实例上立即可用。此时，完整的一行数据是重新加载使用的，因为当执行了 commit()后、就会新建一个事务。SQLAlchemy 默认在事务启动之前更新数据，保证最新的状态都是可用的。重新加载数据的级别定义在 Using the Session 中介绍。

回退

session 是基于事务工作的，那么事务中的修改就可以回退：

ed_user.name = 'dirty name'
session.dirty
session.rollback()
session.dirty

查询

查询对象是 Session 通过 query 方法创建的，这个方法有一堆参数，这些参数可以任意结合类、仪表类描述符。下面，我们指明了一个加载 User 对象的 Query 对象。当我们迭代 Query 对象时，User 列表对象就会返回：

for user in session.query(User).order_by(User.id):
print(user.name, user.fullname)

Query 对象也可以 ORM 描述符作为参数，但返回值就是元组了：

for name, fullname session.query(User.name, User.fullname).
print(name, fullname)

支持查询结果切片：

for user in session.query(User).order_by(User.id)[1:3]:
print(user.name, user.fullname)

常用 filter 操作符

equals:

query.filter(User.name == 'ed')

not equals:

query.filter(User.name != 'ed')

like:

query.filter(User.name.like(‘%ed%’)) #大小写敏感
query.filter(User.name.ilike(‘%ED%’)) #大小写不敏感

in:

query.filter(User.name.in_(['ed', 'ec', 'ea']))
query.filter(User.name.in_(session.query(User.name).filter(User.name.like(‘%e%’))))

not in:

query.filter(~User.name.in_(['ed', 'ec', 'ea']))

is null:

query.filter(User.name == None)
query.filter(User.name.is_(None))

is not null:

query.filter(User.name != None)
query.filter(User.name.isnot(None))

and:

query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))
query.filter(User.name == 'ed', User.fullname == 'Ed Jones')
query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')

or:

query.filter(or_(User.name == 'ed', User.name== 'ec'))

match:

query.filter(User.name.match('ed'))

返回列表和标量

有一些方法可以让查询立刻执行 sql，并且返回数据：
all()：返回列表

session.query(User).all()

first(): 只查询一行数据，并将其作为标量返回。当查询的数据不存在时，返回 NONE：

session.query(User).first()

one()：查询所有满足条件的数据，没有数据或者不只一行都会抛出异常。没有数据时抛出：NoResultFound，不只一行时抛出：MultipleResultFound：

session.query(User).one()

此方法对于那些期望处理“没有发现”和“发现多个项目”的系统来说是很好的;例如一个RESTful web服务，它可能希望在没有找到结果时提出“404 not found”，但是当发现多个结果时，会提出一个应用程序错误。

one_or_none()：类似 one()，当没有数据时，不抛异常，返回 NONE；当有多行数据时，还是会抛出异常：

session.query(User).one_or_none()

scalar()：调用了 one() 方法，返回成功的一行数据，类似 first()。

使用 sql 文本语句

Query 还可以调用 text() 来使用 sql 语句直接进行查询，非常灵活。text() 可以被大部分方法使用，如 filter、order_by等：

from sqlalchemy import text
for user in session.query(User).filter(text('id<224')).order_by(text('id')).all():
print(user.name)

要使用完整的的 sql 语句，那就要通过 from_statement() 调用 text()：

session.query(User).from_statement(text("select * from users where name=:name")).params(name='ed').all()

计数

Query对象有一个很方便的计数方法 count()：

session.query(User).count()

count()是用来统计行数的。

建立关系

现在，创建第二个可映射和查询的表，并且与 User 对象关联。我们系统中的用户，可以保存多个与用户名相关的 email 地址。这意味着要建立一个基本的、从 users 表关联到另一个保存email地址的address表的一对多关系。使用 Declarative 声明，我们来创建这个表的映射类：

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email_address = Column(String, nullable=False)
user_id = Column(Integer, ForeignKey('users.id'))
user = relationship("User", backref="addresses")
def __repr__(self):
return "<Address(email_address='%s')>" % self.email_address

上述代码介绍了 ForeignKey 的概念，这是直接作用于 Column 的指令，指明了本 Column 受到其值指向的 Column 的约束。这是关系型数据库的核心特性之一，也是将原本没有关系的多个表粘合到一起的胶水。上述代码的 ForeignKey 属于 Address.user_id 列，该列被 users.id 列约束。

第二个指令是 relationsip()，这是告诉 ORM Address 类应该与 User 关联，使用 Address.user 就可以访问他所关联的 User 对象。relationship() 指令使用两个表之间的外键关系来检查关联的性质，检查到 Address.user 会是一个多对一的关系。此时，一个附加的 relationship() 指令被放置在属性 User.addresses 上。在双方的 relationship() 中，参数 relationship.back_populates 标记互相引用的属性名，这样，每一个 relationship() 指令，都可以对相同的关系做出明智的决定，就像反向选择一样：一方面，Address.user 指向 User 实例，另一方面，User.addresses 指向 Address 实例列表。

多对一关系的反面就是一对多。完整的 relationship 的参考目录是 Basic Relationship Patterns。

两个互相补充引用的关系 Address.user 和 User.addresses 被称为双向关系，也是 SQLAlchemy ORM 的关键特性。Linking Relationships with Backref 专门详细讨论了 “backref” 的特性。

如果使用 Declarative 系统，那么 relationship() 涉及远程类的参数可以是字符串，如：relationship(“User”, back_populates=”addresses”) 中的 “User”。当所有的映射建立完成后，这些字符串会被当做 python 表达式进行评估，以产生实际的参数，上述例子就是 User 类。评估过程中检查的名字包括所有继承自 declared 的类。

使用 create_all 创建出所有新建的表：

Base.metadata.create_all(engine)

使用关联的对象

创建好 User 和 Address 且建立好关系后，当创建 User 实例时， User.addresses 会是个空的容器。SQLAlchemy 支持多种容器，比如 sets 和 dicts，但默认一般是 python 列表 []。

jack = User(name='jack', fullname='Jack Bean', password='gjffdd')
jack.addresses

这样，给 User 添加一个 Address 就变得非常自由，可以直接赋值一个列表：

jack.addresses = [
...                 Address(email_address='jack@google.com'),
...                 Address(email_address='j25@yahoo.com')]

当建立的关系是双向的时，给一方添加对象，另一方也会自动加上。这个行为是基于 on-change 事件特性的，纯 python 实现，与 sql 无关。

jack.addresses[0]
jack.addresses[0].user

提交 jack 对象到数据库，此时也会吧 jack 下的 addresses 也串联的提交到数据库：

session.add(jack)
session.new
session.commit()

现在来查询 jack：

queryJack = session.query(User).filter_by(name='jack').one()
queryJack.addresses

可以看到，在查看 queryJack 的 addresses，又有 sql 执行了。这其实是一个 lazy load 的示例。
现在，addresses 的查询集合的表现就像是 python 的 list 一样了，接下来我们来看看如何优化加载过程。

使用 join 查询

有两个表了，那么就可以展示更多的查询特性了，尤其是联表查询。
要在 User 和 Address 之间构造隐式的连接，我们可以使用 query.filter() 将他们之间相关的列放在一起比较。
query = session.query(User).filter(User.id==Address.user_id).filter(Address.email_name==’254778’)
query.all()
但这样会加载出完整的 User 和 Address 对象，而我其实只要 User 对象，那么就应该使用 join：

query = session.query(User).join(Address).filter(Address.email_name == '254778')
query.all()

可以看到，这样查询就用到了 join。
Query.join()知道如何将两个表 join 到一起，因为定义好了两个表之间的唯一外键。如果没有外键、或者有多个外键，那么可以如下指定：

query(User).join(Address, User.id = Address.user_id) #指定 join 的条件
query(User).join(User.addresses) #指明了关系
query(User).join(Address, User.addresses) # 同上，指明关系
query(User).join('addresses') #同上，只是指明的是字符串

还可以使用 outerjoin 方法：

query(User).outerjoin(User.addresses)

join 是app流畅使用 sql 非常重要的特性。

使用 Aliases 别名

在查询多个表的时候，如果同一个表在查询时有不同含义时，SQL 就需要表名可以有别名，这样，就可以把表和它其他事件区分开来。SQLAlchemy query 支持使用 aliased。

from sqlalchemy.orm import aliased
alias1 = aliased(User)
alias2 = aliased(User)

使用子查询

query 可以生成可以用作子查询的语句。假设我们希望加载用户对象，并计算每个用户有多少个地址记录。生成这样的SQL的最佳方法是获取由用户id分组的地址数，并连接到父节点。在本例中，我们使用一个左外连接，以便为那些没有地址的用户返回行，例如:

SELECT users.*, adr_count.address_count FROM users LEFT OUTER JOIN  (SELECT user_id, count(*) AS address_count FROM addresses GROUP BY user_id) AS adr_count ON users.id=adr_count.user_id

使用查询，我们从内部构建一个类似这样的语句。语句访问器返回一个SQL表达式，表示特定查询生成的语句——这是select()结构的一个实例，它在SQL表达式语言教程中描述:

from sqlalchemy.sql import func
stmt = session.query(Address.user_id, func.count(*)).label('address_count').group_by(Address.user_id).subquery()

预加载

回想一下，当我们访问用户时，我们演示了一个延迟加载操作。发出了一个用户和SQL的集合。如果您想减少查询的数量(在很多情况下是显著的)，我们可以向查询操作应用一个热切的负载。SQLAlchemy提供了三种类型的热加载，其中两种是自动的，第三种是定制的。这三种方法通常都通过称为查询选项的函数来调用，通过query.options()方法，可以为查询提供额外的指令。

subqueryload

在本例中，我们想要指出的用户地址应该加载急切。加载一组对象及其相关集合的一个好的选择是orm.subqueryload()选项，它会发出第二个SELECT语句，该语句将完全加载与刚刚加载的结果相关联的集合。“子查询”的名称源于这样一个事实:通过查询直接构造的SELECT语句被重新使用，将子查询嵌入到与相关表的SELECT中。这个有点复杂，但很容易使用:

from sqlalchemy.orm import subqueryload
jack = session.query(User).options(subqueryload(User.addresses)).filter_by(name = 'jack').one()
jack
jack.addresses

Joined load

另一个自动加载函数更广为人知，称为orm.joinedload()。这种类型的加载会发出一个JOIN，默认是一个左外连接，这样就可以在一个步骤中加载主对象和相关的对象或集合。我们用这种方式来说明加载相同的地址集合——注意即使是用户。现在，jack的地址集合实际上已经被填充，查询将会发出额外的连接:

from sqlalchemy.orm import joinedload
jack = session.query(User).options(joinedload(User.addresses)).filter_by(name = 'jack').one()
jack
jack.addresses

Join + 预加载

第三种类型的加载方式是当我们明确地构造一个连接以定位主行，并且希望另外将额外的表应用到一个相关的对象或在主对象上的集合上。这个特性是通过orm.contains_eager()函数来提供的，并且对于在需要在同一个对象上进行过滤的查询中预先加载多对一对象非常有用。下面，我们演示了加载地址行以及相关的用户对象，对名为“jack”的用户进行过滤，并使用orm.contains_eager() 将“用户”列应用到 Address.user 属性

from sqlalchemy.orm import contains_eager
jack = session.query(Address).join(Address.user).filter(User.name == 'jack').options(contains_eager(Address.user)).one()
jack
jack.addresses

创建多对多关系

下面我们讲解多对多关系，也顺带讲一下其他的特性。写一个博客应用程序，用户可以在其中编写博客文章条目，条目就是有与之相关的关键字项。

对于一个简单的多对多，我们创建一个未映射的表结构来作为关联表，如下：

from sqlalchemy import Table, Text
post_keywords = Table('post_keywords', Base.meradata,
        Column('post_id', ForeignKey('posts.id'), primary_key=True),
        Column('keyword_id', ForeignKeY('keywords.id'), primary_key=True))

使用 Table 声明跟声明映射对象还是有些区别的。
接下来声明 BlogPost 和 Keyword，用到 relationship()。涉及到 post_keywords 都是相关的类：

class BlogPost(Base):
    __tablename__ = 'posts'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    headline = Column(String(255), nullable=False)
    body = Column(Text)

    keywords = relationship('Keyword',
            secondary = post_keywords,
            back_polulates='posts')

    def __init__(self, headline, body, author):
        self.author = author
        self.headline = headline,
        self.body = body

    deg __repr__(self):
        return "BlogPost(%r, %r, %r)" % (self.headline, self.body. self.author)

class Keyword(Base):
    __tablename__ = 'keywords'

    id = Column(Integer, primary_key=True)
    keyword = Column(String(50), nullable=False, unique=True)
    posts = relationship('BlogPost',
            secondary=post_keywords,
            back_polulates='keywords')

    def __init__(self, keyword):
        self.keyword = keyword

上述多对多的关系指的是 BlogPost.keywords 和 Keyword.posts 。声明多对多关系的特性的关键字是 secondary ，这个关键字表名Table对象post_keywords是关联的表，这个表只有两列，分别代表一个关系的两边，如果这个表有其他的列，比如它的主键、或关联到其他表的外键，SQLAlchemy都需要使用“关联对象”这个模式来处理，详见 Association Object。
我当然想让 BlogPost 类有 author 字段，给 User 添加一个双向的关系，还有，一个User可以有很多 Blog。当我访问 User.posts 的时候，我希望可以进行懒加载。

用法也没什么不同：

#创建 wendy 用户
wendy = User(name="wendy", fullname="Wendy", password="123123123")
session.add(wendy)
# wendy 发表一篇博客
post = BlogPost("Wendy's Blog", "this is a test", wendy)
session.add(post)
# 给博客打上标签
post.keywords.append(Keyword('wendy'))
post.keywords.append(Keyword('firstpost'))
# 检查 session 状态
session.new
# 通过 keyword 查询 blog
session.query(BlogPost).filter(BlogPost.keywords.any(keyword='firstpost')).all()
# 通过 author 查询 blog
session.query(BlogPost).filter(BlogPost.author==wendy).all()
# blog的keywords
post.keywords
# blog的author
post.author

进阶文档

对象映射进阶：Mapper Configuration
关系配置进阶：Relationship Configuration
会话使用进阶：Using the Session

本文代码

#!/bin/python


import sqlalchemy
from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:', echo=True)

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref

from sqlalchemy import Column, Integer, String


from sqlalchemy import Table, Text
post_keywords = Table('post_keywords', Base.metadata,
        Column('post_id', ForeignKey('posts.id'), primary_key=True),
        Column('keyword_id', ForeignKey('keywords.id'), primary_key=True))

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)


    posts = relationship('BlogPost',
            back_populates='author',
            lazy='dynamic')

    def __repr__(self):
        return "<User(name=%s, fullname=%s, password=%s)>" % (self.name, self.fullname, self.password)

class Address(Base):
    __tablename__ = 'addresses'

    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=True)
    user_id = Column(Integer, ForeignKey('users.id'))

    user = relationship('User', backref="addresses")

    def __repr__(self):
        return "<Address(email_address=%s)>" % self.email_address


class BlogPost(Base):
    __tablename__ = 'posts'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    headline = Column(String(255), nullable=False)
    body = Column(Text)

    author = relationship("User", back_populates='posts')

    keywords = relationship('Keyword',
            secondary = post_keywords,
            back_populates='posts')

    #keywords = relationship('Keyword',
    #        secondary = post_keywords)

    def __init__(self, headline, body, author):
        self.author = author
        self.headline = headline
        self.body = body

    def __repr__(self):
        return "<BlogPost(%r, %r, %r)>" % (self.headline, self.body, self.author)

class Keyword(Base):
    __tablename__ = 'keywords'

    id = Column(Integer, primary_key=True)
    keyword = Column(String(50), nullable=False, unique=True)

    #posts = relationship('BlogPost',
    #        secondary=post_keywords)

    posts = relationship('BlogPost',
            secondary=post_keywords,
            back_populates='keywords')

    def __init__(self, keyword):
        self.keyword = keyword

    def __repr__(self):
        return "<Keyword(%s)>" % self.keyword


Base.metadata.create_all(engine)

session = Session()

wendy = User(name='wendy', fullname='Wendy', password='123123123')
session.add(wendy)

post = BlogPost("Wendy's Blog", "this is a test", wendy)
session.add(post)

post.keywords.append(Keyword('wendy'))
post.keywords.append(Keyword('firstpost'))

session.commit()

postq = session.query(BlogPost).filter(BlogPost.keywords.any(keyword='firstpost')).all()

postqq = session.query(BlogPost).filter(BlogPost.author==wendy).all()