数据库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 |
+-------+-------------------------------------