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