solomonxie / blog-in-the-issues

A personalised tech-blog, notebook, diary, presentation and introduction.
https://solomonxie.github.io
67 stars 12 forks source link

Database Based Basics 数据库研习 #28

Open solomonxie opened 6 years ago

solomonxie commented 6 years ago

涉及数据库相关话题:

注意:

solomonxie commented 5 years ago

❖ Redis入门 [DRAFT]

Redis是目前最流行的NoSQL数据库,最重要的是它是运行在内存上的数据库。所以几乎所有高并发需求的产品都会考虑使用Redis作为数据库缓存。

不同于MongoDB的以硬盘存储为主、内存为辅,Redis是真·内存存储,即所有数据都存在内存中,只是偶尔间歇性的保存到硬盘上备份。

Redis特点:

理解Redis

原子性

Redis处理高并发最强的就是其原子性。完全基于单线程,抛弃多进程、多线程等逻辑。

什么是“原子性”?参考:深入学习RedisAPI的原子性分析

原子性是数据库的事务中的特性。在数据库事务的情景下,原子性指的是: 一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。 对于Redis而言,命令的原子性指的是:一个操作的不可以再分,操作要么执行,要么不执行。

Redis vs. JSON

作为NoSQL初学者,我觉得所有的NoSQL不过是一个更复杂的JSON文件而已。

但是只是一个文本文件的JSON面临IO堵塞、文本解析等很大屏障,速度决定了它的天花板。即使是放在Ram Disk内存盘上的JSON,也解决不了高并发的问题 而Redis不光用了内存,还用了原子性逻辑来加速运行,同时还加入了一系列的备份、恢复、分布式多机器运行的功能。

所以真的没法再说和JSON一样了。

Redis vs. MongoDB

MongoDB是基于Documentation的,

....

Redis vs. Memcached

....

安装

参考:How To Install and Secure Redis on Ubuntu 18.04

整个软件大约1M左右。

Mac的Homebrew安装:

brew install redis

Ubuntu安装:

sudo apt install redis-server

Docker安装: 因为Redis实在是太轻量了,而且原生支持多实例运行,配置也是单文件配置,所以不太需要专门用docker来做隔离。如果真有docker需要的话,也不会难。参考hub.docker.com。

编译安装: 如果需要自己编译的话,就到Redis的下载最新的release,一般用tar.gz格式。 目前最新的稳定版5.0。中文官网地址:http://www.redis.cn/

wget http://download.redis.io/releases/redis-5.0.0.tar.gz
tar -xvzf redis-*.tar.gz
cd redis-*

make
make test
sudo make install

配置

Redis配置很好理解,只需一个redis.conf配置文件。

找到文件后,一般需要修改的地方只有以下几点:

# 绑定主机IP和端口,端口是redis默认
bind 127.0.0.1
port 6379

# (推荐)以守护进程方式运行,这样就不会进入命令行”前台堵塞模式“
daemonize yes

# 数据文件
dbfilename dump.db

# 数据存储的位置,运行前需手动创建文件夹,否则报错
dir /var/lib/redis/

# 日志文件
log /var/log/redis/redis-server.log

# 数据库数量,默认16个数据库
database 16

运行交互

启动redis服务器:

redis-server

# 或指定配置文件启动 (Brew安装的话位置在/usr/local/etc/redis.conf)
redis-server /etc/redis/redis.conf

如果没有在配置中设置daemonize,那么这里就会在前端启动,即堵塞整个shell来运行这个程序。如果已经是守护进程了,那么就会在后台运行,可以用ps aux |grep redis看到。 如果要开机启动,直接在/etc/rc.local中加入启动redis的命令即可,不过这样的还不如设置系统service好管理。关闭服务器的方法就是直接kill掉进程即可:pkill redis-server

客户端:

# 客户端shell,与服务器进行命令交互
redis-cli 

# 关闭客户端
redis-cli shutdown

最简单cli:

$ nc -v 127.0.0.1 6379

# with ssl
$ nc -v --ssl 127.0.0.1 6379

Redis的主从 "RAID 1"

以上是最简单的单机设置。然而,Redis的主从设置也不难,很简单。

记住:Redis的Master-Slave的结构,实际上只是一种备份关系!而不是数据分散在各地的那种。

Redis的Master-slave架构的作用:

image

设置方法:

这个设置方法是最简单的主从设置,甚至有点像ssh-tunnelfrp内网穿越等设置。都是基于一个配置文件就能完成自动连接的。

主从可以在同一台机器(但是没有什么意义),只是注意端口号不要冲突。如果不是同一台机器,那么端口号就无所谓了。

Redis集群 "RAID 0"

如果说主从架构是硬盘组合的RAID 1模式,那么Redis集群就是RAID 0——数据是分布在各个机器上的。

如果只是简单的主从架构,那么主要的压力还是都集中在Master主机上,万百万级别的高并发肯定是扛不住的。所以要用到Redis集群。

Redis集群才是真正的分布式

集群分为软件层面的和硬件层面的。 Redis在同一台机器可以启动多个服务,也就是在本机可以使用多个Redis数据库服务,这叫软件层面集群(没什么用)。因为一台机器死机,整个集群就没了。所以软件方面的只适合同一台机器给不同应用配置redis数据库,不适合集群。 硬件集群是每台机器上都有redis,用于分担数据。

集群有这几大特点:

集群的槽 Slots

Redis怎么把全部数据分配个集群的每台机器? 它会先把数据分为16,384个slots槽,然后把这些槽平均分配个每台机器。比如机器A分了0-1000的槽用来存数据,机器B分了1001-2000的槽。。。每台机器都会知道自己会负责哪些槽。

如果一台机器接收到不是自己负责的slot的数据,就会把请求“转发”给该负责的机器。这个就叫转向 (Redirection)。

怎么确定新来的数据在哪台机器上写入呢?

Redis利用了Hash Table数据结构的基本原理,即通过一个Hash function把key映射为一个固定的整数number。通过number % 16384而得到一个固定的index整数值,根据这个index就能直到它所属的slot在哪个“负责人”位置了。

集群的分区 Partition

如果有partition分区,那么及时有些机器突然不可用、断线,集群也可以继续完成请求任务。

Redis于Python交互

Python需要安装redis包:pip install redis

基础交互代码test.py

import redis
solomonxie commented 5 years ago

❖ MySQL 入门 [DRAFT]

▶参考:CyC2018/CS-Notes/MySQL

RDBMS

RDBMS的全称是:Relational Database Management System.

关系型数据库是建立在关系模型上的。所谓的关系模型,其实就是一大堆独立的表格,通过一个id关联起来的表格库,也就是->关系型数据库

常见的关系型数据库有:

常见的非关系型(NoSQL)数据库:

存储引擎 Engine

MySQL中,可以选择每种数据库使用什么Engine处理引擎,而每种Engine能够计算、处理的事情会有不同。

MySQL常用的引擎有:

一般都是选择默认的InnoDB,因为它支持事务处理外键行级锁。但是MyISAM都不具备,所以它一般只在小项目里能用。 而MyISAM能够达到99.99%的稳定性和高可用性,并且具备高可扩展性,所以也比较流行。

其中,行级锁相对于表级锁,是指在多端同时操作数据库时,能以row为单位锁住一条信息,同一时间只允许一人操作这一行。

总而言之:

安装与启动服务器

Ubuntu下:

# 安装服务器
$ sudo apt-get install mysql-server

# 启停服务器:
$ sudo service mysql start
$ sudo service mysql stop
$ sudo service mysql restart

Mac下:安装比较麻烦。推荐GUI安装,因为命令行安装会有很多问题:

# 安装
brew install mysql

# 修复命令链接
brew link --overwrite mysql

配置MySQL

主要配置文件为/etc/mysql/my.cnf。 (如果不确定位置,可在/etc/mysql/my.cnf中查看具体引用的配置文件的位置)

文件中,常用的配置项有:

# 绑定的服务器地址
bind-address 127.0.0.1
port 3306

# 数据库目录
datadir /var/lib/mysql

# 日志
general_log_file /var/log/mysql/mysql.log
log_error /var/log/mysql/error.log

数据类型

主要需要区分的有:

image

数据约束

有以下集中约束类型:

solomonxie commented 5 years ago

MySQL客户端

MySQL的客户端太多了,各种GUI、CLI、网页等都可以连接到MySQL服务器操作。

CLI命令行客户端

Ubuntu安装:

sudo apt-get install mysql-client

进入交互shell:

# -u为用户名,-p为密码
$ mysql -u root -p mysql123

# 中间空格可以省略
$ mysql -uroot -pmysql123

进入Mysql-shell后,显示如下: image

常用命令:

# 显示版本号
select version();

# 显示服务器中所有的数据库
show databases;

# SQL语句
select * from ....

语法高亮的命令行客户端

mycli:

pip install -U mycli

grc:

$ apt-get install grc

GUI客户端

solomonxie commented 5 years ago

❖ SQL语句基础

SQL Structured Query Language,是专门用来查询关系型数据库的语言。也就是说不是关系型数据库,就不能用SQL查询了。

MySQL的主要学习,其实都是集中在SQL上的。另外一部分,才是数据库的配置和速度优化。

SQL语句的分类:

语法结构

一些特点:

SQL的语法,是将一条语句拆分成几个组成部分:

image

SQL Style 编写风格

参考 Simon Holywell:SQL Style Guide 参考 Simon Holywell:SQL样式指南 · SQL Style Guide

image

image

image

常用语句

参考W3School:SQL Tutorial

服务器查询:

-- 显示服务器中所有的数据库
show databases ;

-- 进入一个数据库
use 数据库名 ;

-- 显示当前所在数据库的信息
select database() ;

-- 显示当前数据库所有表名 (MySQL)
show tables;

-- 显示指定数据库所有表名 (MySQL)
select table_name from information_schema.tables where table_schema='数据库名' and table_type='base table';

-- 显示指定表格的所有字段名(MySQL)
select column_name from information_schema.columns where table_schema='数据库名' and table_name='表名';

-- 查看某表结构
DESC 表名 ;

数据库操作:

-- 创建一个数据库
CREATE DATABASE 数据库名 CHARSET=utf-8 ;

-- 查看数据库的创建信息
SHOW CREATE DATABASE 数据库名 ; 

-- 删除数据库
DROP DATABASE 数据库名 ;
-- 或,用反引号包起来
DROP DATABASE `数据库名`

数据表操作:

-- 显示当前数据库中的所有表
show tables ;

-- 创建表
CREATE TABLE 表名 (字段 类型 约束, 字段 类型 约束, 字段 类型 约束....) ;
-- 如
CREATE TABLE staff (
    id int primary key not null auto_increment, 
    name varchar(30)
);

-- 删除表
DROP TABLE 表名 ;

-- 查看表的创建语句
SHOW CREATE TABLE 表名 ;

-- 查看表结构
DESC 表名 ;

-- 修改表:添加一个字段
ALTER TABLE 表名 ADD 字段 类型 约束 ;

-- 修改表:修改一个字段
ALTER TABLE 表名 MODIFY 字段 类型 约束 ;

-- 修改表:删除一个字段
ALTER TABLE 表名 DROP 字段 ;

-- 添加一条记录
INSERT INTO 表名 VALUES(字段1, 字段2, , 字段4) ;

-- 

CRUD 增删改查

Create / Read / Update / Delete

查询 SELECT

select查询永远是SQL中学习时间最长的。因为增删改都是固定模式,语句也很简单。但是查询拥有极多的方式方法和关键字,能够创造超多的组合搭配查询,且每种查询方式效率速度不一。所以SQL主要学的就是SELCT。

最简单的select查询:

SELECT filed1, field2, field3 FROM table_name ;

