SQLAlchemy ORM

使用SQLAlchemy ORM定义模式

1 使用ORM类定义表

要求：

继承自declarative_base对象
包含__tablename__，这是数据库使用的表名
包含一个或多个属性，它们都是column对象
确保一个或多个属性组成主键

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

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

class Cookie(Base):  # 继承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))

键、约束、索引

在表构造函数中定义键和约束

class SomeDataClass(Base):
__tablename__ = 'somedatatable'
__table_args__ = (ForeignKeyConstraint(['id'], ['other_table.id']),CheckConstraint(unit_cost >= 0.00',name='unit_cost_positive'))

2 关系

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))  # 建立一个一对多关系

# 一对一关系
class LineItems(Base):
    __tablename__ = 'line_items'
    line_items_id = Column(Integer(), primary_key=True)
    order_id = Column(Integer(), ForeignKey('orders.order_id'))
    cookie_id = Column(Integer(), ForeignKey('cookies.cookie_id'))
    quantity = Column(Integer())
    extended_cost = Column(Numeric(12, 2))
    order = relationship("Order", backref=backref('line_items', order_by=line_items_id))
    cookie = relationship("Cookie", uselist=False)

3 模式持久化

from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/core?charset=utf8')

Base.metadata.create_all(engine)

使用SQLAlchemy ORM处理数据

1 会话

会话是SQLAlchemy ORM和数据库交互的方式。
sessionmaker类：确保整个应用程序中能够使用相同的参数创建会话。

# 创建一个可用来与数据库交互的session（不会自行连接到数据库）
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/core?charset=utf8')
Session = sessionmaker(bind=engine)

session = Session()

# __repr__方法，方便查看和重建对象实例（不是必须的）
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 "Cookie(cookie_name='{self.cookie_name}', "                        "cookie_recipe_url='{self.cookie_recipe_url}', "                        "cookie_sku='{self.cookie_sku}', "                        "quantity={self.quantity}, "                        "unit_cost={self.unit_cost})".format(self=self)

2 插入数据

# 插入单条数据
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)
session.add(cc_cookie)  # 将实例添加到会话
session.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()

# 批量插入多条数据
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.commit()
速度很快，但是有缺陷：
1、关系设置和操作得不到遵守或触发
2、对象没有连接到会话
3、默认情况下，不获取主键
4、不会触发任何事件

3 查询数据

# 1 查询所有数据 all()
cookies = session.query(Cookie).all()
print(cookies)

# 2 first()第一个、one()查询所有行，如果返回不是单个结果，抛出异常、saclar() 返回第一个结果的第一个元素。无结果：None；多于一个，引发错误

# 3 控制查询中的列数
print(session.query(Cookie.cookie_name, Cookie.quantity).first())

# 4 排序
## 升序
for cookie in session.query(Cookie).order_by(Cookie.quantity):
    print('{:3} - {}'.format(cookie.quantity, cookie.cookie_name))
## 降序
from sqlalchemy import desc
for cookie in session.query(Cookie).order_by(desc(Cookie.quantity)):
    print('{:3} - {}'.format(cookie.quantity, cookie.cookie_name))

# 5  限制返回条数
## 
query = session.query(Cookie).order_by(Cookie.quantity)[:2]
print([result.cookie_name for result in query])
## 
query = session.query(Cookie).order_by(Cookie.quantity).limit(2)
print([result.cookie_name for result in query])

# 6 内置SQL函数和标签
## sum()
from sqlalchemy import func
inv_count = session.query(func.sum(Cookie.quantity)).scalar()
print(inv_count)
## count()
rec_count = session.query(func.count(Cookie.cookie_name)).first()
print(rec_count)

# 7 重命名统计列
rec_count = session.query(func.count(Cookie.cookie_name)                           .label('inventory_count')).first()
print(rec_count.keys())
print(rec_count.inventory_count)

# 8 过滤
## filter()
record = session.query(Cookie).filter(Cookie.cookie_name == 'chocolate chip').first()
print(record)
## filter_by()
record = session.query(Cookie).filter_by(cookie_name='chocolate chip').first()
print(record)

最佳实践

使用迭代而非all()方法获取记录
获取单条记录，使用first()
谨慎使用scalar()方法，如果查询返回的数据不只一行一列，会引发错误

4 更新数据

# 通过对象更新数据
query = session.query(Cookie)
cc_cookie = query.filter(Cookie.cookie_name == "chocolate chip").first()
cc_cookie.quantity = cc_cookie.quantity + 120
session.commit()
print(cc_cookie.quantity)

