Alan Hou的个人博客

第十八章 MySQL和SQLite数据库管理

精通Python自动化脚本-运维人员宝典完整目录:

第一章 Python脚本概述
第二章 Python脚本调试和性能测试
第三章 单元测试-单元测试框架的介绍
第四章 自动化常规运维活动
第五章 文件、目录和数据处理
第六章 文件存档、加密和解密
第七章 文本处理和正则表达式
第八章 文档和报告
第九章 操作各类文件
第十章 网络基础 – Socket编程
第十一章 使用Python脚本处理邮件
第十二章 使用Telnet和SSH远程监控主机
第十三章 创建图形化用户界面
第十四章 处理Apache和其它的日志文件
第十五章 SOAP和REST API通讯
第十六章 网络抓取 – 从网站上提取有用的信息
第十七章 数据收集及报表
第十八章 MySQL和SQLite数据库管理

本章中我们将学习MySQL和SQLite数据库管理。我们会安装MySQL和SQLite。我们还将学习如何 创建用户、授权、创建数据库、创建数据表、向表中插入数据以及从指定表记录中查看所有记录、更新和删除数据。

本章中我们将学习如下内容:

MySQL数据库管理

这一部分将涵盖使用Python管理MySQL数据库的知识。我们已知Python有各种管理MySQL数据库的模块。这里我们将学习MySQLdb模块。MySQLdb模块是一个MySQL数据库的接口,用于提供Python数据库API。

我们来学习如何安装MySQL以及Python的MySQLdb包。为此在Terminal中运行如下命令:

这一命令安装MySQL服务及其它各个包。在安装包时,会弹出让我们输入MySQL的root账号密码:

这些就是我们用于列出数据库、使用数据库以及列出数据表的命令。

下面,我们将使用create database语句在MySQL终端中创建一个数据库。使用mysql -u root -p命令并输入密码来打开MySQL终端,密码为我们安装时所设置的。然后创建我们自己的数据库。这一部分中,我们将创建一个名为test的数据库并在整个部分中使用该数据库:

首先我们使用show databases列出了所有数据库。接着我们使用create database语句创建了数据库test。又再次运行了show databases语句来查看数据库是否成功创建。可以看到数据库已创建。下面我们使用该数据库来存储我们的内容。

下面,我们将创建一个用户并为该用户授权。运行如下命令:

我们创建了test_user用户,该用户的密码为test123。接着我们对用户test_user进行了授权。这时我们通过在MySQL终端中quit;或exit;可退出控制台。

下面我们来看一些示例:获取取数据库版本、创建数据表、在数据表中插入数据、更新数据和删除数据。

获取数据库版本

首先我们来看一个获取数据库版本的示例。为此创建一个脚本get_database_version.py并在其中编写如下内容:

ℹ️在运行脚本前应按照前述的步骤进行操作,否则将无法正常运行。

运行脚本,我们将得到如下输出:

上例中,我们获取了数据库的版本。实现获取,我们导入了MySQLdb模块。然后编写了连接字符串。在连接字符串中,我们传入了用户名、密码和数据库名。然后创建了一个游标(cursor)对象用于执行SQL查询。在execute()中我们传入了SQL查询语句。fetchone() 获取查询结果的下一行。然后我们打印出了结果。close()方法关闭了数据库连接。

创建数据表和插入数据

下面我们将创建一个数据表并在其中插入数据。为此创建一个脚本create_insert_data.py并在其中编写如下内容:

运行脚本,我们将得到如下输出:

要查看数据表是否成功创建了,打开MySQL控制台并运行如下命令:

可以看到数据表 books 已创建。

获取数据

要从数据表获取数据,我们使用select语句。下面我们将从books数据表中获取数据。为此创建脚本retrieve_data.py并在其中编写如下内容:

 

运行脚本,我们将得到如下输出:

上例中,我们从数据表中获取到了数据。我们使用了MySQLdb模块。编写了一个连接字符串并创建了一个游标对象来执行SQL查询。在execute()中,我们编写了一个SQL select语名。最后,我们打印出了查询到的记录。

更新数据

下面我们要对数据表中的记录做一些修改,这时可使用SQL的update语句。我们来看一个update语句的示例。为此创建一个脚本update_data.py并在其中编写如下内容:

运行脚本如下:

下面来查看记录是否被更新了,运行retrieve_data.py如下:

