sqlchemy - day3
session
直接上代码,创建表结构,初始化部分数据。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 from sqlalchemy import create_engine 2 3 engine = create_engine("mysql+pymysql://root:max123@127.0.0.1/test?charset=utf8", echo=True) 4 5 from sqlalchemy import * 6 from sqlalchemy.orm import relationship 7 from sqlalchemy.ext.declarative import declarative_base 8 from datetime import datetime, date 9 from sqlalchemy.orm import deferred 10 Base = declarative_base() 11 12 13 class Parent(Base): 14 __tablename__ = 'parent' 15 16 id = Column(Integer(), primary_key=True) 17 name = Column(String(50)) 18 summary = deferred(Column(Text)) 19 children = relationship('ParentChild', back_populates='parent', cascade='all,delete-orphan') 20 21 22 class Child(Base): 23 __tablename__ = 'child' 24 25 id = Column(Integer(), primary_key=True) 26 name = Column(String(50)) 27 parents = relationship('ParentChild', back_populates='child') 28 29 30 class ParentChild(Base): 31 __tablename__ = 'parent_child' 32 33 id = Column(Integer(), primary_key=True) 34 child_id = Column(Integer(), ForeignKey('child.id'), nullable=False) 35 parent_id = Column(Integer(), ForeignKey('parent.id'), nullable=False) 36 description = Column(String(100)) 37 38 parent = relationship('Parent', back_populates='children') 39 child = relationship('Child', back_populates='parents') 40 41 42 Base.metadata.drop_all(engine) 43 Base.metadata.create_all(engine) 44 45 46 from sqlalchemy.orm import sessionmaker 47 48 Session = sessionmaker(bind=engine) 49 50 db = Session() 51 52 child_one = Child(name='purk1') 53 child_two = Child(name='purk2') 54 child_three = Child(name='purk3') 55 child_four = Child(name='purk4') 56 parent_one = Parent(name='Wu1') 57 parent_two = Parent(name='Wu2') 58 parent_child_one = ParentChild(description='association one') 59 parent_child_two = ParentChild(description='association two') 60 parent_child_one.child = child_one 61 parent_child_two.child = child_two 62 parent_one.children.extend([parent_child_one, parent_child_two]) 63 64 db.add_all([parent_one, parent_two, child_four]) 65 db.commit()
由简入难的开始
1. update
1 parent_one = db.query(Parent).filter_by(name='wu1').first() 2 parent_one.name='wu_test' 3 db.merge(parent_one) # update or insert 4 db.commit()
1 parent_one = db.query(Parent).filter_by(name='wu1').first() 2 parent_one.name='wu_test' 3 db.bulk_save_objects([parent_one]) 4 db.commit()
结果
2. add
1 parent_three =Parent(name='Wu3') 2 db.add(parent_three) 3 db.commit()
1 parent_three =Parent(name='Wu3') 2 db.add_all([parent_three]) 3 db.commit()
1 parent_three =Parent(name='Wu3') 2 db.merge(parent_three) 3 db.commit()
1 parent_three =Parent(name='Wu3') 2 db.bulk_save_objects([parent_three]) 3 db.commit()
上面四种方式都可以得到下面的结果。
3. delete
1 db.delete(parent_two) 2 db.commit()
parent_two 被删除了。
4. select
查的内容就是涉及到query了,留到下一章节来讲。
注:
class sqlalchemy.orm.session.Session(bind=None, autoflush=True, expire_on_commit=True,_enable_transaction_accounting=True, autocom-mit=False, twophase=False,
weak_identity_map=True,binds=None, extension=None, info=None,query_cls=<class ‘sqlalchemy.orm.query.Query’>)
的expire_on_commit的配置,默认是True
1 parent = db.query(Parent).filter_by(name='wu1').first()
现在还没有做db.commit()的操作,访问parent的name属性,结果如下。
现在我做一个后台操作,直接数据库里面修改这parent实例对应的记录。
现在数据已经变为了wu_test,显然现在parent.name的值还是'Wu1',现在我在执行下面的代码。当我再次使用parent的时候,parent重新获取了一次数据。
只在commit和rollback的时候才是这样,close的时候是不会的。同时这个特性就要注意了。