def refreshViewInDB(connector: dbc.DBConnector, baseViewQuery: str,
viewName: str) -> None:
if (connector.IsConnected == True):
try:
createViewQuery = ' CREATE OR ALTER VIEW ' + viewName + ' AS ' + baseViewQuery
connector.ExecuteQuery(createViewQuery)
except Exception as excp:
print(excp)
def getAllSurveyDataQuery(connector: dbc.DBConnector) -> str:
#IN THIS FUNCTION YOU MUST STRICTLY CONVERT THE CODE OF getAllSurveyData written in T-SQL, available in Survey_Sample_A19 and seen in class
# Below is the beginning of the conversion
# The Python version must return the string containing the dynamic query (as we cannot use sp_executesql in Python!)
strQueryTemplateForAnswerColumn: str = """COALESCE(
(
SELECT a.Answer_Value
FROM Answer as a
WHERE
a.UserId = u.UserId
AND a.SurveyId = <SURVEY_ID>
AND a.QuestionId = <QUESTION_ID>
), -1) AS ANS_Q<QUESTION_ID> """
strQueryTemplateForNullColumnn: str = ' NULL AS ANS_Q<QUESTION_ID> '
strQueryTemplateOuterUnionQuery: str = """
SELECT
UserId
, <SURVEY_ID> as SurveyId
, <DYNAMIC_QUESTION_ANSWERS>
FROM
[User] as u
WHERE EXISTS
( \
SELECT *
FROM Answer as a
WHERE u.UserId = a.UserId
AND a.SurveyId = <SURVEY_ID>
)
"""
strCurrentUnionQueryBlock: str = ''
strFinalQuery: str = ''
#MAIN LOOP, OVER ALL THE SURVEYS
# FOR EACH SURVEY, IN currentSurveyId, WE NEED TO CONSTRUCT THE ANSWER COLUMN QUERIES
#inner loop, over the questions of the survey
# Cursors are replaced by a query retrived in a pandas df
surveyQuery: str = 'SELECT SurveyId FROM Survey ORDER BY SurveyId'
surveyQueryDF: pd.DataFrame = connector.ExecuteQuery_withRS(surveyQuery)
#CARRY ON THE CONVERSION
#TODO
return strFinalQuery
def surveyResultsToDF(connector: dbc.DBConnector,
viewName: str) -> pd.DataFrame:
results: pd.DataFrame = connector.ExecuteQuery_withRS("Select * from " +
viewName)
return results
def getAllSurveyDataQuery(connector: dbc.DBConnector) -> str:
#IN THIS FUNCTION YOU MUST STRICTLY CONVERT THE CODE OF getAllSurveyData written in T-SQL, available in Survey_Sample_A19 and seen in class
# Below is the beginning of the conversion
# The Python version must return the string containing the dynamic query (as we cannot use sp_executesql in Python!)
strQueryTemplateForAnswerColumn: str = """ COALESCE(( SELECT a.Answer_Value FROM Answer as a WHERE a.UserId = u.UserId AND a.SurveyId = <SURVEY_ID> AND a.QuestionId = <QUESTION_ID>), -1) AS ANS_Q<QUESTION_ID> """
strQueryTemplateForNullColumnn: str = 'NULL AS ANS_Q<QUESTION_ID> '
strQueryTemplateOuterUnionQuery: str = """SELECT UserId, <SURVEY_ID> as SurveyId, <DYNAMIC_QUESTION_ANSWERS> FROM [User] as u WHERE EXISTS ( SELECT * FROM Answer as a WHERE u.UserId = a.UserId AND a.SurveyId = <SURVEY_ID>)"""
#MAIN LOOP, OVER ALL THE SURVEYS
# FOR EACH SURVEY, IN currentSurveyId, WE NEED TO CONSTRUCT THE ANSWER COLUMN QUERIES
#inner loop, over the questions of the survey
# Cursors are replaced by a query retrived in a pandas df
surveyQuery: str = 'SELECT SurveyId FROM Survey ORDER BY SurveyId'
surveyQueryDF: pd.DataFrame = connector.ExecuteQuery_withRS(surveyQuery)
#CARRY ON THE CONVERSION
#TODO
#OutterForLoop over surveyId
strFinalQuery: str = ''
for i, data in surveyQueryDF.iterrows():
currentSurveyId = data['SurveyId']
print(currentSurveyId)
strCurrentUnionQueryBlock: str = ''
currentQuestionCursorStr: str = """SELECT * FROM ( SELECT SurveyId, QuestionId, 1 as InSurvey FROM SurveyStructure WHERE SurveyId = %s UNION SELECT %s as SurveyId,Q.QuestionId,0 as InSurvey FROM Question as Q WHERE NOT EXISTS(SELECT *FROM SurveyStructure as S WHERE S.SurveyId = %s AND S.QuestionId = Q.QuestionId )) as t ORDER BY QuestionId; """ % (
currentSurveyId, currentSurveyId, currentSurveyId)
currentQuestionCursorDF: pd.DataFrame = connector.ExecuteQuery_withRS(
currentQuestionCursorStr)
strColumnsQueryPart: str = ''
for j, currQData in currentQuestionCursorDF.iterrows():
currentSurveyIdInQuestion = currQData['SurveyId']
currentQuestionID = currQData['QuestionId']
currentInSurvey = currQData['InSurvey']
if currentInSurvey == 0:
strColumnsQueryPart = strColumnsQueryPart + strQueryTemplateForNullColumnn.replace(
'<QUESTION_ID>', str(currentQuestionID))
else:
strColumnsQueryPart = strColumnsQueryPart + strQueryTemplateForAnswerColumn.replace(
'<QUESTION_ID>', str(currentQuestionID))
if j != len(currentQuestionCursorDF.index) - 1:
strColumnsQueryPart = strColumnsQueryPart + ', '
###Inner For loop ends
##BACK IN THE OUTER LOOP OVER SURVEYS
strCurrentUnionQueryBlock = strCurrentUnionQueryBlock + strQueryTemplateOuterUnionQuery.replace(
'<DYNAMIC_QUESTION_ANSWERS>', str(strColumnsQueryPart))
strCurrentUnionQueryBlock = strCurrentUnionQueryBlock.replace(
'<SURVEY_ID>', str(currentSurveyId))
strFinalQuery = strFinalQuery + strCurrentUnionQueryBlock
if i != len(surveyQueryDF.index) - 1:
strFinalQuery = strFinalQuery + ' UNION '
return strFinalQuery
def surveyResultsToDF(connector: dbc.DBConnector, viewName:str)->pd.DataFrame:
"""Sends query to DB to get the View table as pandas dataframe"""
results:pd.DataFrame = None
strViewQuery = "SELECT * FROM {}".format(viewName)
results = connector.ExecuteQuery_withRS(strViewQuery)
return results
def refreshViewInDB(connector: dbc.DBConnector, baseViewQuery:str, viewName:str)->None:
"""Creates/Refreshes View in the database"""
if(connector.IsConnected == True):
strSQLSurveyData = 'CREATE OR ALTER VIEW {} AS '.format(viewName) + '(' + baseViewQuery + ')'
connector.CreateRefreshView(strSQLSurveyData)
def getAllSurveyDataQuery(connector: dbc.DBConnector) -> str:
"""Generates query str getAllSurveyData => Conversion of code of getAllSurveyData written in T-SQL """
strQueryTemplateForAnswerColumn: str = """COALESCE(
(
SELECT a.Answer_Value
FROM Answer as a
WHERE
a.UserId = u.UserId
AND a.SurveyId = <SURVEY_ID>
AND a.QuestionId = <QUESTION_ID>
), -1) AS ANS_Q<QUESTION_ID> """
strQueryTemplateForNullColumnn: str = ' NULL AS ANS_Q<QUESTION_ID> '
strQueryTemplateOuterUnionQuery: str = """
SELECT
UserId
, <SURVEY_ID> as SurveyId
, <DYNAMIC_QUESTION_ANSWERS>
FROM
[User] as u
WHERE EXISTS
( \
SELECT *
FROM Answer as a
WHERE u.UserId = a.UserId
AND a.SurveyId = <SURVEY_ID>
)
"""
strCurrentUnionQueryBlock: str = ''
strFinalQuery: str = ''
# Cursors are replaced by a query retrived in a pandas df
surveyQuery:str = 'SELECT SurveyId FROM Survey ORDER BY SurveyId'
surveyQueryDF:pd.DataFrame = connector.ExecuteQuery_withRS(surveyQuery)
#MAIN LOOP, OVER ALL THE SURVEYS
for index1, row in surveyQueryDF.iterrows():
currentSurveyId = row.SurveyId
strCurrentQuestionQuery: str = """
SELECT *
FROM
(
SELECT
SurveyId,
QuestionId,
1 as InSurvey
FROM
SurveyStructure
WHERE
SurveyId = {currentSurveyId}
UNION
SELECT
{currentSurveyId} as SurveyId,
Q.QuestionId,
0 as InSurvey
FROM
Question as Q
WHERE NOT EXISTS
(
SELECT *
FROM SurveyStructure as S
WHERE S.SurveyId = {currentSurveyId} AND S.QuestionId = Q.QuestionId
)
) as t
ORDER BY QuestionId;
""".format(currentSurveyId = currentSurveyId)
CurrentQuestionQueryDF = connector.ExecuteQuery_withRS(strCurrentQuestionQuery)
strColumnsQueryPart: str = ''
#Inner loop: for the current survey, iterate over the questions and construct the answer column queries
for index2, row in CurrentQuestionQueryDF.iterrows():
currentInSurvey = row.InSurvey
currentQuestionId = row.QuestionId
if currentInSurvey == 0:
strColumnsQueryPart = strColumnsQueryPart + strQueryTemplateForNullColumnn.replace('<QUESTION_ID>', str(currentQuestionId))
else:
strColumnsQueryPart = strColumnsQueryPart + strQueryTemplateForAnswerColumn.replace('<QUESTION_ID>', str(currentQuestionId))
if index2 != (len(CurrentQuestionQueryDF) - 1):
strColumnsQueryPart = strColumnsQueryPart + ','
strCurrentUnionQueryBlock = strQueryTemplateOuterUnionQuery.replace('<DYNAMIC_QUESTION_ANSWERS>', strColumnsQueryPart)
strCurrentUnionQueryBlock = strCurrentUnionQueryBlock.replace('<SURVEY_ID>', str(currentSurveyId))
strFinalQuery = strFinalQuery + strCurrentUnionQueryBlock
if (index1 != len(surveyQueryDF)-1):
strFinalQuery = strFinalQuery + ' UNION '
return strFinalQuery