SELECT filed1 AS age, field2 AS gender, field3 FROM table_name ;

SELECT table_name.filed1, table_name.field2, table_name.field3 FROM table_name ;

SELECT t.filed1, t.field2, t.field3 FROM table_name as t ;

-- 删除重复行
SELECT DISTINCT field1 FROM table_name ;

以下为各种Select语句的方式方法总结。

条件 WHERE

SELECT filed1, field2 FROM table_name 
    WHERE field3 > 10;

... WHERE field1 = "hello" AND field2 = "world" ;

... WHERE filed1 LIKE "Hel%" or filed2 LIKE "Hel__" ;

... WHERE field1 RLIKE "^He.*$" ;

... WHERE filed1 IN (12, 18, 19) and NOT IN (30, 40, 50) ;

... WHERE field1 BETWEEN 10 AND 20 and NOT BETWEEN 40 AND 50 ;

... WHERE filed1 IS NULL OR field3 IS NOT NULL ;

排序 ORDER BY

... WHERE ... ORDER BY age, gender ;

... WHERE ... ORDER BY age ASC ;

... WHERE ... ORDER BY age ASC, id DESC, gender ASC ;

内置聚合函数 FUNCTIONS

内置函数能够处理一些很简单的计算问题。 但是切记,查询一个函数值时不要查询其它字段,除非使用GROUP分组等方法。

SELECT COUNT(*) FROM ...

SELECT MAX(age) FROM ...

SELECT SUM(age) FROM ...

SELECT AVG(age) FROM ...

SELECT ROUND( SUM(age)/COUNT(*), 2 ) FROM ...

SELECT MAX(age) FROM ...

-- 不允许:(因为逻辑不通,需要用到分组才行)
-- SELECT name, age, ROUND( SUM(age)/COUNT(*), 2 ) FROM ...

分组 GROUP BY

SQL分组是一个比较容易混淆的概念。

SQL的分组是会完全破坏原先表结构的,然后生成一个统计表,纯粹是为了数量统计用的。

分组GROUP单独使用是没什么意义的,除非是和聚合函数Functions一起用。

分组的做法是:如果按gender分组,就只把gender一列取出来,做成一个unique的唯一gender列表,如男; 女,然后再创建一列,值对应的是每一种gender的记录条数。

image

如果要查看各组的其它信息,需要用到特殊的函数group_concat(filed1):

image

-- 报错:
-- SELECT name FROM ... GROUP BY gender ;
-- SELECT * FROM ... GROUP BY gender ;

-- 显示gender的每种分组类别以及其下的记录条数!
SELECT gender, COUNT(*) FROM ... GROUP BY gender ;

-- 显示分组的平均
SELECT gender, AVG(*) FROM ... GROUP BY gender ;

-- 显示分组最大值
SELECT gender, MAX(*) FROM ... GROUP BY gender ;

-- 显示分组所包含的其它信息
SELECT gender, GROUP_CONCAT(name) FROM ... GROUP BY gender ;
SELECT gender, GROUP_CONCAT(name, "_", age) FROM ... GROUP BY gender ;

分组还有一个配合的关键字having,类似与where的筛选功能:

SELECT gender, COUNT(*) FROM ... GROUP BY gender HAVING COUNT(*) > 3 ;

分页 LIMIT

-- 限制显示结果的条数
SELECT ... FROM ... LIMIT 2 ;

-- 分页 格式为:LIMIT (第N页-1)*每页个数, 每页个数

-- 第1页,每页2条
SELECT ... FROM ... LIMIT 0,2 ;

-- 第2页,每页2条
SELECT ... FROM ... LIMIT 2,2 ;

-- 第3页,每页2条
SELECT ... FROM ... LIMIT 4,2 ;

-- 第4页,每页2条
SELECT ... FROM ... LIMIT 6,2 ;

连接 JOIN

SQL中的JOIN连接,实际上是用了数学上的集合概念。其中:

image

怎么理解Left Join和Right Join? 首先,两表匹配,各表都会有各自的未匹配数据条目那么怎么处理这些未匹配数据,就是这些左右的考量目标。 Left join,保留左表的未匹配数据。Right join,保留右表中的未匹配数据。Full join,保留所有未匹配数据。 那么保留下的这些未匹配数据,肯定会有几个来自外面的字段是空的,这时候都统一以null填充。

另外,SQL连接两表,不光要指定连接方式,还要指定主键-外键的对应关系,使用ON关键字。

-- 内连接
SELECT ... FROM tb1 INNER JOIN tb2 ON tb1.key = tb2.id ;

-- 左连接
SELECT ... FROM tb1 RIGHT JOIN tb2 ...

-- 右连接
SELECT ... FROM tb1 LEFT JOIN tb2 ...

-- 全连接 (MySQL不支持)
SELECT ... FROM tb1 FULL OUTER JOIN tb2 ...

-- 差集连接 (MySQL不支持)
SELECT ... FROM tb1 FULL OUTER JOIN tb2 ON tb1.key = tb2.id 
    WHERE tb1.key IS NULL OR tb2.id IS NULL;

自关联

自连接,连接的两个表都是同一个表,同样可以由内连接,外连接各种组合方式,按实际应用去组合。

SELECT a.*, b.* FROM tb1 a, tb2 as b WHERE a.[name] = b.[name]  

联合 Union

UNION 操作符用于合并两个或多个 SELECT 语句的结果集

使用Union联合的前提条件:

列出所有在中国和美国的不同的雇员名:

SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

image

UNION ALL 命令和 UNION 命令几乎是等效的,不过 UNION ALL 命令会列出所有的值

列出在中国和美国的所有的雇员:

SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA

image

子查询

实际上就是用( select ...)子语句返回一个值,来方便主句查询。相当于bash脚本中的$(...)功能。

SELECT ... FROM ... WHERE height = (SELECT MAX(height) FROM ...)
solomonxie commented 5 years ago

❖ 理解数据库设计 [DRAFT]

谈到数据库设计,其实主要就是设计关系型数据库。 而非关系型的NoSQL不需要太麻烦的设计,只要把数据一股脑塞进去就行了。

数据库设计软件:

参考其它免费设计软件:Free SAP PowerDesigner Alternatives - AlternativeTo.net

image

ER模型 (Entity-Relationship Model)

关系型数据库是建立在ER模型的理论基础上的。ER-Model 或 ER-Diagram,包括了各个实体间的对应关系、关系连接所需的因素(主键等)。

其中:

范式 Normal Form (NF)

经过前人经验总结出来的数据库设计模式、范式,目前有8种常用范式。 我们最最常用的只有三种,称为著名的三范式

8种范式:

其中涉及几个比较混淆的概念:

这些范式,如果没有遵循,那么就会产生错误、冗余,造成生产减速。

solomonxie commented 5 years ago

❖ MySQL常用高级操作:视图、事务、账户管理 [DRAFT]

视图 View

一个View视图是一个虚拟的表格,是从各个真实表格中抽取各种数据而成,但是只能查询不能删改。 为什么要用视图?因为一般查询经常会涉及多个表多个字段,非常繁琐。为了简化流程,适合未来业务改变,所以更方便的是抽象出一个视图来查询。

也就是,用View的意义在于:

定义View视图:

CREATE VIEW 视图名称 AS
    SELECT ... FROM ... WHERE ... ;

定义好后,一个View视图就会成为一个数据库中的“表格”,当我们用show tables;时就会显示出来所有的表和视图。

删除视图:

DROP VIEW 视图名称 ;

事务 Transaction

一个事务Transactin代表一个操作序列,即联动的好几条语句。那么,

当我们将Transaction作为一个_整体_执行时,操作序列中的语句要么全部执行成功,要么全体执行失败

在很多时候,这种操作序列的绑定是至关重要的。比如A向B进行银行转帐分为这三步:

那么,如果转账系统突然故障,只执行了2步就死机,那么这时候必须视为整体执行失败,否则将会引发大问题。

如果一个Transaction中有一个执行失败的语句,那么数据库会立即进行Rollback回滚操作。

MySQL中执行事务的语句格式:

START TRANSACTION ;

    SELECT ... FROM ... ;
    UPDATE ... SET .... ;
    INSERT INTO ... ;

COMMIT;

(以上的Start Transaction ;也可以用Begin ;来代替。)

如果Commit报错,那么我们可以进行手动回滚:

ROLLBACK ;

事务的四大特性 (ACID)

事务Transaction是保证数据更新100%成功的一种方式。 (注意:事务只是为了增删改而设计的。而单纯的查询,是不需要事务的。)

它具有如下特性:

账户管理 Account Management

根据权限,分为这几类账户:

其中Root账户有权利创建、修改账户,并分配相应的权利。

所有账户(用户)相关的信息,都存在MySQL服务器上叫dmysql的数据库中的user表中,包括名称、权限、所在主机,甚至密码等信息。

常用操作:

-- 查看user表结构
DESC user ;

-- 查看所有用户
USE mysql ;
SELECT * FROM user ;

-- 查看某用户有哪些权限,如user1
SHOW GRANTS FOR 'user1'@'主机IP' ;

-- 创建一个用户
GRANT 权限列表 ON 数据库 TO 数据库账户名@主机IP IDENTIFIED BY '密码' ;
-- 如:
GRANT select on mydb1.* to user1@192.168.1.111 IDENTIFIED BY 'password123' ;

-- 修改用户权限
GRANT select, insert on mydb1 to user1@localhost WITH GRANT OPTIONS ;
FLUSH PRIVILEDGES ;    -- 刷新,使更改生效

-- 修改密码
UPDATE user SET authentication_string=password('密码') WHERE user='用户名' ;

-- 删除用户
DROP USER 用户名@主机IP ;
-- 或
-- DELETE FROM user WHERE user='用户名' ;
FLUSH PRIVILEDGES ;    -- 刷新,使用更改生效

以上的权限列表有固定的语法格式:操作名 ON 数据库名.表名, 操作名 ON 数据库名.表名.... 如:

远程登录 : 如果主机IP写'%',则代表允许可以从任何IP访问此数据库。如果指定某IP,那么MySQL则只接收来自此IP的登陆,从其它机器不能登录。 另外,如果要直接远程登录,除了在user表中修改允许的IP外,还需要在MySQL服务器的配置文件/etc/mysql/mysql.con.d/mysql.cnf中的bind_address的允许IP。 但是! 但是,这种方法不推荐,会有很大的被黑危险。正确的做法是,通过SSH登录远程主机进行操作,这样就不需要开发IP限制了。

执行时间查看

在MySQL中,可以开启时间监控的功能。

-- 开启时间监控,0为关,1为开
SET profiles = 1 ;

-- 任意执行一个语句
SELECT ... FROM ... ;

-- 显示之前那句的执行时间
SHOW profiles ;

显示效果如下(包括具体的sql语句和执行时间): image

实际上profiles就是一个简单的表,记录了每一次的sql语句和其执行的时间。

solomonxie commented 5 years ago

❖ MySQL索引 Indexing

一个网站的读、写比例一般是10: 1,所以查询永远是数据库的大头。

如果一个网站只有几万条数据,那么完全不需要索引。但是一旦到了百万级以上,没有索引的话,每次查询都会造成数秒级的等待!这是用户绝不能忍的。

为了加快查找速度,数据库一般都会有索引功能,即类似图书馆中书籍编号的索引。 数据库中的索引是一个特殊文件,存储了 某个数据库表所有记录指针引用

而数据库中索引的实现方法,要远比建立图书的索引要复杂很多。(一般不需要深入了解)

数据库建立索引的目的是:让你不用遍历每一条信息去找到结果。 而要达到这种效果,很明显数据结构中的是专门做这种事的。

