Skip to content

18.md

Latest commit

 

History

History
384 lines (257 loc) · 19.9 KB

18.md

File metadata and controls

384 lines (257 loc) · 19.9 KB
Raw

十八、MySQL 和 SQLite 数据库管理

在本章中，您将了解 MySQL 和 SQLite 数据库管理。您将学习安装 MySQL 和 SQLite。您还将学习如何创建用户、授予特权、创建数据库、创建表、将数据插入表、查看表特定记录中的所有记录以及更新和删除数据。

在本章中，您将学习以下内容：

  • MySQL 数据库管理
  • SQLite 数据库管理

MySQL 数据库管理

本节将介绍使用 Python 的 MySQL 数据库管理。您已经知道 Python 有各种用于mysql数据库管理的模块。因此，我们将在这里了解 MySQLdb 模块。mysqldb模块是 MySQL 数据库服务器的接口，用于提供 Python 数据库 API。

让我们学习如何安装 MySQL 和 Pythonmysqldb包。为此，请在终端中运行以下命令：

$ sudo apt install mysql-server

此命令安装 MySQL 服务器和各种其他软件包。安装软件包时，系统会提示我们输入 MySQL 根帐户的密码：

  • 以下代码用于检查要安装的mysqldb包：
$ apt-cache search MySQLdb
  • 下面是安装 MySQL 的 Python 接口：
$ sudo apt-get install python3-mysqldb
  • 现在，我们将检查mysql是否安装正确。为此，请在终端中运行以下命令：
student@ubuntu:~$ sudo mysql -u root -p

命令运行后，您将获得以下输出：

Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.7.24-0ubuntu0.18.04.1 (Ubuntu)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

通过运行sudo mysql -u root -p，您将获得mysql控制台。有一些命令用于列出数据库和表，以及使用数据库存储我们的工作。我们将逐一看到：

  • 这用于列出所有数据库：
show databases;
  • 这是用于使用数据库的：
use database_name;

每当我们走出 MySQL 控制台并在一段时间后再次登录时，我们必须使用use database_name;语句。使用此命令的目的是将我们的工作保存在数据库中。我们可以通过以下示例详细了解这一点：

  • 以下代码用于列出所有表：
show tables;

这些是用于列出数据库、使用数据库和列出表的命令。

现在，我们将在mysql控制台中使用 CREATEDATABASE 语句创建一个数据库。现在，使用mysql -u root -p打开mysql控制台，然后输入您在安装时输入的密码，然后按输入。接下来，创建数据库。在本节中，我们将创建一个名为test的数据库，并在本节中使用该数据库：

student@ubuntu:~/work/mysql_testing$ sudo mysql -u root -p  Output: Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 16 Server version: 5.7.24-0ubuntu0.18.04.1 (Ubuntu)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | sys                | +--------------------+ 4 rows in set (0.10 sec)
mysql> create database test; Query OK, 1 row affected (0.00 sec)
mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | sys                | | test               | +--------------------+ 5 rows in set (0.00 sec)
mysql> use test; Database changed mysql>

首先，我们使用 show 数据库列出了所有数据库。接下来，我们使用 createdatabase语句创建了数据库测试。同样，我们执行 showdatabases 来查找数据库是否已创建。我们的数据库现在已创建。接下来，我们使用该数据库存储我们正在做的工作。

现在，我们将创建一个用户并向该用户授予特权。运行以下命令：

mysql> create user 'test_user'@'localhost' identified by 'test123'; Query OK, 0 rows affected (0.06 sec) mysql> grant all on test.* to 'test_user'@'localhost'; Query OK, 0 rows affected (0.02 sec)
mysql>

我们创建了一个test_user用户；该用户的密码为test123。接下来，我们将所有特权授予我们的test_user用户。现在，通过运行quit;或exit;命令从mysql控制台出来。

现在，我们将看到一些获取数据库版本、创建表、向表中插入一些数据、更新数据和删除数据的示例。

获取数据库版本

首先，我们将看到一个获取数据库版本的示例。为此，我们将创建一个get_database_version.py脚本，并在其中写入以下内容：

import MySQLdb as mdb import sys  con_obj = mdb.connect('localhost', 'test_user', 'test123', 'test') cur_obj = con_obj.cursor() cur_obj.execute("SELECT VERSION()") version = cur_obj.fetchone() print ("Database version: %s " % version) con_obj.close()

It is very important to follow the previous steps before running this script; they should not be skipped. 

运行脚本，您将获得以下输出：

