def update_running(_dict={}):
# 更新任务信息
sql = 'UPDATE spider_setting SET data_status=%s,urls_status=%s WHERE id IN ({});'.format(
_dict['id'])
args = [_dict['status'], _dict['status']]
bool = mysql.sql_write(sql, args)
return bool
def delete_running(_dict={}):
# 删除一个任务
sql = 'DELETE FROM spider_setting WHERE id={};DROP TABLE IF EXISTS {};DROP TABLE IF EXISTS {};'.format(
_dict['id'], SPIDER_URLS.format(_dict['id']),
SPIDER_DATA.format(_dict['id']))
bool = mysql.sql_write(sql=sql)
return bool
def update_setting(_dict={}):
sql = '''
UPDATE spider_setting
SET update_time =%s,
group_name =%s,
task_name =%s,
first_urls =%s,
first_url =%s,
urls_xpath =%s,
second_url =%s,
data_xpath =%s,
timer =%s,
is_full =%s
WHERE
id =%s
AND user_id =%s
'''
args = [
datetime.now(), _dict["group_name"], _dict['task_name'],
_dict["first_urls"], _dict["first_url"], _dict["urls_xpath"],
_dict["second_url"], _dict["data_xpath"], _dict["timer"],
_dict["is_full"], _dict["id"], _dict['user_id']
]
bool = mysql.sql_write(sql, args)
return bool
def insert_user(_dict={}):
sql = 'insert into spider_user (create_time,username,password,company) values (%s,%s,%s,%s)'
args = [
datetime.now(), _dict['username'], _dict['password'], _dict['company']
]
bool = mysql.sql_write(sql, args)
return bool
def update_setting_timer(_dict):
sql = '''
UPDATE spider_setting
SET timer = %s
WHERE
id IN ({})
'''.format(_dict['id'])
args = [_dict['timer']]
bool = mysql.sql_write(sql, args)
return bool
def insert_setting(_dict={}):
create_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
sql = '''
INSERT INTO spider_setting (create_time, group_name, task_name, first_urls,first_url, urls_xpath,second_url, data_xpath, is_example, user_id, timer, is_full)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
'''
args = [
create_time, _dict["group_name"], _dict['task_name'],
_dict["first_urls"], _dict["first_url"], _dict["urls_xpath"],
_dict["second_url"], _dict["data_xpath"], _dict["is_example"],
_dict['user_id'], _dict["timer"], _dict["is_full"]
]
bool = mysql.sql_write(sql, args)
# 拿到insert这一行的id
if bool:
sql = '''
SELECT
id
FROM
spider_setting
WHERE
create_time =%s
AND group_name =%s
AND task_name =%s
AND first_url =%s
AND urls_xpath =%s
AND second_url =%s
AND is_example =%s
AND user_id =%s
AND timer =%s
AND is_full =%s
'''
args = [
create_time, _dict["group_name"], _dict['task_name'],
_dict["first_url"], _dict["urls_xpath"], _dict["second_url"],
_dict["is_example"], _dict['user_id'], _dict["timer"],
_dict["is_full"]
]
id = mysql.sql_read(sql, args)[0]['id']
create_table({'id': id})
return id
def create_table(_dict={}):
sql = '''
CREATE TABLE `{}` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`create_time` datetime NOT NULL,
`second_url` varchar(500) NOT NULL,
`first_url` varchar(500) NOT NULL,
PRIMARY KEY (`id`),
INDEX `ix-create_time` (`create_time`),
INDEX `ix-second_url` (`second_url`)
) ENGINE=InnoDB;
CREATE TABLE `{}` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`url_id` bigint(20) NOT NULL,
`create_time` datetime NOT NULL,
`data` json NOT NULL,
PRIMARY KEY (`id`),
INDEX `ix-create_time` (`create_time`),
INDEX `ix-url_id` (`url_id`)
) ENGINE=InnoDB;
'''.format(SPIDER_URLS.format(_dict['id']),
SPIDER_DATA.format(_dict['id']))
bool = mysql.sql_write(sql)
return bool
def delete_example(_dict={}):
# 删除一条模板信息
sql = 'DELETE FROM spider_setting WHERE id=%s'
args = [_dict['id']]
bool = mysql.sql_write(sql, args)
return bool
def update_session():
sql = 'update spider_user set is_read=0;truncate table django_session;'
bool = mysql.sql_write(sql) and '1' or '0'
return bool
def update_read(username):
sql = 'UPDATE spider_user SET is_read=1 WHERE username=%s'
args = [username]
bool = mysql.sql_write(sql, args)
return bool
def update_user(_dict={}):
sql = 'update spider_user set update_time=%s,password=%s where username=%s'
args = [datetime.now(), _dict['password'], _dict['username']]
bool = mysql.sql_write(sql, args)
return bool