SQLAlchemy 使用笔记（二）ORM模式

最新推荐文章于 2024-06-26 16:46:14 发布

寻寻觅觅oO

最新推荐文章于 2024-06-26 16:46:14 发布

阅读量1.7k

点赞数 3

分类专栏： python知识点整理文章标签：数据库 sqlalchmy orm

本文链接：https://blog.csdn.net/weixin_42146296/article/details/115866382

版权

python知识点整理专栏收录该内容

28 篇文章 16 订阅

订阅专栏

使用 SQLAlchemy ORM 时，定义的模式会略有不同，因为它关注的是用户定义的数据对象，而非底层数据库的模式。在 SQLAlchemy Core 中，我们会先创建个元数据容器，然后声明一个与该元数据相关联的 Table 对象。而在 SQLAlchemy ORM 中，我们会定义一个类，它继承自一个名为 declarative_base 的特殊基类。 declarative_base 把元数据容器和映射器（用来把类映射到数据表）结合在一起。如果类的实例已经保存， declarative_base会把类的实例映射到表中的记录。

1、使用 ORM 类定义表

1.1 使用类定义新表

ORM 使用的类应该满足如下四个要求：
• 继承自 declarative_base 对象。
• 包含__tablename__ , 这是数据库中使用的表名。
• 包含一个或多个属性，它们都是 Column 对象。
• 确保一个或多个属性组成主键。

我们需要仔细检查最后两个与属性相关的需求。首先，在 ORM 类中定义列与在 Table
象中定义列非常相似，但是它们之间有个非常重要的区别。在 ORM 类中定义列时，我们不必为 Column 构造函数提供列名作为第一个参数。ORM用类中的属性名（变量名）作为数据库的列名。

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, Numeric, String, Boolean

Base = declarative_base()     # 创建 declarative_base() 的一个实例。

# 使用类创建新表
class Cookie(Base):                  
    __tablename__ = 'cookies'        #  定义表的名称
    cookie_id = Column(Integer, primary_key=True)    # 定义表的一个字段，列名为cookie_id，整形，并设为主键。
    cookie_name = Column(String(50), index=True)
    cookie_recipe_url = Column(String(255))
    cookie_sku = Column(String(55))
    quantity = Column(Integer)
    unit_cost = Column(Numeric(12, 2))

查看定义好的表的元数据：

print(Cookie.__table__.to_metadata)

返回结果为：

<bound method 
Table.to_metadata of Table('cookies', MetaData(), 
							Column('cookie_id', Integer(), table=<cookies>, primary_key=True, nullable=False), 
							Column('cookie_name', String(length=50), table=<cookies>), 
							Column('cookie_recipe_url', String(length=255), table=<cookies>), 
							Column('cookie_sku', String(length=55), table=<cookies>), 
							Column('quantity', Integer(), table=<cookies>), 
							Column('unit_cost', Numeric(precision=12, scale=2), table=<cookies>),
							schema=None)>

1.2、在数据库中生成表

用上面第一步创建的 Base 实例中的 metadata.create_all() 方法在数据库中生成表

from sqlalchemy import create_engine

engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)

注：可以在create_all中绑定 engine，也可以在创建 Base = declarative_base() 中绑定，如Base = declarative_base(engine)，这样在create_all中就不用绑定了，两个效果一样。

1.3 连接数据库中已存在的表

使用 automap_base 来映射数据库中已存在到表到类中。

from sqlalchemy.ext.automap import automap_base     # 导入 automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine


Base = automap_base()
engine = create_engine("sqlite:///mydatabase.db")  # 创建连接数据库的引擎。

# Base 对象的 prepare 方法，将扫描我们刚刚创建的引擎上的所有可用内容，
# 并为每个表创建了ORM 对象，可以在 Base 的 class 属性下访问这些表 。
Base.prepare(engine, reflect=True)

print(Base.classes.keys())   # 查看数据库中所有的表名。

# ====== 如果上面办法读取不了数据表，可用下面方法 ========
# Base = declarative_base()
# Base.metadata.reflect(engine)
# tables = Base.metadata.tables
# print(tables.keys())



