如何用python写一个简单的postgres json包装器

Postgres is a legendary database. It has been around for a while and is well established with great support. This database is fast, efficient and used by a wide range of companies ranging from startups to international conglomerates. One reason Postgres is so popular is because there are a ton of extensions, plugins and connectors for it. You can connect via so many different programming languages and there are a number of top-notch ORMs available too.

Postgres是一个传奇的数据库。 它已经存在了一段时间，并且在强大的支持下已经建立良好。 该数据库是快速，高效的，并且被初创企业到国际企业集团广泛使用。 Postgres如此受欢迎的原因之一是因为有大量的扩展程序，插件和连接器。 您可以通过许多不同的编程语言进行连接，并且还有许多一流的ORM可用。

Depending on your specific needs and project size, you might not need to setup a ton of extra abstraction layers or frameworks in order to handle queries into Postgres. Making a simple wrapper for different queries and commonly used functions is straightforward and effective in languages like Python.

根据您的特定需求和项目规模，您可能不需要设置大量额外的抽象层或框架来处理对Postgres的查询。 在像Python这样的语言中，为不同的查询和常用函数创建一个简单的包装是简单有效的。

If you’re prototyping a tiny application or working with a highly constrained embedded system then there can be great benefit in writing a light wrapper. Getting JSON results out of PG is super easy using the extremely popular psycopg2 module. This low-level database connection module for Python allows you to hook up to Postgres, execute SQL and perform other database functions. Let’s explore how to setup a database wrapper in Python using this module to make executing queries fast and easy.

如果您要制作小型应用程序的原型或使用高度受限的嵌入式系统，那么编写光包装器可能会带来很大的好处。 使用非常流行的psycopg2模块，从PG中获取JSON结果非常容易。 这个用于Python的低级数据库连接模块允许您连接到Postgres，执行SQL和执行其他数据库功能。 让我们探讨如何使用此模块在Python中设置数据库包装程序，以使执行查询变得快速而简单。

数据库 (The database)

For our example Postgres server we’re going to use a pre-populated database full of test data. This gives us a bunch of sample data to query against. After all what fun is an empty database.

对于我们的示例Postgres服务器，我们将使用充满测试数据的预填充数据库。 这给了我们一堆要查询的样本数据。 毕竟，一个空的数据库很有趣。

We’ll be using Docker to setup the database container so we don’t pollute our host operating system with test installations. If you’ve never used Docker before or need a quick refresher Docker — Beginner’s Guide — Part 1: Images & Containers is a fantastic guide by Sebastian Eschweiler to help you get up and running.

我们将使用Docker设置数据库容器，因此我们不会通过测试安装来污染主机操作系统。 如果您以前从未使用过Docker，或者需要快速入门，那么Doc​​ker —新手指南—第1部分：图片和容器 是塞巴斯蒂安·埃施韦勒 ( Sebastian Eschweiler )的精彩指南，可帮助您入门和运行。

Check out postgres-dataset on Docker Hub (shout out to aa8y for creating this incredibly handy tool) to pull the latest image and deploy it using the commands below. You’ll want to change the first port from 5432 to something else if you’re already running a Postgres instance on your machine.

在Docker Hub上查看postgres-dataset (向aa8y喊叫以创建此非常方便的工具)，以提取最新映像并使用以下命令进行部署。 如果您已经在计算机上运行Postgres实例，则需要将第一个端口从5432更改为其他端口。

docker pull aa8y/postgres-dataset
docker run -d -p 5432:5432 --name pg-ds-fake aa8y/postgres-dataset:latest

Give the database a few minutes to populate with the test data and then go ahead and attach to the container using the following command:

给数据库几分钟以填充测试数据，然后继续使用以下命令将其附加到容器：

docker exec -it pg-ds-fake /bin/bash

We will need to make one tweak to allow access from the host machine into the container’s PG instance. While connected to the container, run the following:

我们将需要进行一些调整，以允许从主机访问容器的PG实例。 连接到容器后，运行以下命令：

echo "host all all 0.0.0.0/0 trust" > /var/lib/postgresql/data/pg_hba.confpg_ctl reload

The first line will allow all TCP/IP connections to Postgres and remove all other authentication. This is strictly for testing to make things easier and not deal with passwords and authentication from the host. Under no circumstances should you ever add this configuration to a production instance of Postgres. The last command reloads Postgres so that it picks up the new configuration.

