Dar1in9's Blog HomeTagsCategoriesArchivesAbout
python 使用SQLAlchemy操纵数据库
|python
字数总计: 2.7k|阅读时长: 10 分钟

SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

ORM全称是：Object Relational Mapping(对象关系映射)，其主要作用是在编程中，把面向对象的概念跟数据库中表的概念对应起来。举例来说就是，定义一个对象，那就对应着一张表，这个对象的实例，就对应着表中的一条记录。

类 –> 表
类的属性 –> 表中的字段
类的实例 –> 表中一条记录

ORM 有下面这些优点。

  • 数据模型都在一个地方定义，更容易更新和维护，也利于重用代码。
  • ORM 有现成的工具，很多功能都可以自动完成，比如数据消毒、预处理、事务等等。
  • 它迫使你使用 MVC 架构，ORM 就是天然的 Model，最终使代码更清晰。
  • 基于 ORM 的业务代码比较简单，代码量少，语义性好，容易理解。
  • 你不必编写性能不佳的 SQL。

引擎和声明基类

使用SQLAlchemy连接数据库需要引擎，创建引擎使用create_engine方法：

1
2
3
4
5
from sqlalchemy import create_engine

# 参数字符串说明：数据库类型+驱动://用户名:密码@主机:端口号/数据库名字?charset=编码格式
# mysql 自带驱动, 端口号可省略
engine = create_engine('mysql+mysqlconnector://root:root@localhost:3306/study?charset=utf8')

创建映射类需要继承声明基类，使用declarative_base:

1
2
3
4
from sqlalchemy.ext.declarative import declarative_base

# 创建声明基类时传入引擎
Base = declarative_base(engine)

创建映射类

创建映射类须继承声明的基类。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# Column 定义字段，Integer、String 分别为整数和字符串数据类型
from sqlalchemy import Column, Integer, String


class User(Base):           # 继承声明基类
    __tablename__ = 'user'  # 设置数据表名字，不可省略
    id = Column(Integer, primary_key=True)   # 设置该字段为主键
    # unique 设置唯一约束，nullable 设置非空约束
    name = Column(String(64), unique=True, nullable=False)
    email = Column(String(64), unique=True)

    # 此特殊方法定义实例的打印样式
    def __repr__(self):
        return '<User: {}>'.format(self.name)

一对多关系

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref


class Course(Base):
    __tablename__ = 'course'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    # ForeignKey 设置外键关联，第一个参数为字符串，user 为数据表名，id 为字段名
    # 第二个参数 ondelete 设置删除 User 实例后对关联的 Course 实例的处理规则
    # 'CASCADE' 表示级联删除，删除用户实例后，对应的课程实例也会被连带删除
    user_id = Column(Integer, ForeignKey('user.id', ondelete='CASCADE'))
    # relationship 设置查询接口，以便后期进行数据库查询操作
    # 第一个参数为位置参数，参数值为外键关联的映射类名，数据类型为字符串
    # 第二个参数 backref 设置反向查询接口
    # backref 的第一个参数 'course' 为查询属性，User 实例使用该属性可以获得相关课程实例的列表
    # backref 的第二个参数 cascade 如此设置即可实现 Python 语句删除用户数据时级联删除课程数据
    user = relationship('User', 
            backref=backref('course', cascade='all, delete-orphan'))

    def __repr__(self):
        return '<Course: {}>'.format(self.name)

定义列时常用的参数：

参数 说明
primary_key 如果设为True，这列就是表的主键
unique 默认值为False，如果为True，表示这列不允许重复
index 如果为True，为这列创建索引
nullable 默认为True，这列允许使用空值
defaule 为这列设置默认值

创建数据表

声明基类 Base 在创建之后并不会主动连接数据库，因为它的默认设置为惰性模式。Base 的 metadata 有个 create_all 方法，执行此方法会主动连接数据库并创建全部数据表，完成之后自动断开与数据库的连接：
Base.metadata.create_all()

Session处理数据

使用映射类创建数据表要用声明基类Base，而处理数据要用到session。它是sessionmaker类的实例，该实例实现了__call__方法，本身可以作为函数来执行，返回值就是能够处理数据的session。

1
2
3
from sqlalchemy.orm import sessionmaker

session = sessionmaker(engine)()

当我们创建了 session 实例，就启动了一个操作 MySQL 数据库的会话。

session对象常用的操作方法：

方法 描述
add(self, instance, __warn=True) 向数据库保存一个实体对象
add_all(self, instance) 向数据库保存一组实体
merge(self, instance, load=True) 合并数据实体（更新）
query(self, *entities, **kwargs) 数据查询
delete(self, instance) 删除指定的数据实体
commit(self) 事物提交
rollback(self) 事物回滚

数据查询

基本查询

session 的 query 方法接收类作为参数，.all 表示获取全部实例
不论查询结果是一个还是多个，返回值都是列表，查不到的话返回值是空列表。等同于 MySQL 语句：SELECT * 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 获取全部实例
session.query(User).all() 

# 查询第一条数据
session.query(User).first()
session.query(User).one()   # 有且只有一个元素时才正确返回，否则报错
session.query(User).one_or_none()  # 当没有数据时，会返回 None 而不是异常。

# filter方法过滤查询
# filter方法支持>、>=、<、<=、==、!= 等比较符号
session.query(User).filter(User.name=="user1").first()

# filter_by 方法过滤查询
session.Query(User).filter_by(name=="user1").first()

# filter_by方法支持多条件查询
session.query(User).filter_by(name="user1", id=1).all()

# 查询User表中全部的name值
session.query(User.name).all()
# 这个语句的查询结果列表中的每个元素的数据类型都是元组。

like方法进行模糊查询，%匹配任意数量的任意字符，_匹配单个任意字符。

