Python——数据库操作

目录

（1）安装Flask-SQLAlchemy

（2）使用Flask-SQLAlchemy操作数据库

（3）连接数据库

 •创建数据表

•增加数据

•查询数据

 •更新数据

•删除数据

---

Flask-SQLAlchemy是Flask中用于操作关系型数据库的扩展包，该扩展包内部集成了SQLAlchemy，并简化了在Flask程序中使用SQLAlchemy操作数据库的功能。

SQLAlchemy是由Python实现的框架，该框架内部封装了ORM(Object Relational Mapping，对象关系映射)的操作，可以让开发人员在不用编写SQL语句的前提下，通过Python对象操作数据库及其内部的数据。

在Web程序中开发人员若使用原生SQL语句操作数据库，主要会存在以下两个问题：

（1）过多的SQL语句会降低代码的易读性，另外也容易出现诸如SQL注入（一种网络攻击方式，它利用开发人员编写SQL语句时的疏忽，使用SQL语句实现无账号登录甚至篡改数据库）等安全问题。

（2）开发人员开发时通常会使用SQLite数据库，而在部署时会切换到诸如MySQL等更为健壮的数据库，由于不同数据库需要用到不同的Python库，所以切换数据库就需要对代码中使用的Python库进行同步修改，增加了一定的工作量。

Python中引入了ORM技术。ORM的全称为Object Relational Mapping，表示对象关系映射，它是一种解决面向对象与关系数据库存在的互不匹配现象的技术，用于实现面向对象编程语言中模型对象到关系数据库数据的映射。

对于Python语言来说，ORM会将底层的SQL语句操作的数据实体转化成Python对象，我们无需了解 SQL语句的编写规则，通过Python代码即可完成数据库操作。

ORM 主要实现了以下三种映射关系：

数据表→Python类

字段（列）→类属性

记录（行）→ 类实例

（1）安装Flask-SQLAlchemy

pip install flask-sqlalchemy

（2）使用Flask-SQLAlchemy操作数据库

Flask 为Flask-SQLAlchemy扩展包提供了一个配置项SQLALCHEMY_DATABASE_URI，该配置项用于指定数据库的连接，它的值是一个有着特殊格式的URI，URI涵盖了连接数据库所需要的全部信息，包括用户名、密码、主机名、数据库名称以及用于额外配置的可选关键字参数。

URL的典型格式如下：

dialect+driver://username:password@host:port/database

•dialect+driver：表示数据库类型和驱动程序。数据库类型的取值可以为postgresql（PostgreSQL 数据库）、mysql（MySQL数据库）、oracle（Oracle数据库）、sqlite（SQLite数据库）等。如果未指定驱动程序，则说明选择默认的驱动程序，这时可以省略加号。

•username：表示数据库的用户名。

•password：表示数据库的密码。

•host：表示主机地址。

•port：表示端口号。

•database：表示连接的数据库名。

 常见数据库的URL

数据库	URI
PostgreSQL	postgresql://root:123@localhost/flask_data
MySQL	mysql://root:123@localhost/flask_data
Oracle	oracle://root:123@127.0.0.1:1521/flask_data
SQLite（Windows平台）	sqlite:///C:\\absolute\\path\\to\\foo.db
SQLite（Unix/Mac平台）	sqlite:absolute/path/to/foo.db

（3）连接数据库

通过一个示例演示如何在Flask程序中连接SQLite数据库。SQLite基于文件，不需要单独启动数据库服务器，适合在开发时使用，或是在数据库操作简单、访问量低的程序中使用。

•创建一个项目，在该项目中新建app.py文件，并在app.py文件中编写连接SQLite数据库的代码。

db对象是SQLAlchemy类的实例，表示应用使用的数据库，通过它可获得Flask-SQLAlchemy提供的所有功能。

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
# 通过URI连接数据库
app.config['SQLALCHEMY_DATABASE_URI']='sqlite:///'+os.path.join(app.root_path, 'flask_data.db')
db = SQLAlchemy(app)

-  Flask中的模型以Python类的形式进行定义，类中的属性对应于数据库表中的列。所有的模型类都需要继承Flask-SQLAlchemy提供的基类db.Model：

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

 db.Column类封装了字段的相关属性或方法：

