PostgreSQL初学者指南

本文概述

• 使用PostgreSQL启动并运行
• PostgreSQL中的CRUD操作
• SQL + Jupyter笔记本
• SQLAlchemy入门并将其与SQL Magic命令结合
• 你走了很长一段路！

能够查询关系数据库系统是数据科学家的必备技能。 SQL或结构化查询语言使你可以非常有效地进行此操作。 SQL不仅使你可以对数据提出有意义的问题, 而且还可以使你以许多不同的方式使用数据。没有数据库, 几乎不可能有实际的应用程序。因此, 数据库知识以及能够处理数据库的知识是数据科学家工具箱的关键部分。

事实：SQL也称为SE-QU-EL。它具有一定的历史意义-SQL的初始名称是简单英语查询语言。

通常, 关系数据库如下所示：

关系也称为表。有多种表示数据库的方法。这只是其中之一, 也是最受欢迎的一种。

本教程介绍了用SQL执行的四个最常见的操作, 分别是创建, 读取, 更新和删除。这四个操作统称为CRUD。在任何涉及用户交互的应用程序中, 只要始终执行这四个操作即可。

你将使用PostgreSQL作为关系数据库管理系统。 PostgreSQL非常轻巧, 它也是免费的。在本教程中, 你将-

• 使用PostgreSQL启动并运行
• 连接到PostgreSQL数据库
• 在该数据库中创建, 读取, 更新和删除表
• 在Jupyter Notebook上运行SQL
• 在Python中运行SQL

让我们开始吧。

使用PostgreSQL启动并运行

PostgreSQL是一种轻量级的开源RDBMS。这是业界极为认可的。你可以从PostgreSQL的官方网站上了解更多有关PostgreSQL的信息。

为了能够在PostgreSQL中开始编写和执行查询, 你需要在计算机上安装它。安装非常简单。以下两个简短的视频向你展示了如何在32位Windows-7计算机上下载和安装PostgreSQL-

• 下载PostgreSQL
• 安装PostgreSQL

注意：在安装PostgreSQL时, 请注意输入的密码和端口号。

在计算机上成功安装PostgreSQL后, 打开pgAdmin。 pgAdmin是PostgreSQL安装附带的一个方便实用的工具, 它使你可以通过漂亮的图形界面执行与数据库有关的常规任务。 pgAdmin的界面看起来像-

当打开pgAdmin时, 你会在界面中看到一个名为” PostgreSQL 9.4(localhost：5432)”的服务器-

注意：你的版本可能与上述版本不同, 因此端口号(5432)。

通过输入在安装过程中输入的密码连接到服务器。供参考-https://bit.ly/2FPO4hR。

成功连接到本地数据库服务器后, 将获得类似于以下内容的界面-

你的第一个任务是创建数据库, 你可以通过右键单击”数据库”选项卡, 然后从下拉选项中选择”新建数据库”来创建数据库。让我们创建一个名为srcmini_Courses的数据库。创建数据库后, 你可以继续本教程的下一部分。

PostgreSQL中的CRUD操作

根据给定的规范创建表-

为了能够在数据库上进行操作, 你将需要一个表。因此, 让我们继续创建具有以下规范(模式)的简单表(也称为关系), 称为srcmini_courses：

该规范为我们提供了有关表各列的一些信息-

• 该表的主键应为course_id(请注意, 只有此键为粗体), 并且其数据类型应为整数。主键是一个约束, 用于强制将列值设置为非空且唯一。它使你可以唯一地标识表中存在的特定实例或一组实例。
• 规范中的其余信息现在应该易于理解。

要创建表, 请右键单击新创建的数据库srcmini_Courses, 然后从选项中选择CREATE Script。你应该得到类似于以下内容的信息-

现在执行以下查询-

CREATE TABLE srcmini_courses(
 course_id SERIAL PRIMARY KEY, course_name VARCHAR (50) UNIQUE NOT NULL, course_instructor VARCHAR (100) NOT NULL, topic VARCHAR (20) NOT NULL
);

要执行查询, 只需选择它, 然后从菜单栏中单击执行按钮-

输出应为-

PostgreSQL中的表创建查询的一般结构如下所示：

CREATE TABLE table_name (
 column_name TYPE column_constraint, table_constraint table_constraint
)

创建表时, 我们没有指定任何table_constraints。现在可以避免这种情况。除关键字SERIAL外, 其他所有内容都可读性强。 PostgreSQL中的Serial使你可以创建一个自动增量列。默认情况下, 它创建整数类型的值。串行使我们免于记住表的最后一个插入/更新的主键的负担, 并且对主键使用自动递增是一种好习惯。你可以从此处了解有关串行的更多信息。

