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 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 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 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 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 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 add_connection(self, cnx=None): """Add a connection to the pool This method instantiates a Connection using the configuration passed when initializing the PyMySQLConnectionPool instance or using the set_config() method. If cnx is a Connection instance, it will be added to the queue. Raises PoolError when no configuration is set, when no more connection can be added (maximum reached) or when the connection can not be instantiated. """ with CONNECTION_POOL_LOCK: if not self._cnx_config: raise PoolError("Connection configuration not available") if self._cnx_queue.full(): raise PoolError("Failed adding connection; queue is full") if not cnx: cnx = Connection(**self._cnx_config) cnx._pool_config_version = self._config_version else: if not isinstance(cnx, Connection): raise PoolError( "Connection instance not subclass of MySQLConnection.") self._queue_connection(cnx)
def get_connection(self): """Get a connection from the pool This method returns an PooledPyMySQLConnection instance which has a reference to the pool that created it, and the next available MySQL connection. When the MySQL connection is not connect, a reconnect is attempted. Raises PoolError on errors. Returns a PooledPyMySQLConnection instance. """ with CONNECTION_POOL_LOCK: try: cnx = self._cnx_queue.get(block=False) except queue.Empty: raise PoolError("Failed getting connection; pool exhausted") if not is_connected( cnx) or self._config_version != cnx._pool_config_version: cnx = Connection(**self._cnx_config) try: cnx.ping(reconnect=True) except Exception as e: # Failed to reconnect, give connection back to pool self._queue_connection(cnx) raise cnx._pool_config_version = self._config_version return PooledPyMySQLConnection(self, cnx)
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 __mysql_connect(self): conn = Connection(host=self.__host, user=self.__user, passwd=self.__password, database=self.__database) cur = conn.cursor() return conn, cur
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 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 __init__(self, host, user, password, db): """ Constructeur :param host: L'adresse du serveur :param user: Le nom d'utilisateur :param password: Le mot de passe :param db: Le nom de la base de donnée """ #Attributs Connection.__init__(self, host=host, user=user, password=password, db=db)
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())
def context(conn: Connection = None) -> Generator[Cursor, None, None]: """ Run multiple MySQL commands in a single connection: >>> with context() as conn, cursor: ... create_account(cursor, owner) ... create_database(cursor, owner) """ conn = conn or connect() try: yield conn.cursor() finally: conn.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 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 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 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 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 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 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 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 __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 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 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 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))
def create_raw_conn(self): conn = Connection(**self._conn_params) conn._reconnect_time = self._reconnect_timestamp() return conn
def __init__(self, *largs, **kwargs): self.forwarded_auth_response = None Connection.__init__(self, *largs, **kwargs)