def getSteamItem(id, conn: pymysql.connections.Connection, lock):
try:
s = steamItem(id)
s.get()
if not s.exists:
return
cur = conn.cursor()
lock.acquire()
try:
cur.execute('USE `steam`;')
s._insert(cur)
conn.commit()
except:
pass
finally:
lock.release()
time.sleep(random.random())
return
except Exception as ex:
print('---' * 20)
print('%s failed.' % s.id)
print(ex)
traceback.print_exc()
try:
s._print()
except:
pass
print('---' * 20)
return
def _close_connection(self,
connection: pymysql.connections.Connection) -> None:
"""
Close the given connection to a database. (Private member method)
Parameters
----------
connection : pymysql.connections.Connection
The pymysql connection object to the database.
"""
connection.close()
def __is_mysql_connect_valid(connect: pymysql.connections.Connection) -> bool:
"""
method:
detect whether the supplied mysql connection alive.
:param
connect: An instance of pymysql.connections.Connection
:return:
"""
try:
connect.ping(False)
except pymysql.err.Error:
return False
return True
def upload_song(db: pymysql.connections.Connection, fields, values, sid):
ret = False
cursor = db.cursor()
sql = 'insert into t_songs({}) values({}) ON DUPLICATE KEY UPDATE song_id={};'
sql = sql.format(fields, values, sid)
try:
cursor.execute(sql)
db.commit()
ret = True
except:
StdError.error("上传模块歌曲上传出现未知错误\tsql=" + sql)
db.rollback()
finally:
cursor.close()
return ret
def get_data(conn: pymysql.connections.Connection) -> list:
with conn.cursor() as cursor:
query = """
SELECT * FROM test.customer
"""
cursor.execute(query)
return cursor.fetchall()
def get_registration(db_conn: pymysql.connections.Connection,
username: str) -> U2FRegistration:
"""Query the mysql database for the current registration
Arguments:
db_conn (pymysql.connections.Connection): a connection object to the db
username (str): The username to query
Raises:
UnknownUserError: If no user is found
TooManyUsersError: If more then one user is found
Returns:
U2FRegistration: A object representing the users u2f registration
"""
sql = 'SELECT * FROM U2FDevice_Registration WHERE `username`=%s'
with db_conn.cursor() as cursor:
cursor.execute(sql, (username, ))
if cursor.rowcount > 1:
raise UnknownUserError(
f'Found multiple registrations matching: {username}')
if cursor.rowcount < 1:
raise TooManyUsersError(
f'Found no registrations matching: {username}')
registration = U2FRegistration(**cursor.fetchone())
LOG.debug('Found reg: %s', registration)
return registration
def raw_query(conn: pymysql.connections.Connection, query: str):
"""
Query Dangerously & Close DB Connection.
:param conn: pymysql Connection Object
:param query: Query Sentence
:return: is_success, result
"""
try:
c = conn.cursor()
c.execute(query)
return True, c.fetchall()
except Exception as e:
print("ERROROROROROROR!!!!!:", e)
return False, e
finally:
conn.close()
def get_all_music(db: pymysql.connections.Connection, start, limit):
cursor = db.cursor()
sql = 'select song_id,song_name,song_artist from t_songs limit {},{};'
cursor.execute(sql.format(start, limit))
data = cursor.fetchall()
cursor.close()
return list(data)
def insert_data(conn: pymysql.connections.Connection):
"""
columns in customer table
- cid : not null
- last_cid
- mid
- customer_img
- enter_time
- leave_time
:param conn:
:return:
"""
with conn.cursor() as cursor:
# a insert example
cid = 'c-test'
customer_img = 'abs/path/to/img'
enter_time = datetime.datetime.now()
time.sleep(2)
leave_time = datetime.datetime.now()
query = f"""
INSERT INTO test.customer
(cid, customer_img, enter_time, leave_time)
VALUES
('{cid}', '{customer_img}', '{enter_time}', '{leave_time}')
"""
cursor.execute(query)
def get_song_by_id(db: pymysql.connections.Connection, sid):
cursor = db.cursor()
sql = 'select song_id,song_name,song_artist from t_songs where song_id={} limit 1;'
cursor.execute(sql.format(sid))
data = cursor.fetchone()
cursor.close()
return data
def upload_list(db: pymysql.connections.Connection, fields, values, lid):
ret = False
cursor = db.cursor()
sql = 'insert into t_lists ({}) values ({}) ON DUPLICATE KEY UPDATE list_id={};'
sql = sql.format(fields, values, lid)
sql = sql.replace("\n", r'\n')
sql = sql.replace("\t", r'\t')
try:
cursor.execute(sql)
db.commit()
ret = True
except:
StdError.error("上传模块歌单上传出现未知错误\tsql=" + sql)
db.rollback()
finally:
cursor.close()
return ret
def get_likes(db: pymysql.connections.Connection):
cursor = db.cursor()
cursor.execute('select list_tags from t_lists;')
likes = set()
datas = cursor.fetchall()
for data in datas:
likes.update((json.loads(data[0].replace("'", '"'))))
cursor.close()
return likes
def delete_registration(db_conn: pymysql.connections.Connection,
registration: U2FRegistration):
"""Delete a u2f registration
Arguments:
db_conn (pymysql.connections.Connection): a connection object to the db
username (str): The username to query
registration (U2FRegistration): A object representing the users u2f registration
"""
sql = 'DELETE FROM U2FDevice_Registration WHERE `username`=%s'
LOG.debug('Deleting: %s', registration.username.decode())
with db_conn.cursor() as cursor:
# We can safely just use the username here as we make sure
# there is only one match earlier in the code
cursor.execute(sql, (registration.username.decode(), ))
LOG.info('Registration Deleted: %s', registration.username.decode())
db_conn.commit()
def get_tags_from_list(db: pymysql.connections.Connection, sid):
cursor = db.cursor()
sql = 'select list_tags from t_lists where list_songs like \'%{}%\';'.format(sid)
songTags = ''
if cursor.execute(sql) != 0:
data = cursor.fetchall()
for i in data:
songTags += ','.join(json.loads(i[0].replace('\'', '"')))
return songTags
def get_song_info(db: pymysql.connections.Connection, song):
cursor = db.cursor()
if isinstance(song, int):
sql = 'select song_name,song_artist,song_album,song_lyric,song_albumPicture,song_tags,song_link from t_songs where song_id={} limit 1;'.format(song)
else:
sql = 'select song_name,song_artist,song_album,song_lyric,song_albumPicture,song_tags,song_link from t_songs where song_name=\'{}\' limit 1;'.format(song)
cursor.execute(sql)
data = cursor.fetchone()
cursor.close()
return data
def get_song_comment(db: pymysql.connections.Connection, song):
cursor = db.cursor()
if isinstance(song, int):
sql = 'select song_comment from t_songs where song_id={} limit 1;'.format(song)
else:
sql = "select song_tags from t_songs where song_name='{}' limit 1;".format(song)
cursor.execute(sql)
comment = cursor.fetchone()[0]
cursor.close()
return comment
def user_forget_passwd(db: pymysql.connections.Connection, userName, userEmail, userNewPwd):
cursor = db.cursor()
ret = False
try:
sql = 'select user_id from t_users where user_name=\'{}\' and user_email=\'{}\';'.format(userName, userEmail)
if cursor.execute(sql) == 0:
raise UserManagerError("用户修改密码错误，没有找到符合的用户")
else:
uid = cursor.fetchone()[0]
sql = 'update t_users set user_pwd=\'{}\' where user_id={};'.format(userNewPwd, uid)
cursor.execute(sql)
db.commit()
ret = True
except UserManagerError as e:
StdError.error(e.message + "\tuser_name=" + userName + "\tuser_pwd=" + userNewPwd + "\tuser_email=" + userEmail)
except:
StdError.error("用户修改密码出现未知错误" + "\tuser_name=" + userName + "\tuser_pwd=" + userNewPwd + "\tuser_email=" + userEmail)
finally:
cursor.close()
return ret
def fetch_image(image_id: str, cnx: pymysql.connections.Connection):
image_id = image_id if image_id != "latest" else None
# Get DictCursor
with cnx.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
cursor.execute(
"SELECT id, path, timestamp, gender_timestamp, age_timestamp, emotion_timestamp, face_recognition_timestamp "
"FROM image "
"WHERE id=COALESCE(%(image_id)s,id) "
"ORDER BY timestamp DESC "
"LIMIT 1;", {'image_id': image_id})
image_row = cursor.fetchone()
return image_row
def get_songs_by_tags(db: pymysql.connections.Connection, tags: list, limit):
cursor = db.cursor()
sql = "select song_id,song_name,song_artist from t_songs where song_tags like '%{}%' limit {};"
ret = []
for i in tags:
limit -= cursor.execute(sql.format(i, limit))
data = cursor.fetchall()
ret.extend(list(data))
if limit <= 0:
break
cursor.close()
return ret
def set_action(db: pymysql.connections.Connection, uid, sid, like, unlike, audition, download):
ret = False
cursor = db.cursor()
sql = 'select action_id from t_actions where action_user={} and action_song={};'.format(uid, sid)
if cursor.execute(sql) == 0:
sql = 'insert into t_actions (action_user, action_song, action_like, action_unlike, action_audition, action_download) values ({}, {}, {}, {}, {}, {});'.format(uid, sid, like, unlike, audition, download)
StdError.info("用户行为新增：uid={},sid={}".format(uid, sid))
else:
sql = 'update t_actions set action_like={}, action_unlike={}, action_audition={}, action_download={} where action_user={} and action_song={};'.format(like, unlike, audition, download, uid, sid)
StdError.info("用户行为更新：uid={},sid={}".format(uid, sid))
try:
cursor.execute(sql)
db.commit()
ret = True
except:
db.rollback()
StdError.error("行为设置出现未知错误\tuid={},sid={},sql={}".format(uid, sid, sql))
ret = False
finally:
cursor.close()
return ret
def get_user_tags(db: pymysql.connections.Connection, user):
cursor = db.cursor()
if isinstance(user, int):
sql = "select user_like from t_users where user_id={} limit 1;".format(user)
else:
sql = "select user_like from t_users where user_name='{}' limit 1;".format(user)
cursor.execute(sql)
data = cursor.fetchone()[0]
cursor.close()
if data != None:
return list(data.split(','))
else:
return None
def get_song_tags(db: pymysql.connections.Connection, song):
cursor = db.cursor()
if isinstance(song, int):
sql = "select song_tags from t_songs where song_id={} limit 1;".format(song)
else:
sql = "select song_tags from t_songs where song_name='{}' limit 1;".format(song)
cursor.execute(sql)
data = cursor.fetchone()[0]
cursor.close()
if data != None:
return list(data.split(','))
else:
return None
def is_user_super(db: pymysql.connections.Connection, uid):
ret = 0
cursor = db.cursor()
sql = 'select user_SUPER from t_users where user_id={} limit 1;'.format(
uid)
try:
cursor.execute(sql)
ret = cursor.fetchone()[0]
except:
ret = 0
finally:
cursor.close()
return ret
def get_songs_by_search(db: pymysql.connections.Connection, s):
cursor = db.cursor()
sql = 'select song_id,song_name,song_artist from t_songs where song_name like \'%{}%\' or song_artist like \'%{}%\''
res = cursor.execute(sql.format(s, s))
if res == 0:
ret = {'num': res}
else:
data = cursor.fetchall()
ret = {
'num': res,
'data': data
}
cursor.close()
return ret
def connected(connection: pymysql.connections.Connection):
"""A context with global convenience functions for MySQL/MariaDB queries. Specifically:
— select(statement, vals)
— execute(statement, vals)
Where cursor.execute(statement, vals) is called.
"""
try:
global select, execute
def execute(statement: str, vals: Tuple = ()) -> None:
with connection.cursor() as cursor:
cursor.execute(statement, vals)
connection.commit()
def select(statement: str, vals: Tuple = ()) -> Iterator[Dict]:
with connection.cursor() as cursor:
cursor.execute(statement, vals)
return cursor
yield
finally:
connection.close()
def execute_select_statement(connection: pymysql.connections.Connection,
sql) -> dict:
"""
Execute select statement and return result as a dict.
:param connection: instance of Connection
:param sql: SQL select statement
:return: dict of result
"""
with connection:
with connection.cursor() as cursor:
cursor.execute(sql)
result = cursor.fetchall()
logger.debug(f"result: {result}, sql: {sql}")
return result
def user_register(db: pymysql.connections.Connection, userName, userPwd, userSUPER, userEmail, userLikes):
cursor = db.cursor()
ret = False
try:
if cursor.execute('select user_id from t_users where user_name=\'{}\';'.format(userName)) != 0:
raise UserManagerError('用户注册错误，已存在相同的用户名')
sql = 'insert into t_users (user_name, user_SUPER, user_like, user_pwd, user_email) values (\'{}\', {}, \'{}\', \'{}\', \'{}\');'
sql = sql.format(userName, userSUPER, ','.join(userLikes), userPwd, userEmail)
StdError.info("注册用户" + sql)
cursor.execute(sql)
db.commit()
ret = True
except UserManagerError as e:
db.rollback()
StdError.warn(e.message + "\tuser_name=" + userName + "\tuser_SUPER=" + str(userSUPER) + "\tuser_pwd=" + str(userPwd) + "\tuser_email=" + userEmail + "\tuser_likes=" + userLikes)
except pymysql.err.IntegrityError as e:
db.rollback()
StdError.error(str(e) + "\tuser_name=" + userName + "\tuser_SUPER=" + str(userSUPER) + "\tuser_pwd=" + str(userPwd) + "\tuser_email=" + userEmail + "\tuser_likes=" + userLikes)
except:
db.rollback()
StdError.error("用户注册出现未知错误" + "\tuser_name=" + userName + "\tuser_SUPER=" + str(userSUPER) + "\tuser_pwd=" + str(userPwd) + "\tuser_email=" + userEmail + "\tuser_likes=" + userLikes)
finally:
cursor.close()
return ret
def get_actions(db: pymysql.connections.Connection, uid=None, sid=None):
cursor = db.cursor()
if uid == None and sid == None:
sql = 'select * from t_actions;'
cursor.execute(sql)
data = cursor.fetchall()
elif uid == None:
sql = 'select action_like,action_unlike,action_audition,action_download from t_actions where action_song={};'
cursor.execute(sql.format(sid))
data = cursor.fetchall()
elif sid == None:
sql = 'select action_like,action_unlike,action_audition,action_download from t_actions where action_user={};'
cursor.execute(sql.format(uid))
data = cursor.fetchall()
else:
sql = 'select action_like,action_unlike,action_audition,action_download from t_actions where action_user={} and action_song={} limit 1;'
cursor.execute(sql.format(uid, sid))
data = cursor.fetchone()
cursor.close()
return data
def user_login(db: pymysql.connections.Connection, userName, userPwd):
cursor = db.cursor()
ret = -1
try:
sql = 'select user_id,user_pwd from t_users where user_name=\'{}\' limit 1;'.format(userName)
if cursor.execute(sql) != 0:
data = cursor.fetchone()
if data[1] == userPwd:
ret = data[0]
else:
raise UserManagerError("用户登陆错误，用户密码不匹配")
else:
raise UserManagerError("用户登陆错误，未找到匹配的注册用户")
except UserManagerError as e:
StdError.warn(e.message + "\tuser_name=" + userName + "\tuser_pwd=" + userPwd)
except:
StdError.error("用户登录出现未知错误" + "\tuser_name=" + userName + "\tuser_pwd=" + userPwd)
finally:
cursor.close()
return ret
def get_music_number(db: pymysql.connections.Connection):
cursor = db.cursor()
cursor.execute('select count(song_id) from t_songs;')
data = cursor.fetchone()[0]
cursor.close()
return data