33.企业级开发进阶6：数据库操作

python作为一个编程语言，在开发B/S或者C/S结构的软件时，不可避免的会设计到和数据库之间的交互操作，和其他高级的面向对象的语言一样，Python在操作数据库的过程中，尽量追求了简洁、统一、易用的风格。

本节内容

mysql数据库连接驱动的安装
python连接mysql数据库
增删改查（CRUD）数据操作

注意，关于mysql数据库的教程，我们后续还在其他的模块进行总结添加，如果大家需要的话^_毕竟现在网络上关于这样常规的技术教程还是非常多滴

1. mysql数据库连接驱动的安装

1.1. 亲，请明白为什么要有数据库连接驱动

首先：我们明白，编程语言和数据库各自都是什么
编程语言：专门用于进行数据处理的独立的个体
数据库：专门用于进行数据储存的独立的个体
也就是说，编程语言和数据库本身是两个完全独立的个体，为了让数据能更加优雅的持久的存储和处理，编程语言就得和数据库配合完成我们的工作

因为编程语言如果独立处理数据的话，程序是运行在系统的内存中的，如果程序一旦终止，意味着处理的数据就会丢失。为了持久的有效的保存数据，我们选择将处理的数据保存在数据库中

其次：编程语言，凭什么可以访问数据库
数据库给编程语言专门开了一个后门（API），通过这个后门（API）就可以让编程语言对数据库中的数据进行增删改查操作了。当然，必须得拿着数据库提供给编程语言的正确的钥匙才是可以的哦【钥匙：数据库连接驱动+连接凭证】

最后：OK，此时，我们明白了，编程语言为什么和数据库配合使用，为什么要有连接驱动，接下来，进入我们的安装环节

python操作数据库，其实就是两个独立个体之间的数据通信，和我们现实生活一样，需要中间连接两个独立的人之间的手机和正确的电话号码

python连接数据库示意图

1.2. 亲，出错了~

安装数据库驱动，我们想到的第一件事应该是搜索官方文档或者问问度娘/谷哥，得到结果如下：

# 安装mysql的python语言的数据库连接驱动
pip install mysql-connector-python --allow-exrternal mysql-connector-python

请注意：如果你使用的python版本是2.7或者3.4以下版本，是不会有任何问题的，因为mysql官方提供的驱动支持的最高版本是Python2.7或者python3.4，如下图

python驱动版本

如果你跟我一样，在一台电脑上安装了python2.7和python3.6的版本，尤其是目前使用的是python3.6的版本，上述安装驱动方式就会出现版本不支持的错误，错误信息如下：

python3.4+版本安装驱动报错提示

1.3. 没事，有我在！

如果是对于Python3.4+的版本，mysql官方提供的驱动已经不满足我们的需要，此时需要安装一个第三方的驱动来完成和数据库的连接支持

这个神奇的第三方数据库就是：PyMySQL

接下来，安装它：

python3 -m pip install pymysql

安装过程如下图所示：

安装pymysql模块

安装完成后，可以通过import引入到我们的python程序中哦

注意：python2和python3连操作数据库的方式稍有差异，python2.x操作数据库主要使用的是mysqldb模块；python3.x操作数据库我们选择使用pymysql。当然，操作方式是一样的，并没有什么太大区别

2. python连接mysql数据库

我们在前面的内容中，已经安装好了数据库连接驱动，接下来，通过python程序来连接数据库
废话不多，上干货：

# 引入我们需要的操作数据库模块
import pymysql

# 连接数据库
conn = pymysql.connect(
    host="localhost",   # 数据库主机IP地址
    user="root",        # 数据库登录账号
    password="",        # 数据库登录密码
    database="pydb",    # 要连接的数据库
    port=3306,          # 连接数据库的端口号
    charset="utf-8"     # 使用指定编码连接数据库
)

请记住上面的代码，连接数据库就是这么简单！
有人说~我记不住怎么办，记不住那么多信息，可以记住pymysql.connect()，这样总是可以的吧，然后进入pymysql提供的connections.py源代码中就可以看到connect()方法，它是这么写的

