postgresql - python 多线程错误

Question

我想用多线程查找数据库，然后进行数据操作。

_list = range(19999, 100000)
pool = ThreadPool(10)
results = pool.map(main, _list)
pool.close()
pool.join()

def main(i):
    print(i)
    # query id, link, keywords in db
    res = query('select id,link,keywords from articles where id = {}'.format(i))

但是报错如下，是数据库扛不住这样的查询还是啥意思？

19999
22000
26002
30004
34006
38008
28003
36007
32005
24001
20000
40009
42010
Traceback (most recent call last):
  File "main.py", line 60, in <module>
44011
46012
    results = pool.map(main, _list)
48013
  File "/usr/local/Cellar/python3/3.5.1/Frameworks/Python.framework/Versions/3.5/lib/python3.5/multiprocessing/pool.py", line 260, in map
52015
    return self._map_async(func, iterable, mapstar, chunksize).get()
54016
  File "/usr/local/Cellar/python3/3.5.1/Frameworks/Python.framework/Versions/3.5/lib/python3.5/multiprocessing/pool.py", line 608, in get
50014
56017
    raise self._value
  File "/usr/local/Cellar/python3/3.5.1/Frameworks/Python.framework/Versions/3.5/lib/python3.5/multiprocessing/pool.py", line 119, in worker
    result = (True, func(*args, **kwds))
  File "/usr/local/Cellar/python3/3.5.1/Frameworks/Python.framework/Versions/3.5/lib/python3.5/multiprocessing/pool.py", line 44, in mapstar
    return list(map(*args))
  File "main.py", line 17, in main
    res = gao_query('select id,link,keywords from articles where id = {}'.format(i))
  File "/Users/Ru/Desktop/crawl_keywords/db.py", line 13, in query_with_desc
    for res in cur.fetchall():
psycopg2.ProgrammingError: no results to fetch
(py3) 

query函数如下: 返回数据以字典格式，keys为colomn名。

query = partial(query_with_desc, cur)
def query_with_desc(cur, sql):
    '''
    cur: postgresl cursor
    sql: sql statement
    '''
    cur.execute(sql)
    index = cur.description
    result = []
    for res in cur.fetchall():
        row = {}
        for i in range(len(index)):
            row[index[i][0]] = res[i]
        result.append(row)
    return result

Answer 1

postgresql默认最大连接数只有 100 , 见:

https://www.postgresql.org/do...

但是你这个一下子连接一坨上去, 连完了又不主动释放(关闭连接), 当然扛不住, 所以有两个解决方法

1. 改大postgresql.conf里的max_connections值, 加大postgresql的默认连接数

2. query_with_desc函数结尾增加cur.close()及时关闭连接