psycopg2 - 无键连接 [英] psycopg2 - Unkeyed Connection
问题描述
我正在尝试通过 ThreadedConnectionPool
同时将项目插入到 postgres 表中,但我不断收到 psycopg2.pool.PoolError:试图放置无键连接
- 不确定为什么会这样.我也试过按顺序运行它,但仍然遇到相同的错误.
I'm trying to concurrently insert items into a postgres table via ThreadedConnectionPool
, but I keep getting psycopg2.pool.PoolError: trying to put unkeyed connection
- not sure why this is happening. I've tried running it sequentially also but still getting the same error.
本质上,代码会抓取网站的产品站点地图,并将抓取的项目插入到数据库中.
Essentially the code scrapes a website's sitemap for products and inserts the scraped items into a database.
代码:
class items:
def __init__(self):
self.conn = ThreadedConnectionPool(10, 100, dbname='postgres', user='xxx', password='xxx', host='xxx')
self.url = "some url"
self.session = requests.Session()
def scrape(self, pageNo):
//some logic
self.page(pageNo)
// scrapes specified page from sitemap
def page(self, page):
resp = self.session.get(self.mens+"?page="+str(page)).json()
products = resp['products']
ts = []
for item in products:
# self.indivProduct(self.url + pageNo)
t = threading.Thread(target=self.indivProduct, args=self.url + pageNo,))
ts.append(t)
t.start()
for item in ts:
item.join()
def indivProduct(self, url):
conn = self.conn.getconn()
cursor = conn.cursor()
// Some logic with requests
try:
sql = 'insert into "Output" ' \
'("productID", "brand", "categoryID", "productName", "price", "sizeInfo", "SKU", "URL", "dateInserted", "dateUpdated")' \
'values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
cursor.execute(sql,
(.., .., ..,))
conn.commit()
except IntegrityError:
conn.rollback()
sql = 'insert into "Output" ' \
'("productID", "brand", "categoryID", "productName", "price", "sizeInfo", "SKU", "URL", "dateInserted", "dateUpdated")' \
'values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) on conflict ("productID") do update set "dateUpdated" = EXCLUDED."dateUpdated"'
cursor.execute(sql,
(.., .., ..,))
conn.commit()
except Exception as e:
print(e)
print()
finally:
self.conn.putconn()
主要内容:
s = items()
s.scrape(3)
推荐答案
您看到此错误是因为您将 None 传递给 putconn() 函数.源码可见:https://github.com/psycopg/psycopg2/blob/master/lib/pool.py
You're seeing this error since you're passing None to the putconn() function. Source can be seen at: https://github.com/psycopg/psycopg2/blob/master/lib/pool.py
您应该将 finally 块调整为:
You should adjust your finally block to be:
finally:
cursor.close()
self.conn.putconn(conn)
我在强制连接池刷新后遇到了错误,并且有一行试图对来自旧池的连接调用 putconn(conn).
I'v encountered the error after forcing a Connection Pool refresh, and had a line that attempted to call putconn(conn) on a connection from the old pool.
这篇关于psycopg2 - 无键连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!