第一行将允许所有与Postgres的TCP / IP连接，并删除所有其他身份验证。 严格来说，这是为了使事情变得更容易进行测试而不使用主机的密码和身份验证。 在任何情况下，您都不应将此配置添加到Postgres的生产实例中。 最后一条命令将重新加载Postgres，以便选择新配置。

Now you should be able to connect to Postgres using psql from the host machine by running (ensure you exit the container first):

现在，您应该能够通过运行从主机上使用psql连接到Postgres(确保首先退出容器)：

psql -U postgres -h localhost

If you don’t have psql installed you can reference the guide available here to install. Try out a test query on the world database:

如果尚未安装psql ，则可以参考此处提供的指南。 在world数据库上尝试测试查询：

\c world
SELECT * FROM country LIMIT 5;

包装纸 (The wrapper)

Now that we’ve got the database up and running with some test data to look at we can begin setting up our wrapper in Python. Let’s open up a new Python file and start editing. We can just call this wrapper.py for now and add the following:

现在我们已经建立了数据库并使用一些测试数据来运行，可以开始在Python中设置包装器。 让我们打开一个新的Python文件并开始编辑。 我们现在可以只调用该wrapper.py并添加以下内容：

#!/usr/bin/env python3import simplejson as json
import psycopg2
from psycopg2.extras import RealDictCursorclass PostgresWrapper:
    def __init__(self, **args):
        self.user = args.get('user', 'postgres')
        self.port = args.get('port', 5432)
        self.dbname = args.get('dbname', 'world')
        self.host = args.get('host', 'localhost')
        self.connection = None

First, we’re pulling in simplejson instead of the standard json library because when we query for Decimal values they will only be serialized correctly using a more updated library with support for dumping this datatype.

首先，我们simplejson而不是标准的json库，因为当我们查询Decimal值时，它们只能使用支持转储此数据类型的更新的库来正确地序列化。

Next, we bring in the psycopg2 library and the RealDictCursor factory. This factory will let us easily serialize query results directly to a Dict type and then subsequently dump that to JSON. We will also define a basic PostgresWrapper class to hold all of our features and supply some default initialization values. Now let’s add some simple connection functions:

接下来，我们引入psycopg2库和RealDictCursor工厂。 这个工厂使我们可以轻松地将查询结果直接序列化为Dict类型，然后将其转储为JSON。 我们还将定义一个基本的PostgresWrapper类，以容纳所有功能并提供一些默认的初始化值。 现在让我们添加一些简单的连接函数：

    def connect(self):
        pg_conn = psycopg2.connect(
            user=self.user,
            port=self.port,
            dbname=self.dbname,
            host=self.host
        )
        self.connection = pg_conn    def get_json_cursor(self):
        return self.connection.cursor(cursor_factory=RealDictCursor)

Inside the connect() function we’re using psycopg2 to connect directly to the Postgres database we setup previously. This connection also specifies which database to connect to inside Postgres. For this example we’ll be using the world data which contains things like cities and countries. The next function uses the previous connection to setup a new cursor. The cursor is similar to the interactive psql prompt that you type SQL statements into, except this one is non-interactive and can be programmatically controlled. This cursor also uses the RealDictCursor specification to return Dict types which are easily returned as a JSON string later.

在connect()函数内部，我们使用psycopg2直接连接到我们先前设置的Postgres数据库。 此连接还指定要在Postgres内部连接的数据库。 在此示例中，我们将使用world数据，其中包含城市和国家/地区。 下一个功能使用先前的连接来设置新的光标。 游标类似于您在其中键入SQL语句的交互式psql提示符，但该光标是非交互式的并且可以通过编程控制。 该游标还使用RealDictCursor规范返回Dict类型，这些类型以后很容易以JSON字符串形式返回。

Finally, let’s setup functions to actually fetch some data:

最后，让我们设置函数以实际获取一些数据：

    @staticmethod
    def execute_and_fetch(cursor, query):
        cursor.execute(query)
        res = cursor.fetchall()
        cursor.close()
        return res    def get_json_response(self, query):
        cursor = self.get_json_cursor()
        response = self.execute_and_fetch(cursor, query)
        return json.dumps(response)    def get_countries(self):
        query = "SELECT * FROM country LIMIT 2;"
        print(self.get_json_response(query))

The execute_and_fetch() function uses the cursor we setup previously to execute a SQL query, close the cursor connection and then return the results. It is important to close the cursor when the action is complete because otherwise each call to execute_and_fetch() will instantiate multiple cursors and consume more resources in the database. The get_json_response() function is a helper function that handles instantiating the cursor and passing both the cursor and query to execute_and_fetch(). Afterwards, this function returns the response as a JSON string.