# 获取表的ORM对象，假设数据库中有 user 和 address 两个数据表。
User = Base.classes.user
Address = Base.classes.address

session = Session(engine)

# 查询 User 表中的前10条数据。
data = session.query(User).limit(10)

2、详细的创建表

2.1 添加主键、约束

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, Numeric, String, DateTime
from sqlalchemy import create_engine
from datetime import datetime

engine = create_engine('sqlite:///mydatabase.db')
Base = declarative_base(engine)

class User(Base):
    __tablename__ = 'users'

    user_id = Column(Integer, primary_key=True)                 # 主键
    username = Column(String(15), nullable=False, unique=True)    # 必须有值，且唯一。
    email_address = Column(String(255), nullable=False)
    phone = Column(String(20), nullable=False)
    password = Column(String(25), nullable=False)
    created_on = Column(DateTime, default=datetime.now)
    updated_on = Column(DateTime, default=datetime.now, onupdate=datetime.now)  # onupdate 每次更新数据时，记录当前时间。

Base.metadata.create_all()

也可以使用__table_args__ 属性来添加和修改，如下添加外键：

……
from sqlalchemy import ForeignKeyConstraint

class User(Base):
    __tablename__ = 'users'
    __table_args__ = ForeignKeyConstraint(['account_id', 'parent_id'],
         								  ['folder.account_id', 'folder.folder_id'])

2.2 添加外键

使用 ForeignKey 添加外键，
还使用 relationship 指令来提供一个属性，该属性可用于访问相关对象。

一对多的外键例子：

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

class Order(Base):
    __tablename__ = 'orders'
    order_id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.user_id'))
    shipped = Column(Boolean, default=False)
    
    user = relationship("User", backref=backref('orders', order_by=id))  # 实际在表中不会创建该列。

与 User 类建立了一对多的关系，
在 relationship 中通过 backref 关键字参数在 User 类上建立了orders 属性，按照 order_id 排序。
在 relationship 中添加关键字参数 uselist=False 来建立一对一的关系。
注：relationship中的反向引用，只能在类定义表的代码文件中使用。如果是读取已存在的表，则用 表名__collection 属性来读取多一侧表的内容。
参考

3、增加插入数据

为了创建新会话， SQLAlchemy 提供了 sessionmaker 类，这个类可以确保在整个应用程序中能够使用相同的参数创建会话。 sessionmaker 类通过创建 Session 类来实现这一点，Session 类是根据传递给 sessionmaker 工厂的参数配置的。 sessionmaker 工厂在应用程序全局作用域中应该只使用 1次，并且被视为配置设置。

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///mydatabase.db')
Session = sessionmaker(bind=engine)
session = Session()

现在已经有了一个可用来与数据库交互的 session 。虽然 session 拥有连接数据库所需的一
切，但在要求它之前，它是不会自行连接到数据库的。

3.1 插入单条数据

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, Integer, Numeric, String

# 1、连接数据库
engine = create_engine('sqlite:///mydatabase.db')    # 添加 echo=True 参数会打印执行过程。
Base = declarative_base(engine)

# 2、新建表
class Cookie(Base):
    __tablename__ = 'cookies'

    cookie_id = Column(Integer, primary_key=True)
    cookie_name = Column(String(50), index=True)
    cookie_recipe_url = Column(String(255))
    cookie_sku = Column(String(55))
    quantity = Column(Integer())
    unit_cost = Column(Numeric(12, 2))
    
    def __repr__(self):   # 查询的返回实例“简介”
        return f"表：Cookie（cookied_id={self.cookie_id}，cookie_name={self.cookie_name}）"

# 3、在数据库中生成表
Base.metadata.create_all(engine)

# 4、建立会话
Session = sessionmaker(bind=engine)
session = Session()

# 5、实例化一条数据，此数据为需要写入数据库的内容。
cc_cookie = Cookie(cookie_name='chocolate chip',
                   cookie_recipe_url='http://some.aweso.me/cookie/recipe.html',
                   cookie_sku='CC01',
                   quantity=12,
                   unit_cost=0.50)

