python - 向 PostgreSQL 插入元组的问题

Question

我有一个用于废弃网页数据的代码，它工作正常。下面是我正在使用的代码。面临以下问题。

结果我得到了 5 个列表，我已经使用 ZIP 函数合并了这些列表，如下所示

 result = zip(positions,filtered, urls, company,price)
 print(tuple(result))

此print(tuple(result))的结果如下所示

((1, 'product_1', 'URL_1', 'company_1', '34.90'), 
(2, 'product_2', 'URL_2', 'company_2', '5.99'),
(3, 'product_3', 'URL_3', 'company_3', '30.20'))

已使用以下内容插入 Postgres 但未插入，

  for d in result:
         print(d)
         psql_cursor.execute("INSERT into results(position, product_name, url,company,price) VALUES (%s, %s, %s,%s, %s)", d)

任何建议都会非常有帮助。提前致谢!

完整查询:

from selenium import webdriver 
from selenium.webdriver.common.by import By 
from selenium.webdriver.support.ui import WebDriverWait 
from selenium.webdriver.support import expected_conditions as EC 
from selenium.common.exceptions import TimeoutException
import psycopg2
import datetime

results=[]

option = webdriver.ChromeOptions()
option.add_argument("—-incognito")
#browser = webdriver.Chrome(executable_path='/usr/local/bin/chromedriver/', chrome_options=option)
browser = webdriver.Chrome(executable_path='/users/lins/downloads/chrome_driver/chromedriver', chrome_options=option)

try:
#Database connection string
 DSN = "dbname='postgres' user='postgres' host='localhost' password='postgres' port='5432'"
 #DWH table to which data is ported
 TABLE_NAME = 'search_url'
 #Connecting DB..
 conn = psycopg2.connect(DSN)
 print("Database connected...")
 conn.set_client_encoding('latin-1')
 cur = conn.cursor()
except (Exception, psycopg2.Error) as error:
 print('database connection failed')
 quit()

search_url_fetch="""select url_to_be_searched from search_url limit 1"""
psql_cursor = conn.cursor()
psql_cursor.execute(search_url_fetch)
serach_url_list = psql_cursor.fetchall()
print('Fetched DB values')
for row in serach_url_list:
    passed_url=''
    passed_url=str(row)
    passed_url=passed_url.replace(',)','')
    passed_url=passed_url.replace('(','')
    new_url=''
    new_url=passed_url[1:len(passed_url)-1]
    print(new_url)
    print("\n")
    print(new_url)
    browser.get(new_url)

    product_name_list = browser.find_elements_by_xpath("//span[@class='pymv4e']")

    upd_product_name_list=list(filter(None, product_name_list))
    product_name = [x.text for x in product_name_list]
    #print('Product names:')
    filtered = [x for x in product_name if len(x.strip()) > 0]
    element_length=(len(filtered))
    print('length: '+str(element_length))

    if element_length ==0 and 'buy' not in new_url :
        browser.get(new_url+ '+buy')
        product_name_list = browser.find_elements_by_xpath("//span[@class='pymv4e']")
        upd_product_name_list=list(filter(None, product_name_list))
        product_name = [x.text for x in product_name_list]
        filtered = [x for x in product_name if len(x.strip()) > 0]

    positions=[]
    for x in range(1, element_length+1):
        positions.append(x)

    company_name_list = browser.find_elements_by_xpath("//div[@class='LbUacb']")
    company = [x.text for x in company_name_list]

    price_list = browser.find_elements_by_xpath("//div[@class='e10twf T4OwTb']")
    price = [x.text for x in price_list]

    urls=[]
    find_href = browser.find_elements_by_xpath("//a[@class='plantl pla-unit-single-clickable-target clickable-card']")
    for my_href in find_href:
        url_list=my_href.get_attribute("href")
        urls.append(url_list)

    print('Final Result:')
    result = zip(positions,filtered, urls, company,price)
    print("\n")
    print(tuple(result))

    for d in result:
     print(d)
     psql_cursor.execute("INSERT into results(position, product_name, url,company,price) VALUES (%s, %s, %s,%s, %s)", d)

异常(exception):

Traceback (most recent call last):
  File "/Users/lins/Documents/hgh.py", line 118, in <module>
    cur.execute("INSERT into results(position, product_name, url,company,price) VALUES (%s, %s, %s,%s, %s)", d)
  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/psycopg2/extensions.py", line 125, in getquoted
    qobjs = [o.getquoted() for o in pobjs]
  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/psycopg2/extensions.py", line 125, in <listcomp>
    qobjs = [o.getquoted() for o in pobjs]
UnicodeEncodeError: 'latin-1' codec can't encode character '\u20ac' in position 0: ordinal not in range(256)

Answer 1

您需要将更改提交到数据库。

来自docs :(强调我的)

commit()

Commit any pending transaction to the database.

By default, Psycopg opens a transaction before executing the first command: if commit() is not called, the effect of any data manipulation will be lost.

The connection can be also set in “autocommit” mode: no transaction is automatically open, commands have immediate effect. See Transactions control for details.