def __init__(self, host=None, user=None, password="",
                 database=None, port=0, unix_socket=None,
                 charset='', sql_mode=None,
                 read_default_file=None, conv=None, use_unicode=None,
                 client_flag=0, cursorclass=Cursor, init_command=None,
                 connect_timeout=10, ssl=None, read_default_group=None,
                 compress=None, named_pipe=None, no_delay=None,
                 autocommit=False, db=None, passwd=None, local_infile=False,
                 max_allowed_packet=16*1024*1024, defer_connect=False,
                 auth_plugin_map={}, read_timeout=None, write_timeout=None,
                 bind_address=None):

上述pymysql的connections.py中上面的代码的意思比较简单，每一个参数都通过参数名称我们基本就能明白参数是什么意义了。常用的也就那么几个。

3. python操作数据库中的数据

首先，我们打开mysql数据库编辑工具（这里我使用的是sqlyog操作mysql，大家可以随意），创建用户表（我们将数据库表创建的稍微正式点）：

# 创建数据库
CREATE DATABASE pydb;

# 指定使用数据库
USE pydb;

# 创建用户表
CREATE TABLE users(
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL COMMENT '用户账号',
    userpass VARCHAR(50) NOT NULL COMMENT '登录密码',
    nickname VARCHAR(50) COMMENT '昵称',
    age INT COMMENT '年龄',
    gender VARCHAR(5) COMMENT '性别',
    phone VARCHAR(15) COMMENT '联系方式',
    email VARCHAR(50) COMMENT '邮箱',
    createTime DATETIME COMMENT '账号创建时间',
    updateTime DATETIME COMMENT '账号最后修改时间',
    lastLogin DATETIME COMMENT '账号最后登录时间',
    usersFlag INT COMMENT '账号状态：0 正常 1 锁定 2 删除',
    remark TEXT COMMENT '备注'
) DEFAULT CHARSET "utf8";

# 增加测试数据
INSERT INTO users(username, userpass, nickname, age, gender, phone, email, createTime, updateTime, lastLogin, usersFlag, remark)
VALUES("tom", "123", "凯特", 48, "男", "13868686868", "cat@163.com", "2017-06-01","2017-06-02","2017-06-05",0,"tom and jerry 管理员"),
("jerry", "111", "杰瑞", 46, "女", "15688888888", "mouse@163.com", "2017-06-01","2017-06-03","2017-06-04",0,"tom and jerry 管理员");

3.1. 操作数据库数据的步骤

连接数据库
获取一个访问数据库的操作对象
定义SQL语句
执行SQL语句
处理结果
关闭和数据库之间的连接

我们使用python操作数据库，打开和数据库的连接并维持连接是需要消耗系统资源滴，切记操作完成之后一定要关闭和数据库之间的连接

3.2. 查询数据库中的数据

核心API：
executer(sql)：执行指定的sql语句，返回影响的行数
fetchall()：获取SQL操作的所有数据
fetchone()：获取SQL操作的第一条数据

接下来，上干货：

# 引入我们需要的操作数据库模块
import pymysql

# 数据库连接信息
HOST = "localhost"
USER = "root"
PASSWORD = ""
DATABASE = "pydb"
PORT = 3306
CHARSET = "utf8"

# 使用异常包含处理过程，方便在finally中关闭和数据库的连接
try:
    # 连接数据库
    conn = pymysql.connect(
        host=HOST,   # 数据库主机IP地址
        user=USER,        # 数据库登录账号
        password=PASSWORD,        # 数据库登录密码
        database=DATABASE,    # 要连接的数据库
        port=PORT,          # 连接数据库的端口号
        charset=CHARSET     # 使用指定编码连接数据库
    )

    # 获取执行对象
    cursor = conn.cursor();

    # 定义查询sql语句
    sql = "select * from users"

    # 执行sql语句
    rows = cursor.execute(sql)

    # 获取查询结果
    result = cursor.fetchall()

    # 遍历查询结果
    for user in result:
        print("userid<%d>username<%s>userpass<%s>nickname<%s>createTime<%s>"
              % (user[0], user[1], user[2], user[3], user[8]))

except Exception as e:
    print("执行过程出现异常<%s>" % str(e))
finally:
    # 不论是否出现异常，执行完成后，保证数据库连接关闭
    cursor.close()
    conn.close()

执行上述代码，返回如下预期的结果

userid<1>username<tom>userpass<123>nickname<凯特>createTime<2017-06-01 00:00:00>

userid<2>username<jerry>userpass<111>nickname<杰瑞>createTime<2017-06-01 00:00:00>

