python - 无法将数据从拼凑的项目插入sql表

以下是错误：

[scrapy.core.scraper] ERROR: Error processing {'level': None,
 'school': 'Some school name',
 'place': None,
 'subject': None}
Traceback (most recent call last):
  File "/home/reducedgosling/.virtualenvs/data/lib/python3.6/site-packages/twisted/internet/defer.py", line 653, in _runCallbacks
    current.result = callback(current.result, *args, **kw)
  File "/home/reducedgosling/Programming/schools/pipelines.py", line 28, in process_item
    self.cur.execute(sql, data)
psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block

项目.py

class SchoolsItem(scrapy.Item):
    subject = scrapy.Field()
    level = scrapy.Field()
    place = scrapy.Field()
    school = scrapy.Field()

蜘蛛.py

def parse_school(self, response):
    item = SchoolsItem()
    school = response.css('h1 span.title::text').extract_first()
    table_rows = response.css('tr')
    for x in table_rows:
        item['subject'] = x.css('td.views-field-title a::text').extract_first()
        item['level'] = x.css('td.views-field-field-level').xpath('normalize-space(./text())').extract_first()
        item['place'] = x.css('td.views-field-field-campus').xpath('normalize-space(./text())').extract_first()
        item['school'] = school
        yield item

管道.py

def process_item(self, item, spider):
    sql = "INSERT INTO udir_content (subject, level, school, place) VALUES (%s, %s, %s, %s);"
    data = (item['subject'], item['level'], item['school'], item['place'])
    self.cur.execute(sql, data)
    self.connection.commit()
    return item

我做错什么了？我怀疑是空值，哪个Python（或psycopg？）转换为无值？但是PostgreSQL接受空值，除非我指定不为空，对吗？
psql日志文件中显示的第一个错误是：

ERROR:  relation "udir_content" does not exist at character 13
STATEMENT:  INSERT INTO udir_content (subject, level, school, place) VALUES (NULL, NULL, 'Some school name', NULL);

其余的只是说“交易中止”。

最佳答案

您的插入有问题，报告的错误是因为插入失败，并且您在发出另一个事务之前没有回滚该事务。
execute应该使用问号作为占位符（?），而不是%s
所以对于你的陈述你应该用

sql = "INSERT INTO udir_content (subject, level, school, place) VALUES (?, ?, ?, ?);"

关于python - 无法将数据从拼凑的项目插入sql表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50758256/