而MySQL建立的索引就是采用数据结构的树形结构来存储一整列的信息的,从而代替线性表,以加速查找效率:

image

创建Index索引须知:

为一个表tb1title列创建一个索引,名为index1

CREATE INDEX index1 ON tb1( title(10) ) ;

其中列名(length)中的length代表字段的长度,一般等于原表的列类型的长度,如果少于它的话则会降低速度。INT型无需指定长度,一般只有文本需要。

~这时候,MySQL会自动给每条数据的指定列生成一个索引值。然后这些索引也变成了一个列index1。这时候我们就可以把这个列加入到表中,然后被查询引用了。~

这时候可以直接从索引中搜索了:

SELECT title FROM index1 ;

如果我们用show profiles ;就可以看到,与直接查询原表比,当查询数万、百万级数据时,索引快了不是一个数量级。

显示一个表中的所有索引:

SHOW INDEX FROM tb1 ;

生成索引时,系统会生成一个表格,名为index1,其中包含index索引的很多详细数据。 image

删除索引:

DROP INDEX 索引名字 ON 表名 ;

MySQL自动生成索引

MySQL会为每个表的Primary key主键和每个Foreign key外键自动创建索引,无需我们手动创建。 所以如果我们是通过主键和外键去搜索,速度是极快的。

给哪些列加索引

因为索引是单独的表,占用空间。所以只会给常用的列建立索引。

另一点,索引的建立相当于增加了数据库维护的工作量,即指针更新问题。当原表改了值的时候,索引是需要更新的。

solomonxie commented 5 years ago

❖ MySQL的主从架构Master-Worker

所有数据库的主从架构,主要做的都是读写分离

数据库的主从概念,就是指的数据库存储在多台电脑上,互作备份,同时读写分离。也就相当于硬盘组合中的RAID 1形式。 一般的设计是,写就直接写入Master数据库,但是读就从各个Worker从数据库来读取。这样的分配是因为一般的读写配比是10: 1。

所以一般商业网站,最少要有2台电脑,一台Master,一台Worker。因为主从在同一台机器上,是完全没有意义的。

image

整个数据库的备份与修复:

# 备份某个数据库的所有表结构和数据
$ mysqldump -u root -p "password123" 数据库名 > backup.sql

# 备份整个服务器的所有数据库和数据
$ mysqldump -u root -p "password123" --all-databases --lock-all-tables > master_db.sql

# 修复(导入)备份的数据库:
$ mysql -u root -p "password123" 数据库名 < backup.sql

主从的配置

前提条件:

Master电脑和Worker电脑,分别都有一个同样的配置文件/etc/mysql/my.cnf。 注意:MySQL的主从设置,在配置文件里是没有说明的。需要在MySQL的shell里输入命令来指明。

Master配置

Master需要在mysql.cnf中配置以下几个选项:

server-id = 123    #  为本机设置的服务器ID,可以是任意整数,但不能和其它主机重复
log_bin = /var/log/mysql/mysql-bin.log  # 日志文件

重启服务器:$ sudo service mysql restart

然后在Master的MySQL服务中,创建专属的账号,作为Worker服务器远程连接登录用:

GRANT REPLICATION SLAVE ON *.* TO worker1@'%' IDENTIFIED BY 'password123' ;
FLUSH PRIVILEGES ;

Worker配置

同样是修改/etc/mysql/my.cnf:

server-id = 234    # 为本机设置的服务器ID,可以是任意整数,但不能和其它主机重复
log_bin = /var/log/mysql/mysql-bin.log

重启服务器:$ sudo service mysql restart

开启运行主从架构

以上配置完成后,实际上MySQL是分不出谁是主谁是从的。需要在每个Worker服务器的MySQL的shell里来指明自己的主人是谁:

-- 指明主人是谁,以及连接方式
CHANGE MASTER TO master_host='192.168.1.101', 
    master_user='worker1', master_password='password123', 
    master_log_file='mysql-bin.000006', master_log_pos=590 ;

-- 开始连接
START SLAVE ;

-- 查看Worker从属的状态 (自己的状态)
SHOW SLAVE STATUS \G ; 

image

只有以上标注的两个Yes后,才证明同步成功。

此时如果在Master主机上,可以看到自己的状态:

-- 查看Master主人的状态 (自己的状态)
SHOW MASTER STATUS ;

image

此时,任何在Master主机上的修改,立刻就会同步更新到Worker从服务器。

solomonxie commented 5 years ago

❖ 一篇文章入门 MongoDB

MongoDB既是NoSQL数据库,又是内存数据库,而且它是现在最强大、最流行的NoSQL数据库。区别与别的NoSQL数据库,MongoDB主要是基于Documents文档(即一条JSON数据)的。

MongoDB的特点:

MongoDB持久化

MongoDB虽然是内存数据库,但是它主要是将数据存储在硬盘的,所有要操作的数据通过mmap的方式映射到内存某个区域内。所以相对于Redis的真·内存数据库而言,MongoDB只是将大部分的操作数据存在内存中。

Mac中,Mongodb的数据存储位置默认为:/usr/local/var/mongodb。 里面名称类似collection-4-3122184014923990948.wt即为一个collection。

安装

Ubuntu安装:

$ sudo apt-get install mongodb-org

Mac安装:

$ brew install mongodb

# 启动mongodb服务
$ brew services start mongodb
#或前端启动
$ mongod --config /usr/local/etc/mongod.conf

配置

MongoDB的配置文件在/etc/mongod.conf。 常用的配置项有:

# 默认端口27107

# 日志位置 /var/log/mongodb/mongod.log

Mongo Shell (Javascript)

当我们进入MongoDB客户端后,实际上是进入了一个类Javascript语言的Shell交互环境。 也就是说,MongoDB中的很多命令,尤其是包括定义函数等高级命令,实际上都是Javascript语言。 了解了这点,一些高级命令如Aggregation学起来就会放松很多。

常用命令

# 服务端启动
$ mongod /etc/mongod.conf

# 服务端启停 (Ubuntu上)
$ sudo service mongod start
$ sudo service mongod stop
$ sudo service mongod restart

# 进入客户端
mongo

进入mongo客户端后,就进入了shell交互页面了。 常用的命令如下(注意mongodb区分大小写):

# 显示当前数据库
db

# 显示所有数据库
show databases
# 或
show dbs

# 切换数据库
use 数据库名

# 删除当前数据库
db.dropDatabase()

集合

首先要记住,

MongoDB中,有这么几个概念:

在关系数据库中,是以表为一个数据集。而MongoDB中,是以Collection为一个数据集: image

其中每一个配有_id的记录,就是一个Document文档。相当于一条记录。

可以看出,MongoDB对数据之间事务关系支持比较弱,如果业务这一方面要求比较高的话,MongoDB还是并不适合此类型的应用。

创建集合:不用手动创建集合,当第一条数据插入时,集合自动就生成了。当然,手动创建也是可以的:

db.createCollection( "集合名", {各种属性设置} )
# 如 (capped表示是否设置容量上限)
db.createCollection( "Products", {capped: true, size: 1000} )

# 查看所有集合
show collections

# 删除集合
db.集合名称.drop()

数据类型

在MongoDB中的一个Document,即一个JSON格式的文档中,每个值都是要指定数据类型的。 现有数据类型如下:

数据操作

# 插入数据
db.集合名称.insert( {数据} )
# 如
db.mycollection1.insert( {"name": "Jason", "age": 18} )
# 或
db.mycollection1.insert( {name: "Jason", age: 18} )

# 显示集合中所有数据
db.集合名.find()

# 修改数据 (如果不存在对应的ID,则创建一条新数据)
db.集合名.save( {"_id": "ID号", 数据} )

# 更新单条数据
db.集合名.update( {查询条件}, {更新项目} )
# 如
db.mycollection1.update( {name:"Jason"}, {age:30} )
# 或
db.mycollection1.update( {name:"Jason"}, { $set:{age:30} } )

# 更新多条数据 (使用"multi"选项)
db.mycollection1.update( {job: "HR"}, {salary: 8000}, {multi: true} )

# 删除单条数据
db.mycollection1.remove( {查询}, {justOne: true} )

# 删除多条数据 ("justOne"选项默认为false)
db.mycollection1.remove( {查询} )

image

MongoDB数据备份和恢复

注意:MongoDB导出时候不是单文件,而是巨多JSON和BSON文件。

# 备份
$ mongodump -h 主机IP:端口 -d 数据库名 -o 导出路径
# 如
$ mongodump -h 192.168.1.101:27017 -d mydb1 -o /var/db/mongodb/

# 从本机恢复
$ mongorestore -d mydb1 --dir /var/db/mongodb/
solomonxie commented 5 years ago

❖ MongoDB 高级查询

参考官方文档(图文并茂非常好看):Getting Started - MongoDB Documentation

MongoDB的查询功能非常强大,同时有些地方也会有点复杂。所以需要下点功夫学习和操练才能用好。

关于Mongo Shell

当我们进入Mongo Shell客户端后,实际上是进入了一个Javascript语言的交互环境。 也就是说,MongoDB中的很多命令,尤其是包括定义函数等高级命令,实际上都是Javascript语言,甚至说可以是jQuery。 了解了这点,一些高级命令如Aggregation学起来就会放松很多。

官方说明: image

基本查询功能

比较运算

# age大于等于18
db.mycollection1.find( { age:{$gt: 18} } )

逻辑运算

db.mycollection1.find( {
    $or: [
        { age: {$gte: 20} },
        { salary: {$gt: 5000} },
        { job: "HR" }
    ]
} )

范围运算

db.mycollection1.find( {
    age: {
        $in: [10, 20, 30]
    }
} )

正则表达式

有两种方法:

db.mycollection1.find( {
    name: /^Ja\w+$/
} )

# 或
db.mycollection1.find( {
    name: {
        $regex: "/^Jaso\w?$"
    }
} )

limit和skip

# 限定显示条数
db.mycollection1.find().limit(数量)

# 跳过指定第几条数据
db.mycollection1.find().skip(2)

# 混合使用
db.mycollection1.find().limit(10).skip(3)

自定义函数查询

自定义查询是指使用自定义函数,格式为$where: function(){...}

db.mycollection1.find( {
    $where: function() {
        return this.age >= 18;
    }
} )

投影

即搜索的返回值中,只显示指定的某些字段。字段指为0的不现实,指为1的显示,默认为1。

# 格式为:
db.mycollection1.find(
    {查询条件},
    {显示与否的选项}
)

# 如:
db.mycollection1.find(
    {},
    { _id: 0, name: 1, age: 1 }
)

排序

可以按指定的某些字段排序,字段标记为1的为Asc升序,标记为-1的为Desc降序。

db.mycollection1.find().sort({  name:1, age:-1 })

统计

使用count()函数。

db.mycollection1.find().count()

db.mycollection1.count( {查询条件} )

消除重复

使用distinct()函数。

# 格式为:
db.集合名.distinct( "指定字段", {查询条件} )

# 如
db.mycollection1.distinct( 
    "job", 
    { age: {$lt: 40} } 
)

聚合管道 Aggregation

Aggregation是MongoDB特有的一种Pipline管道型、聚合查询方式。语法稍微复杂一些。

聚合管道可以达到多步骤的分组、筛选功能。这个管道中的每一个步骤,成为一个stage

image

常用的管道有:

image

语法格式为:

db.集合名.aggregate( [
    {管道表达式1},
    {管道表达式2},
    {管道表达式2}
] )

示例:

db.Orders.aggregate( [
    {$match: {
        status: "A"
    } },
    {$group: {
        _id: "$cut_id",
        total: { $sum: "$amount" }
    } }
] )

image

管道的Map Reduce

image

solomonxie commented 5 years ago

