def export_sql_dump(self,db_name,query):
fileOpsObj = FileOps()
#export_location = re.findall(r"\'(.*?)\'", query)[0]
#save_path = export_location
save_path = "/Users/yash/database_5408_project_integration/output"
full_name = os.path.join(save_path,db_name+"_SQLDUMP.sql")
f1 = fileOpsObj.filereader(db_name+"_SQLDUMP.sql")
fileOpsObj.filewriter(full_name,f1)
#copyfile("/Users/yash/database_5408_project_create_insert/"+db_name+"_SQLDUMP.sql", export_location + db_name+"_SQLDUMP.sql")
# sqldumpObj = Export_SQLDUMP()
# query = "export sql dump;"
# sqldumpObj.export_sql_dump("5408",query)
def print_relationships(self, file_name, datatype_dict_object):
fileopobj = FileOps()
# add methods as required seperately donot modify these
#print("\n======Relationships between Tables=======")
save_path = "/Users/yash/database_5408_project_integration/output"
full_name = os.path.join(save_path, file_name)
fileopobj.filewriter(full_name, "\n=========ER Diagram==========\n")
fileopobj.filewriterAppend(full_name,
"\nRelationships between Tables\n")
for i in datatype_dict_object['Tables']:
#print("\n")
#print("Table Name: " + i['Table_name'].capitalize())
fileopobj.filewriterAppend(
full_name,
"\nTable Name: " + i['Table_name'].capitalize() + "\n")
tables_headers = ["Relationship"]
val = i['Table_columns'][0]
#print(tabulate(pd.DataFrame(val, columns=tables_headers),headers='keys', tablefmt='psql'))
fileopobj.filewriterAppend(
full_name,
tabulate(pd.DataFrame(val, columns=tables_headers),
headers='keys',
tablefmt='psql'))
def __init__(self):
self.fileopsobj = FileOps()
def parse_query(self, username, dbname, query, logger, fname=None):
logger.info("Query sent by the user {}, is {}".format(username, query))
start_time = time.time()
query = query.lower()
words = query.split(' ')
check_permissions = self.check_permissions(username)
if words[0] in check_permissions:
if words[0].lower() == 'select':
#select parsing
try:
self.parse_select(username, dbname, query, logger, fname,
start_time)
except:
print(
"Error in your Select query!!! Please check syntax!!")
logger.error(
"Error in your Select query!!! Please check syntax!!")
self.login_status(username, dbname, logger, start_time)
elif words[0].lower() == 'delete':
#delete parsing
try:
self.parse_delete(username, dbname, query, logger, fname,
start_time)
except:
print(
"Error in your Delete query!!! Please check syntax!!")
logger.error(
"Error in your Delete query!!! Please check syntax!!")
self.login_status(username, dbname, logger, start_time)
elif words[0].lower() == 'drop':
#drop table
try:
self.parse_drop(username, dbname, query, logger, fname,
start_time)
except:
print("Error in your drop query!!! Please check syntax!!")
logger.error(
"Error in your drop query!!! Please check syntax!!")
self.login_status(username, dbname, logger, start_time)
elif words[0].lower() == 'create':
crtObj = CreatQuery()
try:
status = crtObj.create_table(username, dbname, query,
logger, fname)
if status:
return
else:
self.login_status(username, dbname, logger, start_time)
except:
print(
"Error in your Create query!!! Please check syntax!!")
logger.error(
"Error in your drop query!!! Please check syntax!!")
self.login_status(username, dbname, logger, start_time)
elif words[0].lower() == 'insert':
insertObj = InsertQuery()
try:
status = insertObj.insert_row(username, dbname, query,
logger, fname)
if status:
return
else:
self.login_status(username, dbname, logger, start_time)
except:
print(
"Error in your Insert query!!! Please check syntax!!")
logger.error(
"Error in your Insert query!!! Please check syntax!!")
self.login_status(username, dbname, logger, start_time)
elif words[0].lower() == 'update':
updateObj = Update()
try:
status = updateObj.update_row(username, dbname, query,
logger, fname)
if status:
return
else:
self.login_status(username, dbname, logger, start_time)
except:
print(
"Error in your update query!!! Please check syntax!!")
logger.error(
"Error in your update query!!! Please check syntax!!")
self.login_status(username, dbname, logger, start_time)
elif words[0].lower() == 'truncate':
truncateObj = Truncate()
try:
status = truncateObj.truncate_table(
username, dbname, query, logger, fname)
if status:
return
else:
self.login_status(username, dbname, logger, start_time)
except:
print(
"Error in your truncate query!!! Please check syntax!!"
)
logger.error(
"Error in your truncate query!!! Please check syntax!!"
)
self.login_status(username, dbname, logger, start_time)
elif words[0].lower() == 'show':
try:
displayObj = Display()
fileopobj = FileOps()
f1 = fileopobj.filereader(dbname + "_Tables.txt")
usertable_dict_obj = json.loads(f1)
status = displayObj.print_tables(usertable_dict_obj)
if status:
return
else:
self.login_status(username, dbname, logger, start_time)
except:
print(
"Error in your query!!! Please check syntax!! Show Tables;"
)
logger.error(
"Error in your query!!! Please check syntax!! Show Tables;"
)
self.login_status(username, dbname, logger, start_time)
elif (words[0].lower() == 'export' and words[1].lower()
== 'data') and (words[2].lower() == 'dictionary'
or words[2].lower() == 'dictionary;'):
try:
displayObj = Display()
fileopobj = FileOps()
f1 = fileopobj.filereader(dbname + "_Tables_Datatypes.txt")
usertable_datatype_dict_obj = json.loads(f1)
status = displayObj.print_datadictionary(
"DataDictionary.txt", usertable_datatype_dict_obj)
print(
"Data Dictionary exported. Check your output folder.")
logger.info(
"Data Dictionary exported. Check your output folder.")
if status:
return
else:
self.login_status(username, dbname, logger, start_time)
except:
print(
"Error in your query!!! Please check syntax!! export data dictionary;"
)
logger.error(
"Error in your query!!! Please check syntax!! export data dictionary;"
)
self.login_status(username, dbname, logger, start_time)
elif (words[0].lower()
== 'export') and (words[1].lower() == 'erd'
or words[1].lower() == 'erd;'):
try:
displayObj = Display()
fileopobj = FileOps()
f1 = fileopobj.filereader(dbname + "_Tables_Datatypes.txt")
usertable_datatype_dict_obj = json.loads(f1)
status = displayObj.print_relationships(
"ERD.txt", usertable_datatype_dict_obj)
print("ERD exported. Check your output folder.")
logger.info("ERD exported. Check your output folder.")
if status:
return
else:
self.login_status(username, dbname, logger, start_time)
except:
print(
"Error in your query!!! Please check syntax!! export erd;"
)
logger.error(
"Error in your query!!! Please check syntax!! export erd;"
)
self.login_status(username, dbname, logger, start_time)
elif (words[0].lower() == 'export' and words[1].lower()
== 'sql') and (words[2].lower() == 'dump'
or words[2].lower() == 'dump;'):
try:
sqldumpObj = Export_SQLDUMP()
status = sqldumpObj.export_sql_dump(dbname, query)
print("SQL Dump exported. Check your output folder.")
logger.info("SQL Dump exported. Check your output folder.")
if status:
return
else:
self.login_status(username, dbname, logger, start_time)
except:
print(
"Error in your query!!! Please check syntax!! export sql dump;"
)
logger.error(
"Error in your query!!! Please check syntax!! export sql dump;"
)
self.login_status(username, dbname, logger, start_time)
else:
print("Invalid query!!! Please check syntax!!")
logger.error("Invalid query!!! Please check syntax!!")
self.login_status(username, dbname, logger, start_time)
else:
print("no permissions granted")
self.login_status(username, dbname, logger, start_time)