将一些记录插入到新创建的表中-

在此步骤中, 你将在表中插入一些记录。你的记录应包含-

• 课程名称
• 课程名称
• 课程主题

Course_id列的值将由PostgreSQL本身处理。 PostgreSQL中插入查询的一般结构如下所示：

INSERT INTO table(column1, column2, …)
VALUES
 (value1, value2, …);

让我们插入一些记录-

INSERT INTO srcmini_courses(course_name, course_instructor, topic)
VALUES('Deep Learning in Python', 'Dan Becker', 'Python');

INSERT INTO srcmini_courses(course_name, course_instructor, topic)
VALUES('Joining Data in PostgreSQL', 'Chester Ismay', 'SQL');

请注意, 你没有明确指定主键。稍后你将看到其效果。

执行以上两个查询时, 成功插入后应获得以下结果-

查询成功返回：受影响的一行, 执行时间为11毫秒。

从表中读取/查看数据-

这可能是你在数据科学之旅中会做的很多事情。现在, 让我们看看表srcmini_courses的状态如何。

这通常称为选择查询, 而选择查询的一般结构如下所示：

SELECT
 column_1, column_2, ...
FROM
 table_name;

让我们从表srcmini_courses中选择所有列

SELECT * FROM srcmini_courses;

然后你得到-

现在注意主键。如果你只想查看课程名称, 可以通过-

SELECT course_name from srcmini_courses;

然后你得到-

你可以指定尽可能多的列名, 只要它们存在于表中, 就可以在结果中看到它们。如果运行, 请选择srcmini_courses中的course_name, number_particpant；你会遇到错误, 因为表中确实存在number_particpants列。现在, 你将看到如何更新表中的特定记录。

更新表中的记录-

SQL中更新查询的一般结构如下所示：

UPDATE table
SET column1 = value1, column2 = value2 , ...
WHERE
 condition;

你将更新course_instructor =” Chester Ismay”的记录, 并将course_name设置为” Joining Data in SQL”。然后, 你将验证记录是否已更新。执行此操作的查询将是-

UPDATE srcmini_courses SET course_name = 'Joining Data in SQL'
WHERE course_instructor = 'Chester Ismay';

让我们通过运行选择查询来查看更新查询是否达到了预期的效果-

你可以看到更新查询完全按照你想要的方式执行。现在, 你将看到如何从表中删除记录。

删除表格中的记录-

SQL中删除查询的一般结构如下：

DELETE FROM table
WHERE condition;

你将删除course_name =” Python深度学习”的记录, 然后验证记录是否已删除。按照该结构, 你可以看到以下查询应该能够执行此操作-

DELETE from srcmini_courses
WHERE course_name = 'Deep Learning in Python';

请记住, 关键字在SQL中不区分大小写, 但是数据区分大小写。这就是为什么你在查询中看到大写和小写混合的原因。

让我们看看预期记录是否已从表中删除-

是的, 它确实删除了预期的记录。

本教程中提到的查询的一般结构可从postgresqltutorial.com中获得。

你现在知道了如何在SQL中进行基本的CRUD查询。你们中的有些人可能会大量使用Jupyter Notebook, 并且可能会认为, 如果可以选择直接从Jupyter Notebook执行这些查询, 那会很好。在下一节中, 你将看到如何实现这一目标。

SQL + Jupyter笔记本

为了能够从Jupyter Notebook运行SQL查询, 第一步是安装ipython-sql软件包。

如果尚未安装, 请使用以下命令进行安装：pip install ipython-sql

完成此操作后, 请执行以下命令将sql扩展名加载到Jupyter Notebook中：

%load_ext sql

下一步将连接到PostgreSQL数据库。你将连接到你创建的数据库-srcmini_Courses。

为了能够连接到系统中已经创建的数据库, 你将必须指示Python检测其方言。简单来说, 你将不得不告诉Python这是一个PostgreSQL数据库。为此, 你将需要可以使用以下命令安装的psycopg2：

pip install psycopg2

一旦安装了psycopg, 请使用-连接到数据库

%sql postgresql://postgres:postgres@localhost:5432/srcmini_Courses

'Connected: postgres@srcmini_Courses'

注意％sql的用法。这是一个神奇的命令。它使你可以从Jupyter Notebook执行SQL语句。 ％sql之后的内容称为数据库连接URL, 你可以在其中指定-

• 方言(postgres)
• 用户名(postgres)
• 密码(postgres)
• 服务器地址(本地主机)
• 端口号(5432)
• 数据库名称(DaaCamp_Courses)

现在, 你可以从Jupyter Notebook中执行在pgAdmin界面中执行的所有操作。让我们从创建具有完全相同模式的表_srcminicourses开始。

