SQLAlchemy添加数据

#看例子：

node = Node()

node.name = 'name'

db.session.add(node)

db.session.flush()    # 主要是这里，写入数据库，但是不提交

this_id = node.id   # 这样就可以获得自增id了

db.session.commit()

目录

Session 其实 就是一个会话, 可以和数据库打交道的一个会话

在一般的意义上， 会话建立与数据库的所有对话，并为你在其生命周期中加载或关联的所有对象表示一个“等待区”。他提供了一个入口点获得查询对象， 向数据库发送查询，使用会话对象的当前数据库连接， 将结果行填充在对象中， 然后存储在会话中， 在这种结构中称为身份映射 – 这种数据结构维护了每一个副本的唯一， 这种唯一意味着一个对象只能有一个特殊的唯一主键。

会话以基本无状态的形式开始，一旦发出查询或其他对象被持久化，它就会从一个引擎申请连接资源，该引擎要么与会话本身相关联，要么与正在 *** 作的映射对象相关联。此连接标识正在进行的事务， 在会话提交或回滚其挂起状态之前，该事务一直有效。

会话中维护的所有变化的对象都会被跟踪 - 在再次查询数据库或提交当前事务之前， 它将刷新对数据库的所有更改， 这被称为工作模式单元。

在使用会话时候，最重要的是要注意与它相关联的对象是会话所持有的事务的代理对象 - 为了保持同步，有各种各样的事件会导致对象重新访问数据库。可能从会话中分离对象并继续使用他们，尽管这种做法有其局限性。但是通常来说，当你希望再次使用分离的对象时候，你会将他们与另一个会话重新关联起来， 以便他们能够恢复表示数据库状态的正常任务。

可能会将这里的session与http中的session搞混，需要注意的是，它有点用作缓存，因为它实现了 身份映射 模式，并存储了键入其主键的对象。但是，它不执行任何类型的查询缓存。 此外，默认情况下，Session使用弱引用存储对象实例。这也违背了将Session用作缓存的目的。关于session强应用下次再讨论。

1. session创建和管理数据库连接的会话 2. model object 通过session对象访问数据库，并把访问到的数据以 Identity Map 的方式，映射到Model object 中

1. session在刚被创建的时候，还没有和任何model object 绑定，可认为是无状态的 2. session 接受到query查询语句, 执行的结果或保持或者关联到session中 3. 任意数量的model object被创建，并绑定到session中，session会管理这些对象 4. 一旦session 里面的objects 有变化，那可是要commit/rollback提交或者放弃changs

一般来说，session在需要访问数据库的时候创建，在session访问数据库的时候，准确来说，应该是“add/ update / delete ”数据库的时候，会开启 database transaction 。 假设没有修改autocommit的默认值( False ), 那么， database transaction 一直会保持，只有等到 session 发生rolled back、committed、或者closed的时候才结束，一般建议，当 database transaction 结束的时候，同时 close session ,以保证，每次发起请求，都会创建一个新的 session 特别是对web应用来说，发起一个请求，若请求使用到 Session 访问数据库，则创建 session ，处理完这个请求后，关闭 session

Session 是一个直接实例化的常规的Python 类。然而， 为了标准会会话的配置和获取方式， sessionmaker 类通常用于创建顶级会话配置， 然后可以在整个应用程序中使用它， 就不需要重复配置参数。

下面是sessionmaker 的使用方式

在上面，该 sessionmaker()创建了一个工厂类，在创建这个工厂类时我们配置了参数绑定了引擎。将其赋值给Session。每次实例化Session都会创建一个绑定了引擎的Session。 这样这个session在访问数据库时都会通过这个绑定好的引擎来获取连接资源当你编写应用程序时， 请将sessionmaker 工厂放在全局级别，视作应用程序配置的一部分。例如：应用程序包中有三个.py文件，您可以将该sessionmaker行放在__init__.py文件中在其他模块“from mypackage import Session”。这样，所有的Session()的配置都由该配置中心控制。

直接只用 create_engine 时，就会创建一个带连接池的引擎：

创建一个session，连接池会分配一个connection。当session在使用后显示地调用 session.close()，也不能把这个连接关闭，而是由由QueuePool连接池管理并复用连接。

