import argparse import datetime import os import pymysql import sys from sql.select_tables_by_terms import SELECT_TABLES from sql.drop_table_if_exists import DROP_TABLES_IF_EXISTS try: db_config = { 'host': os.environ.get('AR_MYSQL_HOST', 'reportsar.theorchard.com'), 'user': os.environ.get('AR_MYSQL_USER'), 'password': os.environ.get('AR_MYSQL_PASSWORD'), 'database': os.environ.get('AR_MYSQL_DATABASE', 'art_relations'), 'port': int(os.environ.get('AR_MYSQL_PORT', 3306)), 'charset': os.environ.get('AR_MYSQL_CHARSET', 'utf8mb4'), } except Exception: print("\n\nThere is an error in the environment: ", sys.exc_info()[1]) print( "Please make sure your environment variables are configured properly.") print(""" Required variables: AR_MYSQL_USER AR_MYSQL_PASSWORD AR_MYSQL_HOST AR_MYSQL_DATABASE AR_MYSQL_PORT AR_MYSQL_CHARSET\n\n Optional: LIQUIERASE_TABLE_PREFIX """) sys.exit() env_table_prefix = os.environ.get('LIQUIERASE_TABLE_PREFIX', 'ROLLBACK-MR') def _show_variables_to_user(schema='', table_prefix='', user_name='', date_since=''): """Formats a view of variables assigned for the user.""" print('\nSchema: {}'.format(schema)) print('Table Prefix: {}'.format(table_prefix)) print('User Name: {}'.format(user_name)) print('Created Before: {}'.format(date_since)) if __name__ == '__main__': # Setup commandline args parser = argparse.ArgumentParser( description='Create Liquibase DROP TABLE PR\'s for multple tables', add_help=False) parser.add_argument( '-?', '--help', action='help', help='Show this help message and exit') parser.add_argument( '-f', '--filename', help='The file to be imported.', type=str) parser.add_argument( '-t', '--table_prefix', help='The shared prefix found on all tables to be dropped. ' '\nExample: ROLLBACK-MR, REPLACED-DIG, etc.', type=str) parser.add_argument( '-s', '--schema', help='The schema to access on the DB.', type=str) parser.add_argument( '-h', '--host', help='The host DB with which to connect.', type=str) parser.add_argument( '-u', '--user', help='The username with which to connect to the host DB', type=str) parser.add_argument( '-p', '--password', help='The password which corresponds to the passed username.', type=str) parser.add_argument( '-r', '--port', help='The port for connection to the host DB.', type=str) parser.add_argument( '-d', '--database', help='The database which contains the tables to be dropped.', type=str) parser.add_argument( '-k', '--ticket', help='The Jira code of the ticket under which this drop operation is ' 'defined. This will be prepended to the front of all output ' 'liquibase files.', type=str) # Parse commandline args args = parser.parse_args() # Open database connection db = pymysql.connect(cursorclass=pymysql.cursors.DictCursor, **db_config) # Prepare a cursor object using cursor() method cursor = db.cursor() # Request user input, or read from input config file progress_report = [] if args.schema: progress_report.append('Schema `{}` being used.'.format(args.schema)) schema = args.schema else: _show_variables_to_user() schema = input( 'Which schema are you trying to drop tables from? [{}]: '.format( db_config['database'])) or db_config['database'] if args.table_prefix: progress_report.append( 'Table Prefix `{}` being used'.format(args.table_prefix)) table_prefix = args.table_prefix else: _show_variables_to_user(schema=schema) table_prefix = input( 'What is the prefix of the tables you would like to drop? [{}]: ' .format(env_table_prefix)) or env_table_prefix print() for p in progress_report: print(p) _show_variables_to_user(schema=schema, table_prefix=table_prefix) today_date = datetime.datetime.now().strftime("%Y-%m-%d") date_since = input('Would you like to ONLY drop tables created before a ' 'certain date? [{}]: '.format(today_date)) or today_date _show_variables_to_user( schema=schema, table_prefix=table_prefix, date_since=date_since) ticket = args.ticket or None while not ticket: ticket = input( 'What is the Jira code of the ticket under which this drop ' 'operation is defined?: ') if not ticket: print( '\nA Jira ticket code is required to create Liquibase PR\'s.') user_name = None while not user_name: user_name = input('\nWhat is your user name? (ex: brad): ') if not user_name: print('\nUser name is required to create Liquibase PR\'s.') clear_screen = os.system('clear') _show_variables_to_user( schema=schema, table_prefix=table_prefix, user_name=user_name, date_since=date_since) print('\nSelecting all matching tables....') # Query information_schema for tables which fit the pattern within the date cursor.execute(SELECT_TABLES.format( schema='art_relations', table_prefix=table_prefix, date_since=date_since)) # Report counts of tables and any findings. Either quit, or continue data = cursor.fetchall() tables_returned_count = len(data) print('{} results returned'.format(tables_returned_count)) if not tables_returned_count: sys.exit('No tables matching the parameters were found. Exiting.') output_filename_template = '{ticket}-DROP-{table_prefix}.sql' meta_output_filename = \ 'Summary_{ticket}-{table_prefix}-table-date-list.csv'.format( ticket=ticket, table_prefix=table_prefix) # Open sidecar summary file for writing with open(meta_output_filename, 'w') as mf: output_filename = output_filename_template.format( ticket=ticket, table_prefix=table_prefix) # Open and write to new liquibase file with open(output_filename, 'w') as f: f.write('--liquibase formatted SQL\n\n') f.write('--changeset {user_name}:1\n\n'.format( user_name=user_name)) # Begin writing out PR liquibase files. for d in data: # Append to summary file mf.write('{},{}\n'.format(d['table_name'], d['create_time'])) f.write( DROP_TABLES_IF_EXISTS.format(table_name=d['table_name'])) f.write('\n') f.write('\n--rollback SELECT "no rollback"\n') f.close() mf.close() print('File `{}` created'.format(output_filename)) # Disconnect from server db.close() # Terminate with report of created files. print('\nOperation completed. Liquibase file created with {} directives.' .format(tables_returned_count)) print('\nView file `{}` for table list and creation dates.'.format( meta_output_filename))