def run_via_cursor(self, sql_query):
if self.conn:
try:
cursor = self.conn.cursor()
cursor.execute(sql_query)
for row in cursor:
Verbose.print_ln(row)
return cursor
except Exception as err:
Verbose.print_ln(
"Was not able to execute the query via the cursor")
Verbose.print_ln("Error:", str(err))
quit()
else:
Verbose.print_ln("Something wrong happened, not connected!")
def connect(self):
try:
conn = pyodbc.connect(self.connection_string)
Verbose.print_ln('Connected to the database successfully!')
except Exception as err:
conn = None
Verbose.print_ln(
f'\nFailed to connect to the database! \nError: {str(err)}')
Verbose.print_ln(self.connection_string)
quit()
return conn
def create(self, sql_query):
if self.conn:
try:
cursor = self.conn.cursor()
cursor.execute(sql_query)
self.conn.commit()
except Exception as err:
Verbose.print_ln('Was not able to create view', sql_query)
Verbose.print_ln("Error:", str(err))
quit()
else:
Verbose.print_ln("Something wrong happened, not connected!")
def run(self, sql_query):
result = None
if self.conn:
try:
result = pd.read_sql(sql_query, self.conn)
except Exception as err:
Verbose.print_ln("Was not able to run the query", sql_query)
Verbose.print_ln("Error:", str(err))
quit()
else:
Verbose.print_ln("Something wrong happened, not connected!")
return result
def main(self):
pkl_df = None
db_updated = False
first_run = False
db_df = self.db.run('SELECT * FROM [SurveyStructure]')
try:
pkl_df = pd.read_pickle('result.pkl')
db_updated = pkl_df.shape != db_df.shape or not pkl_df.equals(db_df)
except FileNotFoundError:
first_run = pkl_df is None
except Exception as err:
Verbose.print_ln("Something went bad \nError: ", err)
if first_run or db_updated:
try:
db_df.to_pickle('result.pkl')
except Exception as err:
Verbose.print_ln("Was not able to create new pkl file, Error:", err)
self.create_or_update_view()
Verbose.print_ln("update the view and the pkl")
else:
Verbose.print_ln('No changes, database table status remained same.')
def create_or_update_view(self):
procedure = Procedure(self.db)
final_query = ' CREATE OR ALTER VIEW vw_AllSurveyData AS ' + procedure.get_all_survey_data()
self.db.create(final_query)
Verbose.print_ln("View updated.")
def __del__(self):
Verbose.print_ln('Script executed.')
except Exception as err:
Verbose.print_ln("Something went bad \nError: ", err)
if first_run or db_updated:
try:
db_df.to_pickle('result.pkl')
except Exception as err:
Verbose.print_ln("Was not able to create new pkl file, Error:", err)
self.create_or_update_view()
Verbose.print_ln("update the view and the pkl")
else:
Verbose.print_ln('No changes, database table status remained same.')
""" Documentation: create_or_update_view
Description:
a method used to create new view "vw_AllSurveyDataPython" and store all survey data we got from procedure object.
"""
def create_or_update_view(self):
procedure = Procedure(self.db)
final_query = ' CREATE OR ALTER VIEW vw_AllSurveyData AS ' + procedure.get_all_survey_data()
self.db.create(final_query)
Verbose.print_ln("View updated.")
if __name__ == '__main__':
args = Argument.get()
Verbose.show_messages(args.verbose)
starter = Starter()
starter.main()