__init__(name, type_, autoincrement, default, doc, key, index, info, nullable, onupdate, primary_key, server_default, server_onupdate,quote, unique, system, comment, *args, **kwargs)

•name：表示数据表中此列的名称。若省略，默认使用类属性的名称。

•type_：表示字段的类型。若该参数的值为None或省略，则将使用默认类型NullType，表示未知类型。

•default：表示为字段设置默认值。

•index：表示是否为字段创建索引。

•nullable：确定字段的值是否允许为空，若设置为False，则不允许。

•primary_key：表示是否将字段设置为主键，若设为True，则会将此列标记为主键列。多个列可以设置此标志以指定复合主键。

•unique：表示该字段是否具有唯一约束，若设为True，则该字段将不允许出现重复值。

•*args：其他位置参数，该参数的值可以为Constraint（表级SQL约束）、ForeignKey（外键）、ColumnDefault、Sequence和Computed Identity类实例。

 其中的type_参数用于指定字段的类型：

字段类型	说明
Integer	整数
String(size)	字符串，可通过size设置最大长度
Text	较长的Unicode文本
DateTime	日期和时间，存储Python的 datetime对象
Float	浮点数
Boolean	布尔值
PickleType	任何Python对象， 自动使用Pickle序列化
LargeBinary	存储任意二进制数据

注：若字段的类型为String，则建议为其指定长度，这是因为有些数据库会要求限制字符串的长度。 

建议将SQLALCHEMY_TRACK_MODIFICATIONS键设为False，以便在不需要跟踪对象变化时降低内存消耗。

# 动态追踪数据库的修改，不建议开启

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

 •创建数据表

Flask-SQLAlchemy会按照ORM的映射关系将模型类转换成数据表，数据表的名称具体分成以下两种情况：

（1）若模型类中包含类属性__tablename__，则会将类属性__tablename__的值作为数据表的名称。

（2）若模型类中没有包含类属性__tablename__，则会将模型类的名称按照一定的规则转换成数据表的名称。

转换的规则主要有两种情况：

•模型类的名称是一个单词，则会将所有字母转换为小写的单词作为数据表的名称，例如，模型类User对应的数据表为user；

•模型类的名称是多个单词，则会将所有字母转换为小写，以下画线连接的多个单词作为数据表的名称，例如，模型类MyUser对应的数据表为my_user。

定义了模型类以后，如果希望根据模型类生成一个对应的数据表，那么就需要通过db对象调用create_all()方法实现。在命令行窗口中进入虚拟环境，输入flask shell命令启用Flask Shell工具，在该工具中输入创建数据表的命令：

flask shell

>>> from app import db

>>> db.create_all()

 -  如果数据库表已经存在于数据库中，那么db.create_all()不会重新创建或者更新相应表。

-  如果修改模型后要把改动现有的数据库，那么先删除旧表再重新创建。

>>> db.drop_all()

>>> db.create_all()

 -  在数据库中，可以通过关系让不同数据表之间的字段建立联系，数据表的关系包括一对多关系、一对一关系和多对多关系，模型类之间也需要根据实际需要建立这些关系。

在Flask的模型类中，建立关系一般需要两步实现，分别是创建外键和定义关系属性。

创建外键：

-  外键是数据表的一个特殊字段，它经常与主键约束一起搭配使用，用于将两张或多张数据表之间进行关联。对于两张有关联关系的数据表来说，关联字段中主键所在的表就是主表（也称为父表），外键所在的表就是从表（也称为子表）。

- 当在模型类中通过Column类的构造方法创建字段时，可以传入一个db.ForeignKey类的对象，用于将关联表中的字段指定为外键。db.ForeignKey类的构造方法中必须传入一个column参数，column参数表示外键关联表的字段，column参数的取值为包含字段名称的字符串，字符串的格式为“数据表名.字段名”。

定义关系属性：

-  定义关系属性通过db.relationship()函数实现。大多数情况下，db.relationship()函数能够自行找到关系中的外键。