student@ubuntu:~/work/mysql_testing$ python3 get_database_version.py Output: Database version: 5.7.24-0ubuntu0.18.04.1

在前面的示例中，我们得到了数据库版本。为此，我们首先导入了 MySQLdb 模块。然后我们编写了连接字符串。在连接字符串中，我们提到了用户名、密码和数据库名。接下来，我们创建了一个游标对象，用于执行 SQL 查询。在execute()中，我们传递了一个 SQL 查询。fetchone()检索下一行查询结果。接下来，我们打印结果。close()方法关闭数据库连接。

创建表并插入数据

现在，我们将创建一个表，并向其中插入一些数据。为此，创建一个create_insert_data.py脚本，并在其中写入以下内容：

import MySQLdb as mdb con_obj = mdb.connect('localhost', 'test_user', 'test123', 'test') with con_obj:
 cur_obj = con_obj.cursor() cur_obj.execute("DROP TABLE IF EXISTS books") cur_obj.execute("CREATE TABLE books(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(100))") cur_obj.execute("INSERT INTO books(Name) VALUES('Harry Potter')") cur_obj.execute("INSERT INTO books(Name) VALUES('Lord of the rings')") cur_obj.execute("INSERT INTO books(Name) VALUES('Murder on the Orient Express')") cur_obj.execute("INSERT INTO books(Name) VALUES('The adventures of Sherlock Holmes')") cur_obj.execute("INSERT INTO books(Name) VALUES('Death on the Nile')") print("Table Created !!") print("Data inserted Successfully !!")

运行脚本，您将获得以下输出：

student@ubuntu:~/work/mysql_testing$ python3 create_insert_data.py Output: Table Created !! Data inserted Successfully !!

要检查您的表是否成功创建，请打开mysql控制台并运行以下命令：

student@ubuntu:~/work/mysql_testing$ sudo mysql -u root -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.24-0ubuntu0.18.04.1 (Ubuntu)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | books          | +----------------+ 1 row in set (0.00 sec)

您可以看到已创建了“table books”。

检索数据

为了从表中检索数据，我们使用select语句。现在，我们将从 books 表中检索数据。为此，创建一个retrieve_data.py脚本，并在其中写入以下内容：

import MySQLdb as mdb con_obj = mdb.connect('localhost', 'test_user', 'test123', 'test') with con_obj:
 cur_obj = con_obj.cursor() cur_obj.execute("SELECT * FROM books") records = cur_obj.fetchall() for r in records: print(r)

运行脚本，您将获得如下输出：

student@ubuntu:~/work/mysql_testing$ python3 retrieve_data.py Output: (1, 'Harry Potter') (2, 'Lord of the rings') (3, 'Murder on the Orient Express') (4, 'The adventures of Sherlock Holmes') (5, 'Death on the Nile')

在前面的示例中，我们从表中检索数据。我们使用了 MySQLdb 模块。我们编写了一个连接字符串并创建了一个游标对象来执行 SQL 查询。在execute()中，我们编写了一条 SQLselect语句。最后，我们打印了记录。

更新数据

现在，如果我们想对记录进行一些更改，我们可以使用 SQLupdate语句。我们将看到一个update语句的示例。为此，创建一个update_data.py脚本，并在其中写入以下内容：

import MySQLdb as mdb con_obj = mdb.connect('localhost', 'test_user', 'test123', 'test') cur_obj = con_obj.cursor() cur_obj.execute("UPDATE books SET Name = 'Fantastic Beasts' WHERE Id = 1")try:
 con_obj.commit() except:
 con_obj.rollback()

按如下方式运行脚本：

student@ubuntu:~/work/mysql_testing$ python3 update_data.py

现在，要检查您的记录是否更新，请按如下方式运行retrieve_data.py：

student@ubuntu:~/work/mysql_testing$ python3 retrieve_data.py Output: (1, 'Fantastic Beasts') (2, 'Lord of the rings') (3, 'Murder on the Orient Express') (4, 'The adventures of Sherlock Holmes') (5, 'Death on the Nile')

您可以看到 ID1已更新的数据。在前面的示例中，我们在execute()中编写了一条update语句，该语句将更新 ID1的数据。

删除数据

要从表中删除特定记录，请使用delete语句。我们将看到一个删除数据的示例。创建一个delete_data.py脚本，并在其中写入以下内容：

import MySQLdb as mdb con_obj = mdb.connect('localhost', 'test_user', 'test123', 'test') cur_obj = con_obj.cursor() cur_obj.execute("DELETE FROM books WHERE Id = 5"); try:
 con_obj.commit() except:
 con_obj.rollback()

