Python操作数据库(高级)

1. 数据集charset

MySQL的字符集之前一直是个大问题,因为保存的字符集和使用的字符集如果不同,那么很容易出现乱码。

1.1 原因

由于我们经常要存储中文,文字是用数字来表示的,相同的文字,在不同的编码格式下,得到的数值不一样。如果使用不同的编码,则导致解释紊乱,出现乱码。

当保存数据时,数据流是这样走的:应用程序==>数据库系统==>数据库。
当读取数据时,数据流是这样走的:数据库==>数据库系统==>应用程序==>系统解析

保存数据和读取数据,可能在不同的电脑上,使用了不同的编码,这样就容易产生乱码。

1.2 MySQL的选择

MySQL安装时,有些MySQL默认是utf8,有的默认是ascii,估计和系统平台有关。

1.3 依旧出现乱码的原因

但是还是可能出现乱码,原因是应用程序保存数据时,数据并不是以utf8编码的,或者读取数据时,应用程序不是以utf8解码的。为了解决这个问题,终极方案是:

dbConn = pymysql.connect(host='127.0.0.1', # 数据库服务器
                user='root', # 默认数据库的名字
                password='123456', # 自己设置的密码
                db='asia') # 链接上数据库系统之后,要操作的数据库名字
dbConn.set_charset('utf8')

获取MySQL连接后,设置这个连接使用utf8编码。

在MySQL命令行窗口,可以使用命令:show variables like '%char%';查看MySQL编码。

2. 数据类型

MySQL有丰富的数据类型,说实话我不太喜欢用那么多的数据类型,从来都是整数和字符串打天下,丰富的数据类型看上去很美好,但是写起代码来也很累。

毕竟数据库保存的是数据,而数据的意义,我们程序员可以去定义的。

3. 字段属性和约束

创建一个数据表时,字段除了类型之外,还可以定义其他属性和约束。

3.1 not null

not null 表示这个字段不能为空,如果没有not null,那么默认这个字段是可以为空的。
比如使用以下语句创建表:

create table test (name varchar(16), tel varchar(11));

那么插入数据时,可以这样:

insert into test (name) values ('tom');

插入数据时,没有指定tel的值,那么tel的值就是null。

如果希望某个字段的值不能为空,那么需要在后面加not null来标记。

create table test (name varchar(16), tel varchar(11) not null);

3.1 primary key

  • 简单的primary key

主键,用于唯一标识记录。这个字段不能为空,也不能重复。

create table test (name varchar(16), tel varchar(11) primary key);

这样tel字段就是主键了,并且在数据库里,想插入一个tel是相同的记录,是不行的。

create table test(name varchar(16), tel varchar(11), primary key(tel));

主键默认就带not null属性。

  • 复杂的primary key
create table test(e varchar(12), p varchar(12), primary key(e, p));

这里e表示公司,p表示产品,那么主键是e和p一起来唯一标识这个产品的。因为一个公司有很多产品,一个产品也可能很多人生产,所以两个字段都不是唯一的,但是它们联合在一起是唯一的。

mysql> create table test(e varchar(12), p varchar(12), primary key(e, p));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test (e, p) values('sandisk', 'udisk');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test (e, p) values('kingston', 'udisk');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test (e, p) values('kingston', 'harddisk');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test (e, p) values('sandisk', 'harddisk');
Query OK, 1 row affected (0.00 sec)
  • 在一个表里,主键只能定义一次
  • 使用主键字段作为where子句的查询条件,查询速度会快一些

3.2 auto_increment

auto_increment表示自动增长,有的时候我们的数据库,有些字段就是一个自动增长的整数。比如员工表里的工号,就是可以自动增长来表示,免得人工分配。

自动增长的字段,一定是整数类型,而且auto_increment字段必须是主键。而且插入数据时,并不需要带自动增长字段的值,当然带了也是可以的。

mysql> create table employee (id int auto_increment primary key, name varchar(16));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into employee (name) values ('xiaowang');
Query OK, 1 row affected (0.01 sec)

mysql> insert into employee (name) values ('xiaoming');
Query OK, 1 row affected (0.00 sec)

mysql> select * from employee;
+----+----------+
| id | name     |
+----+----------+
|  1 | xiaowang |
|  2 | xiaoming |
+----+----------+
2 rows in set (0.00 sec)

3.3 unique

如果一些字段,既不是主键,但是又要求唯一,那么可以使用unique去约束它。

  • 简单的unique
create table employee (cid int primary key auto_increment, name varchar(16), tel varchar(11) unique not null, id varchar(18) unique not null);

或者这样写也行:

create table employee (cid int primary key auto_increment, name varchar(16), tel varchar(11) not null, id varchar(18) not null, unique(tel), unique(id));

从上面的例子看,可以有多个列定义为unique。

  • 复杂的unique

如果多个字段是唯一的,还是比如那个产品表