选项名	说明
backref	在关系的另一个模型中添加反向引用(另外一个模型可获取该模型)
primaryjoin	明确指定两个模型之间使用的联结条件；只在模棱两可的关系中需要指定
lazy	指定如何加载相关记录，可选值有select（访问到属性时，一次性加载该属性数据）、joined(对关联的两个表使用联结)、subquery(与joined类似，但使用子查询)、dynamic(返回查询对象，可调用对应的one()/all()/filter()方法，大批量数据查询处理时推荐使用)
uselist	如果设为False,不使用列表，而使用标量值
order_by	指定关系中记录的排序方式
secondary	指定多对多关系中关联表的名称
secondaryjoin	SQLAlchemy无法自行决定时，指定多对多关系中的二级联结条件
back_populates	定义反向引用，用于建立数据表之间的双向关系。

一个用户可以发表多篇文章，反过来一篇文章只能属于一个用户，像用户与文章之间的对应关系就是一对多关系。在模型类中定义一对多关系时，一般是在“多”这一侧对应的模型类中创建外键，在“一”这一侧对应的模型类中定义关系属性。

定义分别表示用户和文章的类User和Article，在“一”这一侧的User类中定义关系属性，在“多”这一侧对应的Article类中创建外键：

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    # 定义关系属性
    article = db.relationship("Article", backref='user', lazy='dynamic')
class Article(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(20), nullable=False)
    # 创建外键
    author_id = db.Column(db.Integer, db.ForeignKey('user.id'))

Flask  Shell中对应的数据如下：

>>> u1 = User(username='小花',email='118@qq.com')           
>>> u2 = User(username='小明',email='119@qq.com')   
>>> u3 = User(username='小李',email='120@qq.com') 
>>> db.session.add(u1)
>>> db.session.add(u2) 
>>> db.session.add(u3) 
>>> a1 = Article(title='数据库1',author_id=1)
>>> a2 = Article(title='数据库2',author_id=1) 
>>> a3 = Article(title='数据库3',author_id=2) 
>>> db.session.add(a1)
>>> db.session.add(a2) 
>>> db.session.add(a3)
>>> db.session.commit() 
>>> c1 = User.query.first()
>>> c1.article.all()
[<Article 1>, <Article 2>]
>>> c1.username
'小花'
>>> a1 = Article.query.first()
>>> a1.title
'数据库1'
>>> a1.user
<User 1>
>>> a1.user.username
'小花'

•增加数据

在Flask-SQLAlchemy中，db.session提供了增加数据的add()和add_all()方法，其中add()方法用于向数据库中增加一条记录，add_all()方法用于向数据库中增加多条记录。

@app.route("/")
def hello_flask():
    # 创建User类的对象
    user1 = User(username="小明", email='123@qq.com')
    user2 = User(username="小张", email='456@qq.com')
    user3 = User(username="小红", email='789@qq.com')
    # 将User类的对象添加到数据库会话中
    db.session.add(user1)
    db.session.add_all([user2, user3])
    # 使用commit()方法从会话提交至数据库
    db.session.commit()
    return "OK"
if __name__ == "__main__":
    app.run()

注：创建User类的对象时并没有传入id字段的值，这是因为主键由SQLAlchemy管理。创建User类对象后会将这些对象作为临时对象，直至程序提交至数据库会话后才会将这些对象转换为记录写入到数据库中，并且自动获得id字段的值。运行程序，访问http://127.0.0.1:5000/后页面展示了OK，这时打开Navicat工具查看flask_data数据库的user表：

•查询数据

-Flask-SQLAlchemy的Model类中提供了一个query属性，模型对象通过访问query属性可以获取一个查询对象（Query类实例），该对象中提供了一些过滤方法和查询方法对查询结果进行过滤和筛选，以便精准地查找。

<模型类>.query.<过滤方法(可选)>.<查询方法>

在上述格式中，过滤方法和查询方法是可选的，由于使用过滤方法查询后会返回一个新的查询对象，所以过滤方法和查询方法可以叠加使用，另外也可以单独使用查询方法，常见的过滤方法：

