如何在 Raspberry Pi 上设置 Postgres 数据库

图片来源

Raspberry Pi 基金会。CC BY-SA 4.0。

数据库是将数据持久性添加到您的项目或应用程序的绝佳方式。您可以在一个会话中写入数据，下次您想查看时它仍然在那里。一个设计良好的数据库可以高效地查找大型数据集中的数据，您无需担心它的外观，只需担心您想找到什么。设置一个用于基本 CRUD（创建、记录、更新、删除）应用程序的数据库非常简单，这是一种常见的模式，在许多项目中都很有用。

为什么选择 PostgreSQL，通常称为 Postgres？它被认为是功能和性能方面最好的开源数据库。如果您使用过 MySQL，您会感到熟悉，但是当您需要更高级的用法时，您会发现 Postgres 中的优化要优越得多。它易于安装、易于使用、易于保护，并且在 Raspberry Pi 3 上运行良好。

本教程解释了如何在 Raspberry Pi 上安装 Postgres；创建表；编写简单查询；在 Raspberry Pi、PC 或 Mac 上使用 pgAdmin GUI；以及从 Python 与数据库交互。

一旦您学习了基础知识，您就可以通过连接多个表的复杂查询进一步扩展您的应用程序，此时您需要考虑优化、最佳设计实践、使用主键和外键等等。

安装

要开始，您需要安装 Postgres 和其他一些软件包。打开终端窗口并在连接到互联网时运行以下命令

sudo apt install postgresql libpq-dev postgresql-client 
postgresql-client-common -y

图片来源

opensource.com

完成后，切换到 Postgres 用户以配置数据库

sudo su postgres

现在您可以创建一个数据库用户。如果您创建一个与您的 Unix 用户帐户之一同名的用户，则该用户将自动被授予对数据库的访问权限。因此，为了本教程的简单起见，我假设您正在使用默认的 pi 用户。运行 createuser 命令继续

createuser pi -P --interactive

当提示时，输入密码（并记住它是什么），为超级用户选择 n，为接下来的两个问题选择 y。

图片来源

opensource.com

现在使用 shell 连接到 Postgres 并创建一个测试数据库

$ psql
> create database test;

按两次 Ctrl+D 退出 psql shell 并再次退出 Postgres 用户，您将再次以 pi 用户身份登录。由于您创建了一个名为 pi 的 Postgres 用户，您可以从此处访问 Postgres shell，无需凭据

$ psql test

您现在已连接到“test”数据库。数据库当前为空，不包含任何表。您可以从 psql shell 创建一个简单的表

test=> create table people (name text, company text);

现在您可以将数据插入到表中

test=> insert into people values ('Ben Nuttall', 'Raspberry Pi Foundation');

test=> insert into people values ('Rikki Endsley', 'Red Hat');

并尝试一个 select 查询

test=> select * from people;

     name      |         company         
---------------+-------------------------
 Ben Nuttall   | Raspberry Pi Foundation
 Rikki Endsley | Red Hat
(2 rows)

图片来源

opensource.com

test=> select name from people where company = 'Red Hat';

     name      | company 
---------------+---------
 Rikki Endsley | Red Hat
(1 row)

pgAdmin

您可能会发现使用图形工具访问数据库很有用。PgAdmin 是一个功能齐全的 PostgreSQL GUI，允许您创建和管理数据库和用户、创建和修改表、编写和执行查询，并在更熟悉的视图中浏览结果，类似于电子表格。psql 命令行工具对于简单查询来说很好，您会发现许多高级用户坚持使用它以提高速度（并且因为他们不需要 GUI 提供的帮助），但中级用户可能会发现 pgAdmin 是一种更平易近人的方式来学习和更多地使用数据库。

关于 pgAdmin 的另一个有用的地方是，您可以直接在 Pi 上使用它，也可以在远程连接到 Pi 上数据库的另一台计算机上使用它。

