def sell_good(data: dict, conn: Connection):
"""
:param data:
python dictionary, containing keys as follows:
username: string
type: string
description: string
price: string
goodname: string
:param conn:
pymysql connection
:return:
sell_message:
response_code:
0 for success
1 for wrong data
"""
sell_message = dict()
if not check(['username', 'type', 'description', 'price', 'goodname'], data, 'sell good'):
sell_message['response_code'] = 1
return sell_message
dt = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
cursor = conn.cursor()
sql = F"insert into goods(sold, type, description, price, seller, uptime, goodname)"\
F"VALUE(0, '{data['type']}', '{data['description']}', {data['price']}, '{data['username']}'," \
F"'{dt}', '{data['goodname']}');"
cursor.execute(sql)
conn.commit()
cursor.close()
sell_message['response_code'] = 0
return sell_message
def cancel_good(data: dict, conn: Connection):
"""
:param data:
python dictionary, containing keys as follows:
goodsid: string
:param conn:
pymysql connection
:return:
cancel_message:
response_code:
0 for success
1 for wrong data
"""
cancel_message = dict()
if not check(['goodsid'], data, 'cancel good'):
cancel_message['response_code'] = 1
return cancel_message
cursor = conn.cursor()
sql = F"delete from goods "\
F"where goodsid = {data['goodsid']};"
cursor.execute(sql)
conn.commit()
cursor.close()
cancel_message['response_code'] = 0
return cancel_message
def good_comment(data: dict, conn: Connection):
"""
:param data:
python dictionary, containing keys as follows:
comment: string
goodsid: string
:param conn:
pymysql connection
:return:
comment_message:
response_code:
0 for success
1 for wrong data
"""
comment_message = dict()
if not check(['comment'], data, 'buyer comment'):
comment_message['response_code'] = 1
cursor = conn.cursor()
sql = F"update goods set comment = '{data['comment']}' "\
F"where goodsid = {data['goodsid']};"
cursor.execute(sql)
conn.commit()
cursor.close()
comment_message['response_code'] = 0
return comment_message
def change_setting(data: dict, conn: Connection):
"""
:param data:
python dictionary, containing keys as follows:
username: string
type: string
content: string
:param conn:
pymysql connection
:return:
setting_message:
response_code:
0 for success
1 for wrong data
"""
setting_message = dict()
if not check(['username', 'type', 'content'], data, 'change setting'):
setting_message['response_code'] = 1
return setting_message
cursor = conn.cursor()
sql = F"update users set {data['type']} = '{data['content']}' "\
F"where username = '******'username']}';"
cursor.execute(sql)
conn.commit()
cursor.close()
setting_message['response_code'] = 0
return setting_message
def remove_cart(data: dict, conn: Connection):
"""
:param data:
python dictionary, containing keys as follows:
username: string
goodsid: string
:param conn:
pymysql connection
:return:
add_message:
response_code:
0 for success
1 for wrong data
"""
remove_message = dict()
if not check(['username', 'goodsid'], data, 'remove cart'):
remove_message['response_code'] = 1
return remove_message
cursor = conn.cursor()
sql = F"delete from cart "\
F"where goodsid = {data['goodsid']} and username = '******'username']}';"
cursor.execute(sql)
conn.commit()
cursor.close()
remove_message['response_code'] = 0
return remove_message
def good_return(data: dict, conn: Connection):
"""
:param data:
python dictionary, containing keys as follows:
goodsid: string
:param conn:
pymysql connection
:return:
return_message:
response_code:
0 for success
1 for wrong data
"""
return_message = dict()
if not check(['goodsid'], data, 'buyer return'):
return_message['response_code'] = 1
return return_message
cursor = conn.cursor()
sql = F"update goods set sold = 0 "\
F"where goodsid = {data['goodsid']};"
cursor.execute(sql)
conn.commit()
sql = F"delete from orders "\
F"where goodsid = {data['goodsid']}"
cursor.execute(sql)
conn.commit()
cursor.close()
return_message['response_code'] = 0
return return_message
def enter_chatroom(data: Dict[str, str], conn: Connection):
"""
:param data:
python dictionary, containing keys as follows:
account: string (len < 20)
room_name: string (len < 20)
:param conn:
pymysql connection
:return:
message:
success: entering succeeded
failed: some other errors
"""
members = get_members_from_room_name(data, conn)
cursor = conn.cursor()
if data['account'] in members:
return 'duplicate'
dt = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
sql = F"insert into chatting(account, room_name, enter_time, if_active) " \
F"value ('{data['account']}', '{data['room_name']}', '{dt}', 1)"
cursor.execute(sql)
conn.commit()
cursor.close()
logging.debug(F'account : {data["account"]} entering {data["room_name"]}')
return 'success'
def delete_usr(data: dict, conn: Connection):
"""
:param data:
python dictionary, containing keys as follows:
username:string
:param conn:
pymysql connection
:return:
dl_message:
response_code:
0 for success
1 for wrong data
"""
dl_message = dict()
if not check(['username'], data, "delete usr"):
dl_message['response_code'] = 1
return dl_message
cursor = conn.cursor()
sql = F"delete from users "\
F"where username = '******'username']}';"
cursor.execute(sql)
conn.commit()
cursor.close()
dl_message['response_code'] = 0
return dl_message
def job1(pages):
db = Connection(host="localhost",
user="******",
password="******",
port=3306,
database='world',
charset='gbk')
cur = db.cursor()
for i in range(pages[0], pages[1]):
url1 = "http://news.xmu.edu.cn/1552/list" + str(i) + ".htm"
html = crawl(url1)
urls = parse(html)
for url in urls:
html = urlopen(url).read().decode('utf-8')
soup = BeautifulSoup(html, 'html.parser')
title = soup.find('span', {"class": 'Article_Title'})
title = title.get_text()
readnum = soup.find('span', {"class": 'WP_VisitCount'},
{"style": 'display'})
readnum = readnum.get_text()
date = soup.find('span', {"class": 'Article_PublishDate'})
date = date.get_text()
print("url=" + url)
insert_xmunews = 'insert into xmunews3(title,date1,url,views) values(%s,%s,%s,%s);'
try:
cur.execute(insert_xmunews, [title, date, url, readnum])
except Exception as e:
print("！！！！！！！！！！异常是%s" % e)
print("题目：" + title)
print("浏览次数：" + readnum)
print("发布日期：" + date)
db.commit()
cur.close()
db.close()
def exit_chatroom(data: Dict[str, str], conn: Connection):
"""
:param data:
python dictionary, containing keys as follows:
account: string (len < 20)
room_name: string (len < 20)
:param conn:
pymysql connection
:return:
message:
success: exiting succeeded
failed: some other errors
"""
cursor = conn.cursor()
sql = F"update chatting set if_active=0 where room_name = '{data['room_name']}' and account = '{data['account']}'"
cursor.execute(sql)
conn.commit()
cursor.close()
logging.debug(F'account : {data["account"]} exiting {data["room_name"]}')
return 'success'
async def chat_button(client: Client, chat: dict,
connection: Connection) -> InlineKeyboardButton:
"""
A coroutine that creates an InlineKeyboardButton form tha data of a chat
:param client: The application
:param chat: The chat's data
:return: InlineKeyboardButton
"""
if chat["username"] is not None:
invite_link = "https://t.me/{}".format(chat["username"])
elif chat["invite_link"] is not None:
invite_link = chat["invite_link"]
else:
# Generating the new invite_link
invite_link = await client.export_chat_invite_link(chat["id"])
# Saving the new invite_link
with connection.cursor() as cursor:
cursor.execute(
"UPDATE `Chats` SET `invite_link`=%(invite_link)s WHERE `id`=%(id)s;",
{
"id": chat["id"],
"invite_link": invite_link
})
connection.commit()
return InlineKeyboardButton(text=chat["title"], url=invite_link)
def query(
conn: Connection,
sql: str,
fetch_mode: FetchMode = FetchMode.ALL,
size: int = 1,
args: Optional[Union[dict, tuple, list]] = None,
):
print(sql.replace("\n", " ").replace(" ", " "))
print(args)
conn.ping(True)
# Throws QueryKeyError
with conn.cursor() as cursor:
try:
cursor.execute(sql, args)
except KeyError as err:
raise QueryKeyError(key=err.args[0])
except ProgrammingError as err:
print("A Programming error occurs =========")
print(sql)
print(err.args[0])
print(err.args[1])
print("=================")
except InternalError as err:
print(sql)
print(err.args[0])
if fetch_mode is FetchMode.ONE:
return cursor.fetchone()
elif fetch_mode is FetchMode.MANY:
return cursor.fetchmany(size)
elif fetch_mode is FetchMode.ALL:
return cursor.fetchall()
conn.commit()
def change_good(data: dict, conn: Connection):
"""
:param data:
python dictionary, containing keys as follows:
goodsid: string
type: string
content: string
:param conn:
pymysql connection
:return:
change_message:
response_code:
0 for success
1 for wrong data
"""
change_message = dict()
if not check(['goodsid', 'type', 'content'], data, "change good"):
change_message['response_code'] = 1
cursor = conn.cursor()
if data['type'] == "price":
sql = F"update goods set price = {data['content']} "\
F"where goodsid = {data['goodsid']};"
else:
sql = F"update goods set {data['type']} = '{data['content']}' "\
F"where goodsid = {data['goodsid']};"
cursor.execute(sql)
conn.commit()
cursor.close()
change_message['response_code'] = 0
return change_message
def insert_query(conn: Connection, category_id, text):
try:
category_id = str(category_id)
text = str(text)
cur = conn.cursor()
cur.execute(
'insert into `faq_management_query`(`category_id`, `text`) values (%s, %s)',
args=(category_id, text))
cur.execute('SELECT LAST_INSERT_ID() from faq_management_query')
query_id = int(cur.fetchone()[0])
conn.commit()
cur.close()
except Exception:
traceback.print_exc()
return STATE_ERROR_NUMBER
return query_id
def update_category(conn: Connection, category_id, answer):
try:
category_id = str(category_id)
answer = str(answer)
cur = conn.cursor()
update_num = cur.execute(
'update faq_management_category set answer=%s where category_id=%s',
args=(answer, category_id))
if update_num == 0:
return STATE_ERROR_NUMBER
conn.commit()
cur.close()
except Exception:
traceback.print_exc()
return STATE_ERROR_NUMBER
return 0
def insert_category(conn: Connection, name, answer):
try:
name = str(name)
answer = str(answer)
cur = conn.cursor()
cur.execute(
'insert into `faq_management_category`(`name`, `answer`) values (%s, %s)',
args=(name, answer))
cur.execute('SELECT LAST_INSERT_ID() from faq_management_category')
category_id = int(cur.fetchone()[0])
conn.commit()
cur.close()
except Exception:
traceback.print_exc()
return STATE_ERROR_NUMBER
return category_id
def delete_query(conn: Connection, query_id):
try:
query_id = str(query_id)
cur = conn.cursor()
record_num = cur.execute(
'select category_id from faq_management_query where query_id=%s',
args=query_id)
if record_num == 0:
return STATE_ERROR_NUMBER
cur.execute('delete from `faq_management_query` where query_id=%s',
args=query_id)
conn.commit()
cur.close()
except Exception:
traceback.print_exc()
return STATE_ERROR_NUMBER
return 0
def update_model_record(conn: Connection, uid, state):
try:
uid = str(uid)
state = str(state)
cur = conn.cursor()
update_num = cur.execute(
'update faq_management_model set state=%s where record_id=%s',
args=(state, uid))
if update_num == 0:
return STATE_ERROR_NUMBER
conn.commit()
cur.close()
except Exception:
traceback.print_exc()
print(uid, state)
return STATE_ERROR_NUMBER
return 0
def add_cart(data: dict, conn: Connection):
"""
:param data:
python dictionary, containing keys as follows:
username: string
goodsid: string
:param conn:
pymysql connection
:return:
add_message:
response_code:
0 for success
1 for wrong data
good_list
"""
add_message = dict()
if not check(['goodsid', 'username'], data, "add_cart"):
add_message['response_code'] = 2
return add_message
cursor = conn.cursor()
sql = F"select goodsid from cart "\
F"where username = '******'username']}';"
cursor.execute(sql)
rows = cursor.fetchall()
goodsids = [row[0] for row in rows]
for id in goodsids:
if int(data['goodsid']) == id:
cursor.close()
add_message['response_code'] = 1
return add_message
sql = F"insert into cart(goodsid, username) "\
F"VALUE({data['goodsid']}, '{data['username']}');"
cursor.execute(sql)
conn.commit()
cursor.close()
add_message['response_code'] = 0
return add_message
def req_manage(data: dict, conn: Connection):
"""
:param data:
python dictionary, containing keys as follows:
idrequests: string
agree: string
:param conn:
pymysql connection
:return:
manage_message:
response_code:
0 for success
1 for wrong data
"""
manage_message = dict()
if not check(['idrequests', 'agree'], data, "req manage"):
manage_message['response_code'] = 1
return manage_message
cursor = conn.cursor()
if int(data['agree']) == 1:
sql = F"select username, password, nickname, phone, gender, age from requests "\
F"where idrequests = {data['idrequests']};"
cursor.execute(sql)
rows = cursor.fetchall()
row = rows[0]
sql = F"insert into users(username, password, authority, nickname, phone, gender, age) "\
F"VALUE('{row[0]}', '{row[1]}', 1, '{row[2]}', '{row[3]}', '{row[4]}', {row[5]});"
cursor.execute(sql)
conn.commit()
sql = F"delete from requests where idrequests = {data['idrequests']};"
cursor.execute(sql)
conn.commit()
cursor.close()
manage_message['response_code'] = 0
return manage_message
def register_user(data: Dict[str, str], conn: Connection):
"""
:param data:
python dictionary, containing keys as follows:
account: string (len < 20)
password: string (len < 20)
:param conn:
pymysql connection
:return:
message:
success: registering succeeded
duplicate: account name already exists
failed: some other errors (probably wrong keys)
"""
if not check(['account', 'password'], data, 'register'):
return 'failed'
cursor = conn.cursor()
### checking duplicates in a very stupid method...
sql = 'select account from user;'
cursor.execute(sql)
rows = cursor.fetchall()
rows = [row[0] for row in rows]
for row in rows:
if data['account'] == row:
logging.debug(F'user account {data["account"]} already exists')
return 'duplicate'
dt = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
sql = F"insert into user (account, password, register_date)" \
F" VALUE ('{data['account']}', '{data['password']}', '{dt}');"
cursor.execute(sql)
conn.commit()
cursor.close()
logging.debug(F'register for account {data["account"]} succeeded')
return 'success'
def insert_model_record_new(conn: Connection, name, domain, state, categories,
comment):
try:
name = str(name)
domain = str(domain)
state = str(state)
category_num = len(categories)
categories = ",".join([str(i) for i in categories])
comment = str(comment)
cur = conn.cursor()
cur.execute(
'insert into `faq_management_model`(`name`, `domain`, `state`, `categories`, `category_num`, `comment`) values (%s, %s, %s, %s, %s, %s)',
args=(name, domain, state, categories, category_num, comment))
cur.execute('SELECT LAST_INSERT_ID() from faq_management_model')
record_id = int(cur.fetchone()[0])
conn.commit()
cur.close()
except Exception:
traceback.print_exc()
return STATE_ERROR_NUMBER
return record_id
def change_pwd(data, conn: Connection):
cursor = conn.cursor()
sql = 'select account, password from user'
cursor.execute(sql)
tmp = cursor.fetchall()
if data['account'] not in [a[0] for a in tmp]:
return 'Invalid UserName'
if tmp[[a[0]
for a in tmp].index(data['account'])][1] != data['old_password']:
return 'Wrong Password'
sql = 'update user set password = "******" where account = "{}"'.format(
data['new_password'], data['account'])
cursor.execute(sql)
conn.commit()
cursor.close()
return 'success'
def send_message(data: Dict[str, str], conn: Connection):
"""
:param data:
:param conn:
:return:
"""
cursor = conn.cursor()
dt = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
sql = F"insert into messages(account, room_name, message, send_time) " \
F"value('{data['account']}', '{data['room_name']}', '{data['message']}', '{dt}')"
cursor.execute(sql)
conn.commit()
cursor.close()
logging.debug(
F'account : {data["account"]} sending message in room : {data["room_name"]}'
)
return 'success'
def create_chatroom(data: Dict[str, str], conn: Connection):
"""
:param data:
python dictionary, containing keys as follows:
room_name: string (len < 20)
:param conn:
pymysql connection
:return:
message:
success: creation succeeded
duplicate: room name already exists
failed: some other errors (probably wrong keys)
"""
if not check(['room_name'], data, 'register'):
return 'failed'
cursor = conn.cursor()
### checking duplicates in a very stupid method...
sql = 'select room_name from chat_room;'
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
if data['room_name'] == row[0]:
logging.debug(F'room {data["room_name"]} already exists')
return 'duplicate'
dt = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
sql = F"insert into chat_room (room_name, create_time)" \
F" VALUE ('{data['room_name']}', '{dt}');"
cursor.execute(sql)
conn.commit()
cursor.close()
logging.debug(F'creating room: {data["room_name"]} succeeded')
return 'success'
def buy_good(data: dict, conn: Connection):
"""
:param data:
python dictionary, containing keys as follows:
username: string
goodsid: string
:param conn:
pymysql connection
:return:
add_message:
response_code:
0 for success
1 for wrong data
"""
buy_message = dict()
if not check(['username', 'goodsid'], data, "find_cart"):
buy_message['response_code'] = 1
return buy_message
ntime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
cursor = conn.cursor()
sql = F"insert into orders(goodsid, buyer, time) "\
F"VALUE({data['goodsid']}, '{data['username']}', '{ntime}');"
cursor.execute(sql)
conn.commit()
sql = F"update goods set sold = 1 "\
F"where goodsid = {data['goodsid']};"
cursor.execute(sql)
conn.commit()
cursor.close()
buy_message['response_code'] = 0
return buy_message
def delete_model_record(conn: Connection, record_id):
try:
record_id = str(record_id)
cur = conn.cursor()
record_num = cur.execute(
'select state from faq_management_model where record_id=%s',
args=record_id)
if record_num == 0:
return STATE_ERROR_NUMBER
state = int(cur.fetchone()[0])
print("state ", state)
if state == STATE_READY_NUMBER:
cur.execute(
'delete from `faq_management_model` where record_id=%s',
args=record_id)
conn.commit()
cur.close()
print(record_id)
except Exception:
traceback.print_exc()
print(record_id)
return STATE_ERROR_NUMBER
return state
def insert_into(conn: Connection, table_name: str,
**kwargs: Any) -> Optional[int]:
conn.ping(True)
keys, values, make_str = get_key_val_lists(**kwargs)
result = None
try:
with conn.cursor() as cursor:
cursor.execute(
INSERT_INTO.format(table_name, ",".join(keys), make_str),
(*values, ))
result = cursor.lastrowid
except IntegrityError as err:
matches = DUPLICATE_KEY_ERROR_PATTERN.match(err.args[1])
if err.args[0] == 1062 and matches is not None:
raise QueryDuplicateError(matches.group(3), matches.group(1))
else:
raise QueryError(*err.args)
except ProgrammingError as err:
raise QueryError(*err.args)
except OperationalError as err:
raise QueryError(*err.args)
conn.commit()
if result is not None:
return result
class Ip89Spider:
def __init__(self, size=5, test_url="http://www.baidu.com"):
self.test_url = test_url
self.size = size
# 数据库相关
self.db = Connection(host='127.0.0.1',
port=3306,
user='******',
password='******',
database='proxy_ip')
self.cour = self.db.cursor()
# 正则预编译
self.rer = re.compile(r"\n|\t")
# 队列对象
self.proxy = Queue()
self.ip = Queue()
# 请求头
self.headers = {"User-Agent": UserAgent().chrome}
def build_url(self):
url_list = list() # 创建一个保存URL的列表
base_url = "http://www.89ip.cn/index_{}.html" # 基础url
# 构建新的URL
for page in (1, int(self.size) + 1):
new_url = base_url.format(page)
url_list.append(new_url)
return url_list
def parse(self, url_list):
# 发送请求获取响应
for url in url_list:
response = requests.get(url, headers=self.headers)
content = response.content.decode()
self.parse_content(content)
def parse_content(self, content):
soup = BeautifulSoup(content, 'lxml')
tr_list = soup.select("div[class='layui-form'] tbody tr") # 分组
for tr in tr_list:
item = {}
item['ip'] = tr.select("td")[0].get_text()
item['ip'] = self.rer.sub('', item['ip'])
item['port'] = tr.select("td")[1].get_text()
item['port'] = self.rer.sub('', item['port'])
print(item)
self.proxy.put(item)
def test_ip(self):
while True:
item = self.proxy.get() # 获取一个待检测ip对象
test_ip = "http://" + item['ip'] + ":" + item['port']
try:
# 检测
proxies = {"http": test_ip}
response = requests.get(self.test_url,
headers=self.headers,
proxies=proxies,
timeout=5)
if response.status_code == 200:
print(test_ip, "代理可用")
self.save_ip(test_ip)
self.proxy.task_done()
except Exception as e:
print(e)
print(test_ip, "不可用")
self.proxy.task_done()
def save_ip(self, test_ip):
SQL = "INSERT INTO t_ip (ip) VALUES (%s)"
print(SQL)
self.cour.execute(SQL, args=(test_ip, ))
self.db.commit()
print("保存成功")
def run(self):
url_list = self.build_url() # 构建url
self.parse(url_list) # 解析url
# 启动批量检测线程，提取可用ip 并保存
t_list = []
for i in range(5):
print("启动检测线程", i)
t = Thread(target=self.test_ip)
t_list.append(t)
for i in t_list:
i.setDaemon(True)
i.start()
self.proxy.join()
def register_user(data: dict, conn: Connection):
"""
:param data:
python dictionary, containing keys as follows:
username: string (len < 20)
password: string (len < 20)
nickname: string
gender: int
phone: string
indent: int
:param conn:
pymysql connection
:return:
reg_message:
response_code:
0 for success
1 for username duplicated
2 for nickname duplicated
3 for wrong data
"""
reg_message = dict()
if not check(['username', 'password', 'nickname', 'gender', 'phone', 'ident', 'age'], data, 'register'):
reg_message['response_code'] = 3
return reg_message
cursor = conn.cursor()
sql = 'select username from users'
cursor.execute(sql)
rows = cursor.fetchall()
usernames = [row[0] for row in rows]
sql = 'select nickname from users'
cursor.execute(sql)
rows = cursor.fetchall()
nicknames = [row[0] for row in rows]
for row in usernames:
if data['username'] == row:
cursor.close()
logging.debug(F'username {data["username"]} already exists')
reg_message['response_code'] = 1
return reg_message
for row in nicknames:
if data['nickname'] == row:
cursor.close()
logging.debug(F'nickname {data["nickname"]} already exists')
reg_message['response_code'] = 2
return reg_message
if data['ident'] == 0:
sql = F"insert into users (username, password, nickname, gender, phone, authority, age)" \
F" VALUE ('{data['username']}', '{data['password']}', '{data['nickname']}', '{data['gender']}', "\
F"'{data['phone']}', '{data['ident']}', '{data['age']}');"
cursor.execute(sql)
conn.commit()
cursor.close()
elif data['ident'] == 1:
sql = F"insert into requests (username, password, nickname, gender, phone, age)" \
F" VALUE ('{data['username']}', '{data['password']}', '{data['nickname']}', '{data['gender']}', " \
F"'{data['phone']}', '{data['age']}');"
cursor.execute(sql)
conn.commit()
cursor.close()
logging.debug(F'register for account {data["username"]} succeeded')
reg_message['response_code'] = 0
return reg_message