def getData():
db = DB()
# get article
article = db.one("SELECT * FROM article WHERE date=?", (getToday(), ))
if article is not None:
# pic TEXT, content TEXT
article = {'pic': article[1], 'content': article[2]}
# get music
music = db.one("SELECT * FROM music WHERE date=?", (getToday(), ))
if music is not None:
# name TEXT, artist TEXT, pic TEXT, link TEXT
music = {
'name': music[1],
'artist': music[2],
'pic': music[3],
'link': music[4]
}
# get movie
movie = db.one("SELECT * FROM movie WHERE date=?", (getToday(), ))
if movie is not None:
# name TEXT, pic TEXT, type TEXT, score INT, plot TEXT, link TEXT
movie = {
'name': movie[1],
'pic': movie[2],
'type': movie[3],
'score': movie[4],
'plot': movie[5],
'link': movie[6]
}
return {'article': article, 'music': music, 'movie': movie}
def get_db():
"""关于其作用域请移步查看官方文档"""
try:
db = DB()
yield db
finally:
db.close()
def get_db(data_clearing):
"""关于其作用域请移步查看官方文档"""
try:
db = DB()
yield db
finally:
db.close()
def get_db():
"""
:return:
"""
try:
db = DB()
yield db
finally:
db.close()
def getData():
db = DB()
# get article
article = db.one("SELECT * FROM article WHERE date=?", (getToday(),))
if article is not None:
# pic TEXT, content TEXT
article = {
'pic': article[1],
'content': article[2]
}
# get music
music = db.one("SELECT * FROM music WHERE date=?", (getToday(),))
if music is not None:
# name TEXT, artist TEXT, pic TEXT, link TEXT
music = {
'name': music[1],
'artist': music[2],
'pic': music[3],
'link': music[4]
}
# get movie
movie = db.one("SELECT * FROM movie WHERE date=?", (getToday(),))
if movie is not None:
# name TEXT, pic TEXT, type TEXT, score INT, plot TEXT, link TEXT
movie = {
'name': movie[1],
'pic': movie[2],
'type': movie[3],
'score': movie[4],
'plot': movie[5],
'link': movie[6]
}
return {
'article': article,
'music': music,
'movie': movie
}
def init_database():
if Path.is_file(Path.db_path):
Debug.logger.debug(u"Connect to the database...")
Debug.logger.debug(u"db_path: " + str(Path.db_path))
DB.set_conn(sqlite3.connect(Path.db_path))
else:
Debug.logger.debug(u"Create db file...")
DB.set_conn(sqlite3.connect(Path.db_path))
with open(Path.sql_path) as sql_script:
DB.cursor.executescript(sql_script.read())
DB.commit()
def handle_sql(cls, sql: str, db: DB):
"""
处理sql，如果sql执行的结果不会空，执行sql的结果和响应结果字典合并
:param sql: 支持单条或者多条sql，其中多条sql使用 ; 进行分割
多条sql,在用例中填写方式如下select * from user; select * from goods 每条sql语句之间需要使用 ; 来分割
单条sql,select * from user 或者 select * from user;
:param db: 数据库连接对象
:return:
"""
sql = rep_expr(sql, DataProcess.response_dict)
for sql in sql.split(";"):
sql = sql.strip()
if sql == '':
continue
# 查后置sql
result = db.execute_sql(sql)
allure_step(f'执行sql: {sql}', result)
logger.info(f'执行sql: {sql} \n 结果: {result}')
if result is not None:
# 将查询结果添加到响应字典里面，作用在，接口响应的内容某个字段 直接和数据库某个字段比对，在预期结果中
# 使用同样的语法提取即可
DataProcess.response_dict.update(result)
import sys
sys.path.append('..')
from tools.spider import Spider
from tools.db import DB
from tools.public import *
from lxml import etree
url = 'http://wufazhuce.com/'
if __name__ == '__main__':
spider = Spider()
html = spider.crawl(url)
selector = etree.HTML(html)
url = selector.xpath('//div[@class="carousel-inner"]/div[@class="item active"]/a/img/@src')
text = selector.xpath('//div[@class="carousel-inner"]/div[@class="item active"]/div[@class="fp-one-cita-wrapper"]/div[@class="fp-one-cita"]/a/text()')
url = url[0] if len(url) == 1 else ""
text = text[0] if len(text) == 1 else ""
db = DB()
select = db.one("SELECT * FROM article WHERE date=?", (getToday(),))
if select is None:
insertData = (getToday(), url, text)
db.execute("INSERT INTO article VALUES (?, ?, ?)", insertData)
from tools.public import *
import random
headers = {
'Cookie': 'appver=1.5.0.75771;',
'Referer': 'http://music.163.com/'
}
payload = {
'id': '140330894',
'updateTime': -1
}
url = 'http://music.163.com/api/playlist/detail'
if __name__ == '__main__':
spider = Spider()
data = spider.req(headers=headers, params=payload).crawl(url, pattern='json')
if data['code'] == 200 and data['result'] is not None:
tracks = data['result']['tracks']
mCount = data['result']['trackCount']
index = random.randint(0, mCount-1)
track = tracks[index]
db = DB()
select = db.one("SELECT * FROM music WHERE date=?", (getToday(),))
if select is None:
insertData = (getToday(), track['name'], track['artists'][0]['name'], track['album']['picUrl'], track['mp3Url'])
db.execute("INSERT INTO music VALUES (?, ?, ?, ?, ?)", insertData)
import sys
sys.path.append('..')
from tools.db import DB
db = DB()
db.execute('create table article (date TEXT, pic TEXT, content TEXT)')
db.execute(
'create table music (date TEXT, name TEXT, artist TEXT, pic TEXT, link TEXT)'
)
db.execute(
'create table movie (date TEXT, name TEXT, pic TEXT, type TEXT, score INT, plot TEXT, link TEXT)'
)
import sys
sys.path.append('..')
from tools.db import DB
db = DB()
db.execute('create table article (date TEXT, pic TEXT, content TEXT)')
db.execute('create table music (date TEXT, name TEXT, artist TEXT, pic TEXT, link TEXT)')
db.execute('create table movie (date TEXT, name TEXT, pic TEXT, type TEXT, score INT, plot TEXT, link TEXT)')
def get_db():
"""关于其作用域请移步查看官方文档"""
db = DB()
yield db
db.close()
def install(self, db='sg'):
if self.conn == None:
self.db = DB(db)
class SgPipeline(object):
conn = None
db = None
def install(self, db='sg'):
if self.conn == None:
self.db = DB(db)
def add_cate_goods_index(self, cate_id, goods_id):
self.db.execute("INSERT INTO le_cate_goods_index SET cate_id=%s, goods_id=%s,weight=0",[cate_id, goods_id])
'''
name,url,oldImg,descOldImg,cate,price,originalPrice,countBought,ExpiryTime,
highlight,condition,description,address,postCode,merchant,phone
`last_modified`, `goods_id`, `uid`, `img`, `deal_img`, `display_order`, `img_w`, `img_h`,
`desc_bigpic`, `bigpic`, `small_pic`, `desc_oldimg`, `oldimg`, `name`, `seo_title`, `url`,
`currency`, `original_price`, `price`, `cate_id`, `source`, `addtime`, `expiry_time`, `uptime`,
`website_id`, `store_id`, `isdeal`, `ispublish`,isshow`,`highlight`, `conditions`, `description`,
`merchant`, `phone`, `address`, `city`, `country`, `post`
'''
def process_item(self, item, spider):
self.install(item['db'])
Classifier = SimpleClassifier(item['db'])
if type(item) != SgGoodsItem:
return item
if item['name'] == False or item['name'] == '':
return item
img = small_pic = big_pic = old_pic = ''
if len(item['images']) > 0 :
img = '/uploaded/' + item['images'][0].replace( 'original','thumb400')
for src in item['images']:
small_pic = small_pic + '/uploaded/'+ src.replace('original', 'thumb100') + '|'
big_pic = big_pic + '/uploaded/'+ src +'|'
small_pic = small_pic.strip('|')
big_pic = big_pic.strip('|')
if len(item['oldImg']) > 0:
old_pic = '|'.join(item['oldImg'])
for i in item:
if type(item[i]) == unicode or type(item[i]) == str:
item[i] = item[i].encode('utf-8')
if item['goods']:
goods_cate_id = 0
# 更新cate_id
if item['goods']['cate_id'] < 1:
classlist = Classifier.findCateAndTags(item['name'], 4)
if classlist['cate']:
goods_cate_id = classlist['cate']
self.add_cate_goods_index(classlist['cate'], item['goods']['goods_id'])
for cate_id in classlist['cates']:
self.add_cate_goods_index(cate_id, item['goods']['goods_id'])
else:
goods_cate_id = item['goods']['cate_id']
if item['goods']['price'] != item['price'] or item['goods']['original_price'] != item['originalPrice'] or item['goods']['name'] != item['name']:
res = self.db.execute("UPDATE le_goods SET isshow=1,name=%s,price=%s,original_price=%s, uptime=%s,expiry_time=%s,site_id=%s,cate_id=%s WHERE goods_id=%s",[item['name'],item['price'],item['originalPrice'],int(time.time()),item['ExpiryTime'], item['site_id'],goods_cate_id,item['goods']['goods_id']])
else:
res = self.db.execute("UPDATE le_goods SET isshow=1,uptime=%s,expiry_time=%s,site_id=%s,cate_id=%s WHERE goods_id=%s",[int(time.time()),item['ExpiryTime'], item['site_id'],goods_cate_id,item['goods']['goods_id']])
print res._last_executed
else:
classlist = Classifier.findCateAndTags(item['name'], 4)
goods_cate_id = classlist['cate']
res = self.db.execute("INSERT INTO le_goods SET `uid`=%s,`site_id`=%s,`img`=%s, `deal_img`=%s,`display_order`=%s,`desc_bigpic`=%s, `oldimg`=%s, `small_pic`=%s,`desc_oldimg`=%s,`bigpic`=%s, `name`=%s, `seo_title`=%s, `url`=%s, `currency`=%s,`original_price`=%s, `price`=%s, `cate_id`=%s, `source`=%s, `addtime`=%s,`expiry_time`=%s, `uptime`=%s, `website_id`=%s,`isdeal`=%s,`ispublish`=%s,`isshow`=%s,`highlight`=%s, `conditions`=%s, `description`=%s, `merchant`=%s,`phone`=%s, `address`=%s,`city`=%s, `country`=%s, `post`=%s",[1,item['site_id'], img,img,0,'',old_pic,small_pic,'',big_pic,item['name'],get_seo_title(item['name']),item['url'],'SGD',item['originalPrice'],item['price'], goods_cate_id,'reptile',time.time(),item['ExpiryTime'],time.time(),item['website_id'],1,1,1,item['highlight'],item['condition'],item['description'],item['merchant'],item['phone'],item['address'],1,1,item['postCode']])
goods_id = res.lastrowid
if classlist['cate'] > 0:
self.add_cate_goods_index(classlist['cate'],goods_id)
for cate_id in classlist['cates']:
self.add_cate_goods_index(cate_id, goods_id)
return item
"""
import random
from typing import List
from fastapi import FastAPI, File, UploadFile
from tools.db import DB
from faker import Faker
fake = Faker('zh_CN')
app = FastAPI()
# 连接数据库
db = DB()
# 创建游标
cursor = db.connection.cursor()
@app.post("/upload_file/", name='上传单文件接口')
async def create_upload_file(file_excel: UploadFile = File(...)):
# 单文件上传接口，并将文件写到服务器地址， 接收文件对象的参数 是 file_excel
# 读取文件
contents = await file_excel.read()
# 保存本地
with open(file_excel.filename, "wb") as f:
f.write(contents)
return {
'msg': '操作成功',
"filename": file_excel.filename,