sqlalchemy 7 ORM进阶

会话session，是 ORM 与 数据库 进行交互的方式。session里已经包含了connect，所以你在代码里看不到connect。

session里还包括了 事务，事务将一直打开，直到会话 提交事务commit 或 回滚事务rollback。

添加 session 和 __repr__.（注意不要用6的代码）。c7.py

from sqlalchemy import Table, Column, Integer, Numeric, String, Boolean
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime
from sqlalchemy import DateTime
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref

Base = declarative_base()

class Cookie(Base):
    __tablename__ = 'cookies'
    cookie_id = Column(Integer(), primary_key=True)
    cookie_name = Column(String(30), 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)

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)

    def __repr__(self):
        return "User(username='{self.username}', "\
                "email_address='{self.email_address}', "\
                "phone='{self.phone}', "\
                "password='{self.password}')".format(self=self)

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=order_id))    #这里第6章写成order_by=id，报错

    def __repr__(self):
        return "Order(user_id={self.user_id}, " \
                "shipped={self.shipped})".format(self=self)

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)

    def __repr__(self):
        return "LineItems(order_id={self.order_id}, " \
                "cookie_id={self.cookie_id}, " \
                "quantity={self.quantity}, " \
                "extended_cost={self.extended_cost})".format(self=self)
#初始化session
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine('sqlite:///c7.db')    #不用内存数据库了
Session = sessionmaker(bind=engine)    #session拥有连接到数据库的一切，但在要求它之前(commit等)，session是不会自行连接到数据库的。
session = Session()

Base.metadata.create_all(engine)

插入数据。c7_insert.py

from c7 import session, Cookie
c = Cookie(cookie_name = 'chocolate chip',
    cookie_recipe_url = 'http://some.aweso.me/cookie/recipe.html',
    cookie_sku = 'cc01',
    quantity=12,
    unit_cost=0.5)
session.add(c)
session.commit()    
#session.commit() #若commit重复执行，c会重复插入到数据库，由于没有重复性约束，就会有很多chocolate chip
print(c.cookie_id)    #插入以后，cookie_id会数据库自动赋值，并刷新会话里的c

在create_engine里插入echo=True，可以返回底层sql代码细节。（只在测试中使用）

#...
engine = create_engine('sqlite:///c7.db', echo=True)

执行session.commit()以后
...
INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 启动事务
INFO sqlalchemy.engine.base.Engine INSERT INTO cookies (cookie_name, cookie_recipe_url, cookie_sku, quantity, unit_cost) VALUES (?, ?, ?, ?, ?) 记录插入到数据库
INFO sqlalchemy.engine.base.Engine ('chocolate chip', 'http://some.aweso.me/cookie/recipe.html', 'cc01', 12, 0.5) 插入的值
INFO sqlalchemy.engine.base.Engine COMMIT 提交事务
....

一个工作单元：启动一个新事务，把记录插入数据库，发送insert语句的值，提交事务到数据库，关闭该事务。

插入多条数据

from c7 import session, Cookie
dc = 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(dc)
session.add(mol)
session.flush()    #用flush，即便重复执行，也不会重复插入到数据库。因为只是和数据库产生联系，没有入库永久化。
#flush可以rollback
print(dc.cookie_id)
print(mol.cookie_id)

书上关于flush的解释不完全。

flush()和commit()的区别：SQLAlchemy中flush和commit的区别_布丁老厮的博客-CSDN博客，这里是总结

python - SQLAlchemy: What's the difference between flush() and commit()? - Stack Overflow 这个帖子讲得很系统，下面是翻译

A Session object is basically an ongoing transaction of changes to a database (update, insert, delete). 会话里主要就是一个运行着的事务，事务是一组对数据库的修改操作。These operations aren't persisted to the database until they are committed (if your program aborts for some reason in mid-session transaction, any uncommitted changes within are lost).这些修改操作在commit之前 对数据库而言都不是永久性的（如果commit之前程序中断了，所有修改都没了）

The session object registers transaction operations with session.add(), but doesn't yet communicate them to the database until session.flush() is called.实例化对象被用add加到事务里，处于挂起状态pending，但现在仍是未和数据库有联系的(主键的值自然也就没有)。直到我调用flush

