SQLAlchemy笔记

作者: 宽哥好 | 来源:发表于2019-11-26 11:19 被阅读0次

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 null

query.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')

or

form 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)

SQLAlchemy笔记

SQLAlchemy介绍和基本使用

通过`SQLAlchemy`来连接数据库，示例如下：

通过SQLAlchemy执行原生`SQL`

`ORM`的介绍

使用`ORM`的优点：

定义 `ORM`模型，并映射到数据库中

SQLAlchemy常用数据类型

`Column`常用参数

`query`可用参数：

过滤条件：

外键及其约束

外键

ORM层外键外键关系一对多

一对一

多对多

ORM层面删除数据的注意事项

`relationship`方法中的 `cascade`参数

排序

`limit`、`offset` 和切片操作

数据库操作懒加载

高级查询

`group_by`:

`having`

`join`方法：

别名

子查询

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

SQLAlchemy笔记

SQLAlchemy介绍和基本使用

通过SQLAlchemy来连接数据库，示例如下：

通过SQLAlchemy执行原生SQL

ORM的介绍

使用ORM的优点：

定义 ORM模型，并映射到数据库中

SQLAlchemy常用数据类型

Column常用参数

query可用参数：

过滤条件：

外键及其约束

外键

ORM层外键 外键关系 一对多

一对一

多对多

ORM层面删除数据的注意事项

relationship方法中的 cascade参数

排序

limit、offset 和 切片操作

数据库操作懒加载

高级查询

group_by:

having

join方法：

别名

子查询

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

通过`SQLAlchemy`来连接数据库，示例如下：

通过SQLAlchemy执行原生`SQL`

`ORM`的介绍

使用`ORM`的优点：

定义 `ORM`模型，并映射到数据库中

`Column`常用参数

`query`可用参数：

ORM层外键外键关系一对多

`relationship`方法中的 `cascade`参数

`limit`、`offset` 和切片操作

`group_by`:

`having`

`join`方法：