python3基础知识复习 -- 数据库DB(mysql)

数据库DB

---

数据库类别

关系型数据库

付费的商用数据库：

• Oracle，典型的高富帅；
• SQL Server，微软自家产品，Windows定制专款；
• DB2，IBM的产品，听起来挺高端；
• Sybase，曾经跟微软是好基友，后来关系破裂，现在家境惨淡。

免费的开源数据库：

• MySQL，大家都在用；(普及率高，有大量监控维护工具，安装使用方便)
• PostgreSQL，学术气息有点重，其实挺不错，但知名度没有MySQL高；
• sqlite，嵌入式数据库，适合桌面和移动应用。

SQL概念

• 什么是SQL？SQL是Structured Query Language 结构化查询语言的缩写，用来访问和操作数据库系统。SQL语句既可以查询数据库中的数据，也可以添加、更新和删除数据库中的数据，还可以对数据库进行管理和维护操作。不同的数据库，都支持SQL，这样，我们通过学习SQL这一种语言，就可以操作各种不同的数据库。

• 不通的数据库对SQL进行了不通的扩展，Oracle把自己扩展的SQL称为PL/SQL，Microsoft把自己扩展的SQL称为T-SQL。

• 总的来说，SQL语言定义了这么几种操作数据库的能力：

  DDL：Data Definition Language

  DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。

  DML：Data Manipulation Language

  DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。

  DQL：Data Query Language

  DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。

• 约定：SQL关键字总是大写，以示突出，表名和列名均使用小写。

MySQL历史

08被SUN收购，09SUN被Oracle收购

MySQL本身实际上只是一个SQL接口，它的内部还包含了多种数据引擎，常用的包括：

• InnoDB(首选)：由Innobase Oy公司开发的一款支持事务的数据库引擎，2006年被Oracle收购；
• MyISAM：MySQL早期集成的默认数据库引擎，不支持事务。
• MariaDB是MySQL分支，采用XtraDB引擎
• Aurora是Amazon改进的一个MySQL版本，专门提供给在AWS托管MySQL用户，号称5倍的性能提升。
• PolarDB,由Alibaba改进的一个MySQL版本，专门提供给在阿里云托管的MySQL用户，号称6倍的性能提升。

而MySQL官方版本又分了好几个版本,功能依次递增，价格也递增：

• Community Edition：社区开源版本，免费；https://dev.mysql.com/downloads/mysql/
• Standard Edition：标准版；
• Enterprise Edition：企业版；
• Cluster Carrier Grade Edition：集群版。

数据类型

对于一个关系表，除了定义每一列的名称外，还需要定义每一列的数据类型。关系数据库支持的标准数据类型包括数值、字符串、时间等：

名称	类型	说明
INT	整型	4字节整数类型，范围约+/-21亿
BIGINT	长整型	8字节整数类型，范围约+/-922亿亿
REAL	浮点型	4字节浮点数，范围约+/-1038
DOUBLE	浮点型	8字节浮点数，范围约+/-10308
DECIMAL(M,N)	高精度小数	由用户指定精度的小数，例如，DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算
CHAR(N)	定长字符串	存储指定长度的字符串，例如，CHAR(100)总是存储100个字符的字符串
VARCHAR(N)	变长字符串	存储可变长度的字符串，例如，VARCHAR(100)可以存储0~100个字符的字符串
BOOLEAN	布尔类型	存储True或者False
DATE	日期类型	存储日期，例如，2018-06-22
TIME	时间类型	存储时间，例如，12:20:59
DATETIME	日期和时间类型	存储日期+时间，例如，2018-06-22 12:20:59

上面的表中列举了最常用的数据类型。很多数据类型还有别名，例如，REAL又可以写成FLOAT(24)。还有一些不常用的数据类型，例如，TINYINT（范围在0~255）。通常来说，BIGINT能满足整数存储的需求，VARCHAR(N)能满足字符串存储的需求，这两者使用最广泛。

SQLite

SQLite是一种嵌入式数据库，它的数据库就是一个文件。由于SQLite本身是C写的，而且体积很小，所以，经常被集成到各种应用程序中，Python就内置了SQLite3。

• 表是数据库中存放关系数据的集合，一个数据库里面通常都包含多个表，表和表之间通过外键关联。

