SQLAlchemy——Python SQL toolkit and Object Relational Mapper

http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#what-does-the-session-do

Session

The ORM’s “handle” to the database is the Session.

define a Session class which will serve as a factory for new Session objects:

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

如果在你定义模块级的对象时应用中还没有引擎，可以先这样写

>>> Session = sessionmaker()

然后当使用create_engine()创建好了引擎, 使用configure()连接到引擎:

>>> Session.configure(bind=engine)  # once engine is available

实例化Session（instantiate a Session）：

>>> session = Session()

以上创建的session只是与数据库引擎进行了关联，但并没有打开任何的连接。当第一次使用的时候，将会从引擎维护的连接池中取得一个连接，然后保持连接知道我们提交所有的操作或者关闭session对象

The above Session is associated with our Engine, but it hasn’t opened any connections yet. When it’s first used, it retrieves a connection from a pool of connections maintained by the Engine, and holds onto it until we commit all changes and/or close the session object.

一次性提交多组数据

session.add_all([
        User(id="11",name='wendy', fullname='Wendy Williams', password='foobar'),
        User(id="12",name='mary', fullname='Mary Contrary', password='xxg527'),
        User(id="13",name='fred', fullname='Fred Flinstone', password='blah')]
        )

或者

user1 = User(id="11",name='wendy', fullname='Wendy Williams', password='foobar')
user2 = User(id="12",name='mary', fullname='Mary Contrary', password='xxg527')
user3 = User(id="13",name='fred', fullname='Fred Flinstone', password='blah')
session.add_all([user1,user2,user3])

session-object-states

It’s helpful to know the states which an instance can have within a session:

• Transient（暂态） - an instance that’s not in a session, and is not saved to the database; i.e. it has no database identity. The only relationship such an object has to the ORM is that its class has a mapper() associated with it.

• Pending（挂起） - when you add() a transient instance, it becomes pending. It still wasn’t actually flushed to the database yet, but it will be when the next flush occurs.

• Persistent（持久） - An instance which is present in the session and has a record in the database. You get persistent instances by either flushing so that the pending instances become persistent, or by querying the database for existing instances (or moving persistent instances from other sessions into your local session).

• Deleted（删除） - An instance which has been deleted within a flush, but the transaction has not yet completed. Objects in this state are essentially in the opposite of “pending” state; when the session’s transaction is committed, the object will move to the detached state. Alternatively, when the session’s transaction is rolled back, a deleted object moves back to the persistent state.

• Detached（游离） - an instance which corresponds, or previously corresponded, to a record in the database, but is not currently in any session. The detached object will contain a database identity marker, however because it is not associated with a session, it is unknown whether or not this database identity actually exists in a target database. Detached objects are safe to use normally, except that they have no ability to load unloaded attributes or attributes that were previously marked as “expired”.

获取对象的当前状态
所有映射对象的状态在任何时候都可以使用inspect()进行检查获取：

>>> from sqlalchemy import inspect
>>> insp = inspect(my_object)
>>> insp.persistent
True

merge() transfers state from an outside object into a new or already existing instance within a session.

回滚（Rolling Back）

Since the Session works within a transaction, we can roll back changes made too.

查询（Querying）

session.query(User).order_by(User.id)
session.query(User).get(11)
#query.get({some primary key})
session.query(User, User.name).all()

通过切片操作实现LIMIT和OFFSET操作

session.query(User).order_by(User.id)[1:3]

session.query(User.name).filter_by(fullname=’Ed Jones’)
等同于：
session.query(User.name).filter(User.fullname==’Ed Jones’)#you can call filter() twice

计数（Counting）

Query includes a convenience method for counting called count():

>>> session.query(User).filter(User.name.like('%ed')).count()

删除数据（Deleting）

delete(self, instance) method of sqlalchemy.orm.session.Session instance
Mark an instance as deleted.

The database delete operation occurs upon flush().

session.query(User).filter(User.id == 13).delete()

session.commit()

更新数据（Updating）

try:
    session.query(User).update({'name':"test"})#全部更新
    session.query(User).filter_by(id="11").update({'name':"lockey"})#选择更新
    session.commit()
except:
    session.rollback()
    raise
finally:
    session.close()

How can I get the Session for a certain object?

Use the object_session() classmethod available on Session:

session = Session.object_session(someobject)

The newer Runtime Inspection API system can also be used:

from sqlalchemy import inspect
session = inspect(someobject).session

session是线程安全的吗？（Is the session thread-safe?）

The Session is very much intended to be used in a non-concurrent fashion, which usually means in only one thread at a time.

官网教程
http://docs.sqlalchemy.org/en/latest/orm/tutorial.html