下面是我们操作的过程中，明确操作结果就是一条数据的情况下

import pymysql

# 数据库连接信息
HOST = "localhost"
USER = "root"
PASSWORD = ""
DATABASE = "pydb"
PORT = 3306
CHARSET = "utf8"

# 使用异常包含处理过程，方便在finally中关闭和数据库的连接
try:
    # 连接数据库
    conn = pymysql.connect(
        host=HOST,   # 数据库主机IP地址
        user=USER,        # 数据库登录账号
        password=PASSWORD,        # 数据库登录密码
        database=DATABASE,    # 要连接的数据库
        port=PORT,          # 连接数据库的端口号
        charset=CHARSET     # 使用指定编码连接数据库
    )

    # 获取执行对象
    cursor = conn.cursor()

    # 定义sql语句
    sql = "select * from users"

    # 执行sql语句
    rows = cursor.execute(sql)

    # 抓取查询结果：获取结果中的第一条数据
    result = cursor.fetchone()

    print("result:%s--%s--%s--%s" % (result[0], result[1], result[2], result[3]))
except Exception as e:
    print("出现异常<%s>" % str(e))
finally:
    # 关闭数据库连接
    cursor.close()
    conn.close()

执行上述代码，可以看到数据也是正常获取的

result:1--tom--123--凯特

3.3. 新增/更新/删除数据到数据库

废话不说，直接上代码，一定要看注释啊

# 引入数据库模块
import pymysql

# 定义数据库连接信息
HOST = "localhost"
USER = "root"
PASSWORD = ""
DATABASE = "pydb"
PORT = 3306
CHARSET = "utf8"

try:
    # 连接数据库
    conn = pymysql.connect(
        host=HOST,
        user=USER,
        password=PASSWORD,
        database=DATABASE,
        port=PORT,
        charset=CHARSET
    )

    # 获取执行对象
    cursor = conn.cursor()

    """
    增加数据到数据库的操作：insert
    """
    # 定义sql语句
    insertSql = 'INSERT INTO users(username, userpass, nickname, age, gender, phone, email, createTime, updateTime, lastLogin, usersFlag, remark)\
            VALUES("shuke", "123", "舒克", 42, "男", "15686868686", "shuke@163.com", "2017-06-01","2017-06-02","2017-06-05",0,"shuke and beita")'

    # 执行sql语句
    rows = cursor.execute(insertSql)
    # 将更改的数据提交更新
    conn.commit()
    print("共有%d条数据被添加到数据库中了" % rows)

    """
    更新数据到数据库的操作：update
    """
    # 定义sql语句
    updateSql = 'update users set nickname = "凯特大叔" where id = 1'

    # 执行sql语句
    rows = cursor.execute(updateSql)
    # 将更改的数据提交更新
    conn.commit()
    print("共有%d条数据在数据库中被修改了" % rows)

    """
    从数据库中删除数据：delete
    """
    # 定义sql语句
    deleteSql = 'delete from users where id = 2'

    # 执行sql语句
    rows = cursor.execute(deleteSql)
    # 将删除数据进行提交更新
    conn.commit()
    print("共有%d条数据在数据库中被删除了" % rows)

except Exception as e:
    print("出现异常<%s>" % str(e))
finally:
    cursor.close()
    conn.close()

上述代码，包含了基本的insert/update/delete三种类型的操作，分别操作了不同的数据

操作数据之间数据库中的数据

python数据库基本操作

上述程序运行结束之后数据库中的数据，仔细观察

python数据库基本操作

3.4. 使用占位符进行数据操作【需要掌握】

在SQL操作的过程中，如果我们通过将SQL字符串和对应的数据通过拼接来操作的话，会变得非常的麻烦，大家可以试试上面的程序中的数据，如果都是用户输入的，然后增加到SQL语句中，会是什么样的场景

所以有了占位符的方式，来简化数据和SQL语句之间的操作，废话不多，代码大家一看就懂，上干货：

# 引入数据库模块
import pymysql

# 定义数据库连接信息
HOST = "localhost"
USER = "root"
PASSWORD = ""
DATABASE = "pydb"
PORT = 3306
CHARSET = "utf8"

