Scrapy:MySQL Pipeline-遇到意外错误 [英] Scrapy: MySQL Pipeline -- Unexpected Errors Encountered

问题描述

根据插入/更新的内容，我会遇到许多错误.

I'm getting a number of errors, depending upon what is being inserted/updated.

以下是用于处理商品的代码:

Here is the code for processing the item:

 def process_item(self, item, spider):

    try:
        if 'producer' in item:
            self.cursor.execute("""INSERT INTO Producers (title, producer) VALUES (%s, %s)""", (item['title'], item['producer']))
        elif 'actor' in item:
            self.cursor.execute("""INSERT INTO Actors (title, actor) VALUES (%s, %s)""", (item['title'], item['actor']))
        elif 'director' in item:
            self.cursor.execute("""INSERT INTO Directors (title, director) VALUES (%s, %s)""", (item['title'], item['director']))
        else:
            self.cursor.execute("""UPDATE example_movie SET distributor=%S, rating=%s, genre=%s, budget=%s WHERE title=%s""", (item['distributor'], item['rating'], item['genre'], item['budget'], item['title']))
        self.conn.commit()
    except MySQLdb.Error, e:
        print "Error %d: %s" % (e.args[0], e.args[1])

    return item

以下是刮板返回的items的示例:

Here is an example of the items returned from the scraper:

 [{'budget': [u'N/A'], 'distributor': [u'Lorimar'], 'genre': [u'Action'], 'rating': [u'R'],'title': [u'Action Jackson']}, {'actor': u'Craig T. Nelson', 'title': [u'Action Jackson']}, {'actor': u'Sharon Stone', 'title': [u'Action Jackson']}, {'actor': u'Carl Weathers', 'title': [u'Action Jackson']}, {'producer': u'Joel Silver', 'title': [u'Action Jackson']}, {'director': u'Craig R. Baxley', 'title': [u'Action Jackson']}]

以下是返回的错误:

    2013-08-25 23:04:57-0500 [ActorSpider] ERROR: Error processing {'budget': [u'N/A'],
 'distributor': [u'Lorimar'],
 'genre': [u'Action'],
 'rating': [u'R'],
 'title': [u'Action Jackson']}
Traceback (most recent call last):
  File "/Library/Python/2.7/site-packages/scrapy/middleware.py", line 62, in _process_chain
    return process_chain(self.methods[methodname], obj, *args)
  File "/Library/Python/2.7/site-packages/scrapy/utils/defer.py", line 65, in process_chain
    d.callback(input)
  File "/System/Library/Frameworks/Python.framework/Versions/2.7/Extras/lib/python/twisted/internet/defer.py", line 361, in callback
    self._startRunCallbacks(result)
  File "/System/Library/Frameworks/Python.framework/Versions/2.7/Extras/lib/python/twisted/internet/defer.py", line 455, in _startRunCallbacks
    self._runCallbacks()
--- <exception caught here> ---
  File "/System/Library/Frameworks/Python.framework/Versions/2.7/Extras/lib/python/twisted/internet/defer.py", line 542, in _runCallbacks
    current.result = callback(current.result, *args, **kw)
  File "/Users/fortylashes/Documents/Management_Work/BoxOfficeMojo/BoxOfficeMojo/pipelines.py", line 53, in process_item
    self.cursor.execute("""UPDATE example_movie SET distributor=%S, rating=%s, genre=%s, budget=%s WHERE title=%s""", (item['distributor'], item['rating'], item['genre'], item['budget'], item['title']))
  File "/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQLdb/cursors.py", line 159, in execute
    query = query % db.literal(args)
exceptions.ValueError: unsupported format character 'S' (0x53) at index 38

   Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), 'Craig T. Nelson')' at line 1
   2013-08-25 23:04:57-0500 [ActorSpider] DEBUG: Scraped from <200 http://www.boxofficemojo.com/movies/?id=actionjackson.htm>
{'actor': u'Craig T. Nelson', 'title': [u'Action Jackson']}
   Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), 'Sharon Stone')' at line 1
   2013-08-25 23:04:57-0500 [ActorSpider] DEBUG: Scraped from <200 http://www.boxofficemojo.com/movies/?id=actionjackson.htm>
{'actor': u'Sharon Stone', 'title': [u'Action Jackson']}
   Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), 'Carl Weathers')' at line 1
   2013-08-25 23:04:57-0500 [ActorSpider] DEBUG: Scraped from <200 http://www.boxofficemojo.com/movies/?id=actionjackson.htm>
{'actor': u'Carl Weathers', 'title': [u'Action Jackson']}
  Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), 'Joel Silver')' at line 1
  2013-08-25 23:04:57-0500 [ActorSpider] DEBUG: Scraped from <200 http://www.boxofficemojo.com/movies/?id=actionjackson.htm>
{'producer': u'Joel Silver', 'title': [u'Action Jackson']}
  Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), 'Craig R. Baxley')' at line 1
  2013-08-25 23:04:57-0500 [ActorSpider] DEBUG: Scraped from <200 http://www.boxofficemojo.com/movies/?id=actionjackson.htm>
{'director': u'Craig R. Baxley', 'title': [u'Action Jackson']}

显然有很多问题.感谢您的阅读！任何建议或想法都将不胜感激！

Apparently there are a lot issues. Thank you for reading! Any and all suggestions or ideas are greatly appreciated!

:::: UPDATE/MORE INFO :::::

::::UPDATE/MORE INFO::::

在总共52个测试集中，似乎有三部电影被插入到Actors，Producers和Directors表中. 注意:UPDATE语句根本不起作用.

There appear to be three movies, of the test set of 52 total, which are being inserted into the the Actors, Producers and Directors tables. Note: The UPDATE statement isn't working at all.

这些电影是:Abraham Lincoln: Vampire Hunter，Ace Ventura: Pet Detective和Ace Ventura: When Nature Calls

有趣的是，这些都是title中包含:的电影-我不确定这是什么意思，但是如果有人有主意，请与我们分享！

Interestingly, these are all of the movies that have : in the title- I'm not sure what this means, but if anyone has an idea please share it!

:::::: INSERT SOLVED ::::::

:::::INSERT SOLVED:::::

事实证明，该问题是由scraper将单个项目放在列表中引起的.因此，{'actor': [u'this one guy']相对于顶部{'actor': u'this one guy'}.

Turns out the problem was caused by the scraper putting individual items in a list. So {'actor': [u'this one guy'] as opposed top {'actor': u'this one guy'}.

推荐答案

您在代码的第53行中为字符串数据类型使用了错误的格式说明符.应该是小写的"s"而不是大写的"S".

You have used wrong format specifier for string data type at line 53 of your code. It should be small 's' not the capital 'S'.

self.cursor.execute("""UPDATE example_movie SET distributor=%S, rating=%s, genre=%s, budget=%s WHERE title=%s""", (item['distributor'], item['rating'], item['genre'], item['budget'], item['title']))

应该是这样.

self.cursor.execute("""UPDATE example_movie SET distributor=%S, rating=%s, genre=%s, budget=%s WHERE title=%s""", (item['distributor'], item['rating'], item['genre'], item['budget'], item['title']))

这篇关于Scrapy:MySQL Pipeline-遇到意外错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！