Python学习笔记 - day11 - Python操作数据库

Python学习笔记 - day11 - Python操作数据库

MySQL的事务

MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关，MySQL的两种引擎如下：

　　1.MyISAM：不支持事务，用于只读程序提高性能

　　2.InnoDB：支持ACID事务、行级锁、并发 3.Berkeley DB：支持事务

一个事务是一个连续的一组数据库操作，就好像它是一个单一的工作单元进行。换言之，永远不会是完整的事务，除非该组内的每个单独的操作是成功的。如果在事务的任何操作失败，则整个事务将失败。

实际上，会将许多SQL查询到一个组中，将执行所有的人都一起作为事务的一部分。比如你在银行给别人卡里转账就是一个事务，首先前需要从你的卡里扣掉，然后在对方的卡里增加，不可能 你的钱扣了，对方没有增加，又或者对方的卡里增加了，而你的卡没有扣钱。

事务的特性

事务有以下四个标准属性的缩写ACID，通常被称为：Atomicity（原子性）、Consistency（一致性）、Isolation（隔离性）、Durability（持久性）

1. 原子性: 确保工作单元内的所有操作都成功完成，否则事务将被中止在故障点，和以前的操作将回滚到以前的状态。
2. 一致性: 确保数据库正确地改变状态后，成功提交的事务。
3. 隔离性: 使事务操作彼此独立的和透明的。
4. 持久性: 确保提交的事务的结果或效果的系统出现故障的情况下仍然存在。

在MySQL中，事务开始使用COMMIT或ROLLBACK语句开始工作和结束。开始和结束语句的SQL命令之间形成了大量的事务。

COMMIT & ROLLBACK 

这两个关键字提交和回滚主要用于MySQL的事务。

当一个成功的事务完成后，发出COMMIT命令应使所有参与表的更改才会生效。

如果发生故障时，应发出一个ROLLBACK命令返回的事务中引用的每一个表到以前的状态。

而我们使用终端连接mysql的时候，输入命令后直接会提交，这是因为MySQL的自动提交参数：

mysql> show variables like 'auto%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_generate_certs      | ON    |
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
| autocommit               | ON    |
| automatic_sp_privileges  | ON    |
+--------------------------+-------+
5 rows in set (0.01 sec)

mysql>    # 注意到 autocommit ，自定提交是打开的。

PS：代码里不能自动提交，所以每次操作完毕后，需要手动的执行commit进行提交。

Python操作数据库

Python 提供了程序的DB-API，支持众多数据库的操作。由于目前使用最多的数据库为MySQL，所以我们这里以Python操作MySQL为例子，同时也因为有成熟的API,所以我们不必去关注使用什么数据，因为操作逻辑和方法是相同的。

安装模块

Python 程序想要操作数据库，首先需要安装 模块 来进行操作，Python 2 中流行的模块为 MySQLdb，而该模块在Python 3 中将被废弃，而使用PyMySQL，这里以PyMySQL模块为例。

使用pip命令安装PyMSQL模块

 pip3 install pymysql

如果没有pip3命令那么需要确认环境变量是否有添加，安装完毕后测试是否安装完毕。