如果您想在 Raspberry Pi 本身访问它，您可以直接使用 apt 安装它

sudo apt install pgadmin3

如果您在基于 Debian 的系统（如 Ubuntu）上，则完全相同；如果您在另一个发行版上，请尝试适合您系统的等效命令。或者，如果您在 Windows 或 macOS 上，请尝试从 pgAdmin.org 下载 pgAdmin。请注意，apt 中提供的版本是 pgAdmin3，而更新的版本 pgAdmin4 可从网站获得。

要在同一 Raspberry Pi 上使用 pgAdmin 连接到您的数据库，只需从主菜单打开 pgAdmin3，单击 new connection 图标，并填写注册字段。在这种情况下，您只需要一个名称（您选择连接名称，例如 test），将用户名更改为“pi”，并将其余字段留空（或保持原样）。单击“确定”，您将在左侧的侧面板中找到一个新的连接。

图片来源

opensource.com

要从另一台计算机使用 pgAdmin 连接到您的 Pi 的数据库，您首先需要编辑 PostgreSQL 配置以允许远程连接

1. 编辑 PostgreSQL 配置文件 /etc/postgresql/9.6/main/postgresql.conf 以取消注释 listen_addresses 行，并将其值从 localhost 更改为 *。保存并退出。

2. 编辑 pg_hba 配置文件 /etc/postgresql/9.6/main/pg_hba.conf 以将 IPv4 的 127.0.0.1/32 更改为 0.0.0.0/0，并将 IPv6 的 ::1/128 更改为 ::/0。保存并退出。

3. 重启 PostgreSQL 服务：sudo service postgresql restart。

请注意，如果您使用的是较旧的 Raspbian 镜像或其他发行版，版本号可能会有所不同。

edit the postgresql configuration to allow remote connections

图片来源

opensource.com

完成后，在您的另一台计算机上打开 pgAdmin 并创建一个新连接。这次，除了为连接命名之外，输入 Pi 的 IP 地址作为主机（可以通过将鼠标悬停在任务栏中的 WiFi 图标上或在终端中键入 hostname -I 来找到）。

图片来源

opensource.com

无论您是本地连接还是远程连接，单击打开 服务器组 > 服务器 > test > 模式 > public > 表，右键单击 people 表，然后选择 查看数据 > 查看前 100 行。您现在将看到您之前输入的数据。

图片来源

opensource.com

您现在可以使用 GUI 创建和修改数据库和表、管理用户以及编写您自己的自定义查询。您可能会发现这种可视化方法比使用命令行更易于管理。

Python

要从 Python 脚本连接到您的数据库，您需要 Psycopg2 Python 包。您可以使用 pip 安装它

sudo pip3 install psycopg2

现在打开一个 Python 编辑器并编写一些代码来连接到您的数据库

import psycopg2

conn = psycopg2.connect('dbname=test')
cur = conn.cursor()

cur.execute('select * from people')

results = cur.fetchall()

for result in results:
    print(result)

运行此代码以查看查询结果。请注意，如果您是远程连接，您需要在连接字符串中提供更多凭据，例如，添加主机 IP、用户名和数据库密码

conn = psycopg2.connect('host=192.168.86.31 user=pi 
password=raspberry dbname=test')

您甚至可以创建一个函数来专门查找此查询

def get_all_people():
    query = """
    SELECT
        *
    FROM
        people
    """
    cur.execute(query)
    return cur.fetchall()

以及一个包含查找的函数

def get_people_by_company(company):
    query = """
    SELECT
        *
    FROM
        people
    WHERE
        company = %s
    """
    values = (company, )
    cur.execute(query, values)
    return cur.fetchall()

甚至一个用于添加记录的函数

def add_person(name, company):
    query = """
    INSERT INTO
        people
    VALUES
        (%s, %s)
    """
    values = (name, company)
    cur.execute(query, values)

请注意，这使用了一种安全的方法将字符串注入到查询中。您不想被 little bobby tables 抓住！