按如下方式运行脚本：

student@ubuntu:~/work/mysql_testing$ python3 delete_data.py

现在，要检查您的记录是否被删除，请按如下方式运行retrieve_data.py脚本：

student@ubuntu:~/work/mysql_testing$ python3 retrieve_data.py Output: (1, 'Fantastic Beasts') (2, 'Lord of the rings') (3, 'Murder on the Orient Express') (4, 'The adventures of Sherlock Holmes')

您可以看到您的 ID 为5的记录被删除。在前面的示例中，我们使用了delete语句来删除特定的记录。这里，我们删除了 ID 为5的记录。您还可以根据选择的任何字段名删除记录。

SQLite 数据库管理

在本节中，我们将学习如何安装和使用 SQLite。Python 有sqlite3模块来执行 SQLite 数据库任务。SQLite 是一个无服务器、零配置、事务性 SQL 数据库引擎。SQLite 非常快速和轻量级。整个数据库存储在一个磁盘文件中。

现在，我们将首先安装 SQLite。在终端中运行以下命令：

$ sudo apt install sqlite3

在本节中，我们将学习以下操作：创建数据库、创建表、将数据插入表、检索数据以及更新和删除表中的数据。我们将逐一查看每个操作。

现在，首先，我们将看到如何在 SQLite 中创建数据库。要创建数据库，只需在终端中编写命令，如下所示：

$ sqlite3 test.db

运行此命令后，您将在终端中打开sqlite控制台，如下所示：

student@ubuntu:~$ sqlite3 test.db SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. sqlite>

现在，您的数据库已通过简单运行sqlite3 test.db创建。

连接到数据库

现在，我们将看到如何连接到数据库。为此，我们将创建一个脚本。Python 已经在标准库中包含了一个sqlite3模块。我们只要在使用 SQLite 时导入它就行了。创建一个connect_database.py脚本，并在其中写入以下内容：

import sqlite3 con_obj = sqlite3.connect('test.db') print ("Database connected successfully !!")

运行脚本，您将获得以下输出：

student@ubuntu:~/work $ python3 connect_database.py Output: Database connected successfully !!

在前面的示例中，我们导入了sqlite3模块来执行该功能。现在，检查您的目录，您将找到在您的目录中创建的test.db文件。

创建表

现在，我们将在数据库中创建一个表。为此，我们将创建一个create_table.py脚本，并在其中写入以下内容：

import sqlite3 con_obj = sqlite3.connect("test.db") with con_obj:
 cur_obj = con_obj.cursor() cur_obj.execute("""CREATE TABLE books(title text, author text)""")  print ("Table created")

运行脚本，您将获得如下输出：

student@ubuntu:~/work $ python3 create_table.py Output: Table created

在前面的示例中，我们使用CREATE TABLE语句创建了一个表 books。首先，我们使用test.db与我们的数据库建立了连接。接下来，我们创建了一个游标对象，用于在数据库上执行 SQL 查询。

插入数据

现在，我们将把数据插入表中。为此，我们将创建一个insert_data.py脚本，并在其中写入以下内容：

import sqlite3 con_obj = sqlite3.connect("test.db") with con_obj:
 cur_obj = con_obj.cursor() cur_obj.execute("INSERT INTO books VALUES ('Pride and Prejudice', 'Jane Austen')") cur_obj.execute("INSERT INTO books VALUES ('Harry Potter', 'J.K Rowling')") cur_obj.execute("INSERT INTO books VALUES ('The Lord of the Rings', 'J. R. R. Tolkien')") cur_obj.execute("INSERT INTO books VALUES ('Murder on the Orient Express', 'Agatha Christie')") cur_obj.execute("INSERT INTO books VALUES ('A Study in Scarlet', 'Arthur Conan Doyle')") con_obj.commit() print("Data inserted Successfully !!")

运行脚本，您将获得以下输出：

student@ubuntu:~/work$ python3 insert_data.py Output: Data inserted Successfully !!

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

检索数据

现在，我们将从表中检索数据。为此，创建一个retrieve_data.py脚本，并在其中写入以下内容：

import sqlite3 con_obj = sqlite3.connect('test.db') cur_obj = con_obj.execute("SELECT title, author from books")
for row in cur_obj:
 print ("Title = ", row[0]) print ("Author = ", row[1], "\n") con_obj.close()

运行脚本，您将获得如下输出：