lidaxindeMacBook-Pro:~ DahlHin$ python3
Python 3.6.1 (v3.6.1:69c0db5050, Mar 21 2017, 01:21:04) 
[GCC 4.2.1 (Apple Inc. build 5666) (dot 3)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import pymysql
>>> 


# 如果没有报错，则表示安装成功

创建一个连接

首先我们需要手动安装一个MySQL数据库，这里不再赘述，参考博文：http://www.cnblogs.com/dachenzi/articles/7159510.html 

使用pymysql.connect方法来连接数据库

import pymysql

conn = pymysql.connect(host=None, user=None, password="",
                 database=None, port=0, unix_socket=None,
                 charset=''......)

• • host：表示连接的数据库的地址
  • user：表示连接使用的用户
  • password：表示用户对应的密码
  • database：表示连接哪个库
  • port：表示数据库的端口
  • unix_socket：表示使用socket连接时，socket文件的路径
  • charset：表示连接使用的字符集　
  • read_default_file：读取mysql的配置文件中的配置进行连接

上面仅仅列举了几个常用的参数，其他还有很对连接的选项，可以通过pymysql.connect函数来查看源码。

程序的健壮性

现在的程序基本是都是要连接数据库的，那么在一个程序里面可能会有很多功能都会去连接数据库，那么如果每次都去创建连接指定数据库的地址，端口等信息，先不说麻烦，如果我的数据库端口改了，我们岂不是要在所有调用数据库的地方进行修改？ 所以这就引出了一个概念：模块化。

前面我们已经学过包和模块了，我们把连接数据库的操作写成一个函数做成一个包，那么什么地方使用，只需要调用即可，并且修改的时候只需要修改模块的代码即可，这样大大的方便了我们日后可能的操作。

import pymysql


def connect_mysql():

    db_config = {
        'host':'127.0.0.1',
        'port':3306,
        'user':'root',
        'password':'abc.123',
        'charset':'utf8'
    }

    conn = pymysql.connect(**db_config)

    return conn

# 注意：端口不能加引号，因为port接受的数据类型为整型
# 注意：charset的字符集不是utf-8，是utf8

PS：当然对于这种可能频繁修改的当作配置写到文件中去也是可以的。

连接

调用connect函数，将创建一个数据库连接并得到一个Connection对象，Connection对象定义了很多的方法和异常。

• begin：开始事务
• commit：提交事务
• rollback：回滚事务
• cursor：返回一个Cursor对象
• autocommit：设置事务是否自动提交
• set_character_set：设置字符集编码
• get_server_info：获取数据库版本信息

在实际的编程过程中，一般不会直接调用begin、commit和rollback函数，而是通过上下文管理器实现事务的提交与回滚操作。

游标

游标是系统为用户开设的一个数据缓存区，存放SQL语句执行的结果，用户可以用SQL语句逐一从游标中获取记录，并赋值给变量，交由Python进一步处理。

在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条SQL 选择语句相关联因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。

正如前面我们使用Python对文件进行处理，那么游标就像我们打开文件所得到的文件句柄一样，只要文件打开成功，该文件句柄就可代表该文件。对于游标而言，其道理是相同的。

利用游标操作数据库

在进行数据库的操作之前需要创建一个游标对象，来执行sql语句。

import pymysql


def connect_mysql():

    db_config = {
        'host':'127.0.0.1',
        'port':3306,
        'user':'root',
        'password':'abc.123',
        'charset':'utf8'
    }

    conn = pymysql.connect(**db_config)

    return conn


if __name__ == '__main__':
    conn = connect_mysql()
    cursor = conn.cursor()    # 创建游标
    sql = r" select user,host from mysql.user "  # 要执行的sql语句
    cursor.execute(sql)  # 交给 游标执行
    result = cursor.fetchall()  # 获取游标执行结果
    print(result)

游标的方法

游标内置了很多方法常用的有： cursor.fetchall(),cursor.fetchone(),cursor.fetchmany()

cursor.execute(sql),executemany(sql,parser)

　　用于执行sql语句,execut执行一条sql语句，executemany执行多条语句

# execute 执行单条sql语句
conn = connect_mysql()
cursor = conn.cursor()  # 创建游标
sql = r" select user,host from mysql.user "  # 要执行的sql语句
cursor.execute(sql)  # 交给 游标执行
    

# executemany 执行多条SQL语句  
conn = connect_mysql()
cursor = conn.cursor()
sql = 'insert into tmp(id) VALUES(%s)'     # %s 表示占位符
parser = [1,2,3,4,5,6] 
cursor.execute('use test')
cursor.executemany(sql,parser)      # 把参数进行填充，parset是参数的列表
conn.commit()
conn.close()

cursor.fetchone(),fetchmany(),fetchall()

　　用户获取数据，fetchone获取一行，fetchmany获取多行，fetchall获取所有

conn = connect_mysql()
cursor = conn.cursor()    # 创建游标
sql = r" select user,host from mysql.user "  
cursor.execute(sql)  # 交给 游标执行
result1 = cursor.fetchone()
result2 = cursor.fetchmany(2)  # 获取2行
result3 = cursor.fetchall()  

print(result1)
print(result2)
print(result3)

注意：cursor的数据只能取一次，比如上面的示例，一共有三行数据，fetchone取了一行，fetchmany(2)取了两行，那么fetchall()将会得到空数据。

corsor.rowncount 常量，表示sql语句的结果集中，返回了多少条记录

cursor.arraysize 常量，保存了当前获取记录的下标

corsor.close() 关闭游标

小结

　　在Python中操作数据库，基本步骤如下：

1. 导入相应的Python模块
2. 使用connect函数连接数据库，并返回一个Connection对象
3. 通过Connection对象的cursor方法，返回一个Cursor对象
4. 通过Cursor对象的execute方法执行SQL语句
5. 如果执行的是查询语句，通过Cursor对象的fetchall语句获取返回结果
6. 调用Cursor对象的close关闭Cursor
7. 调用Connection对象的close方法关闭数据库连接

利用数据库链接池操作数据库

在python编程中可以使用MySQLdb/pymysql等模块对数据库的连接及诸如查询/插入/更新等操作，但是每次连接mysql数据库请求时，都是独立的去请求访问，相当浪费资源，而且访问数量达到一定数量时，对mysql的性能会产生较大的影响。因此，实际使用中，通常会使用数据库的连接池技术，来访问数据库达到资源复用的目的。

数据库连接池包 DBUtils

DBUtils是一套Python数据库连接池包，并允许对非线程安全的数据库接口进行线程安全包装。DBUtils来自Webware for Python。

DBUtils提供两种外部接口：

• * PersistentDB ：提供线程专用的数据库连接，并自动管理连接。
• * PooledDB ：提供线程间可共享的数据库连接，并自动管理连接。

import pymysql
from DBUtils.PooledDB import PooledDB   # 导入线程池对象
pool = PooledDB(pymysql,host='127.0.0.1',port=3306,user='root',password='abc.123',mincached=20)   # 利用线程池对象，实例化mysql连接对象，这里最少空闲20个链接，即实例化后会立即创建20个MySQL链接，并且空闲数少于20，会自动创建
# 第一个参数表示链接数据库使用的API，这里我使用pymysql，也可以使用MySQLdb。
conn = pool.connection()    # 获取链接池中的mysql链接
cursor = conn.cursor()
cursor.execute('select user,host from mysql.user;')
data = cursor.fetchall()
cursor.close()
conn.close()

print(data)

PooledDB对象提供的参数还有：

　　1. mincached，最少的空闲连接数，如果空闲连接数小于这个数，pool会创建一个新的连接
　　2. maxcached，最大的空闲连接数，如果空闲连接数大于这个数，pool会关闭空闲连接
　　3. maxconnections，最大的连接数，
　　4. blocking，当连接数达到最大的连接数时，在请求连接的时候，如果这个值是True，请求连接的程序会一直等待，直到当前连接数小于最大连接数，如果这个值是False，会报错，
　　5. maxshared 当连接数达到这个数，新请求的连接会分享已经分配出去的连接

连接池对性能的提升表现在：

　　1.在程序创建连接的时候，可以从一个空闲的连接中获取，不需要重新初始化连接，提升获取连接的速度
　　2.关闭连接的时候，把连接放回连接池，而不是真正的关闭，所以可以减少频繁地打开和关闭连接

数据库相关操作

数据库主要有库和表来组成，库是表的集合，针对库和表有如下基本操作。

库相关操作

数据库在文件系统上就是用目录体现的，所以对库的操作，可以理解为对目录的操作。

• 创建数据库,会在MySQL的data目录下创建同名文件夹

创建数据库

1 2 3 4

create  database  db_name; create  database  db_name  default  charset utf8; --> 语法格式：create database  数据库名称 --> 建议同时指定数据库的字符集

删除数据库

1 2 3

drop  database  db_name; --> 语法格式：drop database 数据库名称