execute_and_fetch()函数使用我们之前设置的游标执行SQL查询，关闭游标连接，然后返回结果。 操作完成后关闭游标很重要，因为否则，每次对execute_and_fetch()调用都会实例化多个游标并消耗数据库中的更多资源。 get_json_response()函数是一个辅助函数，该函数处理实例化游标并将游标和查询都传递给execute_and_fetch() 。 然后，此函数将响应作为JSON字符串返回。

The get_countries() function will leverage get_json_response() and pass in the query for countries. When we call this function we receive the final JSON blob of queried rows from the database.

get_countries()函数将利用get_json_response()并传入国家/地区查询。 当我们调用此函数时，我们将从数据库中接收最终的JSON行查询。

放在一起 (Putting it all together)

Let’s recap what the whole wrapper class should look like:

让我们回顾一下整个包装类的外观：

#!/usr/bin/env python3import simplejson as json
import psycopg2
from psycopg2.extras import RealDictCursorclass PostgresWrapper:
    def __init__(self, **args):
        self.user = args.get('user', 'postgres')
        self.port = args.get('port', 5432)
        self.dbname = args.get('dbname', 'world')
        self.host = args.get('host', 'localhost')
        self.connection = None    def connect(self):
        pg_conn = psycopg2.connect(
            user=self.user,
            port=self.port,
            dbname=self.dbname,
            host=self.host
        )
        self.connection = pg_conn    def get_json_cursor(self):
        return self.connection.cursor(cursor_factory=RealDictCursor)    @staticmethod
    def execute_and_fetch(cursor, query):
        cursor.execute(query)
        res = cursor.fetchall()
        cursor.close()
        return res    def get_json_response(self, query):
        cursor = self.get_json_cursor()
        response = self.execute_and_fetch(cursor, query)
        return json.dumps(response)    def get_countries(self):
        query = "SELECT * FROM country LIMIT 2;"
        print(self.get_json_response(query))dbconn = PostgresWrapper()
dbconn.connect()
dbconn.get_countries()

Near the bottom of the file, we handle instantiating the PostgresWrapper, connecting and then calling get_countries() to print some sample JSON data.

在文件底部附近，我们将实例化PostgresWrapper ，进行连接，然后调用get_countries()来打印一些示例JSON数据。

You should be able to simply run the file and produce the results:

您应该能够简单地运行文件并产生结果：

./wrapper.py

Now you should see JSON output similar to this:

现在，您应该看到类似于以下内容的JSON输出：

[{"code": "AFG", "name": "Afghanistan", "continent": "Asia", "region": "Southern and Central Asia", ...}]

If everything worked correctly, you’ll end up with a very simple wrapper class for Postgres that can be tweaked to output JSON blobs of different queries. This can be incorporated into an existing Python application or even used as a rudimentary backend for an API. Keep in mind this is a simple example class, you could extend this class quite a bit and make performing queries even more flexible.

如果一切正常，您将获得一个非常简单的Postgres包装器类，可以对该类进行调整以输出不同查询的JSON Blob。 可以将其合并到现有的Python应用程序中，甚至可以用作API的基本后端。 请记住，这是一个简单的示例类，您可以扩展该类很多并使执行查询更加灵活。

Pairing Postgres with the power of Python and great modules you can simplify and abstract the complexity of connecting to and getting data out of databases. For an even more polished and elegant approach, consider exploring an ORM module called SQLAlchemy. This allows you to interact with database results as actual Python objects and write even less database-specific code.

将Postgres与Python和强大模块的功能结合使用，您可以简化和抽象连接到数据库以及从中获取数据的复杂性。 对于更精致，更优雅的方法，请考虑研究一个名为SQLAlchemy的ORM模块。 这样，您就可以与数据库结果作为实际的Python对象进行交互，并编写更少的数据库特定代码。

Thanks for reading! Postgres and Python are a match made in heaven. This wrapper is super lightweight and easy to follow, but could be extended with even more functionality. Reach out on Twitter with some of your own Python & Postgres pairing adventures.

谢谢阅读！ Postgres和Python是天作之合。 该包装器重量极轻，易于遵循，但可以扩展以提供更多功能。 通过自己的一些Python和Postgres配对冒险 在 Twitter 上进行 交流 。