可以看到ID为1的数据被更新了。上例在execute()中,我们编写了一个update语句来更新ID为1记录的数据。

删除数据

要在数据表中删除指定记录,可以使用delete语句。我们来看一个删除数据的示例。创建一个脚本delete_data.py并在其中编写如下内容:

运行脚本如下:

下面查看记录是否成功删除,运行retrieve_data.py脚本如下:

我们可以看到记录中ID为5的数据已删除。上例中,我们使用了delete语句来删除指定记录。这里我们删除了ID为5的记录。我们还可以根据其它所选的字段名来删除记录。

SQLite数据库管理

这一部分中,我们将学习如何安装和使用SQLite。Python中有一个sqlite3模块来执行SQLite数据库相关任务。SQLite是一个无服务端、零配置的事务性SQL数据库引擎。SQLite非常快速且轻量。整个数据库存储在单个磁盘文件中。

下面我们首先安装SQLite。在终端中运行如下命令:

这一部分中,我们将学习如下操作:创建数据库、创建数据表、在数据表中插入数据、获取数据、从数据表中更新和删除数据。我们将逐一来看各个操作。

首先我们将来看如何在SQLite中创建数据库。要创建数据库,我们仅需在终端中写入如下命令:

在运行这一命令后,将会在终端中打开sqlite控制台如下:

于是我们仅仅通过运行sqlite3 test.db就创建了数据库。

连接数据库

下面我们来看如何连接数据库。为此我们将创建一个脚本。Python的标准库中已经包含了sqlite3模块。我们只需在要操作SQLite时导入该模块就可以了。创建一个脚本connect_database.py并在其中编写如下内容:

运行脚本,我们将得到如下输出:

上例中,我们导入了sqlite3模块来执行相关功能。此时查看所在目录,就会看到其中创建了test.db文件。

创建数据表

下面我们来数据库中创建数据表。为此创建一个脚本create_table.py并在其中编写如下内容:

运行脚本,我们将得到如下输出:

上例中我们使用CREATE TABLE语句创建了数据表books。首先,我们使用test.db建立了数据库连接。然后,我们创建一个游标对象用于对数据库执行SQL查询。

插入数据

下面我们将在数据表中插入数据。为此创建一个脚本insert_data.py并在其中编写如下内容:

运行脚本,我们将得到如下输出:

上例中我们在数据表中插入了一些数据。为此我们在SQL语句中使用了insert。通过使用commit(),我们告诉数据库保存所有的当前事务。

获取数据

下面我们将从数据表中获取数据。为此创建一个脚本retrieve_sqlite_data.py并在其中编写如下内容:

运行脚本,我们将得到如下输出:

上例中,我们导入了sqlite3模块。然后我们连接了test.db数据库。我们使用了select来获取数据。最后,我们打印出了所获取的数据。

我们也可以在sqlite3控制台中获取数据。首先启动SQLite终端并随后获取数据如下:

更新数据

可以使用update语句来更新数据表中的数据。下面我们来看一个更新数据的示例。为此创建一个脚本update_sqlite_data.py并在其中编写如下内容:

运行脚本,我们将得到如下输出:

这时要检查是否更新了数据,可以运行retrieve_sqlite_data.py或者进行SQLite控制台并运行select * from books;。我们将得到更新后的输出如下:

删除数据

下面我们来看一个从数据表中删除数据的示例。我们将使用delete语句来进行实现。创建一个脚本delete_sqlite_data.py并在其中编写如下内容:

运行脚本,我们将得到如下输出:

上例中,我们从数据表中删除了一条记录。我们使用了SQL中的delete语句。下面来查看数据是否删除成功,可运行retrieve_sqlite_data.py或在SQLite终端中启动如下:

可以看到作者为 John Smith 的记录被删除了。

总结

本章中,我们学习对MySQL和SQLite数据库的管理。我们创建了数据库及数据表。然后在数据表中插入了几条记录。使用select语句我们获取到了记录。我们还学习了更新和删除数据。

课后问题

  1. 数据库的作用是什么?
  2. 数据库中的CRUD是什么?
  3. 我们是否可以远程连接数据库?若可以,请用示例进行说明。
  4. 是否可在Python代码内编写触发器和存储过程?
  5. DML和DDL语句是什么?

扩展阅读

退出移动版