• 要操作关系数据库，首先需要连接到数据库，一个数据库连接称为Connection；

• 连接到数据库后，需要打开游标，称之为Cursor，通过Cursor执行SQL语句，然后，获得执行结果。

• Python定义了一套操作数据库的API接口，任何数据库要连接到Python，只需要提供符合Python标准的数据库驱动即可。

  我们在Python交互式命令行实践一下：

  >>> import sqlite3
  
  # 连接到SQLite数据库
  # 数据库文件是test.db, 如果文件不存在，会自动在当前目录创建:
  >>> conn = sqlite3.connect('test.db')
  # 创建一个Cursor:
  >>> cursor = conn.cursor()
  # 执行一条SQL语句，创建user表:
  >>> cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')
  <sqlite3.Cursor object at 0x10f8aa260>
  # 继续执行一条SQL语句，插入一条记录:
  >>> cursor.execute('insert into user (id, name) values (\'1\', \'Michael\')')
  <sqlite3.Cursor object at 0x10f8aa260>
  
  # 通过rowcount获得插入的行数:
  >>> cursor.rowcount
  1
  # 提交事务:
  >>> conn.commit()
  
  # 关闭Cursor:
  >>> cursor.close()
  # 关闭Connection:
  >>> conn.close()
  

  我们再试试查询记录：

  >>> conn = sqlite3.connect('test.db')
  >>> cursor = conn.cursor()
  # 执行查询语句:
  >>> cursor.execute('select * from user where id=?', ('1',)) # 占位符？
  <sqlite3.Cursor object at 0x10f8aa340>
  
  # 获得查询结果集:
  >>> values = cursor.fetchall()
  >>> values
  [('1', 'Michael')]
  >>> cursor.close()
  >>> conn.close()
  

  使用Python的DB-API时，只要搞清楚Connection和Cursor对象，打开后一定记得关闭，就可以放心地使用。

  使用Cursor对象执行insert，update，delete语句时，执行结果由rowcount返回影响的行数，就可以拿到执行结果。

  使用Cursor对象执行select语句时，通过fetchall()可以拿到结果集。结果集是一个list，每个元素都是一个tuple，对应一行记录。

  如果SQL语句带有参数，那么需要把参数按照位置传递给execute()方法，有几个?占位符就必须对应几个参数

  eg:

  #!/usr/bin/env python3
  # -*- coding: UTF-8 -*-
  
  import os, sqlite3
  
  db_file = os.path.join(os.path.dirname(__file__), 'test.db') # 获取当前文件的父目录
  if os.path.isfile(db_file):
      os.remove(db_file)
  
  conn = sqlite3.connect(db_file)
  cursor = conn.cursor()
  cursor.execute('create table user (id varchar(20) primary key, name varchar(20), score int)')
  cursor.execute(r"insert into user values('A-001', 'Adam', '95')")
  cursor.execute(r"insert into user values('A-002', 'Bart', '62')")
  cursor.execute(r"insert into user values('A-003', 'Lisa', '78')")
  cursor.rowcount
  conn.commit()
  cursor.close()
  conn.close()
  
  def get_score_in(low, high):
      try:
          conn = sqlite3.connect(db_file)
          cursor = conn.cursor()
          cursor.execute('SELECT name FROM user WHERE (score >= ? AND score <= ?) ORDER by score', (low, high))
          cursor.rowcount
          values = cursor.fetchall() # 得到的是含有元组的列表[('Bart',), ('ALisa',)]
          return [each[0] for each in values] # 用列表表达式提取姓名组成新的列表
  
      except Exception as e:
          print(str(e))
      finally:
          cursor.close()
          conn.close()
  
  assert get_score_in(80, 95) == ['Adam'], get_score_in(80, 95)
  assert get_score_in(60, 80) == ['Bart', 'Lisa'], get_score_in(60, 80)
  assert get_score_in(60, 100) == ['Bart', 'Lisa', 'Adam'], get_score_in(60, 100)
  
  print('Pass')
  

  关于__file__的用法：

  # 获取当前文件所在的位置

  # 如果当前文件包含在 sys.path 里面，那么 file 返回一个相对路径

  # 如果当前文件不包含在 sys.path 里面，那么 file 返回一个绝对路径

  # os.path.dirname(__file__) 表示当前文件的父目录

MySQL