1
2
3
4
5
# 查询邮箱为谷歌邮箱的 User 实例
session.query(User).filter(User.email.like('%gmail%')).all()

# 查询邮箱第二个字符为 i 的 User 实例
session.query(User).filter(User.email.like('_i%')).all()

in_方法查询”某个字符安的值属于某个列表中”的数据

1
2
# 如果列表中的字段在 name 列中查不到，并不会报错
session.query(User).filter(User.name.in_(['王雷', '夏晶', 'xxx'])).all()

and_方法进行多条件查询，需要引入，等同于filter_by方法

1
2
3
from sqlalichemy import and_

session.query(User).filter(and_(User.name=="user1",User.id=1)).all()

or_ 也是多条件查询，符合任一条件即可，也是需要引入。

1
2
3
from sqlalchemy import or_

session.query(User).filter(or_(User.email=='na55@zhouwan.cn',User.id==1)).all()

高级查询

排序

使用order_by方法根据某一字段进行排序

1
2
3
4
session.query(User).order_by(User.name).all()

# desc 方法进行降序排序， 默认为升序
session.query(User).order_by(User.email.desc()).all()

设置查询数量（分页查询）

使用limit方法限制查询数量

1
2
3
4
session.query(User).limit(4).all()

# 因为查询结果为有序可迭代对象，所以也可以使用列表的切片
session.query(User).order_by(User.id.desc()).all()[:4]

count方法统计查询数量

1
session.query(User).count()

多表联结查询

使用join方法进行联结查询

1
2
# 查询user1的全部课程，即查询course表中的数据
session.query(Course).join(User).filter(User.name=="user1").all()

添加数据

1
2
3
4
user = Users(username="user3", email="user3@qq.com")  # 实例化数据映射类作为表的一行数据
session.add(user)       # 在session中添加一个添加数据的动作
session.commit()        # 提交session中的动作
session.close()         # 关闭session

更新数据

1
2
3
4
user = Users(id=4, username="user4", email="user4@qq.com")  # 实例化数据映射类作为表的一行数据
session.merge(user)       # 添加一个更新动作
session.commit()          # 提交
session.close()           # 关闭

删除数据

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

执行原生sql

connection方式

使用传统的connection的方式连接和操作数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
from sqlalchemy import create_engine


engine = create_engine("mysql+mysqlconnector://root:root@localhost:3306/study?charset=utf8mb4")

# 创建一个connection，这里的使用方式与python自带的mysql的使用方式类似
with engine.connect() as con:
    # 执行sql语句，如果是增删改，则直接生效，不需要commit

    # 查询数据
    res = con.execute('SELECT 1')
    # 获取第一条查询结果，结果为一个元组，可以通过data.id等方式获取数据
    data = res.fetchone()    # ==> (1,)
    # 获取所有查询结果，结果为一个列表，列表中元素为元组，可以通过data.id等方式获取数据
    data = res.fetchall()    # ==> [(1,)]

    # 插入数据，如果插入错误，则会报错
    try:
        con.execute("insert into user values (5, 'user5', 'password5')")
    except:
        print("插入错误")
    
    # 修改数据
    try:
        con.execute("update user set name='user_update' where id =5")
    except:
        print("插入错误")

    # 删除数据
    try:
        con.execute("delete from user where id =5")
    except:
        print("删除错误")

在connection方式下，传递sql语句参数的参数形式与传统方式相同，使用?占位，元组形式传参，但前提是下层的 DBAPI 支持。更好的方式是使用 text()。

1
con.execute("insert into user (name,password) values (?,?)",('admin','password'))

使用text构建SQL
相比直接使用 string，text() 的优势在于它：提供了统一的参数绑定语法，与具体的 DBAPI 无关。

  1. 参数绑定语法

    1
    2
    3
    4
    from sqlalchemy import text

     # 使用dict 传参数
    result = con.execute(text("select * from table where id = :id"), {"id":1})

  2. 参数类型指定

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    from sqlalchemy import text
    from sqlalchemy import DateTime

    date_param = datetime.today()+timedelta(days=-1*10)
    sql="delete from caw_job_alarm_log  where alarm_time < :alarm_time_param"

    # bindparams 是 bindparam 的列表，bindparam 则提供参数的一些额外信息（类型、值、限制等）
    t=text(sql, bindparams=[bindparam('alarm_time_param', type_=DateTime, required=True)])

    connection.execute(t, {"alarm_time_param": date_param})

connection事务方式

使用事务可以进行批量提交和回滚

1
2
3
4
5
6
7
8
9
with engine.connect() as con:
    trans = con.begin()
    try:
        r1 = con.execute("select * from User")
        r2 = con.execute("insert into User(name, age) values(?, ?)", 'bomo', 24)
        trans.commit()
    except:
        trans.rollback()
        raise

session方式

connection是一般使用数据库的方式，sqlalchemy还提供了另一种操作数据库的方式，通过session对象，session可以记录和跟踪数据的改变，在适当的时候提交，并且支持强大的ORM的功能

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

engine = create_engine("mysql+mysqlconnector://root:root@localhost:3306/study?charset=utf8mb4")
session = sessionmaker(engine)()
# 通过session.execute方法执行
res = session.execute('select * from User')
data = res.fetchone()
data = res.fetchall()


session.execute("insert into User(name, age) values('bomo', 13)")
session.execute("insert into User(name, age) values(:name, :age)", {'name': 'bomo', 'age':12})

# 如果是增删改，需要commit
session.commit()

注意：参数使用dict，并在sql语句中使用:key占位

文章作者: Dar1in9
文章链接: http://dar1in9s.github.io/2020/03/08/python/python SQLAlchemy 使用/
版权声明: 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 Dar1in9's Blog！