# 6、用会话的 add 方法插入数据
session.add(cc_cookie)
session.commit()            # commit 方法最终提交，在之前不会向数据库提交内容。

插入多条数据方法一：

dcc = Cookie(cookie_name='dark chocolate chip',
             cookie_recipe_url='http://some.aweso.me/cookie/recipe_dark.html',
             cookie_sku='CC02',
             quantity=1,
             unit_cost=0.75)
             
mol = Cookie(cookie_name='molasses',
             cookie_recipe_url='http://some.aweso.me/cookie/recipe_molasses.html',
             cookie_sku='MOL01',
             quantity=1,
             unit_cost=0.80)

session.add(dcc)    # 添加上面实例化的数据
session.add(mol)
session.flush()     # 利用flush写入到事务中，这样就能查看插入数据后得到的信息， 
print(dcc.cookie_id, mol.cookie_id)   # 比如 查看插入数据后的 id。 但用 flush并没有把数据真正写入到数据库中。

flush()方法和 commit 方法相似，但是它不会执行数据库提交并结束事务。因此， dcc 和 mol 实例仍然和会话连接，可以使用它们执行更多数据库任务，而无须另外发起数据库查询。虽然我们要向数据库中添加多个记录，但只调用一次 session.flush() 语句就够了。
最后仍需要 session.commit() 提交到数据库中。

插入多条数据方法二：

c1 = Cookie(cookie_name='peanut butter',
            cookie_recipe_url='http://some.aweso.me/cookie/peanut.html',
            cookie_sku='PB01',
            quantity=24,
            unit_cost=0.25)
c2 = Cookie(cookie_name='oatmeal raisin',
            cookie_recipe_url='http://some.okay.me/cookie/raisin.html',
            cookie_sku='EWW01',
            quantity=100,
            unit_cost=1.00)

session.bulk_save_objects([c1, c2])

