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