如何在多线程python程序中使用PostgreSQL [英] How to use PostgreSQL in multi thread python program

问题描述

我正在使用psycopg2(2.6)在多线程python程序中连接到PostgreSQL数据库.

I am using psycopg2 (2.6) connect to PostgreSQL database in a multi-threading python program.

当程序中的队列大小增加时，选择查询会出现错误没有要提取的结果"，但是将记录插入db效果很好.

When queue size in program increase, select queries get error "no results to fetch", but inserts records to db works very well.

示例代码:

class Decoders(threading.Thread):
    def __init__(self, queue):
        threading.Thread.__init__(self)
        self.queue = queue

    def run(self):
        self.decode()

    def decode(self):
        queue = self.queue
        db = Database()
        while queue.qsize() > 0:    
            # calling db methods, just an example
            temp = queue.get()
            db.select_records()
            db.insert_record(temp)

和:

Decoders(queue).start()
Decoders(queue).start()

注意:多重处理没有这个问题.

note: I don't have this problem with multiprocessing.

当我仅启动一个线程时，程序没有任何问题.

When I start only one thread, the program doesn't have any problem.

数据库类:

class Database:
    db = object
    cursor = object

    def __init__(self):
        self.db = connect(host=conf_hostname,
                          database=conf_dbname,
                          user=conf_dbuser,
                          password=conf_dbpass,
                          port=conf_dbport)
        self.db.autocommit = True
        self.cursor = self.db.cursor()

    def select_records(self):
        self.cursor.execute(simple select)
        return self.cursor.fetchall()


    def insert_record(self, temp):
        # insert query

推荐答案

您是否正在为每个线程创建连接?如果您有多个线程，则每个线程都需要一个连接(或一个在连接周围具有锁定机制的池)，否则您将遇到各种各样的奇怪问题.

Are you creating a connection for each thread? If you have multiple threads you need a connection for each one (or a pool with locking mechanisms around the connections) otherwise you will have all sorts of weird issues.

这就是为什么在多处理中不会出现问题的原因，因为每个进程都将创建自己的连接.

Which is why you would not have issues in multiprocessing, since each process will be creating its own connection.

这篇关于如何在多线程python程序中使用PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！