MySQL是为服务器端设计的数据库，能承受高并发访问，同时占用的内存也远远大于SQLite。最常用的引擎是支持数据库事务的InnoDB。

设置utf8编码

在Windows上，安装时请选择UTF-8编码，以便正确地处理中文。

在Mac或Linux上，需要编辑MySQL的配置文件默认存放在/etc/my.cnf或者/etc/mysql/my.cnf，把数据库默认的编码全部改为UTF-8。

[client]
default-character-set = utf8

[mysqld]
default-storage-engine = INNODB
character-set-server = utf8
collation-server = utf8_general_ci

重启MySQL后，可以通过MySQL的客户端命令行检查编码：

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor...
...

mysql> show variables like '%char%';
+--------------------------+--------------------------------------------------------+
| Variable_name            | Value                                                  |
+--------------------------+--------------------------------------------------------+
| character_set_client     | utf8                                                   |
| character_set_connection | utf8                                                   |
| character_set_database   | utf8                                                   |
| character_set_filesystem | binary                                                 |
| character_set_results    | utf8                                                   |
| character_set_server     | utf8                                                   |
| character_set_system     | utf8                                                   |
| character_sets_dir       | /usr/local/mysql-5.1.65-osx10.6-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------+
8 rows in set (0.00 sec)

注：如果MySQL的版本≥5.5.3，可以把编码设置为utf8mb4，utf8mb4和utf8完全兼容，但它支持最新的Unicode标准，可以显示emoji字符。

MySQL驱动

由于MySQL服务器以独立的进程运行，并通过网络对外服务，Python需要MySQL驱动来连接到MySQL服务器。

MySQL官方提供了mysql-connector-python驱动，但是安装的时候需要给pip命令加上参数--allow-external：

$ pip install mysql-connector-python --allow-external mysql-connector-python

如果上面的命令安装失败，可以试试另一个驱动：

$ pip install mysql-connector

我们演示如何连接到MySQL服务器的test数据库：

>>> import mysql.connector

# 注意把password设为你的root口令:
>>> conn = mysql.connector.connect(user='root', password='password', database='test')
>>> cursor = conn.cursor()

# 创建user表:
>>> cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')

# 插入一行记录，注意MySQL的占位符是%s:
>>> cursor.execute('insert into user (id, name) values (%s, %s)', ['1', 'Michael'])
>>> cursor.rowcount
1
# 提交事务:
>>> conn.commit()
>>> cursor.close()

# 运行查询:
>>> cursor = conn.cursor()
>>> cursor.execute('select * from user where id = %s', ('1',))
>>> values = cursor.fetchall()
>>> values
[('1', 'Michael')]
# 关闭Cursor和Connection:
>>> cursor.close()
True
>>> conn.close()

由于Python的DB-API定义都是通用的，所以，操作MySQL的数据库代码和SQLite类似。注意MySQL的SQL占位符是%s。

Foreign key 外键用法

如果一张表中有一个非主键的字段指向了另一张表中的主键，就将该字段叫做外键。一张表中外键可以有多个，也就是不同字段指向了不同表中的主键。需要注意数据表的存储引擎必须为InnoDB，因为InnoDB提供事务支持以及外部键等高级数据库功能，相反的MyISAM不支持。

外键的作用是保持数据一致性、完整性，主要体现在下面两个方面：

阻止执行

从表插入新行，其外键值不是主表的主键值便阻止插入；
从表修改外键值，新值不是主表的主键值便阻止修改；
主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表的相关行)；
主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)。

级联执行

主表删除行，连带从表的相关行一起删除；
主表修改主键值，连带从表相关行的外键值一起修改。

关联方式有两种：

• 方式1
  create table时：[constraint 外键约束关系的名称] foreign key 从表(外键字段名称) references 主表(主键字段名称)

• 方式2
  后面添加：alter table 从表 add [constraint] [外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);
  我们用第二种方式进行关联：

• 删除外键： mysql> alter table drop foreign key ;