create table product (id int primary key auto_increment, name varchar(16), company varchar(16), constraint cp unique(name, company));

这样要求name和company组合要唯一。

  • unique的字段值默认可以为null,但是由于是unique,所以最多肯定只有一个值是null

3.4 foreign key

  • 为什么定义外键

foreign key是外键,表示约束。假如有如下表:

employee员工表,pid表示员工编号,name表示员工名称。

+-----+-----------+
| pid | name      |
+-----+-----------+
|  1  | xiaowang  |
|  2  | xiaoming  |
|  3  | xiaoli    |
|  4  | xiaozhang |
+-----+-----------+

order订单表,oid表示订单id,pid表示表示employee中的员工id,orderinfo表示订单信息。

+-----+-----+-----------+
| oid | pid | orderinfo |
+-----+-----+-----------+
|  1  |  1  | xxxxxxxxx |
|  2  |  1  | yyyyyyyyy |
|  3  |  3  | zzzzzzzzz |
|  4  |  3  | wwwwwwwww |
+-----+-----+-----------+

这个表中,显然订单表的pid,应该要有限制,必须是employees表中的pid,要不然有个人使坏,在订单表中,插入下面的一个记录

|  5  |  9  | wwwwwwwww |

将导致将来发现这个订单是神处理的。

  • 创建表时定义外键

那么创建这个order表时,必须设置外键。在下面的语句里,表示pid和employee(pid)是有关系的,插入数据时,这个字段的内容,必须在employee的pid字段里存在。

create table order (oid int primary key auto_increment, pid int, orderinfo varchar(16), foreign key (pid) references employee(pid));
  • 定义外键导致的问题以及解决

定义外键盘之后,导致一些问题,比如员工3处理了3号订单,但是后来员工离职了,然后把employee的3号员工删除了,这样3号订单怎么办呢???(所以,有些数据是要保留的,不能删除的)。万一一定要删除怎么办?或者这个员工的编号修改了怎么办呢?

那么可以定义级联删除和级联更新。

create table order (oid int primary key auto_increment, pid int, orderinfo varchar(16), foreign key (pid) references employee(pid) on delete cascade on update cascade);

3.5 default

默认值是可以定义一些字段的默认值的,如果插入一个记录到数据库,而有一些字段没有定义时,默认值是null,如果不希望如此,可以定义默认值。

create table employee (id int primary key not null, name varchar(16), city varchar(16) default 'shenzhen');

4. 事务

更新数据库使用insert, update等sql语句,但是insert和update,都只能处理一个表,这样可能会出现一致性问题。比如某次操作要同时修改table1和table2,修改table1时候成功了,但是修改table2时候失败了,此时需要将修改table1的代码回滚。

如果更加复杂的情况:

  • 操作table1成功
  • 操作table2成功
  • 操作table3成功
  • 操作table4失败

考虑一致性问题,需要将table1,table2,table3的操作回退。但是可能table3出错,但是可能table2错误,这些情况非常复杂。

为了解决这个问题,数据库引入了事务概念。在修改数据库之前,要先开始事务,然后更新数据库,如果更新数据库都是成功的,才调用提交函数,将数据写入数据库,否则调用回滚,把之前的操作都回退。

pymysql库内置事务处理,所以开始事务函数是不需要调用的,伪代码如下:

dbConn = pymysql.connect()
cursor = dbConn.cursor()

sql1 = "update table1 ..."
sql2 = "update table2 ..."
sql3 = "insert into table3 ..."
sql4 = "update table4 ..."
sql5 = "update table2 ..."

try:
    cursor.execute(sql1)  
    cursor.execute(sql2)  
    cursor.execute(sql3)  
    cursor.execute(sql4)
    cursor.execute(sql5)
except Exception as e:
    connect.rollback()  # 事务回滚
    print('事务处理失败', e)
else:
    connect.commit()  # 事务提交
    print('事务处理成功', cursor.rowcount)

5. 查询

查询(select)是数据库最灵活的一个语句,学好select对于数据处理有非常大的帮助。

5.1 基本查询

select * from table; # 无条件查询所有字段和所有记录
select field1, field2 from table; # 查询部分字段
select field1, field2 from table where field3 > 3; # 条件查询,使用where子句

5.2 where子句

where基本子句形态

where field <> 3 # 查询field不等于3的记录
where field between 3 to 5
where field is null
where field is not null

5.3 where子句多条件组合

where field1 > 3 and field2 < 5
where field1 > 3 or field2 < 5

5.4 where子句模糊条件

where field like '%xxx' # 这里%通配任意个任意字符
where field like '_xxx' # _表示任意一个字符
where field like '%\%%' # 如果要表示%本身,则需要转义
where field rlike '[0-9]xxx' #匹配正则表达式
where field rlike '[^0-9]xxx' # 匹配正则表达式

5.5 分组查询

参考:分组查询

5.6 join查询

多表查询

标签: none

添加新评论