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))