session.flush() communicates a series of operations to the database (insert, update, delete). flush把一系列修改传给数据库。The database maintains them as pending operations in a transaction.这些修改 维持挂起状态(处于pending，且 与数据库有联系) The changes aren't persisted permanently to disk, or visible to other transactions until the database receives a COMMIT for the current transaction (which is what session.commit() does). 这些处于pending的修改，在事务commit之前，对硬盘来说都不是永久的，其他事务也不能从数据库里调用这些修改。session.commit() commits (persists) those changes to the database.直到commit

flush() is always called as part of a call to commit() (1). flush总是作为commit的一部分被调用

When you use a Session object to query the database, the query will return results both from the database and from the flushed parts of the uncommitted transaction it holds. By default, Session objects autoflush their operations, but this can be disabled.当你在会话里用query，会返回数据库的内容+flush的部分。默认情况下，执行query之前会自动flush，当然我们也可以禁用它。禁用了以后，若没有手动flush 也没有commit，就无法query到。

Hopefully this example will make this clearer:

#---
s = Session()

s.add(Foo('A')) # The Foo('A') object has been added to the session.
                # It has not been committed to the database yet,
                #   but is returned as part of a query.
print 1, s.query(Foo).all()
s.commit()

#---
s2 = Session()
s2.autoflush = False

s2.add(Foo('B'))
print 2, s2.query(Foo).all() # The Foo('B') object is *not* returned
                             #   as part of this query because it hasn't
                             #   been flushed yet.

s2.flush()                   # Now, Foo('B') is in the same state as
                             #   Foo('A') was above.
print 3, s2.query(Foo).all() 

s2.rollback()                # Foo('B') has not been committed, and rolling
                             #   back the session's transaction removes it
                             #   from the session.
print 4, s2.query(Foo).all()

#---
Output:
1 [<Foo('A')>]    #query隐含了在query之前flush了，Foo('A')处于挂起状态
2 [<Foo('A')>]    #关掉了autoflush，query之前就无法flush Foo('B')了，Foo('B')即不在数据库也没有被挂起，就无法作为query返回的一部分了
3 [<Foo('A')>, <Foo('B')>]
4 [<Foo('A')>]    #rollback可以把挂起的内容清空

This does not strictly answer the original question but some people have mentioned that with session.autoflush = True you don't have to use session.flush()... And this is not always true. If you want to use the id of a newly created object in the middle of a transaction, you must call session.flush(). 有些人已经提到 session.autoflush = True 你不必使用 session.flush() ......这并不总是正确的，如果要在事务中间使用新创建的对象的 id，则必须调用 session.flush()

# Given a model with at least this id
class AModel(Base):
   id = Column(Integer, primary_key=True)  # autoincrement by default on integer primary key

session.autoflush = True

a = AModel()
session.add(a)
a.id  # None
session.flush()
a.id  # autoincremented integer

Why would you not commit all the time? - The answer is atomicity. 方便还原。

For example, if you want to create/update/delete some object (A) and then create/update/delete another (B), but if (B) fails you want to revert (A). This means those 2 operations are atomic. 例如，如果您想创建/更新/删除某个对象 (A)，然后创建/更新/删除另一个对象 (B)，但如果 (B) 失败，您想要还原 (A)。AB若分别commit则无法实现 还原A

Therefore, if (B) needs a result of (A), you want to call flush after (A) and commit after (B). 如果 (B) 需要 (A) 的结果，在 (A) 之后调用 flush 并在 (B) 之后commit。

python - sqlalchemy flush() and get inserted id? - Stack Overflow 关于这个帖子里的问题，是因为版本的问题。正常来说，flush以后就和数据库有联系了，id是有的。

看到后面的8.1，准确来说，pending（挂起状态）指的是只add的状态；flush和commit了的其会话状态都会是persistent（持久化状态）。但从上面看来flush的并不是真正持久化了的，所以为了便于区分，还是说成 挂起的(add)+有联系的（flush） 好一点。

批量插入多条记录。bulk_save_objects

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.aweso.me/cookie/raisin.html',
        cookie_sku= 'eww01',
        quantity=100,
        unit_cost=1.00,)
session.bulk_save_objects([c1,c2])
session.commit()
print(c1.cookie_id)
输出None

这里会输出None是bulk_save_objects的问题，与flush和commit无关。bulk_save_objects比前面用多条add语句方便，且执行速度快很多，但这是以牺牲一些特性为代价的：

