psycopg2和无限的python脚本 [英] psycopg2 and infinite python script

问题描述

我有一个用Python编写的无限脚本，该脚本连接到Postgresql，并在该人出现在连接到我的计算机的摄像头前时在其中插入一条记录。

I have an infinite script written in Python which connects to Postgresql and inserts there a record when the person appears in front of the camera connected to my computer.

I想知道连接（和存储连接）到数据库的最佳方法是什么，如果每次有人出现时都必须进行连接和关闭，或者我是否可以存储连接。因为当我在无限循环之前创建连接并且在镜头前没有任何活动时，该连接保持空闲状态，并且当脚本想要在一段时间后插入新行时，该连接将关闭。每次要插入新行时，连接都没有问题，但是速度较慢。

I would like to know what is the best way to connect (and store connection) to the database, if it is necessary to connect and close every time when the person appears or if I can somehow store connection. Because when I create a connection before the infinite loop and there is no activity in front of the camera, the connection stays idle and when the script wants to insert a new row after some time, the connection is closed. When I connect every time I want to insert a new row, there is no problem, but this is slower.

感谢您的任何建议。

推荐答案

连接池可以很好地解决这类问题。我尚未在生产中使用它（主要使用Django或SQLAlchemy），但是 psycopg2.pool 包括一些不同的实现（ SimpleConnectionPool 或 PersistentConnectionPool ），可能会满足您的需求。一般来说，池不仅有助于将连接作为共享资源进行管理，还可以在需要时测试并重新初始化连接。

A connection pool works well for this kind of thing. I have not worked with it in production (using mainly Django or SQLAlchemy), but psycopg2.pool includes a few different implementations (SimpleConnectionPool or PersistentConnectionPool) that would probably fit your need. Generally speaking, a pool not only helps with managing connections as a shared resource, but also testing and re-initializing the connection when it's needed.

from psycopg2 import pool
conn_pool = pool.PersistentConnectionPool(minconn, maxconn, **dbopts)

def work_method():
    conn = conn_pool.getconn()
    with conn.cursor() as stmt:
        stmt.execute(sql)
    conn_pool.putconn(conn)

putconn 非常重要，以免异常不会导致池认为连接仍在使用中。最好作为上下文管理器来处理它：

The putconn is extremely important, so that an exception doesn't leave the pool thinking the connection is still in use. Would be good to handle it as a context manager:

import contextlib

@contextlib.contextmanager
def get_db_connection():
    conn = conn_pool.getconn()
    yield conn
    conn_pool.putconn(conn)

def work_method():
    with get_db_connection() as conn:
        with conn.cursor() as stmt:
            stmt.execute(sql)

希望有帮助。

这篇关于psycopg2和无限的python脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！