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