1 关系设置 和 操作得不到遵守或触发；

2 对象没有连接到会话（没有用到add），commit完以后无法看到 类 与数据库之间的 联系；所以c1.cookie_id为None

3 默认情况下，不获取主键；

4 不会触发任何事件。

要插入大量记录，且不需要访问 关系或主键时，就用bulk_save_objects。尤其是从外部数据源(csv,json)获取数据时。

查询数据query。返回结果 可迭代。

from c7 import session, Cookie
c = session.query(Cookie).all()
for cc in c:
    print(cc)

for c in session.query(Cookie):    #可迭代，不用all()
    print(c)

还有first, one, scalar

print(session.query(Cookie).first())

print(session.query(Cookie).one())    #如果返回的不是单个结果，则抛出异常。

尽量使用first() 和 迭代。当使用scalar()时，如果查询返回多行多列，则会报错。

对查询返回的 列 进行限制。(只查询指定的 列）（比Core简单）

print(session.query(Cookie.cookie_name, Cookie.quantity).first())

排序order_by()。

for c in session.query(Cookie).order_by(Cookie.quantity):
    print(c.quantity, '-', c.cookie_name)

倒序desc

from sqlalchemy import desc
for c in session.query(Cookie).order_by(desc(Cookie.quantity)):
    print(c.quantity, '-', c.cookie_name)

限制 查询数（行数）。first() 虽然仅返回一行，但实际查询时 会访问所有结果。用 切片 或 limit() 对查询 进行限制。

for c in session.query(Cookie).order_by(Cookie.quantity)[:2]:
    print(c.cookie_name)
for c in session.query(Cookie).order_by(Cookie.quantity).limit(2):
    print(c.cookie_name)

内置SQL函数。例：sum(), count()

from sqlalchemy import func
i = session.query(func.sum(Cookie.quantity)).scalar()    #只返回第一个记录最左边的列
print(i)

r = session.query(func.count(Cookie.cookie_name)).first()
print(r)

标签label()。可以自定keys的名字

rr = session.query(func.count(Cookie.cookie_name).label('mylabel')).first()
print(rr.keys())
print(rr.mylabel)

过滤（筛选）。filter()。可以把 多个filter子句 一起使用，也可以 在单个filter里添加多个逗号分隔的表达式。

r = session.query(Cookie).filter(Cookie.cookie_name == 'chocolate chip', Cookie.cookie_sku == 'cc01').first()
r = session.query(Cookie).filter(Cookie.cookie_name == 'chocolate chip').filter(Cookie.cookie_sku == 'cc01').first()

filter_by()。使用 关键字赋值= 而非 布尔值==

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

或者 where的功能

r = session.query(Cookie).filter(Cookie.cookie_name.like('%chocolate%')).first()

运算符。对数据中的字符串 作连接处理，按我们想要的方式输出。

r = session.query(Cookie.cookie_name, 'sku-' + Cookie.cookie_sku).all()
for rr in r:
    print(rr)

布尔运算符 & | ~ 尽量不用。多用连接词

连接词 and_(), or_(), not_()

from sqlalchemy import and_, or_, not_
q = session.query(Cookie).filter(Cookie.quantity>23,Cookie.unit_cost<0.4)
q2 = session.query(Cookie).filter(or_(Cookie.quantity.between(10,50),Cookie.cookie_name.contains('chip')))

更新数据。可以 获取对象后直接更新，也可以用update

c = session.query(Cookie).filter(Cookie.cookie_name == 'chocolate chip').first()    #获取对象
c.quantity = c.quantity + 120    #赋值
session.commit()    #提交。重复执行，commit会重复加120，flush不会
print(c.quantity)

q = session.query(Cookie).filter(Cookie.cookie_name == 'chocolate chip')    #获得query对象，编列的话，是个元组列表
q.update({Cookie.quantity: Cookie.quantity -2})    #update只能对query对象使用
c = q.first()    #列表里的第一个元组
print(c.quantity)

删除数据delete()

q = session.query(Cookie).filter(Cookie.cookie_name == 'dark chocolate chip')    #query对象
d = q.one()    #query里只有一条记录时可以使用，有多条记录 或 没有记录时报错
session.delete(d)    #删除
session.commit()
d = q.first()
print(d)    #输出None

q = session.query(Cookie).filter(Cookie.cookie_name == 'molasses')    #query对象
for qq in q:
    print(qq)    #看一下有多少记录
q.delete()    #删除query
#session.commit()    #加commit删掉库里所有molasses,不加commit删掉query而已，库里没事。
m = q.first()    #query已经空了
print(m)    #输出None

加点数据。

from c7 import User
cookiemon = User(username= 'cookiemon',
        email_address= 'mon@cookie.com',
        phone= '111-111-1111',
        password= 'password')
cakeeater = User(username= 'cakeeater',
        email_address= 'cakeeater@cake.com',
        phone= '222-222-2222',
        password= 'password')
pieperson = User(username= 'pieguy',
        email_address= 'guy@pie.com',
        phone= '333-333-3333',
        password= 'password')
session.add(cookiemon)
session.add(cakeeater)
session.add(pieperson)
session.commit()

cookie的完整内容（如果之前插入了很多重复的，可以用delete全删了）

c = Cookie(cookie_name = 'chocolate chip',
    cookie_recipe_url = 'http://some.aweso.me/cookie/recipe.html',
    cookie_sku = 'cc01',
    quantity=12,
    unit_cost=0.5)
dc = 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,)
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.aweso.me/cookie/raisin.html',
        cookie_sku= 'eww01',
        quantity=100,
        unit_cost=1.00,)
