老齐教室

Python中的SQL库：SQLite

2020-05-03

作者：老齐

与本文相关的图书推荐：《数据准备和特征工程》

现在所发布的各种网站，或者客户端应用的程序，绝大多数要跟数据库连接——毋庸置疑，大数据时代就这么来了。使用任何一种当下流行的高级编程语言，都可以通过某个专门的模块连接数据库。能够对数据库进行操作的软件程序，我们称之为“数据库管理系统（database management system，DBMS）。不同的数据库具有不同的DBMS，程序连接了DBMS就能够对数据库实施：

  • 增：向数据库中增加记录或者字段内容
  • 删：删除数据库中的记录或字段内容
  • 改：修改数据库中记录或字段内容
  • 查：根据一定要求查询记录或字段内容

这些基本操作。

本系列文章，将着重讨论Python语言为关系型数据库提供的SQL库，即用Python语言编写的程序，通过这些库（模块）连接DBMS，进而通过程序完成对数据库的各项操作。

数据库基本结构

本文中，我们会应用一个结构简单的数据库，它包括以下4张表：

  • users
  • posts
  • comments
  • likes

每张表的基本结构和各表之间的关系模式如下：

users和posts两张表之间是一对多的关系，即一个用户可以发布多篇文章。同样，一个用户也可以发布多篇评论，所以users和comments之间也是一对多的关系。至于users和likes两张表的关系，由此也就一目了然了，也是一对多。

连接数据库

本文要讨论的数据库是SQLite，这是一种小型数据库，它不需要启动数据库服务器，可以用类似读写文件的方式对这种数据库进行操作。Python标准库中已经提供了连接模块。

1
2
3
4
5
6
7
8
9
10
11
import sqlite3
from sqlite3 import Error

def create_connection(path):
    connection = None
    try:
        connection = sqlite3.connect(path)
        print("Connection to SQLite DB successful")
    except Error as e:
        print(f"The error '{e}' occurred")
    return connection

以上创建了一个连接数据库的函数create_connection，函数中的主要操作是connection = sqlite3.connect(path)，创建了与指定数据库（path参数）的连接对象。如果该数据库存在，则连接该数据库，否则，根据path创建此数据库并连接。

例如：

1
connection = create_connection("/Users/qiwsir/DB/sqlite/sm_app.sqlite")

这就在指定位置创建了名为sm_app.sqlite的数据库，并创建了链接对象。

创建数据库表

对于SQLite数据库，要在Python中执行各种SQL语句，需要通过cursor.execute()函数。下面就创建一个专门的函数，来完成各种SQL语句的操作。