确保 session 在使用完成后用 session.close、session.commit 或 session.rollback 把连接还回 pool，这是一个必须在意的习惯。

关于SQLAlchemy 数据库连接池：

session 和 connection 不是相同的东西， session 使用连接来 *** 作数据库，一旦任务完成 session 会将数据库 connection 交还给 pool。 在使用 create_engine 创建引擎时，如果默认不指定连接池设置的话，一般情况下，SQLAlchemy 会使用一个 QueuePool 绑定在新创建的引擎上。并附上合适的连接池参数

create_engine() 函数和连接池相关的参数有：

SQLAlchemy不使用连接池：在创建引擎时指定参数 poolclass=NullPool 即禁用了SQLAlchemy提供的数据库连接池。SQLAlchemy 就会在执行 session.close() 后立刻断开数据库连接。当然，如果没有被调用 session.close()，则数据库连接不会被断开，直到程序终止。

关于 SQLAlchemy 的 engine ,这里有一篇文章写的很好： http://sunnyingit.github.io/book/section_python/SQLalchemy-engine.html

session不是线程安全的，在多线程的环境中，默认情况下，多个线程将会共享同一个session。试想一下，假设A线程正在使用session处理数据库，B线程已经执行完成，把session给close了，那么此时A在使用session就会报错，怎么避免这个问题？

1 . 可以考虑在这些线程之间共享Session及其对象。但是应用程序需要确保实现正确的锁定方案，以便多个线程不会同时访问Session或其状态。SQLAlchemy 中的 scoped_session 就可以证线程安全，下面会有讨论。 2 . 为每个并发线程维护一个会话，而不是将对象从一个Session复制到另一个Session，通常使用Session.merge()方法将对象的状态复制到一个不同Session的新的本地对象中。

上面简单介绍了sessionmaker的作用，下面开始探讨 scoped_session 对创建 Session 的影响。现在先探讨单线程情况。

结论：

通过 sessionmaker 工厂创建了两个 Session ，而且可以看到 s1 s2 是两个不同的 Session 。 在 s1 添加 person 后，继续使用 s2 添加 person 报错. 说 person 这个对象 已经和 另一个 Session 关联一起来了, 所以再次关联另一个 Session 就会报错。

即在上面代码的 s1.add(person) 之后， s1.commit() ,然后再 s2.add(persion)这里就没帖代码了。

结论：

即使在 s1 提交之后， s2 再去添加 person 也会发生错误,但 s1 的提交是成功了的，数据 person 已经存放在数据库了。 当 s1 添加 person 并提交，然后关闭 s1 , s2 再去添加并提交 person 数据库,这不会报错，但是数据库也不会出现两条 person 数据。

结论：

s1 关闭之后， s2 再去添加提交同一个对象，不会报错，但是数据库值有一条 person 数据。

结论：

当然， s1 , s2 添加提交不同的对象，不会出错。在数据库成功新增数据。

以上说明：

一个对象一旦被一个 Session 添加，除非关闭这个 Session ，不然其他的 Session 无法添加这个对象。 一个 Session 添加并提交一个对象，然后关闭该 Session ，其他的 Session 可以添加并提交这个对象，但是数据库并不会有这条数据。

结论：

可以看到，通过 scoped_session再去创建 Session ，返回的是同一个 Session 。 scoped_session类似单例模式，当我们调用使用的时候，会先在Registry里找找之前是否已经创建Session，未创建则创建 Session ，已创建则直接返回。

这里探讨在多线程下使用 scoped_session 与不使用 scoped_session 的情况

当不使用 scoped_session 时，也分两种情况，是否创建全局性 Session

结论：

每个线程下的 Session 都是不同的 Session 数据库成功新增了线程3提交的数据，其他的线程中的数据并没有提交到数据库中去。

结论：

全部线程下的 Session 都时同一个 Session 每个线程下的数据都被提交到了数据库

结论：

每个线程下的 Session 都不相同 只有线程3下的数据被提交到了数据库

结论：

每个线程下的 Session 是同一个 Session 每个线程下的数据都没提交到了数据库

以上说明：

在同一个线程中，有 scoped_session 的时候，返回的是同一个 Session 对象。 在多线程下，即使通过 scoped_session 创建Session，每个线程下的 Session 都是不一样的，每个线程都有一个属于自己的 Session 对象，这个对象只在本线程下共享。 scoped_session 只有在单线程下才能发挥其作用。在多线程下显得没有什么作用。

