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)