# 就地更新数据
query = session.query(Cookie)
query = query.filter(Cookie.cookie_name == "chocolate chip")
query.update({Cookie.quantity: Cookie.quantity - 20})

cc_cookie = query.first()
print(cc_cookie.quantity)

5 删除数据

query = session.query(Cookie)
query = query.filter(Cookie.cookie_name == "dark chocolate chip")
dcc_cookie = query.one()
session.delete(dcc_cookie)
session.commit()
dcc_cookie = query.first()
print(dcc_cookie)

# 直接删除，无需获取待删除对象
query = session.query(Cookie)
query = query.filter(Cookie.cookie_name == "molasses")
query.delete()
mol_cookie = query.first()
print(mol_cookie)

6 连接

# 使用连接查询多个表
query = session.query(Order.order_id, User.username, User.phone,
                      Cookie.cookie_name, LineItem.quantity,
                      LineItem.extended_cost)
query = query.join(User).join(LineItem).join(Cookie)
results = query.filter(User.username == 'cookiemon').all()
print(results)

# 使用外连接查询多个表
query = session.query(User.username, func.count(Order.order_id))
query = query.outerjoin(Order).group_by(User.username)
for row in query:
    print(row)

# remote_side：自引用，形成多对一关系
class Employee(Base):
    __tablename__ = 'employees'
    
    id = Column(Integer(), primary_key=True)
    manager_id = Column(Integer(), ForeignKey('employees.id'))
    name = Column(String(255), nullable=False)
    
    manager = relationship("Employee", backref=backref('reports'), remote_side=[id])

Base.metadata.create_all(engine)

7 分组

query = session.query(User.username, func.count(Order.order_id))
query = query.outerjoin(Order).group_by(User.username)
for row in query:
    print(row)

8 链式调用(函数)

def get_orders_by_customer(cust_name, shipped=None, details=False):
    query = session.query(Order.order_id, User.username, User.phone)
    query = query.join(User)
    if details:
        query = query.add_columns(Cookie.cookie_name, LineItem.quantity,
                          LineItem.extended_cost)
        query = query.join(LineItem).join(Cookie)
    if shipped is not None:
        query = query.filter(Order.shipped == shipped)
    results = query.filter(User.username == cust_name).all()
    return results

print(get_orders_by_customer('cakeeater'))

print(get_orders_by_customer('cakeeater', details=True))

print(get_orders_by_customer('cakeeater', shipped=True))

print(get_orders_by_customer('cakeeater', shipped=False))

print(get_orders_by_customer('cakeeater', shipped=False, details=True))

9 原始查询

from sqlalchemy import text
query = session.query(User).filter(text("username='cookiemon'"))
print(query.all())

理解会话和异常

1 SQLAlchemy会话

会话状态：

transient(瞬时状态)：实例不在会话中，也不在数据库中
pending（挂起状态）：实例由add()方法添加到会话中，但仍未刷新或提交
persistent（持久化状态）：会话中的对象在数据库中有对应的记录
detached（脱管状态）：实例不再与会话相连，但在数据库中仍然有一条记录

2 异常

1 MultipleResultsFound异常

使用.one()方法，返回多个结果，就会触发这个异常

from sqlalchemy.orm.exc import MultipleResultsFound 
try:
    results = session.query(Cookie).one()
except MultipleResultsFound as error: 
    print('We found too many cookies... is that even possible?')

2 DetachedInstanceError

当尝试访问某个实例的某个属性时，如果这个实例需要从数据库加载，但他目前又没有连接到数据库，就会发生DetachedInstanceError异常

3 事务

使用SQLALchemy ORM和自动映射进行反射

1 使用自动映射反射数据库

# 使用automap_base创建base对象
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine

Base = automap_base()   
# 初始化引擎
from sqlalchemy import create_engine
# engine = create_engine('sqlite:///Chinook_Sqlite.sqlite')
engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/core?charset=utf8')

# base.prepare()方法，将扫描我们刚刚创建的引擎上的所有可用内容，并反射它所能反射的所有内容
Base.prepare(engine, reflect=True)
Base.classes.keys()  # 获取数据库所有表
users = Base.classes.users  # 将一个表的关系赋给users，创建引用

# 使用表
from sqlalchemy.orm import Session

session = Session(engine)
for artist in session.query(users).limit(10):
    print(artist.username, artist.email_address)

2 反射关系

自动映射可用自动反射和创建多对一、一对多、多对多的关系

posted on 2020-12-25 15:48 snail_z 阅读(112) 评论(0) 收藏举报

刷新页面返回顶部