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

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

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

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

MySQL数据库管理

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

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

$ sudo apt install mysql-server

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

以下命令用于查看是否存在 mysqldb包来供安装

$ apt-cache search MySQLdb
# 译者注：安装如下包，否则后续安装可能会报OSError: mysql_config not found
sudo apt-get install libmysqlclient-dev

运行如下命令来安装Python的MySQL接口：

$ sudo apt-get install python3-mysqldb
# 译者注：MySQLdb对Python3的支持有些变化，请使用如下方式安装即可导入MySQLdb了
sudo pip3 install mysqlclient
# 也可使用 pymysql，此时应为import pymysql
sudo pip3 install pymysql

下面我们将查看是否正确地安装了MySQL，在终端中运行如下命令：
```
$ sudo mysql -u root -p
```
运行以上命令，我们将得到如下输出：
```
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.25-0ubuntu0.16.04.2 (Ubuntu)

Copyright (c) 2000, 2019, 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;ygqk语句。使用该命令的目的在于将内容保存到我们自己的数据库中。可通过以下的示例来清晰地理解这一问题：
- 以下命令可列出所有的数据表：
```
show tables;
```

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

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

$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.25-0ubuntu0.16.04.2 (Ubuntu)

Copyright (c) 2000, 2019, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 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.01 sec)

mysql> use test;
Database changed
mysql>

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

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

mysql> create user 'test_user'@'localhost' identified by 'test123';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on test.* to 'test_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql>

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

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

获取数据库版本

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

import MySQLdb as db
# import pymysql as db
import sys

con_obj = db.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()

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

运行脚本，我们将得到如下输出：

$ python3 get_database_version.py
# 输出结果：
Database version: 5.7.25-0ubuntu0.16.04.2

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

创建数据表和插入数据

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

# import MySQLdb as db
import pymysql as db

con_obj = db.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!')

运行脚本，我们将得到如下输出：

$ python3 create_insert_data.py

# 输出结果：
Table Created!!
Data inserted successfully!

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

$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.25-0ubuntu0.16.04.2 (Ubuntu)

Copyright (c) 2000, 2019, 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> 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)

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

获取数据

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

# import MySQLdb as db
import pymysql as db

con_obj = db.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)

运行脚本，我们将得到如下输出：

$ python3 retrieve_data.py
# 输出结果
(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()中，我们编写了一个SQL select语名。最后，我们打印出了查询到的记录。

更新数据

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

# import MySQLdb as db
import pymysql as db

con_obj = db.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()

运行脚本如下：

$ python3 update_data.py

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

$ python3 retrieve_data.py
# 输出结果：
(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')

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

删除数据

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

# import MySQLdb as db
import pymysql as db

con_obj = db.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()

运行脚本如下：

$ python3 delete_data.py

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

$ python3 retrieve_data.py
# 输出结果：
(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控制台如下：

$ sqlite3 test.db
SQLite version 3.11.0 2016-02-15 17:29:24
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!!')

运行脚本，我们将得到如下输出：

$ python connect_database.py

# 输出结果：
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')

运行脚本，我们将得到如下输出：

$ python3 create_table.py
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!!')

运行脚本，我们将得到如下输出：

$ python insert_data.py
# 输出结果：
Data inserted successfully!!

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

获取数据

下面我们将从数据表中获取数据。为此创建一个脚本retrieve_sqlite_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()

运行脚本，我们将得到如下输出：

$ python3 retrieve_sqlite_data.py
# 输出结果：
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终端并随后获取数据如下：

$ sqlite3 test.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
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_sqlite_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!!')

运行脚本，我们将得到如下输出：

$ python3 update_sqlite_data.py
# 输出结果：
Data updated successfully!!

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

# 运行retrieve_sqlite_data.py脚本的输出结果如下：
$ python3 retrieve_sqlite_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

# 在SQLite的终端中查看：

$ sqlite3 test.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
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_sqlite_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!!')

运行脚本，我们将得到如下输出：

$ python3 delete_sqlite_data.py
# 输出结果：
Data deleted successfully!!

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

# 通过运行retrieve_sqlite_data.py后的输出结果：

$ python3 retrieve_sqlite_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 的记录被删除了。

# 在SQLite终端中查看：

$ sqlite3 test.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
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

总结

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

课后问题

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

扩展阅读

使用PyMySQL库: http://zetcode.com/python/pymysql/
MySQLdb, Python连接指南: https://mysqlclient.readthedocs.io/
SQLite数据库的DB-API 2.0接口: https://docs.python.org/3/library/sqlite3.html

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

18.md

18.md

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

MySQL数据库管理

获取数据库版本

创建数据表和插入数据

获取数据

更新数据

删除数据

SQLite数据库管理

连接数据库

创建数据表

插入数据

获取数据

更新数据

删除数据

总结

课后问题

扩展阅读

Files

18.md

Latest commit

History

18.md

File metadata and controls

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

MySQL数据库管理

获取数据库版本

创建数据表和插入数据

获取数据

更新数据

删除数据

SQLite数据库管理

连接数据库

创建数据表

插入数据

获取数据

更新数据

删除数据

总结

课后问题

扩展阅读