session.add(c)
session.add(dc)
session.add(mol)
session.add(c1)
session.add(c2)
session.commit()
print(dc.cookie_id)
print(mol.cookie_id)

加order和line_items。绑定关系以后不用全部都add

o1 = Order()
o1.user = session.query(User).filter(User.username == 'cookiemon').first()
session.add(o1)
cc = session.query(Cookie).filter(Cookie.cookie_name == 'chocolate chip').one()
l1 = LineItems(cookie = cc, quantity = 2, extended_cost=1.00)
pb = session.query(Cookie).filter(Cookie.cookie_name == 'peanut butter').one()
l2 = LineItems(quantity = 12, extended_cost=3.00)
l2.cookie = pb    #类属性可以在初始化之外赋值
l2.order = o1    #双重绑定
o1.line_items.append(l1)
o1.line_items.append(l2)    #双重绑定
session.commit()

o2 = Order()
o2.user = session.query(User).filter(User.username == 'cakeeater').first()
cc = session.query(Cookie).filter(Cookie.cookie_name == 'chocolate chip').one()
l1 = LineItems(cookie = cc, quantity = 24, extended_cost=12.00)
oat = session.query(Cookie).filter(Cookie.cookie_name == 'oatmeal raisin').one()
l2 = LineItems(cookie = oat, quantity = 6, extended_cost=6.00)
o2.line_items.append(l1)
o2.line_items.append(l2)
session.add(o2)    #在这里add也可以的
session.commit()

使用连接join()。可以关联 与该数据有关的 其他表的数据。

query = session.query(Order.order_id, User.username, User.phone, Cookie.cookie_name,
                    LineItems.quantity, LineItems.extended_cost)
query = query.join(User).join(LineItems).join(Cookie)    #同样要遵循远近关系
r = query.filter(User.username == 'cookiemon').all()
for rr in r:
    print(rr)

外连接outerjoin()。会把没有orders的users也算进来。

from sqlalchemy import func
query = session.query(User.username, func.count(Order.order_id))
query = query.outerjoin(Order).group_by(User.username)
for r in query:
    print(r)

分组 group_by()

链式调用。（就是放进函数里？）

def get_orders_by_customer(cust_name):
    query = session.query(Order.order_id, User.username, User.phone, Cookie.cookie_name,
                    LineItems.quantity, LineItems.extended_cost)
    query = query.join(User).join(LineItems).join(Cookie)
    r = query.filter(User.username == cust_name).all()
    return r

for rr in get_orders_by_customer('cakeeater'):
    print(rr)

带条件的链式调用。使用条件来 构建查询连，让函数功能变得更强大。

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, LineItems.quantity, LineItems.extended_cost)    #add_columns
        query = query.join(LineItems).join(Cookie)
    if shipped != None:
        query = query.filter(Order.shipped == shipped)
    r = query.filter(User.username == cust_name).all()
    return r

for rr in get_orders_by_customer('cakeeater'):
    print(rr)