❖ MySQL存储过程 (即函数)

参考:mysql存储过程详细教程

SQL的Stored Procedure存储过程,指的其实就是一个函数。 既然是函数,那么就会涉及这几个要点:定义函数、使用函数、变量、参数、返回值等。

为了方便理解,以下就不再叫它存储过程,而直接叫函数了。

在MySQL中,这几个要点的语法如下:

函数定义即调用

单行函数:

CREATE PROCEDURE 函数名(参数) 一句SQL语句;

-- 如:
CREATE PROCEDURE GreetWorld(@whom) SELECT CONCAT('Hello', @whom); 

多行函数:

-- 
DELIMITER //   -- 分隔符
CREATE PROCEDURE 函数名(参数类型 参数名 数据类型)
BEGIN
    具体的SQL语句
    具体的SQL语句
END //
DELIMITER ;   -- 分隔符

-- 示例:
DELIMITER //  
CREATE PROCEDURE funcName(IN p_in int)  
BEGIN   
    SELECT p_in;   
    SET p_in=2;   
    SELECT p_in;   
END //  
DELIMITER ;

调用函数:

-- 变量赋值
SET @p_in=1;  

-- 调用函数
CALL funcName(@p_in);

函数(存储过程)的其它操作:

-- 修改函数
ALTER PROCEDURE .....

-- 删除函数
DROPPROCEDURE 函数名 ;

变量操作

-- 声明一个函数内的新变量(必须放在函数最上方)
DECLARE 变量名 数据类型 DEFAULT 默认值; 

-- 设置变量值
SET @x = 'Goodbye Cruel World';  
-- 或
SELECT 'Hello World' into @x; 

条件控制 IF-ELSE / CASE

IF-ELSE结构:

IF param=0 THEN
    update t set s1=s1+
ELSE 
    update t set s1=s1+
END IF ;

CASE结构:

CASE var  
    WHEN 0 THEN
        insert into t values(17);  
    WHEN 1 THEN
        insert into t values(18);  
    ELSE   
        insert into t values(19);  
END CASE ;

循环语句 WHILE / REPEAT / LOOP / ITERATE

WHILE-DO方式

WHILE var < 100 DO
    具体的SQL语句。
ENDWHILE ;

REPEAT方式:

REPEAT
    具体SQL语句。
UNTIL var <= 100
ENDREPEAT ;

LOOP方式:

label: 
LOOP
    具体SQL语句。
    LEAVE label ;
END LOOP

其中,采用循环标签标签名:的方式可以加在While / Repeat / Loop前,这样就可以使用leave语句退出循环,相当于break的意思。

ITERATE迭代方式:

label:
LOOP
    IF var = 3 THEN
        ITERATE label ;
    END IF ;

    IF var >= 5 THEN
        LEAVE label ;
    END iF ;
END LOOP ;

MySQL常用内置函数

参考:Mysql常用函数总结

solomonxie commented 5 years ago

❖ MariaDB:简而言之 就是MySQL

参考:MariaDB教程 - W3School CN

MariaDB和MySQL

MariaDB是MySQL的直接Fork分支,而且与MySQL 5.5以前的版本都同步适配。5.5之后就不再和MySQL的版本号挂钩了。 主体上,几乎没什么区别。只是在开发中,解决了很多MySQL遗留的问题,添加了一些更好用的功能。

所以这里不会详细介绍所有内容,而是只挑出来与MySQL不同的地方。而相同的地方,直接参考MySQL即可。

安装和使用

Mac安装:

$ brew install mariadb

# 启动服务
$ mysql.server start

Mac用Brew安装的Mariadb或Mysql的默认存储位置为:/usr/local/var/mysql

Ubuntu安装:

# 建议删除mysql后再安装,(注意数据备份)
# 因为他们有很多的配置文件、客户端都在共用。
$ sudo apt-get remove --purge mysql-server

# 安装
$ sudo apt-get install mariadb-server

# 启停
$ sudo systemctl  start  mariadb
$ sudo systemctl  stop  mariadb
$ sudo systemctl  restart  mariadb

安装时会进入交互界面,设置密码后会弹出提示:是否从MySQL迁移到MariaDB,因为会覆盖配置。 image

修改root密码:

$ mysqladmin -u root password "[enter your password here]";

客户端登录: 完全完全完全,和MySQL一样,连客户端名字都没有改:

# 进入数据库终端
$ mysql -uroot -p 
Enter password:*******

image

SQL语句

进入MariaDB后使用的SQL语句命令等,也和MySQL一模一样。

solomonxie commented 5 years ago

❖ MongoDB Indexing 索引 [DRAFT]

速度查询

显示某命令的执行效率:

db.collection1.find().explain('executionStats');

然后会返回一个非常详尽的执行信息,其中的ExecutionTimeMillis代表语句的执行时间,Millis代表milliseconds毫秒。

image

建立索引

MongoDB为某一个集合中的某一列快速建立Index索引:

db.collection1.ensureIndex( {name:1} )

其中1表示按ASC升序建立索引,-1表示按DESC降序建立索引。

在测试的包含10万条数据的集合中,正常查询耗费84ms,而建立索引后只花费近似0ms!所以是快了8400%!

查看当前集合中,所有的Indeces索引:db.collection1.getIndexes()

删除索引只要db.collection1.dropIndex( {索引名:1})

建立索引的更多选项:

# 建立联合索引
db.collection1.ensureIndex( {name:1, age:1, job:1} )

# 建立唯一索引(去除重复)
db.collection1.ensureIndex( {name:1}, {unique: true} )

唯一索引有什么用呢?—— 爬虫数据去重:建立爬取数据关键字段的索引,如url,设置其索引为unique。然后在爬到一个新页面时,肯定要检查数据库中是否已经有此页面。那么就可以通过url来查询数据库。这时候,显然使用Index索引来查询能极大加快查询速度,而这个查询中我们不需要重复的url,只要知道有一个url是和新页面相同的,就可以不用下载页面了。

solomonxie commented 5 years ago

❖ 一篇文章入门PostgreSQL [DRAFT]

安装

详细参考:https://postgresapp.com/

Mac安装:

  1. 需要到官网下载Postgres.app,保存在本地/Application文件夹里,双击运行即可启动服务。
  2. app右上角点击 initialize 初始化server
  3. 安装psql命令行客户端:pip install pgcli --user
  4. 使用app自带psql命令行客户端:alias psql=/Applications/Postgres.app/Contents/Versions/latest/bin/psql
  5. 然后在环境变量$PATH中添加执行文件路径:
    sudo mkdir -p /etc/paths.d &&
    echo /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp

Ubuntu安装:

$ sudo apt-get install postgresql

整个包只有20MB左右,功能强大,但是非常轻量! 检查是否安装成功: $ psql --version 可以看到:Ubuntu 16.04支持的版本为psql (PostgreSQL) 9.5.14,且长期支持维护5年。

Docker安装:

docker run --restart always \
    --name pg \
    -p 54320:5432 \
    -e POSTGRES_PASSWORD=123123 \
    -e POSTGRES_USER=sol \
    -d postgres postgres -c log_statement=all

如果是docker版安装,可以跳过后面的基本配置部分。

客户端: CLI版本的客户端:直接使用官方的psql即可。 GUI的话使用官方推荐的pgadmin,或Mac的psequel

Ubuntu PG server 初始设置

参考:Connecting to PostgreSQL on Linux for the first time

创建初始密码:

# 以OS中的postgres用户的身份打开psql客户端的postgres数据库用户
sudo -u postgres psql postgres

# 交互式输入密码
\password postgres

\q

允许本地连接登录: 需要修改/etc/postgresql/9.3/main/pg_hba.conf文件(其它系统类似):

将以下内容中的peer改为md5
local   all             all                                      peer

将以下内容的ident改为md5
host    all             all             ::1/128                 ident

允许远程连接登录: 需要修改/etc/postgresql/9.3/main/pg_hba.conf文件,其它系统类似:

将这句话
local   all             all                                      peer
改为这句话
host    all             all             0.0.0.0/32               trust

再修改/etc/postgresql/9.3/main/postgresql.conf

将listen_addresses取消注释并改为:
listen_addresses = '*'

同时在文件中确认下port设置的默认端口,以便之后客户端的正确连接。

保存退出后,重启postgresql:

sudo service postgresql restart

客户端连接

在服务器端正确设置好了密码、允许远程连接、默认端口后,GUI客户端就能正常连接了。

PSequel

image

进入Postgres客户端

这一步因为涉及权限问题,所以会复杂一些。

apt-get安装好后,会在系统中自动添加一个postgres用户。 image

我们只有在系统里用postgres帐户登录,然后才能有权限用客户端进行各种交互:

sudo su postgres

有两种用户权限方法来使用postgresql:

一般比较推荐第二个。

进入主目录后,就可以执行很多数据库服务器级的操作了,如:

# 列出当前所存在的所有「数据库」
$ sudo -u postgres psql -l

# 创建数据库
$ sudo -u postgres createdb 数据库名称

# 删除数据库
$ sudo -u postgres dropdb 数据库名称

# 进入Postgresql 的shell客户端,并对指定的数据库进行操作
$ sudo -u postgres psql 数据库名称

进入psql客户端提供的shell后,就可以真正的开始对数据库、数据表进行各种熟悉的操作了。 不过要注意,psql的shell命令与Linux系列的shell完全不同,连参数都是用\h这样的。

image

注意:

常用语句:

-- 查看当前版本号
SELECT version();

-- 查看现在时间
SELECT now();

-- 查看所有的数据库 List
\l

-- 进入某个数据库 Connect
\c DB-Name

-- 查看当前库中的「所有表格」 Define Tables
\dt

-- 查看某个表的详细信息 Define
\d table001

-- 导入外部的sql文本,并执行其中的所有命令 Import
\i db.sql

# 退出客户端 quit
\q

数据类型

参考官网:PostgreSQL Data Types

image

运算符

增删改查 CRUD

高级查询

solomonxie commented 5 years ago

Sqlite 使用

Sqlite 命令行客户端

只需下载官方提供的压缩包,无需编译,解压后直接用里面的二进制文件sqlite3即可进入shell:

# Download
mkdir -p ~/bin/sqlite
cd ~/bin/sqlite
wget https://www.sqlite.org/2018/sqlite-tools-osx-x86-3260000.zip && \
unzip sqlite-tools-osx-x86-3260000.zip

# Make symlink
sudo ln -s ~/bin/sqlite/sqlite-tools-osx-x86-3260000/sqlite3 /usr/local/bin/sqlite3

参考官方命令参考:Command Line Shell For SQLite

常用命令:

# 创建/进入 一个数据库
$ sqlite3 DB_Test

# 显示当前数据库中的所有表
sqlite> .tables

# 显示所有schemas
sqlite> .schema

# 显示某个表的schema
sqlite> .schema tb_Users
sqlite> .schema tb_*

# 显示当前的客户端偏好设定
sqllite> .show
        echo: off
         eqp: off
     explain: auto
     headers: off
        mode: list
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width:
    filename: test.sqlite

# 导入csv数据 (记得把.mode改回默认的list)
sqlite> .mode csv
sqlite> .import /home/pi/data.csv tb_Users

# 导出为csv数据
sqlite> .header on
sqlite> .mode csv
sqlite> .once /home/pi/OUT.csv
sqlite> SELECT * FROM tb_Users;
sqlite> .system /home/pi/OUT.csv
solomonxie commented 5 years ago

Redis等内存数据库的使用场景

solomonxie commented 5 years ago

❖ ORM: SQLAlchemy 初识

ORM: Object Relational Mapper.