一、开始使用：

from sqlalchemy import create_engine

from sqlalchemy.orm import sessionmaker

DB_CONNECT_STRING = 'mysql+mysqldb://root:123@localhost/ooxx?charset=utf8'

engine = create_engine(DB_CONNECT_STRING, echo=True)

DB_Session = sessionmaker(bind=engine)

session = DB_Session()

这里的 DB_CONNECT_STRING 就是连接数据库的路径。“mysql+mysqldb”指定了使用 MySQL-Python 来连接，“root”和“123”分别是用户名和密码，“localhost”是数据库的域名，“ooxx”是使用的数据库名（可省略），“charset”指定了连接时使用的字符集（可省略）。

create_engine() 会返回一个数据库引擎，echo 参数为 True 时，会显示每条执行的 SQL 语句，生产环境下可关闭。

sessionmaker() 会生成一个数据库会话类。这个类的实例可以当成一个数据库连接，它同时还记录了一些查询的数据，并决定什么时候执行 SQL 语句。由于 SQLAlchemy 自己维护了一个数据库连接池（默认 5 个连接），因此初始化一个会话的开销并不大。对 Tornado 而言，可以在 BaseHandler 的 initialize() 里初始化：

class BaseHandler(tornado.web.RequestHandler):

def initialize(self):

self.session = models.DB_Session()

def on_finish(self):

self.session.close()

对其他 Web 服务器来说，可以使用 sqlalchemy.orm.scoped_session，它能保证每个线程获得的 session 对象都是唯一的。不过 Tornado 本身就是单线程的，如果使用了异步方式，就可能会出现问题，因此并没使用它。

拿到 session 后，就可以执行 SQL 了：

session.execute('create database abc')

print session.execute('show databases').fetchall()

session.execute('use abc')

# 建 user 表的过程略

print session.execute('select * from user where id = 1').first()

print session.execute('select * from user where id = :id', {'id': 1}).first()

不过这和直接使用 MySQL-Python 没啥区别；ORM 的方式，这也是采用 SQLAlchemy 的唯一原因。

于是来定义一个表：

from sqlalchemy import Column

from sqlalchemy.types import CHAR, Integer, String

from sqlalchemy.ext.declarative import declarative_base

BaseModel = declarative_base()

def init_db():

BaseModel.metadata.create_all(engine)

def drop_db():

BaseModel.metadata.drop_all(engine)

class User(BaseModel):

__tablename__ = 'user'

id = Column(Integer, primary_key=True)

name = Column(CHAR(30)) # or Column(String(30))

init_db()

declarative_base() 创建了一个 BaseModel 类，这个类的子类可以自动与一个表关联。

以 User 类为例，它的 __tablename__ 属性就是数据库中该表的名称，它有 id 和 name 这两个字段，分别为整型和 30 个定长字符。Column 还有一些其他的参数，我就不解释了。

最后，BaseModel.metadata.create_all(engine) 会找到 BaseModel 的所有子类，并在数据库中建立这些表；drop_all() 则是删除这些表。

接着就开始使用这个表吧：

from sqlalchemy import func, or_, not_

user = User(name='a')

session.add(user)

user = User(name='b')

session.add(user)

user = User(name='a')

session.add(user)

user = User()

session.add(user)

session.commit()

query = session.query(User)

print query # 显示SQL 语句

print query.statement # 同上

for user in query: # 遍历时查询

print user.name

print query.all() # 返回的是一个类似列表的对象

print query.first().name # 记录不存在时，first() 会返回 None

# print query.one().name # 不存在，或有多行记录时会抛出异常

print query.filter(User.id == 2).first().name

print query.get(2).name # 以主键获取，等效于上句

print query.filter('id = 2').first().name # 支持字符串

query2 = session.query(User.name)

print query2.all() # 每行是个元组

print query2.limit(1).all() # 最多返回 1 条记录

print query2.offset(1).all() # 从第 2 条记录开始返回

print query2.order_by(User.name).all()

print query2.order_by('name').all()

print query2.order_by(User.name.desc()).all()

print query2.order_by('name desc').all()

print session.query(User.id).order_by(User.name.desc(), User.id).all()