1
2
3
4
5
6
7
8
def execute_query(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print("Query executed successfully")
    except Error as e:
        print(f"The error '{e}' occurred")

上述函数中的connection参数，是前面所创建的数据库连接对象。query则是SQL语句组成的字符串。

例如，如下是创建users表的SQL语句：

1
2
3
4
5
6
7
8
9
create_users_table = """
CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  age INTEGER,
  gender TEXT,
  nationality TEXT
);
"""

通过此语句，将创建users数据库表，其中包括的字段有：id, name, age, gender, nationality，然后用下面的方式，调用函数：

1
execute_query(connection, create_users_table)

如此，即创建了users数据库表，并具有前述个字段。

同样方法，编写创建posts表的SQL语句字符串：

1
2
3
4
5
6
7
8
9
create_posts_table = """
CREATE TABLE IF NOT EXISTS posts(
  id INTEGER PRIMARY KEY AUTOINCREMENT, 
  title TEXT NOT NULL, 
  description TEXT NOT NULL, 
  user_id INTEGER NOT NULL, 
  FOREIGN KEY (user_id) REFERENCES users (id)
);
"""

注意，前面已经说过了，users和posts两张表之间是一对多的关系，那么在posts表中就增加了一个名为user_id的字段作为外键，它的值与users表中的id字段值对应。

执行如下操作，创建posts表：

1
execute_query(connection, create_posts_table)

下面的程序，是创建另外两张表。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
create_comments_table = """
CREATE TABLE IF NOT EXISTS comments (
  id INTEGER PRIMARY KEY AUTOINCREMENT, 
  text TEXT NOT NULL, 
  user_id INTEGER NOT NULL, 
  post_id INTEGER NOT NULL, 
  FOREIGN KEY (user_id) REFERENCES users (id) FOREIGN KEY (post_id) REFERENCES posts (id)
);
"""

create_likes_table = """
CREATE TABLE IF NOT EXISTS likes (
  id INTEGER PRIMARY KEY AUTOINCREMENT, 
  user_id INTEGER NOT NULL, 
  post_id integer NOT NULL, 
  FOREIGN KEY (user_id) REFERENCES users (id) FOREIGN KEY (post_id) REFERENCES posts (id)
);
"""

execute_query(connection, create_comments_table)  
execute_query(connection, create_likes_table)

从上述操作可以看出，在Python程序中操作SQLite数据库，跟直接使用SQL语句差不多，只不过这里是将所有SQL语句放到字符串里面，而后通过cursor.execute()函数执行。不仅这里，后面的操作也都如此。

插入

向SQLite数据库插入记录，同样还是使用前面已经编写的execute_query()函数，我们需要做的就是编写INSERT INTO语句。例如：

1
2
3
4
5
6
7
8
9
10
11
12
create_users = """
INSERT INTO
  users (name, age, gender, nationality)
VALUES
  ('James', 25, 'male', 'USA'),
  ('Leila', 32, 'female', 'France'),
  ('Brigitte', 35, 'female', 'England'),
  ('Mike', 40, 'male', 'Denmark'),
  ('Elizabeth', 21, 'female', 'Canada');
"""

execute_query(connection, create_users)

上述操作是向users表中增加几条用户记录。

前面在创建users表的时候，将id字段设置为自增加了，所以在这里不需要为这个字段提供值，users表会自动将每个用户记录的id值从1开始增加。

再如，下面是向posts表中增加6条记录：

1
2
3
4
5
6
7
8
9
10
11
12
13
create_posts = """
INSERT INTO
  posts (title, description, user_id)
VALUES
  ("Happy", "I am feeling very happy today", 1),
  ("Hot Weather", "The weather is very hot today", 2),
  ("Help", "I need some help with my work", 2),
  ("Great News", "I am getting married", 1),
  ("Interesting Game", "It was a fantastic game of tennis", 5),
  ("Party", "Anyone up for a late-night party today?", 3);
"""

execute_query(connection, create_posts)

值得注意的是，user_id的值，因为它是posts的外键，其值必须是users表中id记录已有的值，否则就会报错。

用同样的方法，对另外两个表comments和likes都可以插入记录。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
create_comments = """
INSERT INTO
  comments (text, user_id, post_id)
VALUES
  ('Count me in', 1, 6),
  ('What sort of help?', 5, 3),
  ('Congrats buddy', 2, 4),
  ('I was rooting for Nadal though', 4, 5),
  ('Help with your thesis?', 2, 3),
  ('Many congratulations', 5, 4);
"""

create_likes = """
INSERT INTO
  likes (user_id, post_id)
VALUES
  (1, 6),
  (2, 3),
  (1, 5),
  (5, 4),
  (2, 4),
  (4, 2),
  (3, 6);
"""

execute_query(connection, create_comments)
execute_query(connection, create_likes)

以上执行INSERT INTO语句，也同样是应用execute_query()函数完成。

查询

毫无疑问，查询操作的SQL语句，也要放到cursor.execute()中执行，但是，这还没完，因为还要有查询的返回结果，就是调用cursor.fetchall()，得到元组组成的列表，每个元组就是数据库表中的一个记录。

为此，可以编写一个用于查询操作的函数。

1
2
3
4
5
6
7
8
9
def execute_read_query(connection, query):
    cursor = connection.cursor()
    result = None
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except Error as e:
        print(f"The error '{e}' occurred")

例如查询users表中的所有记录：

1
2
3
4
5
select_users = "SELECT * from users"
users = execute_read_query(connection, select_users)

for user in users:
    print(user)

返回结果如下：

1
2
3
4
5
(1, 'James', 25, 'male', 'USA')
(2, 'Leila', 32, 'female', 'France')
(3, 'Brigitte', 35, 'female', 'England')
(4, 'Mike', 40, 'male', 'Denmark')
(5, 'Elizabeth', 21, 'female', 'Canada')

类似地，可以查询posts的所有记录：

1
2
3
4
5
select_posts = "SELECT * FROM posts"
posts = execute_read_query(connection, select_posts)

for post in posts:
    print(post)

输出结果：

1
2
3
4
5
6
(1, 'Happy', 'I am feeling very happy today', 1)
(2, 'Hot Weather', 'The weather is very hot today', 2)
(3, 'Help', 'I need some help with my work', 2)
(4, 'Great News', 'I am getting married', 1)
(5, 'Interesting Game', 'It was a fantastic game of tennis', 5)
(6, 'Party', 'Anyone up for a late-night party today?', 3)

以上是比较简单的查询操作，如果进行稍微复杂的，比SQL语句中的JOIN和WHERE等，也都能用同样的方法实现。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select_users_posts = """
SELECT
  users.id,
  users.name,
  posts.description
FROM
  posts
  INNER JOIN users ON users.id = posts.user_id
"""

users_posts = execute_read_query(connection, select_users_posts)

for users_post in users_posts:
    print(users_post)

输出：

1
2
3
4
5
6
(1, 'James', 'I am feeling very happy today')
(2, 'Leila', 'The weather is very hot today')
(2, 'Leila', 'I need some help with my work')
(1, 'James', 'I am getting married')
(5, 'Elizabeth', 'It was a fantastic game of tennis')
(3, 'Brigitte', 'Anyone up for a late night party today?')

再如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select_posts_comments_users = """
SELECT
  posts.description as post,
  text as comment,
  name
FROM
  posts
  INNER JOIN comments ON posts.id = comments.post_id
  INNER JOIN users ON users.id = comments.user_id
"""

posts_comments_users = execute_read_query(
    connection, select_posts_comments_users
)

for posts_comments_user in posts_comments_users:
    print(posts_comments_user)

输出：

1
2
3
4
5
6
('Anyone up for a late night party today?', 'Count me in', 'James')
('I need some help with my work', 'What sort of help?', 'Elizabeth')
('I am getting married', 'Congrats buddy', 'Leila')
('It was a fantastic game of tennis', 'I was rooting for Nadal though', 'Mike')
('I need some help with my work', 'Help with your thesis?', 'Leila')
('I am getting married', 'Many congratulations', 'Elizabeth')

上述操作中，通过cursor.fetchall()方法，返回了所有的记录内容。如果要查看返回的记录名称，怎么操作？可以使用cursor对象的description属性。

1
2
3
4
5
6
cursor = connection.cursor()
cursor.execute(select_posts_comments_users)
cursor.fetchall()

column_names = [description[0] for description in cursor.description]
print(column_names)

输出：

1
['post', 'comment', 'name']

下面的代码，演示了含WHERE的SQL语句查询结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select_post_likes = """
SELECT
  description as Post,
  COUNT(likes.id) as Likes
FROM
  likes,
  posts
WHERE
  posts.id = likes.post_id
GROUP BY
  likes.post_id
"""

post_likes = execute_read_query(connection, select_post_likes)

for post_like in post_likes:
    print(post_like)

输出：

1
2
3
4
5
('The weather is very hot today', 1)
('I need some help with my work', 1)
('I am getting married', 2)
('It was a fantastic game of tennis', 1)
('Anyone up for a late night party today?', 2)

更新

有了前面的操作经验，再执行UPDATE，就轻车熟路了。

先查询posts中id=2的记录：

1
2
3
4
5
6
select_post_description = "SELECT description FROM posts WHERE id = 2"

post_description = execute_read_query(connection, select_post_description)

for description in post_description:
    print(description)

输出：

1
('The weather is very hot today',)

将此条记录内容进行更新：

1
2
3
4
5
6
7
8
9
10
update_post_description = """
UPDATE
  posts
SET
  description = "The weather has become pleasant now"
WHERE
  id = 2
"""

execute_query(connection, update_post_description)

如果再用前面的方法进行查询，就可以看到更新之后的结果了。

删除

执行删除操作的SQL语句，可以通过前面所创建的execute_query()函数完成。

1
2
delete_comment = "DELETE FROM comments WHERE id = 5"
execute_query(connection, delete_comment)

以上演示了在Python程序中对SQLite数据库的各项操作，后续会继续演示其他常用关系型数据库，敬请关注。

搜索技术问答的公众号：老齐教室

在公众号中回复：老齐，可查看所有文章、书籍、课程。

← 剖析Web技术栈（四） Python中的SQL库：MySQL →
赏
使用支付宝打赏
使用微信打赏

若你觉得我的文章对你有帮助，欢迎点击上方按钮对我打赏

关注微信公众号，读文章、听课程，提升技能