def put_connection(self, conn): if not conn._pool: conn._pool = self conn.cursor().close() try: self._pool.put_nowait(conn) logging.debug("Put connection back to pool(%s)", self.name) except queue.Full: logging.warning( "Put connection to pool(%s) error, pool is full, size:%d", self.name, self.size())
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 get_orders(conn: Connection): """ :param conn: pymysql connection :return: od_message: response_code: 0 for success 1 for wrong data od_list:seller, buyer, goodname, price, time """ od_message = dict() cursor = conn.cursor() sql = F"select G.seller, O.buyer, G.goodname, G.price, O.time "\ F"from orders as O, goods as G "\ F"where O.goodsid = G.goodsid " \ F"ORDER BY O.time DESC;" cursor.execute(sql) rows = cursor.fetchall() cursor.close() od_message['response_code'] = 0 od_message['od_list'] = [row for row in rows] return od_message
def get_userlist(): # connc = Connection(host="39.100.228.129", port=4000, database="stay", user="******", # password="******", charset="utf8") connc = Connection(host="39.100.228.129", port=4000, database="stay", user="******", password="******", charset="utf8") cursor = connc.cursor() # 1100 q_sql = "select * from stay.app_user limit 1100,100" cursor.execute(q_sql) rowcount = cursor.rowcount print(rowcount) # 获取所有查询内容 rows = cursor.fetchall() # print(rows) for row in rows: # print("-" * 20) # print("id:", row[0]) userids.append(row[0]) cursor.close() connc.close()
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 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 manage_usr(conn: Connection): """ :param conn: pymysql connection :return: mn_message: response_code: 0 for success 1 for wrong data usr_list: nickname, phone, gender, age, username, identity """ mn_message = dict() cursor = conn.cursor() sql = F"select nickname, phone, gender, age, username, authority from users "\ F"ORDER BY authority DESC;" cursor.execute(sql) rows = cursor.fetchall() usr_list = [] cursor.close() for row in rows: usr_list.append([row[0], row[1], row[2], row[3], row[4], row[5]]) mn_message['response_code'] = 0 mn_message['usr_list'] = usr_list return mn_message
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'
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 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 find_info(data: dict, conn: Connection): """ :param data: python dictionary, containing keys as follows: goodsid: string :param conn: pymysql connection :return: info_message: response_code: 0 for success 1 for wrong data good_info: goodname and description """ info_message = dict() if not check(['goodsid'], data, "good_info"): info_message['response_code'] = 1 return info_message info_message['response_code'] = 0 cursor = conn.cursor() sql = F"select goodname, description from goods "\ F"where goodsid = {data['goodsid']};" cursor.execute(sql) rows = cursor.fetchall() cursor.close() info_message['good_info'] = [rows[0][0], rows[0][1]] return info_message
def __mysql_connect(self): conn = Connection(host=self.__host, user=self.__user, passwd=self.__password, database=self.__database) cur = conn.cursor() return conn, cur
def update_customer(conn: Connection, full_name: str, emails: List[str]): with conn.cursor() as cursor: cursor.execute("select id from channel_customer where company = %s", (full_name, )) data = cursor.fetchone() if not data: # print(full_name, 'not found in channel_customer') return cid = data['id'] cursor.execute( "select email from channel_contact where customer_id = %s for update", (cid, )) emails_in_db = [x['email'] for x in cursor.fetchall()] emails_to_insert = set(emails) - set(emails_in_db) insert_count = 0 for email in emails_to_insert: if not is_valid_email(email): continue row_count = cursor.execute( "insert into channel_contact(customer_id, email, creater_account_id, creater, real_name, creater_source) values(%s, %s, 0, 'BOSS自建', '-', 1)", (cid, email)) if row_count != 1: print('cannot insert cid(', cid, ') email(', email, ')', file=sys.stderr) else: insert_count += 1 return insert_count
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 tuhao_buyer(conn: Connection): """ :param conn: pymysql connection :return: th_message: response_code: 0 for success good_list:username, gender, age, total """ th_message = dict() cursor = conn.cursor() sql = F"select b, n, g, a, p from "\ F"(select O.buyer as b, U.nickname as n, U.gender as g, U.age as a, sum(G.price) as p "\ F"from orders as O, users as U, goods as G "\ F"where O.goodsid = G.goodsid and O.buyer = U.username "\ F"GROUP BY b, g, a) as info "\ F"ORDER BY p DESC, b DESC;" cursor.execute(sql) rows = cursor.fetchall() cursor.close() good_list = [] for row in rows: good_list.append([row[0], row[1], row[2], row[3],int(row[4])]) th_message['good_list'] = good_list th_message['response_code'] = 0 return th_message
def grant_privileges(client: Connection, database: str, privileges: List[str], user: str, host: str): with client.cursor() as c: privs = ', '.join(p.upper() for p in privileges) c.execute('GRANT %s ON %s.* TO \'%s\'@\'%s\'' % (privs, database, user, host)) logging.info('granted %s on %s to %s@%s', privs, database, user, host)
def update_password(client: Connection, username: str, host: str, password: str): with client.cursor() as c: # NOTE: this syntax is deprecated but is the only syntax that works # consistently for both mysql 5.6 and 5.7 c.execute('SET PASSWORD FOR \'%s\'@\'%s\' = ' 'PASSWORD(\'%s\');' % (escape_string(username), escape_string(host), escape_string(password)))
def get_category_answer(conn: Connection, category_id): cur = conn.cursor() query_num = cur.execute( 'select answer from faq_management_category where category_id=%s', args=(category_id)) if query_num == 0: return "" answer = cur.fetchone()[0] cur.close() return answer
def get_category_queries(conn: Connection, category_id): cur = conn.cursor() query_num = cur.execute( 'select text from faq_management_query where category_id=%s', args=(category_id)) if query_num == 0: return [], 0 texts = [r[0] for r in cur.fetchall()] cur.close() return texts, query_num
def login_user(data: Dict[str, str], conn: Connection): """ :param data: python dictionary, containing keys as follows: username: string (len < 20) password: string (len < 20) :param conn: pymysql connection :return: log_message: response_code: 0 for success 1 for not found 2 for wrong password 3 for wrong data ident: identity(authority) """ log_message = dict() if not check(['username', 'password'], data, 'login'): log_message['response_code'] = 3 return log_message cursor = conn.cursor() sql = 'select username from users;' cursor.execute(sql) rows = cursor.fetchall() rows = [row[0] for row in rows] if data['username'] not in rows: cursor.close() logging.debug(F'user {data["username"]} not found') log_message['response_code'] = 1 return log_message sql = F"select password from users where username = '******'username']}';" cursor.execute(sql) real_pwd = cursor.fetchall()[0][0] if real_pwd != data['password']: cursor.close() logging.debug(F'login for account {data["username"]} wrong password') log_message['response_code'] = 2 return log_message else: logging.debug(F'login for account {data["username"]} succeeded') log_message['response_code'] = 0 sql = F"select authority from users where username = '******'username']}';" cursor.execute(sql) log_message['ident'] = cursor.fetchall()[0][0] cursor.close() return log_message
def grant_privileges(client: Connection, database: str, privileges: List[str], user: str, host: str): with client.cursor() as c: privs = ', '.join(p.upper() for p in privileges) c.execute('GRANT %s ON `%s`.* TO \'%s\'@\'%s\'' % ( privs, database, user, host )) logger.info('granted %s on %s to %s@%s', privs, database, user, host)
def update_password(client: Connection, username: str, host: str, password: str): with client.cursor() as c: # NOTE: this syntax is deprecated but is the only syntax that works # consistently for both mysql 5.6 and 5.7 c.execute('SET PASSWORD FOR \'%s\'@\'%s\' = ' 'PASSWORD(\'%s\');' % ( escape_string(username), escape_string(host), escape_string(password) ))
def get_members_from_room_name(data: Dict[str, str], conn: Connection): cursor = conn.cursor() sql = F"select account from chatting where room_name = '{data['room_name']}' and if_active = 1" cursor.execute(sql) users = cursor.fetchall() cursor.close() return [a[0] for a in users]
def hosts_for_user(client: Connection, name: str) -> List[str]: """ :param client: :param name: the user name :return: true if user with given name exists, false if not """ with client.cursor() as c: c.execute('select `Host` from mysql.user where `User` = %s', (name, )) found_hosts = map(lambda r: r['Host'], c.fetchall()) return list(found_hosts)
def scan_one(self, conn: Connection, table_name: str) -> List[Tuple[str, str, str]]: sql = """ show full fields from %s; """ % (table_name) with conn.cursor() as cursor: cursor.execute(sql) items = cursor.fetchall() return [(item['Field'], item['Comment'], item['Type']) for item in items]
def get_room_by_name(s, conn: Connection): cursor = conn.cursor() sql = 'select chatting.room_name, max(send_time) ' \ 'from chatting join messages using(room_name) ' \ 'where chatting.account = "{}" and chatting.if_active = 1 ' \ 'group by chatting.room_name'.format(s) cursor.execute(sql) res = cursor.fetchall() cursor.close() return res
def hosts_for_user(client: Connection, name: str) -> List[str]: """ :param client: :param name: the user name :return: true if user with given name exists, false if not """ with client.cursor() as c: c.execute('select `Host` from mysql.user where `User` = %s', (name,)) found_hosts = map(lambda r: r['Host'], c.fetchall()) return list(found_hosts)
def create_database(client: Connection, name: str, charset: str=None, collation: str=None): if charset: charset_part = ' DEFAULT CHARACTER SET %s' % charset else: charset_part = '' if collation: collation_part = ' DEFAULT COLLATE %s' % collation else: collation_part = '' with client.cursor() as c: c.execute('CREATE DATABASE `%s`%s%s' % ( escape_string(name), charset_part, collation_part ))
def get_current_databases(client: Connection) -> Iterable[str]: with client.cursor() as c: c.execute('SHOW DATABASES;') return map(lambda r: r['Database'], c.fetchall())
def flush_privileges(client: Connection): with client.cursor() as c: logger.debug('flushing privileges...') c.execute('FLUSH PRIVILEGES;')
def create_user(client: Connection, username: str, host: str, password: str): with client.cursor() as c: c.execute('CREATE USER %s@%s IDENTIFIED BY %s', (username, host, password))