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_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 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 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 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 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 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_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 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 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 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_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 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 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 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 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 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 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 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_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_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 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 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 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_songs_by_random(db: pymysql.connections.Connection, need):
cursor = db.cursor()
cursor.execute('select song_id,song_name,song_artist from t_songs ORDER BY RAND() LIMIT {};'.format(need))
data = cursor.fetchall()
cursor.close()
return list(data)
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
def to_sql(
df: pd.DataFrame,
con: pymysql.connections.Connection,
table: str,
schema: str,
mode: str = "append",
index: bool = False,
dtype: Optional[Dict[str, str]] = None,
varchar_lengths: Optional[Dict[str, int]] = None,
use_column_names: bool = False,
chunksize: int = 200,
) -> None:
"""Write records stored in a DataFrame into MySQL.
Parameters
----------
df : pandas.DataFrame
Pandas DataFrame https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.html
con : pymysql.connections.Connection
Use pymysql.connect() to use credentials directly or wr.mysql.connect() to fetch it from the Glue Catalog.
table : str
Table name
schema : str
Schema name
mode : str
Append, overwrite, upsert_duplicate_key, upsert_replace_into, upsert_distinct.
append: Inserts new records into table
overwrite: Drops table and recreates
upsert_duplicate_key: Performs an upsert using `ON DUPLICATE KEY` clause. Requires table schema to have
defined keys, otherwise duplicate records will be inserted.
upsert_replace_into: Performs upsert using `REPLACE INTO` clause. Less efficient and still requires the
table schema to have keys or else duplicate records will be inserted
upsert_distinct: Inserts new records, including duplicates, then recreates the table and inserts `DISTINCT`
records from old table. This is the least efficient approach but handles scenarios where there are no
keys on table.
index : bool
True to store the DataFrame index as a column in the table,
otherwise False to ignore it.
dtype: Dict[str, str], optional
Dictionary of columns names and MySQL types to be casted.
Useful when you have columns with undetermined or mixed data types.
(e.g. {'col name': 'TEXT', 'col2 name': 'FLOAT'})
varchar_lengths : Dict[str, int], optional
Dict of VARCHAR length by columns. (e.g. {"col1": 10, "col5": 200}).
use_column_names: bool
If set to True, will use the column names of the DataFrame for generating the INSERT SQL Query.
E.g. If the DataFrame has two columns `col1` and `col3` and `use_column_names` is True, data will only be
inserted into the database columns `col1` and `col3`.
chunksize: int
Number of rows which are inserted with each SQL query. Defaults to inserting 200 rows per query.
Returns
-------
None
None.
Examples
--------
Writing to MySQL using a Glue Catalog Connections
>>> import awswrangler as wr
>>> con = wr.mysql.connect("MY_GLUE_CONNECTION")
>>> wr.mysql.to_sql(
... df=df,
... table="my_table",
... schema="test",
... con=con
... )
>>> con.close()
"""
if df.empty is True:
raise exceptions.EmptyDataFrame()
mode = mode.strip().lower()
modes = [
"append",
"overwrite",
"upsert_replace_into",
"upsert_duplicate_key",
"upsert_distinct",
]
if mode not in modes:
raise exceptions.InvalidArgumentValue(
f"mode must be one of {', '.join(modes)}")
_validate_connection(con=con)
try:
with con.cursor() as cursor:
_create_table(
df=df,
cursor=cursor,
table=table,
schema=schema,
mode=mode,
index=index,
dtype=dtype,
varchar_lengths=varchar_lengths,
)
if index:
df.reset_index(level=df.index.names, inplace=True)
column_placeholders: str = ", ".join(["%s"] * len(df.columns))
insertion_columns = ""
upsert_columns = ""
upsert_str = ""
if use_column_names:
insertion_columns = f"({', '.join(df.columns)})"
if mode == "upsert_duplicate_key":
upsert_columns = ", ".join(
df.columns.map(
lambda column: f"`{column}`=VALUES(`{column}`)"))
upsert_str = f" ON DUPLICATE KEY UPDATE {upsert_columns}"
placeholder_parameter_pair_generator = _db_utils.generate_placeholder_parameter_pairs(
df=df,
column_placeholders=column_placeholders,
chunksize=chunksize)
sql: str
for placeholders, parameters in placeholder_parameter_pair_generator:
if mode == "upsert_replace_into":
sql = f"REPLACE INTO `{schema}`.`{table}` {insertion_columns} VALUES {placeholders}"
else:
sql = f"INSERT INTO `{schema}`.`{table}` {insertion_columns} VALUES {placeholders}{upsert_str}"
_logger.debug("sql: %s", sql)
cursor.executemany(sql, (parameters, ))
con.commit()
if mode == "upsert_distinct":
temp_table = f"{table}_{uuid.uuid4().hex}"
cursor.execute(
f"CREATE TABLE `{schema}`.`{temp_table}` LIKE `{schema}`.`{table}`"
)
cursor.execute(
f"INSERT INTO `{schema}`.`{temp_table}` SELECT DISTINCT * FROM `{schema}`.`{table}`"
)
cursor.execute(f"DROP TABLE IF EXISTS `{schema}`.`{table}`")
cursor.execute(
f"ALTER TABLE `{schema}`.`{temp_table}` RENAME TO `{table}`"
)
con.commit()
except Exception as ex:
con.rollback()
_logger.error(ex)
raise
def to_sql(
df: pd.DataFrame,
con: pymysql.connections.Connection,
table: str,
schema: str,
mode: str = "append",
index: bool = False,
dtype: Optional[Dict[str, str]] = None,
varchar_lengths: Optional[Dict[str, int]] = None,
) -> None:
"""Write records stored in a DataFrame into MySQL.
Parameters
----------
df : pandas.DataFrame
Pandas DataFrame https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.html
con : pymysql.connections.Connection
Use pymysql.connect() to use credentials directly or wr.mysql.connect() to fetch it from the Glue Catalog.
table : str
Table name
schema : str
Schema name
mode : str
Append or overwrite.
index : bool
True to store the DataFrame index as a column in the table,
otherwise False to ignore it.
dtype: Dict[str, str], optional
Dictionary of columns names and MySQL types to be casted.
Useful when you have columns with undetermined or mixed data types.
(e.g. {'col name': 'TEXT', 'col2 name': 'FLOAT'})
varchar_lengths : Dict[str, int], optional
Dict of VARCHAR length by columns. (e.g. {"col1": 10, "col5": 200}).
Returns
-------
None
None.
Examples
--------
Writing to MySQL using a Glue Catalog Connections
>>> import awswrangler as wr
>>> con = wr.mysql.connect("MY_GLUE_CONNECTION")
>>> wr.mysql.to_sql(
... df=df
... table="my_table",
... schema="test",
... con=con
... )
>>> con.close()
"""
if df.empty is True:
raise exceptions.EmptyDataFrame()
_validate_connection(con=con)
try:
with con.cursor() as cursor:
_create_table(
df=df,
cursor=cursor,
table=table,
schema=schema,
mode=mode,
index=index,
dtype=dtype,
varchar_lengths=varchar_lengths,
)
if index:
df.reset_index(level=df.index.names, inplace=True)
placeholders: str = ", ".join(["%s"] * len(df.columns))
sql: str = f"INSERT INTO `{schema}`.`{table}` VALUES ({placeholders})"
_logger.debug("sql: %s", sql)
parameters: List[List[Any]] = _db_utils.extract_parameters(df=df)
cursor.executemany(sql, parameters)
con.commit() # type: ignore
except Exception as ex:
con.rollback() # type: ignore
_logger.error(ex)
raise