目前Python有很多ORM工具可以将数据库映像为Python的Objects对象。 其中比较知名的有Django的ORM,SQLAlchemy, PostgreSQL等。 SQLAlchemy有更多的人维护,功能也比较齐全。所以一般是我们的首选项。

对于SQLAlchemy的使用者来说,只要你一开始连接上数据库,不管是Sqlite,MySQL还是什么,后面的处理方式完全一样。这种便利性也是它受欢迎的原因。

抛弃了传统的自己编织SQL语句、制作模型、连接数据库方式,SQLAlchemy直接把这些东西全包在黑盒里面,让我们完全不需要去管。连SQL-Injection注入这种东西也被它帮忙防范了。这样一来,可以说在连接数据库方面,帮我们节省了最少一半以上的代码。

甚至连数据查询,SQLAlchemy也代替了SQL语句,而使用了专门的类似MongoDB的Object.query.filter_by(name='Jason').all()这种方法。

安装:

# 安装sqlalchemy
$ pip install sqlalchemy

安装Drivers:

# Sqlite
# 不需要,Python自带

# MySQL
$ pip install pymysql

# Postgresql
$ pip install psycopg2

SQLAlchemy自身不带数据库driver,需要我们自己安装,并在连接时候指定。 而这些driver,实际上就是我们曾经手动连接数据库所用的包。而SQLAlchemy只是代替我们使用这些同样的包。

连接数据库

参考:Engine Configuration

标准格式:

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

创建一个sqlite的ORM引擎:

from sqlalchemy import create_engine

# 连接格式为:sqlite://<Hostname>/<path>
engine  = create_engine('sqlite:///foo.db', echo=True)

创建一个MySQL的ORM引擎:

from sqlalchemy import create_engine

# 连接格式为:dialect+driver://username:password@host:port/database
engine  = create_engine('mysql+pymysql://root:password123@localhost/db_test_01', echo=True)

创建PostgresQL的引擎(不同的driver):

# Driver: default
engine = create_engine('postgresql://user:password@127.0.0.1/mydatabase')

# Driver: psycopg2
engine = create_engine('postgresql+psycopg2://user:password@127.0.0.1/mydatabase')

数据库的位置(三斜杠为相对路径,四斜杠为绝对路径):

# 使用绝对路径的数据库文件(////),如/tmp/mydatabase.db
engine  = create_engine('sqlite:////tmp/mydatabase.db')

# 使用当前「执行位置」数据库文件(///或///./)
engine  = create_engine('sqlite:///mydatabase.db')

# 使用当前「执行位置」父级目录(///../)的数据库文件
engine  = create_engine('sqlite:///../mydatabase.db')

# 使用当前「脚本位置」的数据库文件
import os
cwd = os.path.split(os.path.realpath(__file__))[0]
engine  = create_engine('sqlite:///{}/mydatabase.db'.format(cwd))

Create Tables 创建表

注意:不同于SQL语句,SQLAlchemy中的表名是完全区分大小写的

创建一个Schema表(指单纯表,不包含ORM对象):

from sqlalchemy import create_engine, MetaData
from sqlalchemy import Table, Column
from sqlalchemy import Integer, String, ForeignKey

engine  = create_engine('mysql+pymysql://root:password123@localhost/db_test_01', echo=True)
metadata = MetaData(engine)

# 创建一个表
user_table = Table( 'tb_user', metadata,
        Column('id', Integer, primary_key=True),
        Column('name', String(50)),
        Column('fullname', String(100))
)

# 让改动生效
metadata.create_all()

创建一个ORM对象(包括表):

# 导入表格创建引擎
from sqlalchemy import create_engine
# 导入列格式
from sqlalchemy import Column, Integer, String, ForeignKey
# 导入创建ORM模型相关
from sqlalchemy.ext.declarative import declarative_base

Base  = declarative_base()

class User(Base):
    __tablename__ = 'tb_Person'

    id = Column('id', Integer, primary_key=True)
    username = Column('username', String, unique=True)

engine = create_engine('sqlite:///test.sqlite', echo=True)
User.__table__.create( engine )

# 或生成所有绑定到Base类的子ORM
Base.metadata.create_all( bind=engine )

用普通表Table和ORM对象创建的表有什么不同? 他们在数据库中创建的,是完全相同的表!唯一区别是,Table创建的不包含ORM对象,也就是不提供让你直接操作Python对象的功能。 这么做的好处是,有很多只是关联作用的表,没有必要生成ORM对象。

删除数据库中的表

# engine = ...
# Base = ...

# 逐个ORM对象删除对应的表,如User类
User.__table__.drop(engine)

# 删除全部表
Base.metadata.drop_all(engine)

设计或调试过程中,我们经常要频繁改动表格,所以有必要在创建表格前把测试数据库中的表都清除掉,再创建新的定义。

Insertion 插入数据

将数据添加到数据库:

# ...
# 导入session相关(用于添加数据)
from sqlalchemy.orm import sessionmaker, relationship

user = User()
user.id = 1
user.username = 'Jason'

Session = sessionmaker(bind=engine)
session = Session()
session.add(user)
session.commit()
session.close()

注意:这里的session和网站上的session概念有点不一样。这里是用来commit提交数据库变动的工具。

批量添加数据(向add_all()传入列表):

session.add_all( [user1, user2, user3] )

添加每条数据的时候自动flush():

session = sessionmaker(bind=engine, autoflush=True)

autoflush是在每次session.add()自动执行session.flush(),即在插入数据库之前就在内存中生成所有对象的动态数据(如主键ID等)。一般默认是选false,因为会影响效率。最好是需要的时候,才手动执行session.flush()

具体缘由,看下一节“数据生效”。

Take effect 数据生效

SQLAlchemy中的create_all()session.commit()都是直接让python文件中定义的对象在数据库中生效的语句。在此之前,无论怎么定义,数据都是在内存中,而没有在数据库中的。

注意区分:

这两个的顺序,当然是先创建表格,再插入数据。

只是,如果我们知道了这个原理,在编码中才能比较运用自由。比如,连create_engine()创建引擎,我们都可以在后面定义,而没必要非得写在文件头,即所有的ORM定义之前。 create_engine只要定义在所有ORM类和Schema表之后即可。

此后,我们再开始进行数据插入工作,也就利用到了session。

session过程中呢,我们也会遇到互相引用主键外键ID的情况。但是注意,这时候因为还没有使用最终的session.commit()真正提交数据到数据库中,这些ID是没有值的。 解决办法就是利用内置的方法session.flush(),将session中已添加的所有对象填充好数据,但是这时候还没有提交到数据库,只是我们内部可以正常访问各种ID了。

更新/删除数据

更新:

# Get a row of data
me = session.query(User).filter_by(username='Jason').first()

# Method 1:
me.age += 1
session.commit()

# Method 2:
session.query().filter(
    User.username == 'Jason'
).update(
    {"age": (User.age +1)}
)
session.commit()

# Method 3:
setattr(user, 'age', user.age+1)
session.commit()

Get Primary Key Value 获取主键值

#sqlalchemy can't get primary key, #sqlalchemy 如何获得主键的值

这个问题花了我很多时间探索查询,不得其解,才明白原来是很显然的事。

参考思否:SQLAlchemy中返回新插入数据的id?

虽然在没有用session或engine插入数据之前,我们可以直接浏览从ORM创建的对象中的属性值。 但是这个时候无论如何都获取不到primar_key主键列的值。

因为这时候主键还没有插入数据库,作为动态的值,在数据库没生效之前也就为None。

为什么需要获取value of primary_key?考虑如下这些场景:

那么该怎么获取主键ID呢?

再参考Stackoverflow:sqlalchemy flush() and get inserted id? 再参考:sqlalchemy获取插入的id 再参考:Sqlalchemy;将主键设置为预先存在的数据库表(不使用sqlite)

如果要想在插入数据之前就获取主键等动态列的值,那么有这几种方法:

推荐做法如下: image

即每次新创建对象后,立刻session.add(..),然后立刻session.flush(),全部都添加好的文末,再session.commit().

Query 查询

注意:query是通过session进行的,也就是必须在session.commit()之后才能进行查询,否则会报错。

这里将的query查询,指的都是在插入到数据库生效之后。理解这个很重要,因为在对象未插入到数据库之前,很多主键、外键等内容都是不存在的,也就无法查询到。

参考:pythonsheets - Object Relational basic query

查询数据:

session.commit()
# ...

users = session.query(User).all()
# 返回的是多个User类的对象:>>> [ <User 1>, <User 2>, .... ]

for u in users:
    print(u.id, u.username)

常用查询方法:

# 获取某ORM中数据 .query(ORM类名)
>>> session.query( User ).all()     # All rows of data
>>> session.query( User ).first()    # First row of data as an object

# 查询结果排序 .order_by(类名.列名)
>>> session.query(User).order_by( User.birth ).all()

# 筛选结果 .filter( True/False 表达式 )
>>> session.query(User).filter( User.name != 'Jason' ).all()
>>> session.query(User).filter( User.name.like('%ed%') ).all()    # Fuzzy search
>>> session.query(User).filter( User.id in [1, 2, 3] ).all()    # IN
>>> session.query(User).filter( ~ User.id in [4, 5, 6] ).all()   # NOT IN
>>> session.query(User).filter( User.school == 'MIT', User.age < 24 ).first()   # AND
>>> session.query(User).filter( _or(User.school == 'MIT', User.age < 24) ).first()   # OR

Sqlalchemy Core (手动执行)

Sqlalchemy除了用ORM去访问数据库外,还可以用Core 像别的Driver驱动器一样去手动执行SQL语句访问数据库,而且速度会快几倍。

执行查询

from sqlalchemy import create_engine
engine = create_engine('postgresql+psycopg2://user:password@127.0.0.1:5432/mydatabase')
sql = 'SELECT name, age FROM persons'
with engine.connect() as conn:
    result = conn.execute(sql)
    for name, age in result:
        print(name, age)

执行增删改

solomonxie commented 5 years ago

SQL表关系

两表之间,可以有如下对应关系:

solomonxie commented 5 years ago

❖ SQLAlchemy中的表关系 Table Relationships

SQL中的表关系一直是比较难理解的地方。同样SQLAlchemy也对他们做了实现,如果对SQL中的表关系理解透彻的话,这里也可以更容易理解。

为什么需要定义Relationships

在相关联的表中,我们可以不创建表关联的定义,而只是单纯互相引用id即可。但是,查询和使用起来就要麻烦很多:

#给定参数User.name,获取该user的addresses
# 参考知乎:https://www.zhihu.com/question/38456789/answer/90470689

def get_addresses_from_user(user_name):
    user = session.query(User).filter_by(name=user_name).first()
    addresses = session.query(Address).filter_by(user_id=user.id).all()
    return addresses

可以看到,这样的效率非常低。 好在原生的SQL就有relationship设置,SQLAlchemy将其引入到了ORM模型中。

它可以让我们只在表中声明表之间的关系,之后每次使用就完全无需手动交叉搜索,而是像对待一个表中的数据一样直接使用。

为什么不需要定义relationships?

经过实践返回来加的这一节:实践中的SQLAlchemy的"relationship"在一定程度上反而导致了整体表关联关系的极大复杂化,还有效率的极其低下。

如果你的数据库只有两个表的话,那么relationship随便定义随便用。如果只有几百条数据的话,那么也请随便玩。

但是,当数据库中有数十个表以上,单个关联层级就多过三个表以上层层关联,而且各个数据量以万为单位。那么,"relationship"会把整个人都搞垮,简直还不如手写SQL语句清晰好理解,并且效率也差在了秒级与毫秒级的区别上。

SQLAlchemy只能很轻松handle Many to Many,但是如果是常见的Many to Many to Many,或者是Many to Many to Many to Many,那简直就是噩梦。

