使用 psycopg2 在 Python 中管理 PostgreSQL 数据库

在 PostgreSQL 中创建表

现在是时候在 datacamp_courses 数据库中创建您的第一张表了。我们希望创建一张包含 DataCamp 课程目录中部分课程信息的表。该表的模式如下：

该规范为我们提供了关于表列的相当多信息。表的主键应为 course_id，其数据类型为整数。主键是一种约束，要求该列的值非空且唯一。它使您能够唯一标识表中的特定实例或一组实例。

其余列提供了课程名称、授课教师姓名以及课程主题等信息。

在创建表之前，有必要解释一下您刚创建的连接实例是如何工作的。本质上，连接封装了一个数据库会话，它允许您使用 cursor() 方法执行 SQL 命令和查询，例如 SELECT、INSERT、CREATE、UPDATE 或 DELETE，并使用 commit() 方法将更改持久化。

创建游标实例后，您可以使用 execute() 方法向数据库发送命令，并使用 fetchone()、fetchmany() 或 fetchall() 从表中检索数据。

最后，在完成操作后，务必关闭游标和与数据库的连接。否则，它们会持续占用服务器端资源。您可以使用 close() 方法来关闭。

下面是创建 datacamp_courses 表的代码：

# Open a cursor to perform database operations
cur = conn.cursor()
# Execute a command: create datacamp_courses table
cur.execute("""CREATE TABLE datacamp_courses(
            course_id SERIAL PRIMARY KEY,
            course_name VARCHAR (50) UNIQUE NOT NULL,
            course_instructor VARCHAR (100) NOT NULL,
            topic VARCHAR (20) NOT NULL);
            """)
# Make the changes to the database persistent
conn.commit()
# Close cursor and communication with the database
cur.close()
conn.close()技术支持

这是在 PostgreSQL 上创建表的一个非常基础的示例，但实际情况可能复杂得多。如果您想进一步学习如何创建 PostgreSQL 数据库，并探索其结构、数据类型以及如何进行规范化，请查看我们的创建 PostgreSQL 数据库课程。

在 Python 中执行基础 PostgreSQL 查询

datacamp_courses 表已就绪，现在是时候使用 SQL 来执行一些基本查询了！

INSERT

您可能注意到，表中目前还没有任何值。要在 datacamp_courses 表中创建记录，我们需要使用 INSERT 命令。

cur = conn.cursor()

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to SQL','Izzy Weber','Julia')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Analyzing Survey Data in Python','EbunOluwa Andrew','Python')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to ChatGPT','James Chapman','Theory')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to Statistics in R','Maggie Matsui','R')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Hypothesis Testing in Python','James Chapman','Python')");

conn.commit()
cur.close()
conn.close()技术支持

生成的表如下所示：

SELECT

在数据科学之旅中，读取 SQL 数据库中的数据可能是您最常做的事情之一。这通常称为 SELECT 查询。现在，让我们来看看 datacamp_courses 表的情况。

我们将调用经典的 SELECT * FROM database_name 语句以读取表中的全部数据。随后，我们使用 fetchall() 方法获取所有可用行。请注意，PostgreSQL 会为 course_id 列自动创建一个数值索引。

cur = conn.cursor()
cur.execute('SELECT * FROM datacamp_courses;')
rows = cur.fetchall()
conn.commit()
conn.close()
for row in rows:
    print(row)技术支持

