def get_past_universe_stock_list(self, universe: Universe) -> list: """ 과거에 한번이라도 universe에 포함된 종목 리스트 반환 :param universe: kospi, kosdaq, kospi200, kosdaq150, top2000, top350(kospi200과 kosdaq150) :return: universe에 포함되었던 종목 리스트 반환 """ if universe in (Universe.total, Universe.kospi, Universe.kosdaq): data_ticker = Table("data_ticker") if universe == Universe.total: query = MySQLQuery.from_(data_ticker).groupby( data_ticker.ticker).select("*") else: query = (MySQLQuery.from_(data_ticker).groupby( data_ticker.id).having( data_ticker.market == universe.name).select("*")) df = self.executor.sql(query.get_sql()) past_universe_stock_list = df["ticker"].to_list() return past_universe_stock_list elif universe in (Universe.top350, Universe.kospi200, Universe.kosdaq150): if universe == Universe.top350: kospi200_past_universe_stock_list = self.get_past_universe_stock_list( Universe.kospi200) kosdaq150_past_universe_stock_list = self.get_past_universe_stock_list( Universe.kosdaq150) return kospi200_past_universe_stock_list + kosdaq150_past_universe_stock_list elif universe in (Universe.kospi200, Universe.kosdaq150): data_universe = Table(f"data_{universe.name}") query = (MySQLQuery.from_(data_universe).groupby( data_universe.ticker_id).select(data_universe.ticker_id)) df = self.executor.sql(query.get_sql()) past_universe_stock_list = df["ticker_id"].to_list() return past_universe_stock_list
def createDance(self, danceID=None, properties=None): if (properties != None): properties.pop("id", None) t = Table("Dance") if (danceID == None): q = Query.into(t).columns(*properties.keys()).insert( *properties.values()) else: q = Query.into(t).columns("id", *properties.keys()).insert( danceID, *properties.values()) logger.debug(f'executing insert string: {str(q)}') with self.connection.cursor() as cur: #TODO combine these into single atomic statement? cur.execute(str(q)) if (danceID != None): rowCount = cur.execute( Query.from_(t).select("*").where(id == danceID).get_sql()) else: rowCount = cur.execute( Query.from_(t).select("*").where( id == LAST_INSERT_ID()).get_sql()) if (rowCount): rows = cur.fetchall() return rows[0] else: return None with self.connection.cursor() as cur: #TODO combine these into single atomic statement? cur.execute(f'INSERT INTO `Dance` () VALUES ()') cur.execute("SELECT * FROM `Dance` where id = LAST_INSERT_ID()") rows = cur.fetchall() return rows[0]
def test_multiple_modifier_select(self): q = (MySQLQuery.from_("abc").select("def").modifier( "HIGH_PRIORITY").modifier("SQL_CALC_FOUND_ROWS")) self.assertEqual( "SELECT HIGH_PRIORITY SQL_CALC_FOUND_ROWS `def` FROM `abc`", str(q))
def get_all_raw_data(cnn, self): cur = cnn.cursor() data_query = MySQLQuery.from_(raw_data_table).select( raw_data_table.timestamp) cur.execute(data_query.get_sql()) res = cur.fetchall() return res
def course_roles(context: SolidExecutionContext, edx_course_ids: List[String]) -> DagsterPath: """Retrieve information about user roles for given courses. :param context: Dagster execution context for propagaint configuration data :type context: SolidExecutionContext :param edx_course_ids: List of edX course ID strings :type edx_course_ids: List[String] :returns: A path definition that points to the rendered data table :rtype: DagsterPath """ access_role = Table('student_courseaccessrole') roles_query = Query.from_(access_role).select( 'id', 'user_id', 'org', 'course_id', 'role').where(access_role.course_id.isin(edx_course_ids)) query_fields, roles_data = context.resources.sqldb.run_query(roles_query) # Maintaining previous file name for compatibility (TMM 2020-05-01) roles_path = context.resources.results_dir.path.joinpath('role_query.csv') write_csv(query_fields, roles_data, roles_path) yield Materialization( label='role_query.csv', description='Course roles records from Open edX installation', metadata_entries=[ EventMetadataEntry.text( label='course_roles_count', description='Number of course roles records', text=str(len(roles_data))), EventMetadataEntry.path(roles_path.name, 'role_query_csv_path') ]) yield Output(roles_path, 'edx_course_roles')
def login(): if request.is_json: data = request.get_json(silent=True) if data == None: abort(400) else: fb_id = data.get("fb_id") phone = data.get("phone") password = data.get("password") cursor = get_db().cursor() user = Table("user") q = Query.from_(user).select(user.id) if fb_id != None: q = q.where(user.fb_id == fb_id) else: q = q.where(user.phone == phone).where( user.password == password) cursor.execute(str(q)) record = cursor.fetchone() cursor.close() if (record) != None: return jsonify({"token": auth.encode(record[0])}) else: abort(404) else: abort(400)
def test_mysql_query_does_not_wrap_unioned_queries_with_params(self): query1 = MySQLQuery.from_(self.table1).select(self.table1.foo) query2 = Query.from_(self.table2).select(self.table2.bar) self.assertEqual( 'SELECT `foo` FROM `abc` UNION SELECT `bar` FROM `efg`', str(query1 + query2))
def get_index_day_price_data(self, universe: Universe, start_date: date, end_date: date) -> Optional[DataFrame]: """ 해당 universe의 index 가격을 조회 :param universe: kospi, kosdaq, kospi200, kosdaq150 :param start_date: :param end_date: :return: """ if universe in (Universe.kospi, Universe.kosdaq): ticker = universe.name.upper() elif universe in (Universe.kospi200, Universe.kosdaq150): if universe == Universe.kospi200: ticker = "KOSPI200" elif universe == Universe.kosdaq150: ticker = "KOSDAQ150" data_index_candle = Table("data_indexcandleday") query = (MySQLQuery.from_(data_index_candle).select("*").where( Criterion.all([ data_index_candle.ticker == ticker, data_index_candle.date >= start_date, data_index_candle.date <= end_date, ]))) df = self.executor.sql(query.get_sql()) df = df.drop(["id", "ticker"], axis=1) return df
def get_today_universe_stock_list(self, universe: Universe, today: date) -> list: """ 당일의 universe에 포함된 종목 리스트 반환 :param universe: kospi, kosdaq, kospi200, kosdaq150, top2000, top350(kospi200과 kosdaq150) :param today: 당일 날짜 :return: 해당 universe의 종목 리스트 """ if universe in (Universe.kospi, Universe.kosdaq, Universe.kospi200, Universe.kosdaq150): data_universe = Table(f"data_{universe.name}history") query = MySQLQuery.from_(data_universe).select( data_universe.tickers).where(data_universe.date == today) df = self.executor.sql(query.get_sql()) today_universe_stock_list = df["tickers"].iloc[0].split(",") elif universe in (Universe.total, Universe.top350): if universe == Universe.total: today_universe_stock_list = self.get_today_universe_stock_list( Universe.kospi, today) + self.get_today_universe_stock_list( Universe.kosdaq, today) elif universe == Universe.top350: today_universe_stock_list = self.get_today_universe_stock_list( Universe.kospi200, today) + self.get_today_universe_stock_list( Universe.kosdaq150, today) return today_universe_stock_list
def test_mysql_query_uses_backtick_quote_chars(self): q = (MySQLQuery.from_(self.table_abc).select( self.table_abc.foo).groupby( self.table_abc.foo).having(self.table_abc.buz == "fiz")) self.assertEqual( "SELECT `foo` FROM `abc` GROUP BY `foo` HAVING `buz`='fiz'", str(q))
def _verify(*args, **kwargs): auth_headers = request.headers.get("Authorization", "").split() invalid_msg = {"message": "invalid token"} expired_msg = {"message": "expired token"} if len(auth_headers) != 2: return jsonify(invalid_msg), 401 try: token = auth_headers[1] data = decode(token) user = Table("user") q = Query.from_(user).select(user.id).where(user.id == data["sub"]) cursor = get_db().cursor() cursor.execute(str(q)) record = cursor.fetchone() cursor.close() if not record: raise RuntimeError("User not found") return f(record, *args, **kwargs) except jwt.ExpiredSignatureError: return jsonify(expired_msg), 401 # 401 is Unauthorized HTTP status code except (jwt.InvalidTokenError) as e: print(e) return jsonify(invalid_msg), 401
def test_multiple_modifier_select(self): q = MySQLQuery.from_('abc').select('def').modifier( 'HIGH_PRIORITY').modifier('SQL_CALC_FOUND_ROWS') self.assertEqual( 'SELECT HIGH_PRIORITY SQL_CALC_FOUND_ROWS `def` FROM `abc`', str(q))
def get_users(cnn, self) -> list: cur = cnn.cursor() q = MySQLQuery.from_(users_table).select(users_table.name) cur.execute(q.get_sql()) users = cur.fetchall() return [x[0] for x in users]
def get_user_id(cnn, self, user_name): cur = cnn.cursor() q = MySQLQuery.from_(users_table).select( users_table.id).where(users_table.name == user_name) cur.execute(q.get_sql()) user_id = cur.fetchone() return user_id
def test_mysql_query_does_not_wrap_intersected_queries_with_params(self): query1 = MySQLQuery.from_(self.table1).select(self.table1.foo) query2 = Query.from_(self.table2).select(self.table2.bar) self.assertEqual( "SELECT `foo` FROM `abc` INTERSECT SELECT `bar` FROM `efg`", str(query1.intersect(query2)), )
def deleteDance(self, danceID): t = Table("Dance") q = Query.from_(t).where(t.id == danceID).delete() with self.connection.cursor() as cur: logger.info(f'executing delete string: {str(q)}') rowCount = cur.execute(str(q)) return 0 if rowCount > 0 else -1
def test_mysql_query_does_not_wrap_minus_queries_with_params(self): query1 = MySQLQuery.from_(self.table1).select(self.table1.foo) query2 = Query.from_(self.table2).select(self.table2.bar) self.assertEqual( "SELECT `foo` FROM `abc` MINUS SELECT `bar` FROM `efg`", str(query1 - query2), )
def select(self, table, fields, **where_values): table = Table(table) q = Query.from_(table) for field in fields: q = q.select(getattr(table, field)) for k, v in where_values.items(): q = q.where(getattr(table, k) == v) return q.get_sql()
def getDanceById(self, danceID): t = Table("Dance") q = Query.from_(t).where(t.id == danceID).select("*") with self.connection.cursor() as cur: rowCount = cur.execute(str(q)) if (rowCount > 0): return cur.fetchone() else: return None
def test_create_temporary_table_from_select(self, mock_execute): query = MySQLQuery.from_('abc').select('*') MySQLDatabase().create_temporary_table_from_select('def', query) mock_execute.assert_called_once_with( 'CREATE TEMPORARY TABLE "def" AS (SELECT * FROM "abc")', connection=None, )
def test_mysql_query_uses_backtick_quote_chars(self): q = MySQLQuery.from_(self.table_abc).select( self.table_abc.foo ).groupby( self.table_abc.foo ).having( self.table_abc.buz == 'fiz' ) self.assertEqual("SELECT `foo` FROM `abc` GROUP BY `foo` HAVING `buz`='fiz'", str(q))
def deleteRejected(self): t = Table("Dance") deleteQuery = Query.from_(t).where(t.favorite == -1).delete() with self.connection.cursor() as cur: logger.info(f'executing delete string: {str(deleteQuery)}') cur.execute(str(deleteQuery)) return 0
def get_password(cnn, self, user: str) -> (str, None): cur = cnn.cursor() q = MySQLQuery.from_(users_table).select( users_table.password).where(users_table.name == user) cur.execute(q.get_sql()) password = cur.fetchone() if len(password) > 0: return password[0] else: return None
def get_insta_data(cnn, self): cur = cnn.cursor() data_query = MySQLQuery.from_(raw_data_table).select( raw_data_table.timestamp) raw_query = data_query.get_sql( ) + " WHERE strftime('%s', 'now') - cast(strftime('%s', timestamp) as integer) < 4" cur.execute(raw_query) res = cur.fetchall() return res
def user_exists(cnn, self, user: str) -> bool: cur = cnn.cursor() user_query = MySQLQuery.from_(users_table).select( users_table.star).where(users_table.name == user) cur.execute(user_query.get_sql()) res = cur.fetchall() if len(res) > 0: return True return False
def register(): if request.is_json: data = request.get_json(silent=True) if data == None: abort(400) else: fb_id = data.get("fb_id") phone = data.get("phone") password = data.get("password") name = data.get("name") if phone == None or password == None or name == None: abort(400) cursor = get_db().cursor() user = Table("user") q = Query.from_(user).select(user.id) if fb_id != None: q = q.where((user.phone == phone) | (user.fb_id == fb_id)) else: q = q.where(user.phone == phone) q = q.limit(1) cursor.execute(str(q)) record = cursor.fetchone() cursor.close() if record != None: return jsonify({"mesage": "user existed"}), 400 else: q = Query.into(user) columns = ["phone", "password", "name", "created_on"] values = [phone, password, name, int(time.time() * 1000)] if fb_id: columns.append("fb_id") values.append(fb_id) cursor = get_db().cursor() q = q.columns(*columns).insert(*values) cursor.execute(str(q)) record = cursor.fetchone() cursor.close() get_db().commit() return {"message": "success"}, 200 else: abort(400)
def categories(user_id): category = Table("category") q = Query.from_(category).select(category.id, category.name).orderby("order") cursor = get_db().cursor() cursor.execute(str(q)) records = cursor.fetchall() cursor.close() records = list(map(Category, records)) return jsonify(records)
def get_column_definitions(self, schema, table, connection=None): columns = Table('columns', schema='INFORMATION_SCHEMA') columns_query = ( MySQLQuery.from_(columns) .select(columns.column_name, columns.column_type) .where(columns.table_schema == Parameter('%(schema)s')) .where(columns.field('table_name') == Parameter('%(table)s')) .distinct() .orderby(columns.column_name) ) return self.fetch(str(columns_query), parameters=dict(schema=schema, table=table), connection=connection)
def get_trading_day_list(self, start_date: date, end_date: date) -> list: """ 과거 거래일 리스트를 조회하는 함수 :param start_date: back test 시작일 :param end_date: back test 종료일 :return: 과거 거래일 리스트 반환 """ data_trading_day = Table("data_iskoreatradingday") query = (MySQLQuery.from_(data_trading_day).select( data_trading_day.date).where( Criterion.all([ data_trading_day.is_tradable == "1", data_trading_day.date >= start_date, data_trading_day.date <= end_date, ]))) df = self.executor.sql(query.get_sql()) trading_day_list = df["date"].to_list() return trading_day_list
def get_exchange_rate(self, exchange_index: ExchangeRate, start_date: date, end_date: date) -> DataFrame: """ 달러, 유로, 엔 환율 조회 :param exchange_index: :param start_date: :param end_date: :return: """ data_exchange_rate = Table("data_exchangeratecandleday") query = (MySQLQuery.from_(data_exchange_rate).select("*").where( Criterion.all([ data_exchange_rate.ticker == exchange_index.value, data_exchange_rate.date >= start_date, data_exchange_rate.date <= end_date, ]))) df = self.executor.sql(query.get_sql()) df = df.drop(["id", "ticker"], axis=1) return df
def connect(user_id): if request.is_json: data = request.get_json(silent=True) if data == None: abort(400) else: fb_id = data.get("fb_id") if fb_id == None: abort(400) cursor = get_db().cursor() user = Table("user") q = Query.from_(user).select( user.id).where(user.fb_id == fb_id).limit(1) cursor.execute(str(q)) record = cursor.fetchone() cursor.close() if record != None: return ( jsonify({ "mesage": "facebok account connected to another user" }), 400, ) else: q = Query.update(user).set(user.fb_id, fb_id).where(user.id == user_id) cursor = get_db().cursor() cursor.execute(str(q)) cursor.close() get_db().commit() return {"message": "success"}, 200 else: abort(400)
def test_mysql_query_uses_backtick_quote_chars(self): q = MySQLQuery.from_('abc').select('foo', 'bar') self.assertEqual('SELECT `foo`,`bar` FROM `abc`', str(q))
def test_do_not_add_hints_to_query_if_not_supported_by_dialect(self): query = MySQLQuery.from_('table').select('*') query_hint = add_hints([query], 'test_hint') self.assertEqual('SELECT * FROM `table`', str(query_hint[0]))
def test_mysql_query_does_not_wrap_unioned_queries_with_params(self): query1 = MySQLQuery.from_(self.table1).select(self.table1.foo) query2 = Query.from_(self.table2).select(self.table2.bar) self.assertEqual('SELECT `foo` FROM `abc` UNION SELECT `bar` FROM `efg`', str(query1 + query2))
def test_mysql_query_uses_backtick_quote_chars(self): q = MySQLQuery.from_(self.t).groupby(self.t.foo).select(self.t.foo) self.assertEqual('SELECT `foo` FROM `abc` GROUP BY `foo`', str(q))