但是,我们都知道,项目做到一定程度,都会摆脱不了ORM。无论是自己造轮子还是用别人的,无论起点是不是纯SQL,终点都是ORM。 那么该怎么办呢?

网友的建议是: 用SQLAlchemy建立各种ORM类对象,不要用内置的关联,直接在查询的时候手动SQL语句!

经过实践,我的建议是:

relationship() 函数

参考官方文档:Linking Relationships with Backref

SQLAlchemy创建表关联时,使用的是relationshi()这个函数。 它返回的是一个类的属性,比如father类的children属性。但是,它实际上并没有在father表中创建任何叫children的列,而是自动帮你到相关联的children表中去找数据,让你用起来感觉没有差别而已。 这是非常方便的!

relationship()这个函数的参数非常多,每一个参数都有很多内容需要理解。因为所有的表关联的形态,都是在这个函数里面定义的。 以下分别讲解。

Reference 正向引用

传统的方法,是在父类中定义一个关系 relationship或叫正向引用 Reference,子类只需定义一个外键。比如:

class Father(..): 
    id = Column(..)
    children = relationship('Child')

class Child(..):
    father_id = Column( Integer, ForeignKey('father.id') )

# 添加数据
daddy = Father()
jason = Child()
emma = Child()

# 将孩子挂到父亲名下
daddy.children.append(jason)
daddy.children.append(emma)

这样当每次我们使用father.children的时候,就会自动返回与这个father相关联的所有children了。

Back Reference 反向引用

单纯定义的relationship('子类名')只是一个正向引用,也就是只能让父类调用子对象。反过来,如果要问children他们的父亲是谁,就不行了。

所以,我们还需要一个反向引用 (Back Reference)的声明,让子对象能够知道父对象是谁。

定义方式是在父类的relationship(..)中加一个参数backref

class Father(..): 
    children = relationship( 'Child', backref='parent' )

注意:

  1. backref参数里面使用的随便写,主要用于之后子类的引用。
  2. backref参数是双向性的,意思是,只需要在父类中声明一次,那么父⇄子的双向关系就确立了,不用再去子类中写一遍。

这时候,我们在添加就可以这样互相调用了:

>>> Jason = Child()
>>> print( Jason.parent )
 <__main__.Father object at 0x10222f860>

Bidirectional & Unidirectional Back Reference 双向和单向的反向引用

后来,SQLAlchemy发现这种只在一边定义双向性backref的方法有点不太直观,所以又添加了另一个参数back_populates参数,而这个back_populates参数是单向性的,也就是说: 你要确立双方向关系就必须在两边的类中都声明一遍。这样比较直观。

可以把backrefback_populates都读为"as",这样就好记忆了。

比如:

class Father(..): 
    id = Column(..)
    children = relationship( 'Child', back_populates='parent' )

class Child(..):
    father_id = Column( Integer, ForeignKey('father.id') )
    parent = relationship( 'Father', back_populates='children' )

注意:back_populates要求父类子类的关系名称必须严格“对称”:

这样一来利用反向引用参数创建的关系就确立了。但是注意, 无论用backref还是back_populates创建的关联,如果我们必须要为父子对象添加对象间的关联才能引用,否则谁也不知道谁是谁的父亲、儿子:

>>> daddy = Father()
>>> son = Child()
>>> daughter = Child()

>>> daddy.children
[]
>>> son.parent
None

>>> daddy.children.append( son )
>>> daddy.children.append( daughter )

>>> daddy.children
[ <Child ...>, <Child ...> ]

>>> son.parent
<Father ...>

另外:上面添加父子关系的时候,不光可以用daddy.children.append, 还可以在声明子对象的时候确定:son = Child( parent=daddy )

反向引用参数对比:

SQL中的表关系

对应关系:

One to Many 一对多

建立一个One-to-Many的多表关联:

# ...

class Person(Base):
    id = Column(...)
    name = Column(...)
    pets = relationship('Pet', backref='owner')
    # 上面这句是添加一关联,而不是实际的列
    # 注意:1. 'Pet'是大写开头,因为指向了Python类,而不是数据库中表
    # 2. backref是指建立一个不存在于数据库的“假列”,
    # 用于添加数据时候指认关联对象,代替传统id指定

class Pet(Base):
    id = Column(...)
    name = Column(...)
    owner_id = Column(Integer, ForeignKey('person.id')
    # 上面这句添加了一个外键,
    # 注意外键的'person'是数据库中的表名,而不是class类名,所以用小写以区分

创建好关联的表以后,我们就可以直接插入数据了。注意,插入带关联的数据也和SQL插入有些不同:

#...

# 添加主人
andy = Person(name='Andrew')
session.add( andy )
seession.commit()

# 添加狗
pp01 = Pet(name='Puppy', owner=andy)
pp02 = Pet(name='Puppy', owner=andy)
# 注意这句话中,owner是刚才主表中注册relationship中的backref指定的参数名,
# 传给owner的是主表的一个Python实例化对象,而不是什么id
# 看起来复杂,实际上sqlalchemy可以自动取出object的id然后匹配副表中的foreignkey。

session.add(pp01)
session.add(pp02)
session.commit()

print( andy.pets )
# >>> [<Pet 1>, <Pet, 2>]
# 返回的是两个Pet对象

print( pp01.owner )
# >>> <Person 'Andrew'>
# 同样,副表中利用owner这个backref定义的假列,返回的是Person对象。

Many to One 多对一

比如职工和公司的关系就是多对一。这和公司与职工对一对多有什么区别? 区别其实是在SQL语句中的:多对一的关联关系,是在多的一方的表中定义,一的一方表中没有任何关系定义:

class Company(...):
    id = Column(...)

class Employee(..):
    id = Column(...)
    company_id = Column( ..., ForeignKey('company.id') )
    company = relationship("Company")

Many to Many 多对多

多对多的关系也很常见,比如User和Radio的关系: 一个Radio可以有多个用户可以订阅,一个用户可以订阅多个Radio。

SQL中处理多对多的关系时,是把多对多分拆成两个一对多关系。做法是:新创建一个表,专门存储映射关系。原本的两个表无需设置任何外键。

SQLAlchemy的实践中,也和SQL中的做法一样。

注意:既然有了专门的Mapping映射表,那么两个表各自就不需要注册任何ForeignKey外键了。

示例:

# 做出一个专门的表,存储映射关系
# 注意:1. 这个表中两个"id"都不是主键,因为是多对多的关系,所以二者都可以有多条数据。
#  2. 映射表必须在前面定义,否则后面的类引用时,编译器会找不到
radio_users = Table('radio_users', Base.metadata,
    Column('whatever_name1', Integer, ForeignKey('radios.id')),
    Column('whatever_name2', Integer, ForeignKey('users.id'))
)

# 定义两个ORM对象:
class Radio(Base):
    __tablename__ = 'radios'

    rid = Column('id', Integer, primary_key=True)
    followers = relationship('User',
        secondary=radio_users,     # `secondary`是专门用来指明映射表的
        back_populates='subscriptions'    # 这个值要对应另一个类的属性名
    )

class User(Base):
    __tablename__ = 'users'

    uid = Column('id', Integer, primary_key=True)
    subscriptions = relationship('Radio',
        secondary=radio_users,
        back_populates='followers'   # 这个值要对应另一个类的属性名
    )

其中,secondary是专门用来指明映射表的。

注意:多对多的时候我们也可以用backref参数来添加互相引用。但是这种方法太不直观了,容易产生混乱。所以这里建议用back_populates参数,在两方都添加引用,表现一种平行地位,方便理解。

然后插入数据时候是这么用:

r1 = Radio()
r2 = Radio()
r3 = Radio()

u1 = User()
u2 = User()
u3 = User()

# 添加对象间的关联
r1.followers += [u1, u2, u3]

# 反过来添加也一样
u1.subscriptions += [r2, r3]

Many to Many to Many 多对多对多 (深层关联)

深层关联,为了避免理解困难,最笨的方法就是简单的使用外键ID,然后手动搜索另一个表的对应ID。 image

但是SQLAlchemy也可以实现这种深层关联:

solomonxie commented 5 years ago

❖ SQLAlchemy的Lazy Load 加载参数 [DRAFT]

参考:flask-sqlalchemy中的lazy的解释

SQLAlchemy的relationship( ..., lazy='??' )方法中的lazy参数一直是初学最容易困扰的地方。

Lazy Load Methods是SQLAlchemy为多表关联而定义的一系列加载方法。为lazy参数选择什么值,决定了 SQLAlchemy 什么时候从数据库中加载数据。每种方法的对应着SQL语句中多表关联的一种写法,所以优缺点、效率高低各有不同。

lazy参数的可选方法有:

下面用SchoolStudents的实例来看各种方法的不同。

假设定义两个ORM类:

class School(..):
    id = Column(..)
    students = relationship( 'Student', backref='school' )

class Student(..):
    id = Column(..)
    school_id = Column(.., ForeignKey('school.id') )

上例中我们建立了一个普通的两表关联:students = relationship( 'Student', backref='school' )。 默认情况下,参数lazy为select,我们不写也可以)。 也就是说,如果定义lazy='select',那么当我们要进行搜索引用时(假设表中已有数据):

>>> school_01 = School.query.first()  # 随便获取一个数据库中已有的school
>>> school_01.students
[ <Student: u'test'>, <Student: u'test2'>, <Student: u'test3'> ]

可以看到,lazy='select'会简单直接的返回所有相关联的数据。 但是,如果数据量非常大:比如百万级,这种全部返回就不理智了,因为会大量侵占内存。 所以我们可以选择lazy='dynamic',即只返回一个query查询对象,供你手动加条件查询,比如query.all()query.filter()等。

假设我们将之前的定义改为:students = db.relationship('Student', backref='_class', lazy="dynamic")。那么:

>>> school_01.students
<sqlalchemy.orm.dynamic.AppenderBaseQuery object at 0x7f007d2e8ed0>

>>> print( school_01.students )
SELECT students.id AS students_id, students.name AS students_name
FROM students, registrations
WHERE :param_1 = registrations.class_id AND students.id = registrations.student_id

>>> school_01.students.all()
[ <Student: u'test'>, <Student: u'test2'>, <Student: u'test3'> ]

可以看到, 执行school_01.students返回的只是一个query对象,甚至说只是返回了一条SQL 语句,就是没有具体数据。可以想像这个消耗的时间相当于0了。 而如果lazy=select 或者 joined均是直接返回结果。 

需要注意的是, lazy="dynamic"只可以用在一对多和多对对关系中,不可以用在一对一和多对一中。

这样也合理:如果返回结果很少的话,就没必要延迟加载数据了。

backref(..., lazy=...) 反向引用的lazy加载

