将html源代码保存到

Question

我正在尝试将网站源代码保存到我的MySQL数据库中。使用urllib成功检索到源文件。下一步，保存数据。与数据库的连接没有问题，问题出在源代码的保存上，因为当我从insert语句中删除源代码时，一切都很正常。

    # get the webpage source
    f = urllib.urlopen(row_urls['url'])
    source_fetched = f.read()
    f.close()

    # Save the webpage source
    scrapy_url_id = row_urls['id']
    url = row_urls['url']
    created = datetime.datetime.now()
    source = unicode(source_fetched,'utf-8')

    cur_webpage_save = con.cursor(mdb.cursors.DictCursor)
    cur_webpage_save.execute("""INSERT INTO webpage(scrapy_url_id,url,created,source) VALUES('%s', '%s', '%s', '%s');""" %(scrapy_url_id, url, created, source))

我猜它与需要转义的字符有关，我尝试了一下，但它产生了相同的错误：

    cur_webpage_save.execute(mdb.escape_string("""INSERT INTO webpage(scrapy_url_id,url,created,source) VALUES('%s', '%s', '%s', '%s');""" %(scrapy_url_id, url, created, source)))

下面您将看到错误。我做错了什么..。

Traceback (most recent call last):
File "clean.py", line 55, in <module>
cur_webpage_save.execute(mdb.escape_string("""INSERT INTO webpage(scrapy_url_id,url,created,source) VALUES('%s', '%s', '%s', '%s');""" %(scrapy_url_id, url, created, source)))
File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
self.errorhandler(self, exc, value)
File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (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 '\\'4\\', \\'http://example.com/test.html?id=108185\\', \\'2012-10-28' at line 1")

Answer 1

不要使用字符串格式将数据放入数据库，而应使用SQL参数：

cur_webpage_save.execute("""INSERT INTO webpage(scrapy_url_id,url,created,source) VALUES(%s, %s, %s, %s);""", (scrapy_url_id, url, created, source))

对于MySQLdb，语法几乎相同，只需去掉单引号即可。

数据库适配器将每个%s替换为正确引用的字符串值。这也可以防止SQL注入攻击。