但是在此之前, 你必须删除表, 因为SQL不允许你存储两个具有相同名称的表。你可以按以下方式放置表格：

%sql DROP table srcmini_courses;

 * postgresql://postgres:***@localhost:5432/srcmini_Courses
Done.





[]

现在已从PostgreSQL中删除了表_srcminicourses, 因此你可以使用该名称创建一个新表。

%%sql
CREATE TABLE srcmini_courses(
 course_id SERIAL PRIMARY KEY, course_name VARCHAR (50) UNIQUE NOT NULL, course_instructor VARCHAR (100) NOT NULL, topic VARCHAR (20) NOT NULL
);

 * postgresql://postgres:***@localhost:5432/srcmini_Courses
Done.





[]

请注意此处使用％sql %% sql。要执行单行查询, 可以使用％sql, 但是如果要一次性执行多个查询, 则必须使用%% sql。

让我们插入一些记录-

%%sql
INSERT INTO srcmini_courses(course_name, course_instructor, topic)
VALUES('Deep Learning in Python', 'Dan Becker', 'Python');
INSERT INTO srcmini_courses(course_name, course_instructor, topic)
VALUES('Joining Data in PostgreSQL', 'Chester Ismay', 'SQL');

 * postgresql://postgres:***@localhost:5432/srcmini_Courses
1 rows affected.
1 rows affected.





[]

查看表格以确保插入操作按预期完成-

%%sql
select * from srcmini_courses;

 * postgresql://postgres:***@localhost:5432/srcmini_Courses
2 rows affected.

让我们保持流程。下一步, 你将更新表中的一条记录-

%sql update srcmini_courses set course_name = 'Joining Data in SQL' where course_instructor = 'Chester Ismay';

 * postgresql://postgres:***@localhost:5432/srcmini_Courses
1 rows affected.





[]

在SQL中处理字符串时, 请密切注意。与传统编程语言不同, 字符串值需要使用单引号引起来。

现在, 让我们验证你的更新查询是否具有预期的效果-

%%sql
select * from srcmini_courses;

 * postgresql://postgres:***@localhost:5432/srcmini_Courses
2 rows affected.

现在, 我们删除一条记录并进行验证-

%%sql
delete from srcmini_courses where course_name = 'Deep Learning in Python';

 * postgresql://postgres:***@localhost:5432/srcmini_Courses
1 rows affected.





[]

%%sql
select * from srcmini_courses;

 * postgresql://postgres:***@localhost:5432/srcmini_Courses
1 rows affected.

到目前为止, 你已经有了在PostgreSQL中执行CRUD操作的清晰思路, 以及如何通过Jupyter Notebook执行它们。如果你熟悉Python, 并且对通过Python代码访问数据库感兴趣, 也可以这样做。下一部分就是关于这一点的。

SQLAlchemy入门并将其与SQL Magic命令结合

对于本节, 你将需要SQLAlchemy软件包。它通常随Anaconda发行。你也可以点安装。安装完毕后, 可以通过-导入它

import sqlalchemy

为了能够使用SQLAlchemy与数据库交互, 你将需要为存储数据库的相应RDBMS创建引擎。在你的情况下, 它是PostgreSQL。 SQLAlchemy允许你仅通过一次create_engine()调用就可以创建RDBMS引擎, 并且该方法采用你之前已看到的数据库连接URL。

from sqlalchemy import create_engine

engine = create_engine('postgresql://postgres:postgres@localhost:5432/srcmini_Courses')
print(engine.table_names()) # Lets you see the names of the tables present in the database

['srcmini_courses']

你可以看到名为_srcminicourses的表, 该表进一步确认你已成功创建引擎。让我们执行一个简单的选择查询, 以查看表_srcminicourses的记录并将其存储在pandas DataFrame对象中。

你将使用read_sql()方法(由pandas提供), 该方法采用SQL查询字符串和引擎。

import pandas as pd

df = pd.read_sql('select * from srcmini_courses', engine)
df.head()

你还可以在Python代码中配对％sql magic命令。

df_new = %sql select * from srcmini_courses
df_new.DataFrame().head()

 * postgresql://postgres:***@localhost:5432/srcmini_Courses
1 rows affected.

你走了很长一段路！

恭喜你, 并为自己拍拍背部！在本教程中, 你不仅开始使用PostgreSQL, 而且还了解了如何以几种方式执行SQL查询。下一步, 你应该观看David Robinson(srcmini的首席数据科学家)的网络研讨会, 其中他展示了他如何在日常任务中使用SQL。如果你想提高SQL技能, 以下是一些非常好的资源, 可以从以下开始：

• SQL for Data Science简介
• SQL by Kaggle学习
• 在SQL中联接数据