直接给relationship(.., lazy='??'),只是给正向引用设置加载方法。 实际上反向引用也是可以设置lazy加载方法的。 做法就是:使用backref(..)函数:

    students = relationship(..., lazy='..', backref=backref('Student, lazy='dynamic') )

可以看到,backref(..)函数返回的是一个backref参数专用的值,在这里面可以指定反向引用的加载方法。

solomonxie commented 5 years ago

❖ SQLAlchemy的Join表连接

参考Stackoverflow:sqlalchemy: how to join several tables by one query?

直接条件查询

假设我们要用直接条件查询的方式连接三个表:

SELECT
    user.email,
    user.name,
    document.name,
    documents_permissions.readAllowed,
    documents_permissions.writeAllowed
FROM
    user, document, documents_permissions
WHERE
    user.email = "user@email.com";

对应的SQLAlchemy的查询语句为:

session.query(
    User, 
    Document, 
    DocumentsPermissions
).filter(
    User.email == Document.author
).filter(
    Document.name == DocumentsPermissions.document
).filter(
    User.email == "user@email.com"
).all()

Join

SELECT 'all the columns'
FROM user
JOIN document ON document.author_id = user.id AND document.author == User.email
JOIN document_permissions ON document_permissions.document_id = document.id 
    AND document_permissions.document = document.name

Then you should do something along the lines of:

session.query(
    User
).join(
    Document
).join(
    DocumentsPermissions
).filter(
    User.email == "user@email.com"
).all()

One note about that...

query.join(Address, User.id==Address.user_id) # explicit condition
query.join(User.addresses)                    # specify relationship from left to right
query.join(Address, User.addresses)           # same, with explicit target
query.join('addresses')                       # same, using a string

For more information, visit the docs.

Inner Join 内连接

如果我们需要定义一个Many-to-Many多对多的关系,我们知道定义这种关系,必须在两表之间设计一个Mapper中间表来保存所有的映射关系。 假设这里的关系是Person <-> Course

from myORMs import Person, Course, Mapper

# Define A Many-to-Many relationship
query = session.query(
    Person
).join(
    Mapper, Mapper.child_id == Person.id
).join(
    Course, Mapper.parent_id == Course.id
).filter(
    Person.name == 'Jason'
)

Join same table multiple times

参考官方:ORM-Specific Query Constructs - sqlalchemy.orm.aliased

正常情况下我们不需要,如果我们想把这三个表全部一起查询、达到同时显示某个Person的School信息和City信息怎么办?

如果我们有多种Many-to-Many多对多关联的表,所以我们必须要在每一对关联表中间加设一个Mapper作为映射多对多关系。 因为用来用去都是一样,我们完全可以用一个mapper包括所有的映射关系,只是多加一个字段予以区分即可。 如果要这样做的话,就免不了要设置别名,在SQL中我们可以通过.. AS ..轻松做到,SQLAlchemy其实也不复杂,只需要用一个aliased()方法即可轻松达到同样的目的,而且不会影响效率。

注意区分alias()aliased()不同

假设我们有4个表、2对多对多的关系,Person <-> Course, Person <-> Major。然后我们用Mapper统一进行关系映射。

from sqlalchemy.orm import aliased

from myORMs import Person, Course, Major, Mapper

PersonCourse = aliased( Mapper )
PersonMajor   = aliased( Mapper )

query = session.query(
    Person
).join(
    PersonCourse, PersonCourse.child_id == Person.id
).join(
    Course, PersonCourse.parent_id == Course.id
).join(
    PersonMajor, PersonMajor.child_id == Person.id
).join(
    Major, PersonMajor.parent_id == Major.id
).filter(
    Person.name == 'Jason'
)
solomonxie commented 5 years ago

❖ SQLAlchemy的类继承、抽象类

Python中的类继承是相当容易的,但是在SQLAlchemy中却不能直接用Python类继承完成,还要多加一些设置。

网上关于这个东西,东说西说的非常多,甚至官网都没有把最简单的解决方案po出来,取而代之的是非常复杂的Inheritance Configuration

首先说最简单的方案,来自Stackoverflow,亲测完美有效,最符合Python类继承。

参考:Sqlalchemy: avoiding multiple inheritance and having abstract base class

正解

在这里,我们称这个方法为__abstract__方法:

Base = declarative_base()

class CommonRoutines(Base):
    __abstract__ = True

    id = Column(Integer, primary_key=True)

    def __init__(self):
        # ...

class Foo(CommonRoutines):
    __tablename__ = 'foo'

    name = Column(...)

    def __init__(self, name):
        super().__init__()
        self.name = name
        # ...

也就是说,抽象类中只要用__abstract__ = True代替__tablename__即可完成一切工作,其它一切都和Python内置的类继承一摸一样了。

继承中的类方法和静态方法

SQLAlchemy的ORM继承,在classmethodstaticmethod继承是和Python OOP面向对象的继承方案一致的。

也就是说:

继承中的外键

奇怪的是,SQLAlchemy定义的ORM,在继承父级ORM时候,Foreign Key外键是不能继承的,它强制要求在子类中重新定义。 参考官方文档:Mapping Class Inheritance Hierarchies 建议直接用Ctrl-f搜索"foreign`关键字,就能看到官方在继承时,也都要重新定义一遍外键。

再参考:SQLAlchemy Inheritance

class Parent(Base):
    __abstract__ = True

    id = Column('id', Integer, primary_key=True)
    name = Column('name', String)
    age = Column('age', String)
    fk = Column('fk', Integer, ForeignKey('anotherTable.id'), primary_key=True)

class Son(Parent):
    __tablename__ = 'son'

    fk = Column('fk', Integer, ForeignKey('anotherTable.id'), primary_key=True)

其它继承方案

如果参考别人的方案、官网的方案,会让你晕头转向。 为了避免重复参考别人的东西,这里贴上一些不是解决方案的解决方案

declarative_base(cls=XX)方法:

class CommonBase(object):
    @classmethod
    def somecommonaction(cls):
        # body here

Base = declarative_base(cls=CommonBase)

class Table1(Base):
    # __tablename__ & Table1 specific fields here

class Table2(Base):
     # __tablename__ & Table2 specific fields here

这样的缺点是,很难看清继承关系。

官方的__mapper_args__方法:

class Person(Base):
    __tablename__ = 'people'
    id = Column(Integer, primary_key=True)
    discriminator = Column('type', String(50))
    __mapper_args__ = {'polymorphic_on': discriminator}

class Engineer(Person):
    __tablename__ = 'engineers'
    __mapper_args__ = {'polymorphic_identity': 'engineer'}
    id = Column(Integer, ForeignKey('people.id'), primary_key=True)
    primary_language = Column(String(50))

可以看出,这个必须在父子类都中分别定义难懂的__mapper_args__属性。这还不算完,官网中还说各种映射需要不同的复杂设置。有兴趣可参考官网:https://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/inheritance.html

solomonxie commented 5 years ago

❖ SQLAlchemy的进阶小操作汇总 [DRAFT]

根据表名获取对应的ORM类引用

def get_class_by_tablename(tablename):
    for cls in Base._decl_class_registry.values():
        if hasattr(cls, '__table__') and cls.__table__.fullname == tablename:
            return cls

print( get_class_by_tablename('tb_Tracks') )

检验是否已存在数据再插入

参考:SQLAlchemy commit(), flush(), expire(), refresh(), merge() - what's the difference?

以下方法先称为session.merge()方法, 它能极快的完成插入之前的数据是否重复的检验:

user1 = User.query.get(1)
user1.name  # ==> 'user1'

new_user = User(user_id=1)  # ==> a second in-memory object with the same key!
new_user.name = 'user2'
user1.name  # ==> 'user1'. Without merging, user1 doesn't know it is the same as new_user

db.session.merge(new_user)
user1.name  # ==> 'user2'. Now updated in memory. Note not yet updated in db, needs flush() and commit()

merge()发生冲突

我们知道虽然merge()可以解决数据库中的冲突问题,但是当我们在还没进入数据库之前,如果批量merge,如果数据中有重复(包括主键重复),都会产生异常,停止程序运行。 报错信息一般如下:

image

很“抱歉”的是,SQLAlchemy没有内置的解决办法,想想可能也不会有。 因为逻辑上来说,你在插入数据库之前,同时准备了多条数据,其中有一些重复的条目,比如有两个主键相同但是其它内容不同的条目,那么问题来了:SQLAlchemy怎么替你选择让这两个条目的哪一个插入到数据库中? 答案是:它不能! 选择哪条数据进入数据库这么重要的选择权必须在我们手里,机器不能随便决定。

所以,主要解决方案有两个:

手动筛选的话,目前只能用最古老的for循环逐个查找。因为SQlalchemy的ORM对象特殊,不能用python的魔法方法__hash____eq__来实现快速筛选。

定义复合主键

有时候表中不是以单一的主键来标示一条数据的唯一定位,而是以组合主键的方式。比如订单表中的订单号和商品号组合,两者单独的话都会有重复数据,组合起来才能定位到一个具体销售出的商品。

SQLAlchemy定义组合主键的有两种方法:直接定义primarykey,或使用PrimaryKeyConstraint

注意:

方法一:直接定义

class User(Base):
    field1 = Column(Integer, primary_key=True)
    field2 = Column(Integer, primary_key=True)

方法二:使用PrimaryKeyConstraint(需要导入相关)

class User(Base):
    field1 = Column(Integer)
    field2 = Column(Integer)
    __table_args__ = (
        PrimaryKeyConstraint('field2', 'field1'),
        {},
    )

ORM类多文件定义

因为要想各个ORM类互相沟通,互相引用ID,首先要保证他们在一个Base之中才行。 所以,最简单的方法是在一个单独文件中定义Base = declarative_base(),然后所有的ORM文件都引用统一个Base,这样就挂钩在一起了。

同理,为了方便,我们也可以把engine都放进来:

common_base.py:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

engine = create_engine('sqlite:///:memory:', echo=True)

然后在另一个文件ORM.py中直接引用:

from common_base import Base, engine

这样一来,以后所有ORM文件就可以正常的互相import了。 注意:不要把session放进来,因为session最好用完随时close()。同理,使用时,也要注意engine的使用,不要长时间连接而不关闭。

只创建一个表/ORM对象

一般我们用Base.metadata.create_all()时候,是让所有绑定到Base类上的子ORM全都创建到数据库中。

但在测试时,我们有时需要只创建一个表。

方法如下:

class Person(Base):
    #.....

Person.__table__.create( engine )
solomonxie commented 5 years ago

UUID (GUID) 理解

参考:关于UUID的二三事

UUID,Universally Unique Identifier的缩写,UUID出现的目的,是为了让分布式系统可以不借助中心节点,就可以生成UUID来标识一些唯一的信息。 GUID,是Globally Unique Identifier的缩写,跟UUID是同一个东西,只是来源于微软。

1个UUID是1个16字节(128位)的数字;为了方便阅读,通常将UUID表示成如下的方式: 123e4567-e89b-12d3-a456-426655440000 它被-符号分为五段,形式为8-4-4-4-12的32个字符。 其中的字母是16进制表示,大小写无关。

UUID的标准格式:xxxxxxxx-xxxx-Axxx-Bxxx-xxxxxxxxxxxx A那个位置,代表版本号,由于UUID的标准实现有5个版本,所以只会是1,2,3,4,5 B那个位置,只会是8,9,a,b

UUID在发展过程中,为了适应不同的需要,产生了5个版本:

Python中生成UUID

参考:Python——uuid

>>> import uuid

>>> uuid.uuid1()
UUID('753be314-0512-11e9-9b06-d4619d2b8628')

>>>  uuid.uuid3(uuid.NAMESPACE_DNS, 'i am a name')
UUID('a7345f46-a3f1-33b7-99ae-a5e88194787d')

>>> uuid.uuid4()
UUID('fa9e34cc-2b31-41f0-8481-f703ce9a95b5')

>>> uuid.uuid5(uuid.NAMESPACE_DNS, 'i am a name')
UUID('1fd2308e-d846-5875-9106-9f6da736a562')

Python中UUID可以用int十进制,或hex十六进制显示,没有-分隔符用起来更方便。

>>> uuid.uuid1().int
73221012087113356936998119716747445800

>>> uuid.uuid1().hex
'6e574a3e051211e99b06d4619d2b8628'

显示UUID的“字段”,即-分隔符分开的各个项:

>>> uuid.uuid1().fields
(216362062, 1299, 4585, 155, 6, 233515713791528)

UUID会不会重复?

参考知乎:UUID是如何保证唯一性的?

如果是基于mac地址等多元素组合生成的UUID,那么基本上可以保证全球所有信息中的唯一性。

image

solomonxie commented 5 years ago

URI在数据库中的应用

由URI代替ID作为Primary Key主键,由很多的好处。

image

使用场景?目前我最高兴的是,在有些表需要“组合主键”的时候,直接合并为一个URI作为单主键即可。 另一方面,由于Type类型也放到了URI中,很多表就可以合成一个表了。

solomonxie commented 5 years ago

❖ “动态外键” 或——放弃外键 [DRAFT]

其实我们有很多的动态外键需求,因为当表格关系达到10+的话,很容易就产生大量数据冗余。要排除数据重复,那么就要“灵巧的”设计映射关系,那么就少不了有多主键或多映射表。 那么这种时候就必然被“固定外键”所捆绑。

自己思考到这一层后,想去网上看看。结果发现实践开发过程中,放弃外键已经成了主流。约束全都在程序里执行。的确!只要放弃外键这个概念,似乎数据库的一切都变得轻松了起来。

参考:Is it possible to have an dynamic foreign key, and what is the best/correct to do so? 参考:互联网开发中不用外键到底是个什么意思? 参考:知乎 大家设计数据库时使用外键吗? 参考:google - generic FKs

solomonxie commented 5 years ago

SQLAlchemy的字段default默认值

我们都知道:

age = Column('age', Integer, default=18)

这样的字段定义方式,即字段age默认值为18。

但是SQLAlchemy中,这个定义会出现一个问题:即默认值只有插入到数据库中才能生效。同样在运行中时,我们如果不用session.query(..)的方法,是无法获得这个值的,也即是说obj.age返回None。 即使session.commit()session.flush()也没用。有人说的ColumnDefault(), server_default=..也都没用。

因为,default只会影响数据库,而不存留在ORM对象里。 所以,除了一些比较麻烦的hack外,还是建议直接给ORM对象的这个column字段显式赋值,比如obj = MyORM(age=18),这样就能获取obj.age了。

参考:Default Objects API 参考:How to apply Column defaults before a commit in sqlalchemy

solomonxie commented 5 years ago

❖ SQLAlchemy 中的Group By用法

这里我们要用到的query对象的方法:

我们需要额外导入的方法: from sqlalchemy import func

func方法主要用来做统计,映射到sql语句中具体的统计方法,如:

func方法的格式为:func('字段名').label('显示名')

SQL语句的用法如下:

SELECT school, COUNT(*) AS c FROM persons WHERE gender="male" GROUP BY age

SQLAlchemy中如下:

from sqlalchemy import func

results = sessin.query( Person.school, func.count('*').label('c') ).filter(
    Person.gender=='male'
).group_by( Person.age )

筛选

SQL中针对Group By还可以再进一步筛选,但是要用另一个关键词Having

SQL语句的用法如下:

SELECT school, COUNT(*) AS c FROM persons WHERE gender="male" GROUP BY age HAVING c >1

SQLAlchemy中如下:

nums = func.count('*').label('c')

results = sessin.query( Person.school, nums ).filter(
    Person.gender=='male'
).group_by(
    Person.age
).having(
    nums > 10
)
solomonxie commented 5 years ago

SQLAlchemy exists 判断某条数据是否存在

参考:https://stackoverflow.com/questions/6587879/how-to-elegantly-check-the-existence-of-an-object-instance-variable-and-simultan

from sqlalchemy import exists

it_exists = Session.query(
    exists().where( SomeObject.field==value )
).scalar()

然后会返回True或False。

多选择条件的较复杂判断:

query = session.query(Users).filter(
    Users.name.in_( ['Jack', 'Bob', 'Sandy'] ),
    Users.age == 18
)

# Below will return True or False
at_least_one_user_exists = session.query(
    query.exists()
).scalar()

参考:Calling exists() in sqlalchemy with multiple values in python

solomonxie commented 5 years ago

❖ SQLAlchemy批量插入数据

SQLalchemy的插入数据不止有session.add()然后session.commit()而已。 实际上,以上做法是最慢的,尤其是面对批量数据插入的时候。

目前,批量插入的方法有:

参考:Optimize Inserts Using SQLAlchemy

假设插入最简单的数据10万条,花费时间如下:

SA ORM - total time: 12.31 seconds
SA ORM with PK - total time: 7.57 seconds
SA ORM Bulk insert - total time: 0.78 seconds
SA Core - total time: 0.09 seconds
Direct sqlite3 - total time: 0.46 seconds

明显最快的是Core,和其它选项完全不在一个数量级上。 速度从快到慢依次是:Core > Direct > ORM Bulk insert > ORM with PK > ORM

假设我们定义了一个简单ORM类:

class Person(Base):
    id = Column(....., primary_key=True)
    name = Column(...)

ORM inserts

people = [ Person( name='NAME-{}'.format(i) ) for i in range(100000) ]

for p in people:
    session.add( o )
session.commit()

ORM inserts with pre-computed Primary Key

people = [ Person( id=i, name='NAME-{}'.format(i) ) for i in range(100000) ]

for p in people:
    session.add( p )
session.commit()

ORM Bulk inserts

people_d = [ dict( name='NAME-{}'.format(i) ) for i in range(100000) ]

session.bulk_insert_mappings( Person, people_d )

SQLAlchemy Core

people_d = [ dict( name='NAME-{}'.format(i) ) for i in range(100000) ]

engine.execute( Person.__table__.insert(), people_d )

Sqlite3

(Won't be discussed here.)

solomonxie commented 5 years ago

❖ SQLAlchemy Core: 执行原生SQL语句

查询:执行固定SQL语句

engine = create_engine('........')
conn = engine.connect()
records = con.execute('SELECT title, author FROM book')
for title, author in records:
    print(title, author)
conn.close()

关于fetchone()和fetchall()

我们可以执行conn.execute('..').fetchone().fetchall()。 前者只返回数据库中第一条,后者一次性从数据库中取出所有条目(效率较低)。

建议不要用fetchall(),取而代之的是iterate迭代式:for r in con.execute('..'):这种方法。

查询:指针查询

engine = create_engine('.......')
conn = engine.raw_connection()
cur = conn.cursor()
cur.execute('SELECT word, weight FROM sensitive_word')
for word, weight in cur:
    print(word, weight)
cur.close()
conn.close()

查询:执行带parameters参数的SQL语句

为了防止SQL-Injection注入,最好不要直接把外部获取的参数加入到SQL字符串中。

SQLAlchemy有一些专门的方法向SQL加入参数:

text()方法:

from sqlalchemy import text

sql = "SELECT id, name FROM users WHERE id=:id"
tsql = text(sql)

with engine.connect() as conn:
    records = conn.execute(tsql, id=123)

    for r in records:
        print( r )

更新:执行原生的SQL语句

session.execute(
    "UPDATE client SET musicVol = :mv, messageVol = :ml",
    {'mv': music_volume, 'ml': message_volume}
)

插入:字典方法

conn.execute(
    table.insert(),
    {"id":1, "value":"v1"},
    {"id":2, "value":"v2"}
)
conn.execute(
    "INSERT INTO table (id, value) VALUES (?, ?)",
    (1, "v1"), (2, "v2")
)
solomonxie commented 5 years ago

JSONSchema [DRAFT]

solomonxie commented 5 years ago

What is Cascading in Computer Science

The confusing word Cascading in programming varies in different context.

We have found that word in different scopes:

In simple terms, Cascading refers to the process of performing multiple operations/tasks in a single line of programming code.

In C++

cout<<”Enter your name”;
cout<<endl;
cout<<”Where do u live?”;

The above 3 lined code can be written in a single line…

cout<<Enter your name”<<endl<<”Where do u live?”;

In OOP: Method Cascading

Refer to: https://www.wikiwand.com/en/Method_cascading

" In object-oriented programming, method cascading is syntax which allows multiple methods to be called on the same object."

In CSS

Cascading styles means that if we write CSS for the , all the branches and leaves below will automatically inherit that CSS too.

For example, if you set the background of <body> as red, then all the elements within body has a red background, unless you set the background of each element specifically.

In Database

In the context of Database, Cascading means to run a sequence of actions. Simply saying, an action will run after an event if you write CASCADE expression to specify it.

Refer to: SQL Server: Foreign Keys with cascade delete

That CASCADE keyword is specified when you define a table:

CREATE TABLE inventory
( inventory_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  quantity INT,
  min_level INT,
  max_level INT,
  CONSTRAINT fk_inv_product_id
    FOREIGN KEY (product_id)
    REFERENCES products (product_id)
    ON DELETE CASCADE
);

The ON DELETE CASCADE means that: when you delete the table, its related table with its foreign key will be deleted as well, so that we don't need to delete related tables one by one but only to delete one main table.

solomonxie commented 5 years ago

Database v.s. Data Warehouse [DRAFT]

terms: OLAP cube, OLTP, data cube, MDX query, multidimensional data model, multidimensional analysis, Dimensional Modeling

https://www.youtube.com/watch?v=zTs5zjSXnvs https://www.youtube.com/watch?v=2ryG3Jy6eIY https://www.wikiwand.com/en/Dimensional_modeling

The purpose of Data Warehousing is to provide aggregate data(total, average..) which is in a suitable format for decision making.

Dimensional Modeling:

Dimensional modeling always uses the concepts of facts (measures), and dimensions (context). Facts are typically (but not always) numeric values that can be aggregated, and dimensions are groups of hierarchies and descriptors that define the facts.

Dimensional modeling does not necessarily involve a relational database. The same modeling approach, at the logical level, can be used for any physical form, such as multidimensional database or even flat files.

solomonxie commented 4 years ago

Nested Data Structure in Redis?

https://stackoverflow.com/questions/8810036/complex-data-structures-redis

solomonxie commented 4 years ago

PostgreSQL 基础

命令行客户端psql操作

登陆数据库:

# -U 用户,-h 主机,-p 端口,结尾 DB名
psql -U postgres -h 127.0.0.1 -p 5432 mydb

数据库级别操作:
```sh
# 列出所有数据库
(psql) \l

# 连接指定数据库
(psql) \c mydb

# 创建数据库

# 删除数据库
solomonxie commented 4 years ago

PostgreSQL 基础

命令行客户端psql操作

登陆数据库:

# -U 用户,-h 主机,-p 端口,结尾 DB名
psql -U postgres -h 127.0.0.1 -p 5432 mydb

数据库级别操作:
```sh
# 列出所有数据库
(psql) \l

# 连接指定数据库
(psql) use mydb

# 创建数据库

# 删除数据库
solomonxie commented 4 years ago

PostgreSQL 基础

参考Cheatsheet: https://www.postgresqltutorial.com/postgresql-cheat-sheet/

命令行客户端psql操作

登陆数据库:

# -U 用户,-h 主机,-p 端口,结尾 DB名
psql -U postgres -h 127.0.0.1 -p 5432 mydb

数据库级别操作:
```sh
# 列出所有数据库
(psql) \l

# 连接指定数据库
(psql) use mydb

# 创建数据库
CREATE DATABASE db_name;

# 删除数据库
DROP DATABASE db_name;

数据表级别操作: (略。正常SQL语法)

solomonxie commented 4 years ago

Postgres 错误:There are other sessions using the database

因为有其它session没有正常关闭,所以在删除数据库过程中会报错。 过多的连接sessin会导致PG无法正常查询数据,所以有时候必须断开所有连接。

参考:http://www.leeladharan.com/drop-a-postgresql-database-if-there-are-active-connections-to-it

# 查看所有正在连接的session
(psql) SELECT * FROM pg_stat_activity WHERE datname = 'MYDB';

(psql) SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'MYDB'  AND pid <> pg_backend_pid();

上面这句话是调用pg_terminate_backend()函数来删除所有active连接。