用 session.bulk_save_objects(）方法，无需再使用 commit来提交了。
相比方法一中使用多条 add 语句和插入语句的做法，方法二的方法执行起来要快得多。
但是，这种速度的提升是以牺牲在正常添加和提交中可使用的一些特性为代价的，例如：
• 关系设置和操作得不到遵守或触发；
• 对象没有连接到会话；
• 默认情况下，不获取主键；（不能像方法一那样通过实例获得）
• 不会触发任何事件。

4、查询

4.1 使用session.query查询

构建查询时要用到会话实例的 query() 方法。首先，把 Cookie 类传递给 query() 方法，这样就能获取 cookies 表中的所有记录。

cookies = session.query(Cookie).all()
print(cookies)

返回的是一个列表对象，内容如下：

[表：Cookie（cookied_id=1，cookie_name=chocolate chip）, 
 表：Cookie（cookied_id=2，cookie_name=dark chocolate chip）, 
 表：Cookie（cookied_id=3，cookie_name=molasses）, 
 表：Cookie（cookied_id=4，cookie_name=peanut butter）, 
 表：Cookie（cookied_id=5，cookie_name=oatmeal raisin）]

将查询用作可迭代对象时，可不调用 all()

for i in session.query(Cookie):
    print(i)

注：使用迭代而非 all() 方法荻取记录，它比处理完整的对象列表更节省内存。
除了 all() 方法，还有：

first()	获取查询结果的第一条数据，推荐使用。
one()	当查询结果只有一条数据时使用，否则报错 exc.MultipleResultsFound，不推荐使用！
scalar()	返回第 1个结果的第1个元素。若无结果，返回 None; 若结果多于1个，则引发错误，不推荐使用！

4.2 控制查询的列（字段）

为了限制查询返回的列数（字段），需要把要查询的列传递给 query() 方法，各个列之间用逗号分
隔。

# 只返回 Cookie 表中 cookie_name 和 cookie_recipe_url 两个字段的 元组。
cookies = session.query(Cookie.cookie_name, Cookie.cookie_recipe_url).all()
print(cookies)

 [('chocolate chip', 'http://some.aweso.me/cookie/recipe.html'),
  ('peanut butter', 'http://some.aweso.me/cookie/peanut.html'), 
  ('oatmeal raisin', 'http://some.okay.me/cookie/raisin.html')]

4.3 排序 order_by

默认升序

cookies = session.query(Cookie.cookie_name, Cookie.cookie_recipe_url).order_by(Cookie.cookie_name).all()
print(cookies)

返回结果：

[('chocolate chip', 'http://some.aweso.me/cookie/recipe.html'), 
 ('oatmeal raisin', 'http://some.okay.me/cookie/raisin.html'), 
 ('peanut butter', 'http://some.aweso.me/cookie/peanut.html')]

降序排序方法：

from sqlalchemy import desc

cookies = session.query(Cookie.cookie_name, Cookie.cookie_recipe_url).order_by(desc(Cookie.cookie_name)).all()
print(cookies)

返回结果：

[('peanut butter', 'http://some.aweso.me/cookie/peanut.html'), 
 ('oatmeal raisin', 'http://some.okay.me/cookie/raisin.html'), 
 ('chocolate chip', 'http://some.aweso.me/cookie/recipe.html')]

4.4 限制返回结果的条数

使用limit()限制查询条数。

cookies = session.query(Cookie.cookie_name, Cookie.cookie_recipe_url).limit(2)
print(list(cookies))

不推荐使用切片

cookies = session.query(Cookie.cookie_name, Cookie.cookie_recipe_url)[0:2]
print(list(cookies))

因为切片会查询所有的数据，对大型结果集来说，这种做法的效率可能会很低。

4.5 内置SQL 函数和标签

使用内置函数：

from sqlalchemy import func

# 返回结果为 对 Cookie.quantity 列 求和。
inv_count = session.query(func.sum(Cookie.quantity)).first()
print(inv_count)

其余函数，可在func模块中查看。

# 对返回结果添加一个 label为 aaa 的标签。
inv_count = session.query(func.sum(Cookie.quantity).label('aaa')).first()
print(inv_count)
print(inv_count.keys())

4.6 过滤 filter 和 filter_by 功能

过滤查询是通过在查询后面添加 filter() 语句完成的。可以把多个 filter()子句接在一起使用，或者在单个 filter()中添加多个采用逗号分隔的表达式(and)。

按 cookie_name 为 ‘chocolate chip’ 进行过滤查询。
注意，此处为两个等号，即 Cookie.cookie_name == ‘chocolate chip’

record = session.query(Cookie).filter(Cookie.cookie_name == 'chocolate chip').first()

filter_by 方法，其工作方式和 filter() 方法类似，只是它没有明确要求把类作为过滤器表达式的一部分，而是使用属性关键字表达式，这些表达式来自于查询的主实体或最后1个连接到语句的实体。另外， filter_by() 使用的是关键字赋值而非布尔值。

record = session.query(Cookie).filter_by(cookie_name='chocolate chip').first()

其余功能 运算符（+，-，*，/），布尔运算（&, |, ~）,and_()，or_()，not_（）等 与 SQLAlchemy 使用笔记（一）Core模式中的 select ，where 使用一样。

注：在使用 AND 重载运算符时一定要特别小心，比如，＆比＜优先级高，所以当你写 A< B & C < D时，你想得到的是(A < B) &(C < D), 而实际得到的却是 A< (B&C) < D

4.7 用 cast() 函数对查询的结果做类型转换

from sqlalchemy import cast

query = session.query(Cookie.cookie_name,
                      cast((Cookie.quantity * Cookie.unit_cost), Numeric(12,2)).label('inv_cost'))

把查询的 Cookie.quantity 列和 Cookie.unit_cost 列的值相乘后，再把相乘后的值转为Numeric(12,2)类型，并使用 ‘inv_cost’ 这个标签。

5、更新数据

5.1 使用对象的属性进行更新

query_item = session.query(Cookie).filter_by(cookie_id=3).one()       # 查询出一条记录
query_item.quantity = 30										      # 对该条记录的quantity字段属性重新赋值。
session.commit()												      # commit()提交修改。

注：该方法只能更改单条数据。

5.2 使用update()进行更新

query_item = session.query(Cookie).filter_by(cookie_id=3)         # 该方法可以批量更新，所以后面无需再加one()进行筛选。
query_item.update({Cookie.quantity: 50, 'cookie_sku': "aaa"})     # 更新内容以字段形式提交，字典的键可以是字段属性，也可以是列名字符串
session.commit()

6、删除数据

6.1 就地删除

即在查询出来后，直接使用delete()方法。

query_item = session.query(Cookie).filter_by(cookie_id=4)
query_item.delete()      # 对 Query对象，使用delete方法删除。
session.commit()

6.2 调用session的delete方法删除

query_item = session.query(Cookie).all()[-1]      # 查询出来最后一条数据
session.delete(query_item)                        
session.commit()

注：两个方法的区别
6.1 是对 Query对象进行调用delete方法进行删除，6.2 是对具体的某条数据进行删除。

7、删除整张表

假设数据库中有这么一张表，表名为：test_del

class TestTable(Base):
    __tablename__ = "test_del"
    pk = Column(Integer(), primary_key=True)

方法1、直接调用 <‘sqlalchemy.sql.schema.Table’>类中的 drop方法，推荐

TestTable.__table__.drop()

方法2、使用 Base.metadata.drop_all() 方法
这里的Base就是一开始连接数据库创建的，用来生成class表继承的Base。

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine

engine = create_engine('sqlite:///mydatabase.db') 
Base = declarative_base(engine)

……（省略创建表的代码）

Base.metadata.drop_all(tables=[TestTable.__table__])   # 删除 TestTable 类创建的表

# Base.metadata.tables.keys()                          # 查看 Base.metadata 中的所有表。
# Base.metadata.drop_all()                             # 不带参数，会删除所有表。

注：如果在调用 Base.metadata.drop_all() 前没有调用过Base.metadata.create_all()创建过表，那么Base.metadata.tables.keys()是空值，Base.metadata.drop_all()不会删除任何表。

方法3、在不知道数据库的表名时，先用 automap_base 查询所有表，再删除

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base

engine = create_engine('sqlite:///mydatabase.db')  
Base = declarative_base(engine)

base = automap_base(Base) 
base.prepare(reflect=True)        # 映射数据库中所有的表
print(base.classes.keys())        # 查看表名 >>> [………………, 'test_del'] 

# ☆☆☆ automap_base(bind=engine) 等效于 declarative_base(engine) ☆☆☆
# base = automap_base(bind=engine)
# base.prepare(reflect=True)

del_table = base.classes.test_del  # 获取到要删除的表Class

base.metadata.drop_all(tables=[del_table.__table__])       # 删除表
# base.metadata.drop_all(tables=[base.metadata.tables['test_del']])   # tables属性直接获取表
# Base.metadata.drop_all(tables=[del_table.__table__])     # 此处用 base 和 Base都可以。

方法3、在不知道数据库的表名时，先用 Core模式中 MetaData 查询表再删除

from sqlalchemy import MetaData

metadata = MetaData(engine)

metadata.reflect()
print(metadata.tables.keys())

t = metadata.tables['test_del']        # 返回一个 <‘sqlalchemy.sql.schema.Table’>
t.drop()                               # 删除表
# Base.metadata.drop_all(tables=[t])   # 也可以

寻寻觅觅oO

关注

3
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
SQLAlchemy 使用笔记（二）ORM模式

1、异常处理sqlalchemy的异常类型在sqlalchemy.exc中，导入可以如下：from sqlalchemy.exc import IntegrityError IntegrityError ：当试图做一些违反列约束或表约束的事情时，就会发生这种错误。这种类型的错误通常出现在操作破坏了唯一性约束的情况下。2、事务启动事务时，数据库系统会先记录数据库的当前状态，然后再执行多个 SQL句。如果事务中的所有 SQL 语句都成功执行，那么数据库将继续正常运行，并丢弃之前的数据库状态。
复制链接

扫一扫