print query2.filter(User.id == 1).scalar() # 如果有记录，返回第一条记录的第一个元素

print session.query('id').select_from(User).filter('id = 1').scalar()

print query2.filter(User.id >1, User.name != 'a').scalar() # and

query3 = query2.filter(User.id >1) # 多次拼接的 filter 也是 and

query3 = query3.filter(User.name != 'a')

print query3.scalar()

print query2.filter(or_(User.id == 1, User.id == 2)).all() # or

print query2.filter(User.id.in_((1, 2))).all() # in

query4 = session.query(User.id)

print query4.filter(User.name == None).scalar()

print query4.filter('name is null').scalar()

print query4.filter(not_(User.name == None)).all() # not

print query4.filter(User.name != None).all()

print query4.count()

print session.query(func.count('*')).select_from(User).scalar()

print session.query(func.count('1')).select_from(User).scalar()

print session.query(func.count(User.id)).scalar()

print session.query(func.count('*')).filter(User.id >0).scalar() # filter() 中包含 User，因此不需要指定表

print session.query(func.count('*')).filter(User.name == 'a').limit(1).scalar() == 1 # 可以用 limit() 限制 count() 的返回数

print session.query(func.sum(User.id)).scalar()

print session.query(func.now()).scalar() # func 后可以跟任意函数名，只要该数据库支持

print session.query(func.current_timestamp()).scalar()

print session.query(func.md5(User.name)).filter(User.id == 1).scalar()

query.filter(User.id == 1).update({User.name: 'c'})

user = query.get(1)

print user.name

user.name = 'd'

session.flush() # 写数据库，但并不提交

print query.get(1).name

session.delete(user)

session.flush()

print query.get(1)

session.rollback()

print query.get(1).name

query.filter(User.id == 1).delete()

session.commit()

print query.get(1)

二、进阶的知识。

1）如何批量插入大批数据？

可以使用非 ORM 的方式：

session.execute(

User.__table__.insert(),

[{'name': `randint(1, 100)`,'age': randint(1, 100)} for i in xrange(10000)]

)

session.commit()

如何批量插入大批数据？

可以使用非 ORM 的方式：

session.execute(

User.__table__.insert(),

[{'name': `randint(1, 100)`,'age': randint(1, 100)} for i in xrange(10000)]

)

session.commit()

上面批量插入了 10000 条记录，半秒内就执行完了；而 ORM 方式会花掉很长时间。

2）如何让执行的 SQL 语句增加前缀？

使用 query 对象的 prefix_with() 方法：

session.query(User.name).prefix_with('HIGH_PRIORITY').all()

session.execute(User.__table__.insert().prefix_with('IGNORE'), {'id': 1, 'name': '1'})

3）如何替换一个已有主键的记录？

使用 session.merge() 方法替代 session.add()，其实就是 SELECT + UPDATE：

user = User(id=1, name='ooxx')

session.merge(user)

session.commit()

或者使用 MySQL 的 INSERT … ON DUPLICATE KEY UPDATE，需要用到 @compiles 装饰器，有点难懂，自己看吧：《SQLAlchemy ON DUPLICATE KEY UPDATE》 和 sqlalchemy_mysql_ext。

4）如何使用无符号整数？

可以使用 MySQL 的方言：

from sqlalchemy.dialects.mysql import INTEGER

id = Column(INTEGER(unsigned=True), primary_key=True)

5）模型的属性名需要和表的字段名不一样怎么办？

开发时遇到过一个奇怪的需求，有个其他系统的表里包含了一个“from”字段，这在 Python 里是关键字，于是只能这样处理了：

from_ = Column('from', CHAR(10))

6）如何获取字段的长度？

Column 会生成一个很复杂的对象，想获取长度比较麻烦，这里以 User.name 为例：

User.name.property.columns[0].type.length

7）如何指定使用 InnoDB，以及使用 UTF-8 编码？

最简单的方式就是修改数据库的默认配置。如果非要在代码里指定的话，可以这样：

class User(BaseModel):

__table_args__ = {

'mysql_engine': 'InnoDB',

'mysql_charset': 'utf8'

}

MySQL 5.5 开始支持存储 4 字节的 UTF-8 编码的字符了，iOS 里自带的 emoji（如