student@ubuntu:~/work$ python3 retrieve_data.py Output: Title =  Pride and Prejudice Author =  Jane Austen
Title =  Harry Potter Author =  J.K Rowling
Title =  The Lord of the Rings Author =  J. R. R. Tolkien
Title =  Murder on the Orient Express Author =  Agatha Christie
Title =  A Study in Scarlet Author =  Arthur Conan Doyle

在前面的示例中，我们导入了sqlite3模块。接下来，我们连接我们的test.db数据库。为了检索数据，我们使用了select语句。最后，我们打印了检索到的数据。

您也可以在sqlite3控制台中检索数据。为此，请首先启动 SQLite 控制台，然后按如下方式检索数据：

student@ubuntu:~/work/sqlite3_testing$ sqlite3 test.db Output: SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. sqlite> sqlite> select * from books; Pride and Prejudice|Jane Austen Harry Potter|J.K Rowling The Lord of the Rings|J. R. R. Tolkien Murder on the Orient Express|Agatha Christie A Study in Scarlet|Arthur Conan Doyle sqlite>

更新数据

我们可以使用update语句更新表中的数据。现在，我们将看到一个更新数据的示例。为此，创建一个update_data.py脚本，并在其中写入以下内容：

import sqlite3 con_obj = sqlite3.connect("test.db") with con_obj:
            cur_obj = con_obj.cursor()
 sql = """ UPDATE books SET author = 'John Smith' WHERE author = 'J.K Rowling' """ cur_obj.execute(sql) print("Data updated Successfully !!")

运行脚本，您将获得以下输出：

student@ubuntu:~/work $ python3 update_data.py Output: Data updated Successfully !!

现在，要检查数据是否实际更新，您可以运行retrieve_data.py，或者您可以转到 SQLite 控制台并运行select * from books;。您将获得更新的输出，如下所示：

By running retrieve_data.py: Output: student@ubuntu:~/work$ python3 retrieve_data.py Title =  Pride and Prejudice Author =  Jane Austen
Title =  Harry Potter Author =  John Smith
Title =  The Lord of the Rings Author =  J. R. R. Tolkien
Title =  Murder on the Orient Express Author =  Agatha Christie
Title =  A Study in Scarlet Author =  Arthur Conan Doyle
Checking on SQLite console: Output: student@ubuntu:~/work$ sqlite3 test.db SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. sqlite> sqlite> select * from books; Pride and Prejudice|Jane Austen Harry Potter|John Smith The Lord of the Rings|J. R. R. Tolkien Murder on the Orient Express|Agatha Christie A Study in Scarlet|Arthur Conan Doyle sqlite>

删除数据

现在，我们将看到一个从表中删除数据的示例。我们将使用delete语句来实现这一点。创建一个delete_data.py脚本，并在其中写入以下内容：

import sqlite3 con_obj = sqlite3.connect("test.db") with con_obj:
 cur_obj = con_obj.cursor()            sql = """
 DELETE FROM books WHERE author = 'John Smith' """ cur_obj.execute(sql) print("Data deleted successfully !!")

运行脚本，您将获得以下输出：

student@ubuntu:~/work $ python3 delete_data.py Output: Data deleted successfully !!

在前面的示例中，我们从表中删除了一条记录。我们使用了deleteSQL 语句。现在，要检查数据删除是否成功，请运行retrieve_data.py或启动 SQLite 控制台，如下所示：

By running retrieve_data.py Output: student@ubuntu:~/work$ python3 retrieve_data.py Title =  Pride and Prejudice Author =  Jane Austen
Title =  The Lord of the Rings Author =  J. R. R. Tolkien
Title =  Murder on the Orient Express Author =  Agatha Christie
Title =  A Study in Scarlet Author =  Arthur Conan Doyle

您可以看到作者为john smith的记录被删除：

Checking on SQLite console: Output: student@ubuntu:~/work$ sqlite3 test.db SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. sqlite> sqlite> select * from books; Pride and Prejudice|Jane Austen The Lord of the Rings|J. R. R. Tolkien Murder on the Orient Express|Agatha Christie A Study in Scarlet|Arthur Conan Doyle sqlite>

总结

在本章中，我们学习了 MySQL 以及 SQLite 数据库管理。我们创建了数据库和表。然后我们在表中插入了一些记录。使用select语句，我们检索了记录。我们还学习了如何更新和删除数据。

问题

  1. 数据库的用途是什么？
  2. 数据库中的 CRUD 是什么？
  3. 我们可以连接远程数据库吗？如果是，请举例说明。
  4. 我们可以在 Python 代码中编写触发器和过程吗？
  5. 什么是 DML 和 DDL 语句？

进一步阅读