[Python基础21]数据库操作

最新推荐文章于 2024-09-10 15:45:22 发布

周小董

最新推荐文章于 2024-09-10 15:45:22 发布

阅读量253

点赞数

分类专栏： # Python基础

原文链接：http://www.jianshu.com/p/3d0281b5caae

版权

Python基础专栏收录该内容

21 篇文章 0 订阅

订阅专栏

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

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

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

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

首先：我们明白，编程语言和数据库各自都是什么

编程语言：专门用于进行数据处理的独立的个体
数据库：专门用于进行数据储存的独立的个体

也就是说，编程语言和数据库本身是两个完全独立的个体，为了让数据能更加优雅的持久的存储和处理，编程语言就得和数据库配合完成我们的工作

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

其次：编程语言，凭什么可以访问数据库

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

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

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，如下图

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

1.3. 没事，有我在！

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

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

接下来，安装它：

python3 -m pip install 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三种类型的操作，分别操作了不同的数据

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

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

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表中的数据

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

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