SQLAlchemy

SQLAlchemy

---

一、 什么是SQLAlchemy？

SQLAlchemy是Python一款非常强大的ORM软件。

对象关系映射（Object Relational Mapping，简称ORM）是通过使用描述对象和数据库之间映射的元数据，将面向对象语言程序中的对象自动持久化到关系数据库中。

---

二、 怎么用？

1. 连接准备

• 建立连接

from sqlalchemy import create_engine
engine_name = create_engine('mysql://root:@localhost:3306/webpy?                                charset=utf8',echo=True)

我们都知道，与数据库进行交互，首先就需要建立连接。create_engine函数返回一个engine对象，第一个参数标明了与哪个数据库建立连接，格式为数据库类型://用户名:密码（没有密码则为空，不填）@数据库主机地址/数据库名?编码，第二个参数echo默认为False，置为True后控制台会输出一些sql信息。

• 创建会话

from sqlalchemy.orm import sessionmaker
session_name = sessionmaker(bind=engine_name)

session = session_name() # 实例化session

ORM通过session与数据库进行会话。一般而言，当第一次载入时（也就是建立连接的同时）会创建一个session类，这个session类为新的session对象提供服务。如果需要和数据库建立连接，只需实例化一个session。

虽然我们已经与数据库engine相关联，但是没有打开任何连接，当它第一次被使用时，就会从engine的一个连接池中检索是否存在连接，如果存在，便会保持连接直到我们提交所有更改或者关闭session对象。

2. 增删改查

建表

from sqlalchemy import Column,Integer,String

class User(Base):
__tablename__:'user'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String，nullable=False)

首先，我们要为数据库建表，ORM的好处就是通过描述对象与数据库的某张表直接映射。上述代码中，我们在项目里定义了User类，与数据库中的user表相对应，表中有整数类型、自增的主键id，有 不允许为空值、字符串类型的name列。

增

def add(cls, name):
    user = cls(name)     # 创建新的User对象
    session.add(user)    # 将对象加入数据库中
    session.commit()     # 提交更改

我们在User类中定义函数add()实现向数据库增加数据的功能。其步骤为：根据参数构造对象、将对象加入数据库、提交修改。

删

def delete_by_id(cls, id):
    user = session.query(cls).filter(cls.id==id)   # 查询
    if user:
        session.delete(user)  # 删除数据  
        session.commit()      # 提交更改

我们在User类中定义函数delete_by_id()实现根据用户id从数据库删除数据的功能。
忘掉这个delete（手动微笑）。

改

def update(cls, id, update_values):
    row_count = session.query(cls).filter(id==id).
                update(values=update_values)
    session.commit()
    return row_count > 0

我们在User类中定义函数update()实现根据用户id从数据库修改数据的功能。

# 官方文档示例
from sqlalchemy import update

stmt = users.update().where(users.d==5).values(name='user #5')

# 函数原型
sqlalchemy.sql.expression.update(table, whereclause=None, values=
                                None, inline=False, bind=None, 
                                prefixes=None, returning=None, 
                                return_defaults=False, 
                                preserve_parameter_order=False, 
                                **dialect_kw)

查

查找是重中之重，设计数据库的大多数操作都与查询有关，即使是删除和更新也免不了查询操作。

• 查询函数

func_name	param
query(*entities, **kwargs)	query(classname) or query(classname.column1,classname.column2) or query(func.max())

query方法会创建一个查询对象，根据参数获取相应的列。query()返回KeyedTuple类型的一个元组或多个元组组成的列表，元组格式为：<User(name='ed',fullname='Ed Jones', password='f8s7ccs')>ed，实际上为User('ed','EdJones','f8s7ccs')，而name,fullname,password为每个数据对应的标签。可以把它当作一个python类来操作。

print(User.name)
>>>'ed'
print(User.fullname)
>>>'Ed Jones'

• 过滤器函数

func_name	param	describe
filter(*criterion)	filter(classname.column==’xxxxxx’)	过滤器，使用关键字变量过滤查询结果
filter_by(**kwargs)	filter_by(column==’xxxxxx’)	过滤器，使用关键字变量过滤查询结果

filter与filter_by都是帮助过滤查询结果的函数，但它们有一些不同。

filter用classname.column，支持运算符、and、or作为参数。

filter_by不必加上classname，不支持运算符、and、or做参数，因为参数为**kwargs支持组合查询。

filter().filter()
filter(and_(User.name =='ed',User.fullname =='Ed Jones'))
filter(or_(User.name =='ed', User.name =='wendy'))
filter(MyClass.name == 'some name', MyClass.id > 5)
filter(classname.column.like('%ed%'))
filter(classname.column.in_([,,]))

filter_by(column1=='xxx', column2=='xxx)

• 返回结果处理函数

func_name	param	describe
all()		返回keyedTuple元组的列表
first()		返回查询结果的第一个
one()		获取所有行，若查询不到or查询到多个对象or查询到一个对象但重复记录，抛出异常
scalar()		在one()的基础上获得该行的第一列
order_by()	order_by(classname.column)	依据某列或某几列对查询结果按字典序排序
label()		为某一列起别名

# 返回一个列表
session.query(User).filter_by(name=='fancy').all()   
# 返回查询到的第一个对象
session.query(User).filter_by(name=='fancy').first() 
# 只获得一条数据，如果没有获得 or 获得多条 则抛出异常
session.query(User).filter_by(name=='fancy').one() 
# 基于one()的基础，获得第一列的数据
session.query(User).filter_by(name=='fancy').scalar() 

• 连表查询

  连表查询有两种方式，一种是通过filter，一种是通过join。

# 通过 query 与 filter 进行连表查询
session.query(User, Address).filter(User.id==Address.user_id).\
        filter(Address.city=='Peking')

# 通过join达到同样效果, 只有一个外键
session.query(User).join(Address).filter(Address.city=='Peking')

# 多个外键或者没有外键时，要给出明确条件
session.query(User).join(Address，User.id==Address.user_id).\
        filter(Address.city=='Peking')
session.query(User).join(Address,User.addresses).\
        filter(Address.city=='Peking')