'''
		再关联表中去添加级联跟新级联删除代码
			on update cascade  # 级联更新
			on delete cascade  # 级联删除
	'''
	create table emp1(
	id int primary key auto_increment comment '编号',
	  name varchar(32) comment '姓名',
	  age int comment '年龄',
	  dep_id int comment '部门编号',
	  foreign key(dep_id) references dep1(id) 
	  on update cascade  # 级联更新
	  on delete cascade  # 级联删除
	);
	create table dep1(
		id int primary key auto_increment comment '编号',
	  dep_name varchar(32) comment '部门名称',
	  dep_desc varchar(32) comment '部门描述'
	);

	"""
	在实际工作中外键也可能不会使用 因为外键会消耗额外的资源
	并且会增加表的复杂度 
	表很多的情况下 我们也可以通过SQL语句的形式建立逻辑意义上的表关系
	"""

SQLAlchemy

数据库表是一个二维表，包含多行多列。Python的DB-API返回的数据结构就是像下面这样表示的。但是用tuple表示一行很难看出表的结构。使用ORM技术：Object-Relational Mapping，把关系数据库的表结构映射到对象上，这样更容易看出表结构

# tuple 来表示返回的行列
[
    ('1', 'Michael'),
    ('2', 'Bob'),
    ('3', 'Adam')
]

# ORM形式的行列
class User(object):
    def __init__(self, id, name):
        self.id = id
        self.name = name

[
    User('1', 'Michael'),
    User('2', 'Bob'),
    User('3', 'Adam')
]

• 在Python中，最有名的ORM框架是SQLAlchemy。$ pip install sqlalchemy

已经存在一个下面的db test1, 有表两个book, user
mysql> use test1

# 导入:
from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# 创建对象的基类:
Base = declarative_base()

# DB中的每一个表使用时都需要定义一个对象继承基类:
class User(Base):
    # 表的名字:
    __tablename__ = 'user'

    # 表的结构:
    id = Column(String(20), primary_key=True)
    name = Column(String(20))
    # 用于外键一对多:
    books = relationship('Book')
    
class Book(Base):
    __tablename__ = 'book'

    id = Column(String(20), primary_key=True)
    name = Column(String(20))
    # “多”的一方的book表是通过外键关联到user表的:
    user_id = Column(String(20), ForeignKey('user.id'))
    
# 初始化数据库连接:
engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/test')
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)

create_engine()用来初始化数据库连接。SQLAlchemy用一个字符串表示连接信息：

'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'

下面，我们看看如何向数据库表中添加一行记录。

由于有了ORM，我们向数据库表中添加一行记录，可以视为添加一个User对象：

# 创建session对象（可视为当前数据库连接）:
session = DBSession()
# 创建新User对象,添加到session:
new_user1 = User(id='1', name='Robin')
new_user2 = User(id='2', name='Tom')
new_book1 = Book(id='1', name='Iron man', user_id='1')
new_book2 = Book(id='2', name='The wind', user_id='2')
new_book3 = Book(id='3', name='Rocket', user_id='1')

session.add(new_user1)
session.add(new_user2)
session.add(new_book1)
session.add(new_book2)
session.add(new_book3)

# 提交即保存到数据库:
session.commit()

# 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:
user = session.query(User).filter(User.id=='1').one() 
list1 = user.books # 由于外键的存在会返回所有book的对象列表，需要分别读取
for each in list1:
	print('books: ', each.name)

print('user name: ', user.name)


# 关闭session: 最好放到try...except...finally
session.close()

• ORM就是把数据库表的行与相应的对象建立关联，互相转换。

• 由于关系数据库的多个表还可以用外键实现一对多、多对多等关联，相应地，ORM框架也可以提供两个对象之间的一对多、多对多等功能。当我们查询一个User对象时，该对象的books属性将返回一个包含若干个Book对象的list。见上面的relationship

执行完后的效果：

root@W10B9T72J3:/mnt/c/Users/meij1/Videos/OdoCSV# python3 t4.py
books:  The Great Wall
books:  Rocket
books:  Iron man
user name:  Robin


mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| book            |
| user            |
+-----------------+
mysql> select * from user;
+----+-------+
| id | name  |
+----+-------+
| 1  | Robin |
| 2  | Tom   |
+----+-------+

mysql> select * from book;
+----+----------------+---------+
| id | name           | user_id |
+----+----------------+---------+
| 1  | Iron man       | 1       |
| 2  | The Wind       | 2       |
| 3  | Rocket         | 1       |
+----+----------------+---------+

mysql> show create table book;
-------------+
| book  | CREATE TABLE `book` (
  `id` varchar(20) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `user_id` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `book_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+-------------------------------------