5. 数据库

作者: ArtioL | 来源:发表于2018-10-29 21:33 被阅读0次

5. 数据库
mongodb数据库操作
SQLCipher加解密的操作
SQLServer创建数据库、登录账户、用户、并授权
Sor基础（下） - 通过数据库数据索引
Mysql的数据库操作
mysql安装
mysql 总结
MySQL中的基本操作指令
数据库设计（〇）计划

本章节涉及的python库

SQLAlchemy
Flask-SQLAlchemy
Alembic
Flask-Migrate

5.1.1 SQL

数据库的分类
常用的SQL DBMS主要包含SQL Server, Oracle, MySQL, PostgreSQL, SQLite等关系型数据库使用表来定义数据对象.不同的表之间使用关系连接

表（table) 存储数据的特定结构
模式(schema) 定义表的结构信息
列/字段(column/field):表中的列，存储一系列特定的数据列组成表
行/记录（row/record):表中的行代表一条记录
标量(scalar): 指的是单一数据与只想对的是集合

5.1.2

Nosql最初指No SQL 或No Relational 现在nosql社区一般会解释为Not Only SQL.

文档存储

{
id: 1,
name: "Nick",
sex: "Male",
occupation: "Journalist"
}

2.键值对存储

5.2 ORM魔法
ORM非常方便但如果你对SQL相当熟悉那么自己编写SQL代码可以获得更大的灵活性和性能优势,就像是使用IDE一样,ORM对初学者来说非常方便，但进阶以后你也许想要自己掌控一切.
ORM主要实现了三层映射关系:

表 -> Python类
字段 -> 类属性
记录 ->类实例
创建表sql实现

CREATE TABLE contacts(
name varchar(100) NOT NULL,
phone_number varchar(32),
);

如果我们使用ORM,我们可以使用类似下面的Python类定义这个表:

from foo_orm import Model, Column, String

class Contact(Model):
  __tablename__ = "contacts"
  name = Column(String(100), nullable=False)
  phone_number = Column(String(32))

要向表插入一条数据 ,SQL语句如下

INSERT INTO concats(name, phone_number)
VALUES ("Artio", "12345678");

ORM则只需要创建一个Contact实例,传入对应的参数表示各个列的数据即可.

contact = Contact(name="Artio", phone_number="12345678")

除了便于使用,ORM还有下面这些优点

灵活性好。你既能使用高层对象来操作数据库,又支持执行原生sql语句
提升效率. 从高层对象转换成原生SQL会牺牲一些心梗，但这微不足道的性能换取的是巨大的效率提升
可以执行好。 ORM通常支持多种DBMS.包括MySQL, PostgreSQL, Oracle, SQLite等

5.3 使用Flask-SQLAlchemy 管理数据库

pip install flask-sqlalchemy

扩展初始化

from flask_sqlalchemy import SQLAlchemy
from flask import Flask

app = Flask(__name__)
db = SQLAlchemy(app)

5.3.1 连接数据库服务器

常用的数据库URI格式

DBMS	URI
PostgreSQL	postgresql://username:password@host/databasename
MySQL	mysql://username:password@host/databsename
Oracle	oracle://username:password@host/databsename
SQLite(UNIX)	sqlite:////absolute/path/to/foo.db
SQLite(Windows)	sqlite:///absolute\path\to\foo.db或 r'sqlite:///absolute\path\to\to\foo.db'
SQlite(内存型)	sqlite:///或sqlite:///:memory

配置数据库URI

import os
...
app.config["SQLALCHEMY_DATABASE_URI"] = os.getenv("DATABASE_URL", "sqlite:///" + os.path.join(app.root_path, "data.db"))

在生成环境下更换到其他类型的DBMS时,数据库URL会包含敏感信息所以这里优先从环境变量DATABASE_URL获取
SQLALCHEMY_TRACK_MODIFICATIONS变量用于开启或关闭sqlalchemy的警告信息

5.3.2 定义数据库模型

定义Note模型
from . import db

