一次写爬虫碰到的问题

发表于 更新于 分类于 本文字数： 5k 阅读时长 ≈ 5 分钟

fork了lanbin510的豆瓣爬虫，想改写下写数据库。 这里有2个问题，一个是作者原先是基于python2.7的，我是直接用的3.6.9，所以有些语法上不大一样。
https://github.com/timoseven/DouBanSpider

因为要用mysql，直接docker来一个就行了，结果发现默认装的都8.0了，我还在用5.6呢

1
sudo docker run --name bookdb -p 3306:3306 -e MYSQL\_ROOT\_PASSWORD=123456 -d mysql

至于8.0增加了什么东西，可以先参考下这个。但是这个不是我碰到的问题。
#https://www.jianshu.com/p/be29467c2b0c

开始建表了这就。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE IF NOT EXISTS `bookinfo`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `book_title` VARCHAR(100) NOT NULL,
   `book_douban_id` INT UNSIGNED NOT NULL,
   `book_rate` FLOAT(3,2) NOT NULL,
   `book_author` VARCHAR(100) NOT NULL,
   `book_rate_user` INT UNSIGNED NOT NULL,
   `book_press` VARCHAR(100) NOT NULL,
   `book_press_date` VARCHAR(20) NOT NULL,
   `book_price` FLOAT(6,2) NOT NULL,
   `book_tag` VARCHAR(100) NOT NULL,
   `createtime` DATE DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                     |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | Specifying number of digits for floating point data types is deprecated and will be removed in a future release.                                                            |
| Warning | 1681 | Specifying number of digits for floating point data types is deprecated and will be removed in a future release.                                                            |
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

我去，建一个表居然出来3个warning，这也是牛逼大发了，不过还好都是关于未来的，而不是现在的。

不过接下去这个问题就不是经验可以覆盖的，必须google，发现创建新用户一直都是报错。

1
2
mysql> GRANT ALL PRIVILEGES ON douban_book.* TO 'douban'@'127.0.0.1'IDENTIFIED BY '123456';
ERROR 1064 (42000): 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 'IDENTIFIED BY '123456'' at line 1

这都没开始flush呢，还居然报错了，实在看不出来是什么问题。只好放狗搜索了下。原来现在只能是先create user再进行grant。

1
2
CREATE USER 'douban'@'127.0.0.1' IDENTIFIED BY '123456';
GRANT ALL ON *.* TO 'root'@'${MYSQL_ROOT_HOST}' WITH GRANT OPTION;

好了，数据库的解决好了就看看python3下的mysql的驱动有哪些了，发现pymysql最合适，那就装上了，可再实际跑的时候一直各种报错啊。

就很简单的几条调用还居然报错了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Traceback (most recent call last):
  File "doubanSpider.py", line 220, in <module>
    do_spider(book_tag_lists)
  File "doubanSpider.py", line 168, in do_spider
    'INSERT INTO bookinfo (book_title, book_douban_id,book_rate,book_author,book_rate_user,book_press,book_press_date,book_price,book_tag) VALUES ("%s", %d, %f, "%s", %d, "%s", "%s", %f, "%s")', (book_list[0],))
  File "/Users/timo/.pyenv/versions/venv369/lib/python3.6/site-packages/pymysql/cursors.py", line 199, in executemany
    self.rowcount = sum(self.execute(query, arg) for arg in args)
  File "/Users/timo/.pyenv/versions/venv369/lib/python3.6/site-packages/pymysql/cursors.py", line 199, in <genexpr>
    self.rowcount = sum(self.execute(query, arg) for arg in args)
  File "/Users/timo/.pyenv/versions/venv369/lib/python3.6/site-packages/pymysql/cursors.py", line 168, in execute
    query = self.mogrify(query, args)
  File "/Users/timo/.pyenv/versions/venv369/lib/python3.6/site-packages/pymysql/cursors.py", line 147, in mogrify
    query = query % self._escape_args(args, conn)
TypeError: %d format: a number is required, not str

我数据表里有int类型啊，这里也写了%d了，可为什么一直报错呢。只能继续放狗搜索下，发现居然使用pymysql的时候，所有字段不管什么类型都是使用%s
https://blog.csdn.net/jy1690229913/article/details/79407224

然后小白小朋友还特地去看看为什么会这样
https://github.com/PyMySQL/PyMySQL/blob/master/pymysql/converters.py
就是这个文件的第324行和53，54行说明了这个问题，居然这个里面把所有的都变成了string了，可你们的帮助文档里压根什么都不说明。

help(pymysql.cursors)里面就只写了

1
2
3
4
5
6
7
8
9
10
|  executemany(self, query, args)
|      Run several data against one query
|
|      :param query: query to execute on server
|      :param args:  Sequence of sequences or mappings.  It is used as parameter.
|      :return: Number of rows affected, if any.
|
|      This method improves performance on multiple-row INSERT and
|      REPLACE. Otherwise it is equivalent to looping over args with
|      execute().

这种帮助就跟没有我看也没什么区别啊。