方法	说明
filter()	根据指定的规则过滤记录，返回新产生的查询对象
filter_by()	根据指定的规则过滤记录(以关键字表达式的形式)，返回新产生的查询对象
order_by()	根据指定条件对记录进行排序，返回新产生的查询对象
limit()	根据指定的值限制原始查询对象返回的结果数量，返回新产生的查询对象
offset()	根据指定的值偏移原始查询对象，返回新产生的查询对象
group_by()	根据指定的条件对记录进行分组，返回新产生的查询对象
with_entities()	根据指定实体替换查询列表，返回新产生的查询对象

表中所有方法都会对原始查询对象进行过滤操作，并将过滤后的结果生成一个新的查询对象。其中，filter()方法是最基础的过滤方法，该方法可以传入包含!=和==操作符的表达式

也可以传入使用了查询操作符的表达式，常用的查询操作符包括LIKE、IN、NOT IN、IS NULL、IS NOT NULL、AND和OR。

查询方法如下：

方法	说明
first()	返回查询的第一条记录，若没有找到，则返回None
first_or_404()	返回查询的第一条记录，若没有找到，则返回404错误响应
get(id)	返回指定主键值对应的记录，若没有找到，则返回None
get_or_404(id)	返回指定主键值对应的记录，若没有找到，则返回404错误响应
count()	返回查询结果的数量
all()	返回包含所有查询记录的列表
paginate()	返回一个Paginate类的对象，用于对记录进行分页

-  通过示例演示如何使用这些方法查询数据库flask_data中的记录，具体代码如下所示：

@app.route("/")# 定义路由及视图函数
def hello_flask():
    users = User.query.all()# 查询全部记录
    print(users)
    first_user = User.query.first()# 查询第一条记录
    print(first_user)
    id_user = User.query.get(2)# 返回主键值2对应的记录
    print(id_user)
    # 过滤username等于"小明"的记录
    users2 = User.query.filter(User.username=="小明").first()
    print(users2)
    # 过滤email等于"123@qq.com"的记录
    users3 = User.query.filter_by(email="123@qq.com").first()
    print(users3)
    return "OK"

-  运行程序，访问http://127.0.0.1:5000/后浏览器页面中显示了“OK”，这时控制台输出的查询结果如下所示：

-  程序输出了形式如“<模型类 主键>”的内容，但这些内容无法直观地显示字段的具体信息，为了解决这个问题，我们可以在User类中重写__repr__()方法，在该方法中返回自定义格式的字符串，例如<模型类 字段1, 字段2...>

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), nullable=False)
    email = db.Column(db.String(120), nullable=False)
    def __repr__(self):
        return f'<User {self.username}, {self.email}>'

结果：

[<User 小明, 123@qq.com>, <User 小张, 456@qq.com>, <User 小红, 789@qq.com>]

<User 小明, 123@qq.com>

<User 小张, 456@qq.com>

<User 小明, 123@qq.com>

<User 小明, 123@qq.com>

 •更新数据

-  更新数据的方式比较简单，它只需要为模型类的字段重新赋值便可以将字段原先的值进行修改，修改完成后需要调用commit()方法提交至数据库。

例如，将数据库flask_data中的主键值为2的记录中字段username的值由小张修改为小兰:

@app.route("/")
def hello_flask():
    # 返回主键值2对应的记录
    result = User.query.get(2)
    print(result.username)
    # 将username的值修改为"小兰"
    result.username = "小兰"
    db.session.commit()
    return "OK"
if __name__ == "__main__":
    app.run()

-  运行程序，访问http://127.0.0.1:5000/后页面展示了OK，这时控制台输出的查询结果为：小张

刷新Navicat工具中flask_data数据库的user表中"小张"已经变为"小兰"

•删除数据

-  删除数据可以使用数据库会话提供的delete()方法，删除完成后同样需要调用commit()方法提交至数据库。

例如，将数据库flask_data中的id值为3的记录直接删除：

@app.route("/")
def hello_flask():
    # 返回主键值3对应的记录
    result = User.query.get(3)
    print(result)
    db.session.delete(result)
    db.session.commit()
    return "OK"
if __name__ =='__main__':
    app.run()

-  运行程序，访问http://127.0.0.1:5000/后页面展示了OK，这时控制台输出的查询结果：

<User '小红', '789@qq.com'>

 刷新Navicat工具中flask_data数据库的user表，就会发现id值为3的数已被删除: