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

Alan

6 年前

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

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

1	$ sudo apt install mysql-server

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

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

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

$ 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

$ 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

1	$ 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>

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)

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;

1

show databases;
以下为使用数据库的命令：

use database_name;

1

use database_name;

一旦退出了MySQL控制台并在之后重新登入，我们应再次使用use database_name;ygqk语句。使用该命令的目的在于将内容保存到我们自己的数据库中。可通过以下的示例来清晰地理解这一问题：
以下命令可列出所有的数据表：

show tables;

1

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>

$ 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)

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>

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()

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

$ 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!')

# 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!

$ 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)

$ 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)

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)

# 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')

$ 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()

# 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

1	$ 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')

$ 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()

# 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

1	$ 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')

$ 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

1	$ sudo apt install sqlite3

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

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

$ sqlite3 test.db

1	$ 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

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!!')

import sqlite3

con_obj = sqlite3.connect('test.db')

print('Database connected successfully!!')

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

$ python connect_database.py

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

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

1 2	$ 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!!')

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!!

$ 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()

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

$ 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>

$ 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!!')

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!!

$ 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>

# 运行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!!')

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!!

$ 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

# 通过运行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

# 在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