(1, 'Introduction to SQL', 'Izzy Weber', 'Julia')
(2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python')
(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory')
(4, 'Introduction to Statistics in R', 'Maggie Matsui', 'R')
(5, 'Hypothesis Testing in Python', 'James Chapman', 'Python')技术支持

UPDATE

数据中经常会出现错误。您可能在上一节中注意到，课程 “Introduction to SQL” 的主题被标记为 Julia。经过核对课程信息，我们发现这是个错误。我们需要将其改为 “SQL”。这可以通过 UPDATE 语句来完成，如下所示：

cur = conn.cursor()
cur.execute("UPDATE datacamp_courses SET topic = 'SQL' WHERE course_name = 'Introduction to SQL';")
conn.commit()
conn.close()技术支持

DELETE

最后，您可能希望删除表中的某条记录。例如，让我们删除课程 Introduction to Statistics in R：

cur = conn.cursor()
cur.execute("""DELETE from datacamp_courses WHERE course_name = 'Introduction to Statistics in R'""");
conn.commit()
cur.close()技术支持

在 Python 中执行高级 PostgreSQL 查询

在上一节中，我们回顾了最基本的 SQL 查询。但值得学习的内容还有很多。让我们来看看一些更高级的查询。

ORDER BY

假设您想按授课教师的姓名对数据库进行排序。您可以使用 ORDER BY 语句：

cur = conn.cursor()

cur.execute('SELECT * FROM datacamp_courses ORDER BY course_instructor')
rows = cur.fetchall()
for row in rows:
    print(row)技术支持

(2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python')
(1, 'Introduction to SQL', 'Izzy Weber', 'SQL')
(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory')
(4, 'Hypothesis Testing in Python', 'James Chapman', 'Python')技术支持

GROUP BY

您可能希望在不同数据分组内执行一些聚合函数。例如，您可能想计算不同授课教师所教授课程的数量。可以使用 GROUP BY 来完成此类操作。

cur = conn.cursor()
cur.execute('SELECT course_instructor, COUNT(*) FROM datacamp_courses GROUP BY course_instructor')
rows = cur.fetchall()
for row in rows:
    print(row)技术支持

('James Chapman', 2)
('Izzy Weber', 1)
('EbunOluwa Andrew', 1)技术支持

JOIN

到目前为止，我们只使用了 datacamp_course 表。然而，只有当您同时使用多张表时，才能真正发挥关系型数据库（如 PostgreSQL）的全部潜力。

组合多张表的“魔法工具”是 JOIN 操作。设想我们的数据库中还有一张名为 programming_languages 的表，包含关于数据科学中最热门编程语言的一些基本信息，包括名称、在 TIOBE 指数中的排名，以及 Datacamp 上关于该语言的课程数量。该表如下所示：

假设您想合并这两张表，以同时获得课程信息以及该语言在 TIOBE 指数中的排名。我们将使用 INNER JOIN 来仅获取出现在 datacamp_course 表中的编程语言的信息。

cur = conn.cursor()
cur.execute("""SELECT course_name, course_instructor, topic, tiobe_ranking
FROM datacamp_courses
INNER JOIN programming_languages
ON datacamp_courses.topic = programming_languages.language_name""")
rows = cur.fetchall()
for row in rows:
    print(row)技术支持

('Introduction to SQL', 'Izzy Weber', 'SQL', 8)
('Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python', 1)
('Hypothesis Testing in Python', 'James Chapman', 'Python', 1)技术支持

这只是一个例子，但 SQL 联结的潜力几乎是无限的，因此这是一个必须掌握的主题。我们的课程、教程和速查表将帮助您入门。

SQLAlchemy 入门

Psycopg2 是连接 PostgreSQL 数据库的优秀工具。但如果您需要与其他 SQL 数据库（如 Oracle、MySQL）托管的数据库一起工作呢？在这些场景下，使用能够连接不同类型 SQL 数据库的工具可能更合适。为此，SQLAlchemy 是一个很好的候选。

简单来说，SQLAlchemy 允许用户使用 Python 语言连接数据库，利用面向对象的方式运行 SQL 查询，并优化工作流。

尽管与 psycopg2 相比语法有所不同，但您几乎可以完成同样的事情。下面是一个示例，演示如何创建引擎以连接我们的 datacamp_courses 数据集，并读取 datacap_course 表中的所有数据

import sqlalchemy as db


engine = db.create_engine("postgresql://datacamp@localhost:5432/datacamp_courses")
conn = engine.connect() 
output = conn.execute("SELECT * FROM datacamp_courses")
print(output.fetchall())
conn.close()技术支持

[(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory'),
 (5, 'Hypothesis Testing in Python', 'James Chapman', 'Python'),
 (2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python'),
 (1, 'Introduction to SQL', 'Izzy Weber', 'SQL')]技术支持

SQLAlchemy 也是一个非常值得学习的工具。想先睹为快其工作方式，请查看我们的 SQLAlchemy 教程。

在 DataCamp DataLab 中使用 PostgreSQL

在 DataCamp，我们致力于让数据科学更简单、更易于每个人获取。这正是我们创建 DataCamp DataLab 的初衷——一款专为数据科学设计的云端笔记本。您可以将 DataCamp DataLab 理解为 Jupyter Notebook 与 Google 文档的结合体，允许用户单独或协同编写代码、分析数据并分享洞见。

将 DataLab 连接到 PostgreSQL（以及其他 SQL 数据库）非常简单。DataLab 允许您安全地连接到数据源，同时还能无缝使用 Python 或 R 继续进行数据分析。您只需按照一个直观的零代码流程创建一个集成，在其中输入您的连接凭据即可。

最后，如果您想尝试集成但手头没有可连接的 PostgreSQL 数据库，您可以使用我们的示例 PostgreSQL 数据库！

对 DataCamp DataLab 感到好奇吗？请查看这篇文章，进一步了解这款新一代数据科学笔记本。

结语

做得很好！在本教程中，您初步了解了 PostgreSQL，并学习了如何借助 psycopg2 包在 Python 中管理数据库。但这仅仅是起点。SQL 是数据科学中使用最广泛的编程语言之一，而且相对容易上手，所以没有理由不学。

幸运的是，Datacamp 已为您准备就绪。以下资源可帮助您熟练掌握 PostgreSQL 以及更广泛的 SQL：

• PostgreSQL 入门教程
• 创建 PostgreSQL 数据库课程
• 提升 PostgreSQL 查询性能课程
• 用 SQL 管理数据库课程
• PostgreSQL 数据操作函数课程