def question_gen_3(request):
ques_gen = Ques_Gen_Book()
ques, query = ques_gen.lvl_3_ques()
feedback = ''
if request.GET.get('answer'):
connection = sqlite3.connect(
"C:/Users/hhash/Desktop/FYP Implementation/books.db")
crsr = connection.cursor()
correct_answer = str(request.GET.get('correct_answer'))
crsr.execute(correct_answer)
ans = crsr.fetchall()
user_ans = request.GET.get('answer')
user_ans_check = sqlvalidator.parse(user_ans)
if not user_ans_check.is_valid():
feedback = 'Incorrect Syntax'
# print(user_ans_check.errors)
else:
crsr.execute(user_ans)
user_ans = crsr.fetchall()
test_1 = set(ans) - set(user_ans)
test_2 = set(user_ans) - set(ans)
final = list(test_1) + list(test_2)
if len(final) == 0:
feedback = 'Correct Answer'
else:
feedback = 'Incorrect Answer'
context = {'ques': ques, 'feedback': feedback, 'query': query}
return render(request, 'question_gen.html', context=context)
def extract_user_tables():
# noinspection SpellCheckingInspection
"""
Extract user created model tables defined in models.py.
@:rtype List
"""
dump_file = utils.locate_sql_dump()
# Open the sql dump and read it as list
ddl_data = open(dump_file, 'r').readlines()
assert len(ddl_data), 'Try running:\n./manage migrate'
ddl_data_clean = utils.fix_names(
apps.DatabuilderConfig.name,
utils.without_django_tables(ddl_data),
)
# noinspection SpellCheckingInspection
assert len(
ddl_data_clean
), 'Try adding some models in models.py\n then run:\n./manage migrate'
# Confirm each line for valid SQL query line
result = any(
[sqlvalidator.parse(cmd).is_valid() for cmd in ddl_data_clean])
assert result, 'Some ddl command line is invalid, maybe the tool need upgrade, file an issue at project REPO'
return ddl_data_clean
def assert_invalid_sql(sql: str, expected_errors: Optional[List[str]] = None):
sql_query = sqlvalidator.parse(sql)
assert sql_query.is_valid() is False, (
"No errors found, but expected: {}".format(expected_errors)
if expected_errors
else "No errors found, but expected some"
)
if expected_errors:
assert sql_query.errors == expected_errors
def is_valid(query):
"""
Simple sql validator.
`sqlvalidator.is_valid` frequently throws errors
on invalid input (like unbalanced quotes), so we wrap it in a bare `except`.
"""
try:
return sqlvalidator.parse(query).is_valid()
except:
return False
def clean_sql_Q():
# read the list of sql querries from the folder
sql_files_list = listdir('sql')
# list the files we read
add_acc_message(f'\r\n We have read the following sql querries from the sql folder: {sql_files_list}')
# load querries in dic:
query_dic = {}
for file in sql_files_list:
if file.startswith("."):
continue
# read query
f = open(f"sql/{file}", "r").read()
if "gcp_cset_crunchbase" in f:
add_acc_message(f'Found reference to dynamic data gcp_cset_crunchbase in {file} querry')
if "gcp_cset_tr_refinitiv" in f:
add_acc_message(f'Found reference to dynamic data tr_refinitiv in {file} querry')
sql_query = sqlvalidator.parse(f)
if not sql_query.is_valid():
print(sql_query.errors)
add_acc_message(f'Found SQL validation error in the querry {file}')
# add querry to the dic
query_dic.update({file[:-4]:f})
return query_dic
def assert_invalid_sql(sql):
sql_query = sqlvalidator.parse(sql)
assert sql_query.is_valid() is False
def assert_valid_sql(sql):
sql_query = sqlvalidator.parse(sql)
assert sql_query.is_valid() is True, sql_query.errors
def ValidateSQLStatement(statement):
s = sv.parse(statement)
if not s.is_valid():
return s.errors
return 'Success'
