Spider--データ永続化MySQL

1、データベースにライブラリ構築表

#    mysql   
mysql -h127.0.0.1 -uroot -p123456
#     
create database maoyandb charset utf8;
use maoyandb;
create table filmtab(
name varchar(100),
star varchar(300),
time varchar(50)
)charset=utf8;

2、回顧pymysql基本使用

import pymysql
​
#   2   
db = pymysql.connect('localhost','root','123456','maoyandb',charset='utf8')
cursor = db.cursor()
​
#   SQL           
# execute()              
ins = 'insert into filmtab values(%s,%s,%s)'
cursor.execute(ins,['    ','   ','1993'])
db.commit()
​
#   
cursor.close()
db.close()

効率的なexecutemany()メソッドを試してみましょう.

import pymysql
​
#   2   
db = pymysql.connect('localhost','root','123456','maoyandb',charset='utf8')
cursor = db.cursor()
​
#      
film_list = [('    ','   ','1994'),('    ','   ','1994')]
​
#   SQL           
# execute()              
cursor.executemany('insert into filmtab values(%s,%s,%s)',film_list)
db.commit()
​
#   
cursor.close()
db.close()

3、映画情報をMySQLデータベースに保存する(できるだけexecutemany方法を使う)

# mysql - executemany([(),(),()])
def write_html(self, film_list):
  L = []
  ins = 'insert into filmtab values(%s,%s,%s)'
  for film in film_list:
    t = (
      film[0].strip(),
      film[1].strip(),
      film[2].strip()[5:15]
    )
    L.append(t)
​
    self.cursor.executemany(ins, L)
    #              
    self.db.commit()

4、SQLクエリ

1、  20              
  select name,time from filmtab where time<(now()-interval 20 year);
2、  1990-2000           
  select name,time from filmtab where time>='1990-01-01' and time<='2000-12-31';