try:
    # 连接数据库
    conn = pymysql.connect(
        host=HOST,
        user=USER,
        password=PASSWORD,
        database=DATABASE,
        port=PORT,
        charset=CHARSET
    )

    # 获取执行对象
    cursor = conn.cursor()

    """
    增加数据到数据库的操作：insert
    """
    # 定义sql语句
    insertSql = 'INSERT INTO users(username, userpass, nickname, age, gender, phone, email, createTime, updateTime, lastLogin, usersFlag, remark)\
            VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'

    # 执行sql语句
    rows = cursor.execute(insertSql, ("shuke", "123", "舒克", 42, "男", "15686868686", "shuke@163.com", "2017-06-01","2017-06-02","2017-06-05",0,"shuke and beita"))
    # 将更改的数据提交更新
    conn.commit()
    print("共有%d条数据被添加到数据库中了" % rows)

    """
    更新数据到数据库的操作：update
    """
    # 定义sql语句
    updateSql = 'update users set nickname = %s where id = %s'

    # 执行sql语句
    rows = cursor.execute(updateSql, ["凯特大叔", 1])
    # 将更改的数据提交更新
    conn.commit()
    print("共有%d条数据在数据库中被修改了" % rows)

    """
    从数据库中删除数据：delete
    """
    # 定义sql语句
    deleteSql = 'delete from users where id = %s'

    # 执行sql语句
    rows = cursor.execute(deleteSql, 1)
    # 将删除数据进行提交更新
    conn.commit()
    print("共有%d条数据在数据库中被删除了" % rows)

except Exception as e:
    print("出现异常<%s>" % str(e))
finally:
    cursor.close()
    conn.close()

上述代码的执行操作，和前面的基本操作是一致的，大家可以试试。

3.5. 批量操作及性能优化建议

在python中，为了方便进行批量数据的处理【批量数据增加、修改、删除等操作】提供了一个executemany()函数，操作方式和占位符的方式有点类似

直接上干货

# 引入数据库模块
import pymysql

# 定义数据库连接信息
HOST = "localhost"
USER = "root"
PASSWORD = ""
DATABASE = "pydb"
PORT = 3306
CHARSET = "utf8"

try:
    # 连接数据库
    conn = pymysql.connect(
        host=HOST,
        user=USER,
        password=PASSWORD,
        database=DATABASE,
        port=PORT,
        charset=CHARSET
    )

    # 获取执行对象
    cursor = conn.cursor()

    """
    增加数据到数据库的操作：使用占位符进行批量操作
    """
    # 定义sql语句
    insertSql = 'INSERT INTO users(username, userpass, nickname, age) VALUES(%s, %s, %s, %s)'
    args = [("member1", "123", "会员1", 12),
            ("member2", "123", "会员2", 34),
            ("member3", "123", "会员3", 23),
            ("member4", "123", "会员4", 42)]

    # 执行sql语句
    rows = cursor.executemany(insertSql, args)
    # 将更改的数据提交更新
    conn.commit()
    print("共有%d条数据被添加到数据库中了" % rows)

except Exception as e:
    print("出现异常<%s>" % str(e))
finally:
    cursor.close()
    conn.close()

上述代码中，我们可以看到，sql语句只是定义了一条语句，但是在后面的参数却是一个列表，列表中包含了多条数据值，执行的时候多条数据值会一起插入到数据库中

打开sqlyog，执行情况数据表users 的操作

truncate table users; # 清空users表中的数据

执行上述程序,数据库中就出现对应的数据

pymysql批量执行增加数据操作

但是，我们要说的是但是
executemany(sql, args)函数只是适合执行多条数据，但是不要去执行大量数据（如执行几千几万条数据）
这是为什么呢？
因为常规项目中，会有批量删除、修改等操作，但是常规项目中的批量只是几十条数据，为了简化操作python提供了executemany()函数来实现了这样的功能
但是大量数据操作，使用executemany()反倒会影响执行效率，让数据库操作变得缓慢，此时建议根据不同的数据库使用多条sql语句拼接的方式来实现。

大牧莫邪.png

最后编辑于：2017.12.07 17:21:26

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 202,723评论 5赞 476
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,080评论 2赞 379
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 149,604评论 0赞 335
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,440评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,431评论 5赞 364
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,499评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,893评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,541评论 0赞 256
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,751评论 1赞 296
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,547评论 2赞 319
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,619评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,320评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,890评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,896评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,137评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,796评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,335评论 2赞 342