SQLAlchemy介绍和基本使用
数据库时一个网站的基础，在 flask 中可以自由的使用MySQL、PostgreSQL、 SQLlite、Redis、MongoDB来写原生的语句实现功能，也可以使用更高级别的数据库抽象方式，如SQLAlchemy 或 MongoEngine这样的 OR(D)M.
开始之前，需要准备的软件有:
mysql-
MySQLdb（python2）或pymysql(python3),通过pip可以安装 -
SQLAlchemy: SQLAlchemy 是一个数据库的ORM框架，安装命令为：
pip install SQLAlchemy
通过SQLAlchemy来连接数据库，示例如下：
from sqlalchemy import create_engine
# 数据库连接变量
HOSTNAME = '127.0.0.1'
PORT = 3306
DATABASE = 'sqlalchemy_demo'
USERNAME = 'root'
PASSWORD = 'chenkuan1110'
DB_URI = 'mysql+mysqldb://{}:{}@{}:{}/{}'.format(USERNAME,PASSWORD,HOSTNAME,PORT,DATABASE)
# 创建数据库引擎
engine = create_engine(DB_URI)
# 创建连接
with engine.connect() as conn:
ret = conn.execute('SELECT 1')
print(ret.fetchone())
首先从sqlalchemy中导入create_engine,这个函数用来创建引擎
然后通过 engine.connect()来连接数据库。
其中 create_engine()需要传递一个字符串，这个字符串是对连接什么数据库的描述，需要符合下面这个规则：
dialect+driver://username:password@host:port/database
-
dialect: 数据库的实现，比如
MySQL、PostgreSQL、SQLlite，并且转换为小写。 -
driver: python 和数据库之前对应的驱动，如果不指定会选择默认的驱动。'MySQL'的默认驱动是
MySQLdb -
username: 数据库连接的用户名
-
password: 数据库连接的密码
-
host: 数据库的域名
-
port：数据库服务器的监听端口，默认为 3306，默认可省略该端口号
-
database: 连接数据库的名称
如果以上输出了 (1,)，那么数据库就是连接成功的
通过SQLAlchemy执行原生SQL
在上面的例子中，通过conn.execute(原生SQL语句)就可以执行原生SQL的操作
ORM的介绍
ORM: Object Relationship Mapping 对象关系映射
对象模型与数据库表的映射，通过ORM，我们可以通过类的方式去操作数据库，而不用再写原生的SQL。
表 <--> 类
记录 <--> 对象示例
字段 <--> 属性
使用ORM的优点：
- 易用性 直观
- 性能损耗小
- 设计灵活 可以轻松写出复杂的查询
- 可移植性 封装了底层的数据库实现，可以轻松切换数据库
定义 ORM模型，并映射到数据库中
-
用
declarative_base根据engine创建一个ORM基类。 -
用
Base这个基类来创建自己的ORM类，在定义自己的模型时，要定义类属性__tablename__,指定这个模型映射到数据库中的表名。 -
创建属性来映射到表中的字段，所有映射到表的属性都是
Cloum的示例对象 -
使用
Base.metadata.create_all()来将模型映射到表。
示例如下：
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
# 数据库连接变量
HOSTNAME = '127.0.0.1'
PORT = 3306
DATABASE = 'sqlalchemy_demo'
USERNAME = 'root'
PASSWORD = 'xxx'
DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
# 创建数据库引擎
engine = create_engine(DB_URI)
# 需要映射的表结构
# create table student(
# id int primary key autoincrement,
# name varchar(50),
# age int
# )
Base = declarative_base(engine) # 利用 declarative_base() 创建一个基类
# 1.类
class Student(Base):
__tablename__ = 'person' # 表名
# 字段
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(50))
age = Column(Integer)
# 3.映射
Base.metadata.create_all()
注意： 一旦将模型通过Base.metadata.create_all()映射到数据库中后，即使表结构发生了变化，数据库中表也不会改变
SQLAlchemy常用数据类型
-
Integer：整形 -
Float：浮点型 -
Boolean：传递 True/False 进去 -
DECIMAL：定点类型 (总位数，小数点后的位数) -
Enum:枚举类型 -
Date: 传递datetime.date()进去 -
DateTime: 传递 datetime.datetime() 进去 -
Time：传递 datetime.time() 进去 -
String: 字符类型，使用时需要指定长度，区别于 Text 类型 -
Text: 文本类型 -
LONGTEXT： 长文本类型 ,需要导入from sqlalchemy.dialects.mysql import LONGTEXT
Column常用参数
-
default：默认值 在发表时间这种字段中很有用 -
nullable: 是否可空 默认可以为空 -
primary_key:是否为主键 -
unique：是否唯一 -
autoincrement: 是否自动增长 -
onupdate: 更新时执行的函数 -
name: 该属性在数据库中的字段映射
query可用参数：
- 模型对象。指定查找这个模型的所有对象。
- 模型中的属性。 可以指定只查询某个模型中的几个属性。
- 聚合函数。
from sqlalchemy import func # 导入聚合函数-
func.count:统计行的数量 -
func.avg: 平均值 -
func.max: 最大值 -
func.min: 最小值 -
func.sum: 求和
func上，其实没有任何聚合函数，但是因为底层做了一些魔术，只要mysql中有的聚合函数，都可以通过func调用
-
过滤条件：
过滤时数据提取的重要功能之一，下面有一些常见的过滤条件，这些过滤条件都只能通过filter方法实现
-
equals:query.fileter(User.name == 'ed') -
not equals:query.fileter(User.name != 'ed') -
like:query.filter(User.name.like('%ed%'))ilike: 不区分大小写 -
in:query.filter(User.name.in_(['en', 'wendy', 'jack'])) # 同时， in 也可以用做一个Query query.filter(User.name.in_(query.filter(User.name.like('%ed%')))) -
not in:query.filter(User.name.in_(['en','wendy','jack'])) -
is null:query.filter(User.name == None) # 或者是 query.fileter(User.name.is_(None)) -
is not nullquery.filter(User.name != None) # 或者是 query.fileter(User.name.isnot(None)) -
and:from sqlalchemy import and_ query.filter(and_(User.name == 'ed',User.fullname == 'Ed Jones')) # 或者是传递多个参数 query.fileter(User.name == 'ed',User.fullname='Ed Jones') # 或者通过多次 filter 操作 query.filter(User.name == 'ed').filter(User.fillname == 'Ed Jones') -
orform sqlalchemy import or_ query.filter(or_(User.name == 'ed',User.fillname == 'wendy'))
外键及其约束
外键
SQLAlchemy 通过 ForeignKey类来实现，并且可以指定表的外键约束。示例代码如下：
class author(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True, autoincrement=True)
username = Column(String(50), nullable=False)
class Article(Base):
__tablename__ = 'article'
id = Column(Integer, primary_key=True, autoincrement=True)
title = Column(String(30), nullable=False)
content = Column(Text, nullable=False)
# 外键
uid = Column(Integer, ForeignKey('user.id'))
外键约束有以下,通过 ForeignKey 的ondelete参数设置：
-
RESTRIC: 父表数据被删除，会阻止删除 （默认） -
NO ACTION: 在 MySQL中 同RESTRIC -
CASCADE: 级联删除 -
SET NULL: 父表数据被删除，字表数据会设置为NULL。
ORM层外键 外键关系 一对多
mysql级别的外键，还不够ORM,必须拿到一个表的外键，然后再通过这个外键去另一张表中查询所需数据，这样太麻烦了。SQLAlchemy 提供了 一个relationship，这个类可以定义属性，以后在访问相关联的表的时候就可以通过 对象.属性来访问。
示例如下：
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True, autoincrement=True)
username = Column(String(50), nullable=False)
# articles = relationship('Article')
def __repr__(self):
return '<User> username:{}'.format(self.username)
class Article(Base):
__tablename__ = 'article'
id = Column(Integer, primary_key=True, autoincrement=True)
title = Column(String(30), nullable=False)
content = Column(Text, nullable=False)
# 外键
uid = Column(Integer, ForeignKey('user.id'))
# 外键引用
author = relationship('User', backref='articles') # backref 表示在user表中反向引用这个，可以通过 User.articles获取
def __repr__(self):
return '<Article> id:{}, title:{},content:{}'.format(self.id, self.title, self.content)
另外可以通过backref来指定 反向访问的属性名。
一对一
在sqlalchemy中，如果需要将两个模型映射为 一对一 的关系，那么就应该在父模型中，指定引用的时候，传递一个uselist=False这个参数进去，就是告诉父模型，以后引用这个模型的时候，不再是一个列表，而是一个属性了，示例代码如下：
# 一
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True, autoincrement=True)
username = Column(String(50), nullable=False)
# 一对一
extend = relationship('UserExtend', uselist=False)
def __repr__(self):
return '<User> username:{}'.format(self.username)
# 一
class UserExtend(Base):
__tablename__ = 'extend'
id = Column(Integer, primary_key=True, autoincrement=True)
school = Column(String(50))
uid = Column(Integer, ForeignKey='user.id')
# 一对一
user = relationship('User', backref='extend')
当然，可以借助 sqlalchemy.orm.backref来简化代码：
# 一
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True, autoincrement=True)
username = Column(String(50), nullable=False)
# 一
class UserExtend(Base):
__tablename__ = 'extend'
id = Column(Integer, primary_key=True, autoincrement=True)
school = Column(String(50))
uid = Column(Integer, ForeignKey='user.id')
# 一对一
user = relationship('User', backref=backref('extend',uselist=False))
此处的backref为函数，参数与在 User 中使用的 relationship的参数相同。
多对多
多对多的关系需要通过一张中间表来绑定他们之间的关系，步骤如下：
-
定义两个多对多关系的模型
-
用
sqlalchemy.Table创建一个中间表对象，中间表一般包含两个模型的外键字段,并且让它们两个作为一个"符合主键" -
在两个需要做多对多模型中随便选择一个模型，定义一个 relationship属性，来绑定三者之间的关系，在使用 relationship 时需要传入一个
secondary=中间表对象。
示例如下：
# 文章(多)
class Article(Base):
__tablename__ = 'article'
id = Column(Integer, primary_key=True, autoincrement=True)
title = Column(String(50), nullable=False)
# 中间表
article_tag = Table('article_tag', Base.metadata,
Column('article_id', Integer, ForeignKey('article.id'), primary_key=True),
Column('tag_id', Integer, ForeignKey('tag.id'), primary_key=True)
)
# 标签(多)
class Tag(Base):
__tablename__ = 'tag'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(50), nullable=False)
# 关联三张表
articles = relationship('Article', backref='tags', secondary=article_tag)
ORM层面删除数据的注意事项
orm层面删除数据，会无视mysql级别的外键约束，直接回将对应的数据删除，然后将从表中的那个外键设置为 NULL。
如果想要避免这种行为，应该将从表的外键设置 nullable=True
relationship方法中的 cascade参数
在 SQLAlchemy 中，只要将一个数据添加到 session 中，和它关联的数据都可以存入数据库中。原因在于，通过 relationship 的时候，又一个关键字参数 cascade 可以设置这些属性：
-
save-update: 默认选项。在添加一个数据的时候与之相关的数据都会被添加 -
delete: 当删除一个数据的时候，是否也删掉使用 relationship 与之相关联的数据。 -
delete-orphan: 当一个ORM对象删除了父表中的关联对象的时候，自己便要会被删除掉。当然如果父表中的数据被删除，自己也会被删除。这个选项只能用在一对多上，不能用在一对一或多对多上。并且还需要在子模型的relationship中，增加一个single_parent=True。 -
merge:默认选项。当在使用session.merge，合并一个对象时，会将使用了 relationship 关联的对象也进行 merge 操作。 -
expunge: 移除操作的时候，会将相关了的对象也进行移除。这个操作只是从 session 中移除，并不会真正从数据表中删除。 -
all: 是对save-update,merge,refresh-expire,expunge,delete几种的缩写。
排序
现在定义有如下 orm 模型，希望能够按照 create_time 来进行排序：
class Article(Base):
__tablename__ = 'article'
id = Column(Integer, primary_key=True, autoincrement=True)
title = Column(String(50), nullable=False)
create_time = Column(DateTime, nullable=False, default=datetime.now)
def __repr__(self):
return '<Article> title: {} create_time:{}'.format(self.title, self.create_time)
-
order_by: 可以根据表中某一个字段进行排序，如果在前面加了一个-,表示 降序排序。- 正序：按 create_time来排序文章
articles = session.query(Article).order_by('create_time').all()- 倒序：
from sqlalchemy import text articles = session.query(Article).order_by(text('-create_time')).all()
-
在模型定义的时候指定默认排序 : 有两种方式：
-
relationship的order_by参数 ： 在指定 relationship 时，传递order_by参数来指定排序的字段。 -
在模型定义中，添加以下代码：
__mapper_args__ = { 'order_by': create_time # 正向排序 # 'order_by': create_time.desc() # 倒序排序 } -
-
正向排序和反向排序： 默认情况下时从小到大，从前到后排序的，如果想要反项排序，可以调用排序的字段的
desc方法。articles = session.query(Article).order_by(Article.create_time.desc()).all()
limit、offset 和 切片操作
-
limit(): 可以限制查询数据条数。 -
offset(): 限制查询数据过滤掉前面的多少条记录。 -
切片/
slice()： 可以对 Query 对象使用切片操作来获取想要的数据,可以使用slice(start,end)，也可以使用[start:end]
数据库操作懒加载
在 一对多 或者 多对多的时候，如果想要获取多的者一部分的数据的时候，往往能通过一个属性就可以全部获取了。
比如有一个作者表和一个文章表，如果获取一个作者的全部文章，可以通过 user.articles 获取。
但是有时候我们不想获取全部的数据，比如只想获取今天发表的文章，那么我们可以在定义这个orm模型的时候，在 relationship中指定lazy='dynamic', 以后通过 user.articles 获取到的就不是一个列表，而是一个 AppenderQuery对象。这样可以通过 该对象的方法进行进一步的排序过滤等操作来得到想要的结果。
这种对象除了可以进行 Query 对象的操作，还能够添加数据。
lazy 可用的选项：
-
select: 默认选项。 没有访问就不加载，访问才加载,组装返回列表对象InstrumentedList(继承的原始 list)。也是懒加载的方式。 -
dynamic: 懒加载的方式之一。返回的是AppenderQuery对象。 -
immediate: 非懒加载，如果访问就直接取数据返回 -
joined: -
subquery: -
selectin: -
noload: -
raise: -
raise_on_sql:
等...
高级查询
group_by:
根据某个字段来分组。比如要查询根据性别来分组，来统计每个分组有多少人，那么可以通过以下代码来完成：
session.query(User.gender,func.count(User.id)).group_by(User.gender).all()
having
having是对查找结果的进一步过滤。比如只想要看未成年人的数量，那么可以先对年龄进行分组统计人数，然后在对分组进行 having 过滤。示例如下：
ret = session.query(User.age,func.count(User.id)).group_by(User.age).having(User.age < 18).all()
join方法：
join 查询分为 两种 ：inner join 和 outer join。 默认是 inner join。left join 和 right join 为 out join 。
- 在sqlalchemy中,使用
join来完成内连接。在写 join de 时候，如果不写join的条件，那么默认使用内连接。
比如现在要实现一个功能，查找所有的用户，按照发表文章的数量来进行排序。示例代码如下：
ret = session.query(User.name, func.count(Article.id)).join(Article, User.id == Article.uid).group_by(
User.id).order_by(func.count(Article.id).desc()).all()
别名
当多表查询时，有时候一个表要用到多次，这时候就可以用别名的烦事解决命名冲突的问题。
子查询
sqlalchemy也支持子查询,子查询可以让多个查询变为一个查询，只要查找一次数据库，性能相对来讲更加高效。也可以实现一些复杂的查询语句。
在sqlalchemy中,使用子查询的步骤：
-
将子查询按照传统的方式写好查询代码，然后在
query对象后面执行subquery方法，将这个查询变为一个子查询。 -
在子查询中，将以后需要用到的字段通过
label方法，取个别名。 -
在父查询中，如果想要使用子查询的字段，那么通过子查询的返回值的
c属性拿到。
整体的示例代码如下：
stmt = session.query(User.city.label('city'), User.age.label('age')).filter(User.name == '李A').subquery() # 子查询
# 把子查询放到父查询中
ret = session.query(User).filter(User.city == stmt.c.city, User.age == stmt.c.age).all()
print(ret)
网友评论