python爬虫需要mysql_python 将爬虫内容保存到mysql中

二十五、python将爬取的内容保存到mysql

--------------------------------------------------------------

#sqlAlchemy的介绍

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String, DateTime, ForeignKey

from sqlalchemy.orm import sessionmaker, relationship, backref

from sqlalchemy.dialects.mysql import LONGTEXT

import requests

from lxml import etree

# 获取段子内容、赞、分享、收藏数

def getJokeOfAllList(basurl='http://www.budejie.com/text/{0}'):

nextPage = True

pageNum = 1

while nextPage:

url = basurl.format(pageNum)

response = requests.get(url)

selector = etree.HTML(response.text)

all = selector.xpath('//*/div[@class="j-r-list"]/ul/li')

for a in all:

joke = a.xpath('div[@class="j-r-list-c"]/div[@class="j-r-list-c-desc"]/a/text()')[0]

like = a.xpath('div[@class="j-r-list-tool"]/div[@class="j-r-list-tool-l "]/ul/li[@class="j-r-list-tool-l-up"]/span/text()')[0]

down = a.xpath('div[@class="j-r-list-tool"]/div[@class="j-r-list-tool-l "]/ul/li[@class="j-r-list-tool-l-down "]/span/text()')[0]

share = a.xpath('div[@class="j-r-list-tool"]/div[@class="j-r-list-tool-ct"]/div[@class="j-r-list-tool-ct-share-c"]/span/text()')[0]

comment = a.xpath('div[@class="j-r-list-tool"]/div[@class="j-r-list-tool-r j-r-list-tool-cc"]/ul/li[@class=" f-tac j-comment j-comment-width  j-comment-down-width"]/a/span[@class="comment-counts"]/text()')[0]

# print joke, '=====', like, '====', down, '====', share.replace(u"分享??",""), '=====', comment, '====='

yield joke, like, down, share.replace(u"分享??", ""), comment

hasNext = selector.xpath('//a[@class="pagenxt"]')

if hasNext:

pageNum += 1

else:

nextPage = False

# print pageNum

#大概步骤如下：

#1、创建连接

#mysql+mysqlconnector 或者mysql+mysqldb    mysql代表数据库，mysqlconnector代表连接驱动

engine = create_engine('mysql+mysqlconnector://root:root@192.168.216.7:3306/testdb?charset=utf8',echo=True) #echo是否打印sql语句

#2、定义映射

Base = declarative_base()  #获取公共类，模板的顶级父类

#定义自己的实体类

class Joke(Base):

__tablename__ = 'jokes'

id = Column(Integer, nullable=True, primary_key=True, index=True)

content = Column(LONGTEXT, nullable=True)

like = Column(Integer, nullable=False)

down = Column(Integer, nullable=False)

share = Column(Integer, nullable=False)

comment = Column(Integer, nullable=False)

#3、映射实例化，创建数据库表

Base.metadata.create_all(engine)

#4、创建会话

Session = sessionmaker(engine)

session = Session()

#5、对象实例持久化

if __name__ == '__main__':

for i,(joke, like, down, share, comment) in enumerate(getJokeOfAllList()):

#print i,joke, like, down, share, comment

#print share.encode("utf-8").replace('??',"")

joke = Joke(content=joke.encode("utf-8"), like=int(like), down=int(down), share=int(share.encode("utf-8").replace('??',"")), comment=int(comment))

print joke.content, joke.like, joke.down, joke.share, joke.comment

session.add(joke)

if i % 10 == 0:

print i

session.commit()

--------------------------------------------------------------