class Note(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  body = db.Column(db.Text)

SQLAlchemy常用的字段类型

字段	说明
Integer	整型
String	字符串,可选参数length可以用来设置最大长度
Text	较长的Unicode文本
Date	日期存储python的datetime.date对象
Time	时间存储python的datetime.time对象
DateTime	时间和日期存储python的datetime对象
Interval	时间间隔存储python的datetime.timedelta对象
Float	浮点数
Boolean	布尔值
PickleType	存储Pickle序列化的python对象
LargeBinary	存储任意二进制数据

Flask-SQLalchemy会根据模型类名称生成一个表名称生成规则如下:
Message --> message # 单个单词转换为小写
FooBar --> foo_bar # 多个单词转换为小写并使用下划线分割

常用的SQLalchemy字段参数

参数名	说明
primary_key	如果设为True, 该字段为主键
unique	如果设为True,该字段不允许出现重复着
index	如果设为True, 为该字段创建索引,以提高查询效率
nullable	确定字段值是否可为空,值为True或False ，默认值为True 允许为空
default	为字段设置默认值

5.3.3 创建数据库和表

from app import db
db.create_all() # 创建所有表

通过下面方式可以查看模型对象的sql模式（建表语句）

from sqlalchemy.schme import CreateTable
print(CreateTable(Note.__table__))

建一个命令用于创建数据库和表的flask命令

import click
...
@app.clo.commond()
def initdb():
  db.create_all()
  click.echo("Initialized database.")

命令行输入下面命令即可创建数据库和表

$ flask initdb

5.4 数据库操作

可以调用add()方法将新创建的对象加到数据库会话中, 或是对会话中的对象进行更新. 只有当你对数据库会话对象调用commit()方法时,改动才被提交到数据库,这确保了数据提交的一致性.另外数据库会话也支持回滚操作.当你对会话调用roolback()方法时,添加到会话中且未提交的改动都将被撤销。

5.4.1 CURD

1.Create
添加一条新纪录到数据库主要分为三步:

创建python对象(实例化模型类)作为一条记录
添加新创建的记录到数据库会话
提交数据库会话

from flask import db.Note
note1 = Note(body="remeber Sammy Jankis")
note2 = Note(body="SHAVE")
db.session.add(note1)
db.session.add(note2)
db.session.commit()

这这个示例中,我们首先创建了几个Note示例,分别表示不同的记录,我们的Note类继承自db.Model基类, db.Model基类会为Note类提供一个构造函数,接收匹配类属性名称的参数值, 并赋值给对象的类属性.所以我们不需要在自己的Note类中定义狗仔方法.接着我们调用add()放吧把这几个Note实例对象添加到会话对象db.session中最后调用commit()方法提交会话
2.Read
常用的SQLAlchemy

查询方法	说明
all()	返回包含所有查询记录的列表
first()	返回查询的第一天记录,如果未找到则返回None
one()	返回第一条记录,且仅允许有一条记录。如果记录数大于1或小于1,则抛出异常
get(ident)	传入主键值作为参数,返回指定主键值的记录,如果未找到,则返回None
count()	返回查询结果的数量
one_or_one()	类似one（）, 如果结束数量不为1 返回None
first_or_404()	类似first(),如果未找到,则返回404错误响应
get_or_404(ident)	类似get,如果未找到,则返回404错误响应
paginate()	返回一个Pagination对象，可以对记录进行分页处理
with_parent(instance)	传入模型类作为参数,返回和这个实例相关联的对象

Note.query.all()
返回所有查询结果

常用的SQLAlchemy过滤方法

查询过滤名称	说明
filter()	使用指定的规则过滤记录,返回新产生的查询对象
filter_by()	使用指定规则过滤记录（以关键字表达式的形式）,返回新产生的查询对象
order_by()	根据指定条件对记录进行排序(desc, asc),返回新产生的查询对象
limit(limit)	使用指定的值限制原查询返回的记录输了,,返回新产生的查询对象
group_by()	根据指定条件对记录进行分组,返回新产生的查询对象
offset(offset)	使用指定的值偏移原查询的结果,返回新产生的查询对象

LIKE:

  filter(Note.body.like("%foo%"))

IN:

  filter(Note.body.in_(["foo", "bar", "baz"])

NOT IN:

  filter(~Note.body.in_(["foo", "bar", "baz"])

AND:

    # 使用and_()
    from sqlalcehmy import and_
    filter(and_(Note.body == "foo", Note.title == "FooBar"))
    # 或者filter()加入多个表达式 使用逗号分隔
    filter(Note.body == "foo", Note.title == "FooBar")
    # 或叠加调用filter
    filter(Note.body == "foo").filter(Note.title == "FooBar")

OR:

from sqlalchemy import or_
filter(or_(Note.body == "foo", Note.body == "bar"))

Update

note = Note.query.get(2)
note.body = "change"
db.session.commit()

Delete

note = Note.query.get(2)
db.session.delete(note)
db.session.commit()

5.4.2 在视图函数里操作数据库

Create

from flask_wtf import FlaskForm
from wtforms import TextAreaField, SubmitField
from wtforms.validators import DataRequired

class NewNoteForm(FlaskForm):
  body = TextAreaField("Body", validators=[DataRequired()]
  submit = SubmitField("Save")

我们创建一个视图负责渲染创建笔记的模板.并处理表单的提交,

@app.route("/new", methods=["GET", "POST"])
def new_note():
  form = NewNoteForm()
  if form.validate_on_submit():
    body = form.body.data
    note = Note(body=body)
    db.session.add(note)
    db.session.commit()
    flash("Your Note is saves")
    return redirect(url_for("index"))
  return render_template("new_note.html", form=form")

模板

{% block content %}
<h1>New Note</h1>
<form method="post">
  {{ form.csrf_token }}
  {{ form_field(form.body, rows=5, cols=50) }}
  {{ form.submit }}
</form>
{% endblock %}

index视图用来线束主页

@app.route("/")
def index():
  return render_template("index.html")

对应的index.html

<h1>NoteBook</h1>
<a href="{{ url_for('new_note') }}New Note</a>

Read
在视图函数中查询数据库记录并传入模板

@app.route("/")
def index():
  form = DeleteForm()
  notes = Note.query.all()
  return render_template("index.html", form=form)

新的index.html

<h1>Note Book</h1>
<a href="{{ url_for('new_note') }}New Note</a>
<h4> {{ note.length }} notes:</h4>
{% for note in notes %}
  <div class="note">
    <p>{{ note.body }}</p>
  </div>
{% endfor %}

Update

class EditorNoteForm(FlaskForm):
  body = TextAreaField("Body", validators=[DataRequired()]
  submit = SubmitField("Update")

更新笔记内容视图

@app.route("/edit/<int:note_id>", methods=["GET", "POST"])
def edit_note(note_id):
  form = EditorNoteForm()
  note = Note.query.get(note_id)
  if form.validate_on_submit():
    note.body = form.body.data
    db.session.commit()
    flash("Your note is updated")
    return redirect(url_for("index"))
  form.body.data = note.body # 渲染表单
  return render_template("edir_note.html", form=form)

新的index.html

<h1>Note Book</h1>
<a href="{{ url_for('new_note') }}New Note</a>
<h4> {{ note.length }} notes:</h4>
{% for note in notes %}
  <div class="note">
    <p>{{ note.body }}</p>
    <a class="btn" href={{ url_for("edit_note", note_id=note.id) }}>Edit</a>
  </div>
{% endfor %}

Delete

< a href="{{ url_for("delete_note", note_id=note.id) }}">Delete</a>

delete_note视图

@app.route("/delete/<int:note_id>")
def delete_note(note_id):
  note = Note.query.get(note_id)
  db.session.delete(note)
  db.session.commit()
  flash("Your note is deleted.")
  return redirect(url_for("index"))

这样添加链接的删除方式看起来很合理,但这种处理方式实际会让程序处于CSRF攻击的风险之中

class DeleteNoteForm(FlaskForm):
  submit = SubmitField("Delete")

视图

@app.route("/delete/<int:note_id>", methods=["POST"])
def delete_note(note_id):
  form = DeleteForm()
  if form.validate_on_submit():
    note = Note.query.get(note_id)
    db.session.delete(note)
    db.session.commit()
    flash("Your note is deleted.")
  else:
    abort(404)
  return redirect(url_for("index"))

最终的index.html

<h1>Note Book</h1>
<a href="{{ url_for('new_note') }}New Note</a>
<h4> {{ note.length }} notes:</h4>
{% for note in notes %}
  <div class="note">
    <p>{{ note.body }}</p>
    <a class="btn" href={{ url_for("edit_note", note_id=note.id) }}>Edit</a>
    <form method="post" action={{ url_for('delete_note', note_id=note.id }}">
      {{ form.csrf_token }}
      {{ form.submit(class='btn') }}
    </form>
  </div>
{% endfor %}

5.5 配置关系

5. 数据库
本章节涉及的python库 SQLAlchemy Flask-SQLAlchemy Alembic Flask-M...
mongodb数据库操作
1. 创建数据库 2. 查看所有的数据库 3. 删除数据库 4.创建集合 5.文档插入
SQLCipher加解密的操作
1. 创建加密数据库 2. 打开加密数据库 3. 修改数据库密码 4. 加密已有的数据库 5. 解密数据库参考资...
SQLServer创建数据库、登录账户、用户、并授权
1.创建数据库 2.切换到创建数据库 3.创建登录账户 4.创建数据库用户 5.用户授权
Sor基础（下） - 通过数据库数据索引
5.从MySQL数据库导入数据至solr 关系型数据库：以MySQL为例请参照其他教程搭建MySQL数据库环境，...
Mysql的数据库操作
1.登录数据库 2.查看数据库 3.创建数据库 4.查看数据库定义 5.删除数据库 6.查看系统所支持的引擎类型 ...
mysql安装
参考 1.使用brew 安装 2.查看软件信息 3.启动数据库服务 4.关闭数据库服务 5.登录数据库
mysql 总结
1 mysql常用术语 2 mysql安装 3 连接数据库 4 数据库的操作 (**) 5. 表的基本操作（***...
MySQL中的基本操作指令
1.显示系统中所有可使用的数据库 2.切换当前数据库 3.创建数据库 4.删除数据库 5.创建数据库并指定语言编码...
数据库设计（〇）计划
1. 数据库概述 2. 数据模型 3. 数据库系统结构 4. 关系模型和关系数据库 5. SQL 6. 数据库完整...