我想用多线程查找数据库,然后进行数据操作。
_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, in44011 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
postgresql默认最大连接数只有 100 , 见:
https://www.postgresql.org/do...
但是你这个一下子连接一坨上去, 连完了又不主动释放(关闭连接), 当然扛不住, 所以有两个解决方法
改大postgresql.conf
里的max_connections
值, 加大postgresql的默认连接数
query_with_desc
函数结尾增加cur.close()
及时关闭连接