爬虫MySQL配置

关于爬虫MySQL的连接及表的创建

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.ext.declarative import declarative_base


engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/test')
db = scoped_session(sessionmaker(bind=engine))

Base = declarative_base()

class Song(Base):
    """创建表"""
    __tablename__ = 'text'

    pk = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100), nullable=True, index=True)
    singer = Column(Integer, nullable=True, index=True)
    album = Column(Integer)
    lrc = Column(Text)

if __name__ == '__main__':
	metadata = Base.metadata
	metadata.create_all(settings.engine)

爬虫中间件 数据库的查询 插入数据

查询
db.query(Song.pk).filter_by(singer=item['singer']).first()

插入
song = Song(
            name=item['song_name'],
            singer=singer.pk,
            album=album.pk,
            lrc=lrc
            )
db.add(song)
db.commit()